Unplug a 12.2.0.1 PDB and plugin into 18c in the Cloud

There’s been silence for a while – but today I’d like to show you how to unplug a 12.2.0.1 PDB and plugin into 18c in the Cloud. I’m doing this exercise because I’ve seen questions already. And I was curious if it works as I’d expect it.

Unplug a 12.2.0.1 PDB and plugin into 18c in the Cloud

As usual I will use our hands-on lab environment – and a freshly deployed 18c OCI-Classic instance (formerly known as DBCS formerly known as DBaaS).

It is a Single Tenant environment with one PDB. If you need for information about how to setup a cloud instance, ssh keys and such, please have a look at Tim Hall’s (as usual) excellent step-by-step instructions.

In my local hands-on lab environment I create a fresh empty PDB12201 with Oracle 12.2.0.1, add a user, create a table and change some parameters.

create pluggable database PDB12201 
  admin user adm identified by adm
  file_name_convert=('/u02/oradata/CDB2/pdbseed', '/u02/oradata/CDB2/pdb12201');

alter pluggable database PDB12201 open;

Preparation and Unplug

When I unplug a 12.2.0.1 PDB and plug it into a 18c CDB it will need an upgrade. Therefore I download the most recent preupgrade.jar from MOS Note 884522.1 and copy it into my /home/oracle. From there I execute it against my PDB12201. It is important that PDB12201 is open.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDB12201			  READ WRITE NO

The downloaded preupgrade.zip contains a few files:

$ unzip preupgrade_181_cbuild_02_lf.zip 
Archive:  preupgrade_181_cbuild_02_lf.zip
  inflating: preupgrade_driver.sql   
  inflating: preupgrade.jar          
  inflating: preupgrade_messages.properties  
  inflating: preupgrade_package.sql

And I execute the preupgrade.jar:

$ java -jar ./preupgrade.jar -c 'PDB12201' TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
18.0.0.0.0 on 2018-04-05T14:48:41

You’ll see that the output has been improved giving now more clear and detailed instructions. In my case I have to execute the preupgrade_fixups.sql only.

ORACLE GENERATED FIXUP SCRIPT
  =============================
  All of the issues in database CDB2 container PDB12201
  which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
  executing the following from within the container

    SQL> @/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql

Now I can shutdown my PDB12201 and unplug it.

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter pluggable database PDB12201 close;
Pluggable database altered.

SQL> alter pluggable database PDB12201 unplug into '/home/oracle/PDB12201.pdb';
Pluggable database altered.

The unplug into a “pdb” file creates a zip file containing everything required for the move. You can unzip it if you want – and which I’ll do as I need the xml file for the compatibility check.

$ unzip PDB12201.pdb 
Archive:  PDB12201.pdb
  inflating: system01.dbf            
  inflating: sysaux01.dbf            
  inflating: undotbs01.dbf           
warning:  stripped absolute path spec from /home/oracle/PDB12201.xml
  inflating: home/oracle/PDB12201.xml

Copy to the Cloud and Plugin

The next step for me is to copy the pdb and the xml files to my cloud instance:

$ scp -i ./myOracleCloudKey PDB12201.pdb oracle@123.123.123.123:/home/oracle
$ scp -i ./myOracleCloudKey PDB12201.xml oracle@123.123.123.123:/home/oracle

In addition I will copy also the postupgrade_fixups_PDB12201.sql file as I will need it after the upgrade of PDB12201 is completed:

$ cp /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups_PDB12201.sql /home/oracle
$ scp -i ./myOracleCloudKey postupgrade_fixups_PDB12201.sql oracle@123.123.123.123:/home/oracle

Furthermore at the moment you’ll have to copy dbms_registry_extended.sql as well and exchange it with the one in your destination ?/rdbms/admin as otherwise you’ll see unexpected errors during the run of postupgrade_fixups.sql. This happens due to the preupgrade.jar I used being newer than the library in the destination home.

$ cp /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/dbms_registry_extended.sql /home/oracle
$ scp -i ./myOracleCloudKey dbms_registry_extended.sql oracle@123.123.123.123:/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin

Then I’ll do the compatibility check and examine the view with the wonderful name PDB_PLUG_IN_VIOLATIONS.

SET SERVEROUTPUT ON

DECLARE
  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  pdb_descr_file => '/home/oracle/PDB12201.xml',
  pdb_name => 'PDB12201')
  WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

Well, it tells me “NO“, not compatible. Hence I check:

column message format a50
column status format a9
column type format a9
column con_id format 9

select  con_id, type, message, status from PDB_PLUG_IN_VIOLATIONS order by time;

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     4 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     4 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     1 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     1 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     1 ERROR	 PDB's version does not match CDB's version: PDB's  PENDING
		 version 12.2.0.1.0. CDB's version 18.0.0.0.0.

     1 WARNING	 CDB parameter compatible mismatch: Previous '12.2. PENDING
		 0' Current '18.0.0'

     1 WARNING	 CDB parameter pga_aggregate_target mismatch: Previ PENDING
		 ous 120M Current 1837647360

     1 WARNING	 CDB parameter _exclude_seed_cdb_view mismatch: Pre PENDING
		 vious FALSE Current TRUE

     1 ERROR	 DBRU bundle patch 180116 (DATABASE RELEASE UPDATE  PENDING
		 12.2.0.1.180116): Not installed in the CDB but ins
		 talled in the PDB

You see that a lot of the WARNINGs are not useful (see Bug 17262059 - PDB_PLUG_IN_VIOLATIONS DISPLAYS USELESS INFORMATION AND DOES NOT GET PURGED). And the two ERRORs refer to CON_ID 1 which is a bit misleading as the error does not actually belong to the CDB$ROOT. But as the new PDB is not plugged in yet a different value would be more obvious.

I believe I can safely ignore the “DBRU Bundle Patch” ERROR as I will upgrade my PDB now. The ERROR saying: “PDB's version does not match CDB's version: PDB's PENDING version 12.2.0.1.0. CDB's version 18.0.0.0.0.” is the one I have to deal with after plugin.

create pluggable database PDB12201
  using '/home/oracle/PDB12201.pdb'
  file_name_convert=('/home/oracle', '/u02/app/oracle/oradata/MIKECDB/pdb12201');
Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDB1 			  READ WRITE NO
	 6 PDB12201			  MOUNTED

For the plugin it is important to mention that your source file name is NOT the original path but the path where my PDB12201.pdb archive is located in currently. And please don’t ask me why it got a CON_ID=6 – I have really no idea.

Upgrading the plugged PDB

First of all I need to open the PDB12201:

SQL> alter pluggable database PDB12201 open upgrade;
Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDB1 			  READ WRITE NO
	 6 PDB12201			  MIGRATE    YES

Afterwards I start the upgrade of this container-only:

[oracle@MIKE18C ~]$ cd /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/

[oracle@MIKE18C admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -c "PDB12201" -l /home/oracle catupgrd.sql

Number of Cpus        = 2
Database Name         = MIKECDB
DataBase Version      = 18.0.0.0.0
Generated PDB Inclusion:[PDB12201]
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB12201]
    Installed [CATALOG CATPROC OWM XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM RAC SDO WK XML XOQ]

------------------------------------------------------
Phases [0-108]         Start Time:[2018_04_05 14:56:09]
Container Lists Inclusion:[PDB12201] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
*********** Executing Change Scripts ***********
Serial Phase #:0 [PDB12201] Files:1 Time: 22s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PDB12201] Files:5 Time: 39s
Restart Phase #:2 [PDB12201] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PDB12201] Files:19 Time: 18s
Restart Phase #:4 [PDB12201] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [PDB12201] Files:7 Time: 17s
***************** Catproc Start ****************
Serial Phase #:6 [PDB12201] Files:1 Time: 10s
***************** Catproc Types ****************
...
------------------------------------------------------
Phases [0-108]         End Time:[2018_04_05 15:10:34]
Container Lists Inclusion:[PDB12201] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 867s [PDB12201]

 LOG FILES: (/home/oracle/catupgrdpdb12201*.log)

Upgrade Summary Report Located in:
/home/oracle/upg_summary.log

Total Upgrade Time:          [0d:0h:14m:27s]

Afterwards I execute the postupgrade_fixups_PDB12201.sql and recompile:

SQL> alter session set container=PDB12201;

SQL> startup

SQL> @/home/oracle/postupgrade_fixups_PDB12201.sql


Auto-Generated by:       Oracle Preupgrade Script
                         Version: 18.0.0.0.0 Build: 1
Generated on:            2018-04-05 14:48:41

For Source Database:     CDB2
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  18.0.0.0.0

Executing in container:  PDB12201

Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      YES         None.
    7.  post_dictionary           YES         None.
    8.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.

Recompilation can be done either with catcon.pl or in this case directly within SQL*Plus:

@?/rdbms/admin/utlrp.sql

My PDB is now upgraded to Oracle 18.1.0.

Final Check

As final check I examine PDB_PLUG_IN_VIOLATIONS again:

select con_id, type, message, status
  from PDB_PLUG_IN_VIOLATIONS
 where status<>'RESOLVED'
 order by time;

CON_ID TYPE	 MESSAGE					    STATUS
------ --------- -------------------------------------------------- ---------
     4 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     4 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     6 WARNING	 Database option RAC mismatch: PDB installed versio PENDING
		 n 18.0.0.0.0. CDB installed version NULL.

     6 WARNING	 Database option APS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option CATJAVA mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option CONTEXT mismatch: PDB installed ve PENDING
		 rsion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option DV mismatch: PDB installed version PENDING
		  NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option JAVAVM mismatch: PDB installed ver PENDING
		 sion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option OLS mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option ORDIM mismatch: PDB installed vers PENDING
		 ion NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option SDO mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option XML mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Database option XOQ mismatch: PDB installed versio PENDING
		 n NULL. CDB installed version 18.0.0.0.0.

     6 WARNING	 Tablespace SYSTEM is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.

     6 WARNING	 Tablespace SYSAUX is not encrypted. Oracle Cloud m PENDING
		 andates all tablespaces should be encrypted.


15 rows selected.

Well, I thought this would be “final”.

First of all, there are just WARNINGs – no ERRORs. This is good. I ignore the two WARNINGs for CON_ID=4 as this is the standard PDB. I won’t need it anyways and may drop it later on.

11 warnings say: “Database option <xyz> mismatch: PDB installed versio PENDING n NULL. CDB installed version 18.0.0.0.0.“. Does this mean anything to me? No, it doesn’t. I still don’t understand why this should be a warning. My PDB has less options in REGISTRY$. That’s not an issue. Therefore, such WARNINGs shouldn’t be flagged.

I moved my pluggable database from on premises to the cloud. The most time got spent on the copy operation from my laptop to the cloud instance in the US. And the recompilation took fairly long. It seemed to hang until I hit several times CTRL-C. But then it apparently progressed.

–Mike