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 WARNING
s are not useful (see Bug 17262059 - PDB_PLUG_IN_VIOLATIONS DISPLAYS USELESS INFORMATION AND DOES NOT GET PURGED
). And the two ERROR
s 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 WARNING
s – no ERROR
s. This is good. I ignore the two WARNING
s 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 WARNING
s 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
Thanks Mike. Information – copy dbms_registry_extended.sql to the target database destination ?/rdbms/admin to avoid errors during the run of postupgrade_fixups.sql resolved my issue.
@postupgrade_fixups.sql
—
Enter value for c_apex_version_4_dots:
Enter value for c_upgradable_versions:
Enter value for c_minimum_compatible:
Enter value for c_upgradable_versions:
Enter value for c_upgradable_versions:
Enter value for c_minimum_compatible:
Enter value for c_minimum_compatible:
Enter value for c_minimum_compatible:
Enter value for c_oracle_high_version_4_dots:
Enter value for c_ltz_content_ver:
Enter value for c_ltz_content_ver:
Enter value for c_ltz_content_ver:
Enter value for c_ltz_content_ver:
Enter value for c_upgradable_versions:
Enter value for c_default_processes:
Enter value for c_oracle_high_version_4_dots:
Warning: Package Body created with compilation errors.
—
Thanks š
And sorry for the inconvenience!
Mike
Hi,
did you try it on oci too?
i getting the following error if i try it oci.
This error occours even if the target db is 18c or 12.2.
An SR is openend since 25th of may but support no solution so far.
SQL> create pluggable database KUF2 using ‘/home/oracle/import/KUF2.pdb’
2 file_name_convert=(‘/home/oracle/import’, ‘+DATA/DBHP122_fra35g/KUF2’);
create pluggable database KUF2 using ‘/home/oracle/import/KUF2.pdb’
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
/home/oracle/import/system01.dbf
ORA-19502: write error on file “+DATA/DBHP122_fra35g/KUF2/system01.dbf”, block
number 26881 (block size=8192)
ORA-17510: An attempt to do I/O of size to block is beyond file size .
Logical block size: .
Linux-x86_64 Error: 8192: Unknown system error
Additional information: 26881
Additional information: 26880
Additional information: 8192
Thanks
Kai
Kai,
I have no OCI account. If you borrow me yours I’ll debug it š
Cheers,
Mike
Hi Mike
I followed this document to upgrade the pdb with TDE enabled from 122 to 18c, Test DB was successful but when i did one of the non-prod PDBs its throwing the below errors when I try to open the PDB in upgrade mode.
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL
Below are the steps I followed
1) export the key 2)unplug the pdb 3) plug the PDB 4) open PDB in upgrade mode.
Appreciate your help on this.
Thank you
Did you import the key?
Thanks,
Mike