Close

Unplug PDB from 12.2 CDB and Plug into 18c CDB

Last week the 18c Linux on-premises software was released for download. So this weekend I installed it on my test server and gave it a whirl.

The software installs themselves went smoothly and then it was time to do an upgrade. Below I’ve capture the steps and output of an upgrade by unplug/plug from older release to newer release.

The Setup:

12cR2 CDB:  sdscdb2
12cR2 PDB: sdspdb2
18c CDB: cdb181

Pre-upgrade steps

Use the preupgrade.jar to check current db status and create pre- and post- upgrade fix scripts.
Run the preupgrade_fixups.sql script

[oracle@sdsserver ~]$ . oraenv
ORACLE_SID = [] ? sdscdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@sdsserver ~]$ /u01/app/oracle/product/12.2.0/db_home1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/preupgrade.jar dir /tmp -c sdspdb2
==================
PREUPGRADE SUMMARY
==================
  /tmp/preupgrade.log
  /tmp/preupgrade_fixups.sql
  /tmp/postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b preup_sdscdb2 /tmp/preupgrade_fixups.sql

2. Review logs under /tmp/

After the upgrade:

1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b postup_sdscdb2 /tmp/postupgrade_fixups.sql

2. Review logs under /tmp/

Preupgrade complete: 2018-07-28T23:01:31
[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b preup_sdscdb2 /tmp/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/tmp//preup_sdscdb2_catcon_30175.lst]
catcon: See [/tmp//preup_sdscdb2*.log] files for output generated by scripts
catcon: See [/tmp//preup_sdscdb2_*.lst] files for spool files, if any
catcon.pl: completed successfully

Unplug from old release

Shutdown the pdb.
Unplug the pdb, creating an xml descriptor file.
Drop the pdb from the old container.

[oracle@sdsserver ~]$ sqlplus sys@sdscdb2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 28 23:26:59 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database sdspdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database sdspdb2 unplug into '/tmp/sdspdb2.xml';

Pluggable database altered.

SQL> drop pluggable database sdspdb2 keep datafiles;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Plug into new release and open for upgrade

Use the xml descriptor to plug into new 18c cdb.
I used “nocopy” option, no need to move files around or copy them, so much faster.
Then open the pdb in upgrade mode

[oracle@sdsserver ~]$ . oraenv
ORACLE_SID = [sdscdb2] ? cdb181
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@sdsserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jul 28 23:29:57 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create pluggable database sdspdb2 using '/tmp/sdspdb2.xml' nocopy;

Pluggable database created.

SQL> alter session set container=sdspdb2;

Session altered.

SQL> alter pluggable database open upgrade;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Upgrade pdb

Run catupgrd.sql script to complete the transition.

[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c 'sdspdb2' -l $ORACLE_BASE catupgrd.sql

Argument list for [/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = sdspdb2
Do not run in         C = 0
Input Directory       d = /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /u01/app/oracle
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [18.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]


/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/dbhome_1]
/u01/app/oracle/product/18.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app/oracle]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/catupgrd_catcon_12454.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = cdb18_1
DataBase Version      = 18.0.0.0.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 8
Concurrent PDB Upgrades               = 4
Generated PDB Inclusion:[SDSPDB2]
CDB$ROOT  Open Mode = [OPEN]

Start processing of PDBs (SDSPDB2)
[/u01/app/oracle/product/18.0.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl -d /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -c 'SDSPDB2' -l /u01/app/oracle -I -i sdspdb2 -n 2 catupgrd.sql]

Argument list for [/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = SDSPDB2
Do not run in         C = 0
Input Directory       d = /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = sdspdb2
Child Process         I = 1
Log Dir               l = /u01/app/oracle
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [18.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]


/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/dbhome_1]
/u01/app/oracle/product/18.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app/oracle]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/catupgrdsdspdb2_catcon_12742.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrdsdspdb2*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrdsdspdb2_*.lst] files for spool files, if any


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

------------------------------------------------------
Phases [0-108]         Start Time:[2018_07_29 00:25:33]
Container Lists Inclusion:[SDSPDB2] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [SDSPDB2] Files:1    Time: 271s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [SDSPDB2] Files:5    Time: 1197s
Restart  Phase #:2    [SDSPDB2] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [SDSPDB2] Files:19   Time: 484s
Restart  Phase #:4    [SDSPDB2] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [SDSPDB2] Files:7    Time: 509s
*****************   Catproc Start   ****************
Serial   Phase #:6    [SDSPDB2] Files:1    Time: 188s
*****************   Catproc Types   ****************
Serial   Phase #:7    [SDSPDB2] Files:2    Time: 198s
Restart  Phase #:8    [SDSPDB2] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [SDSPDB2] Files:66   Time: 507s
Restart  Phase #:10   [SDSPDB2] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [SDSPDB2] Files:1    Time: 1110s
Restart  Phase #:12   [SDSPDB2] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [SDSPDB2] Files:94   Time: 79s
Restart  Phase #:14   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:15   [SDSPDB2] Files:117  Time: 128s
Restart  Phase #:16   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:17   [SDSPDB2] Files:17   Time: 32s
Restart  Phase #:18   [SDSPDB2] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [SDSPDB2] Files:32   Time: 267s
Restart  Phase #:20   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:21   [SDSPDB2] Files:3    Time: 87s
Restart  Phase #:22   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:23   [SDSPDB2] Files:24   Time: 604s
Restart  Phase #:24   [SDSPDB2] Files:1    Time: 1s
Parallel Phase #:25   [SDSPDB2] Files:12   Time: 314s
Restart  Phase #:26   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:27   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:28   [SDSPDB2] Files:3    Time: 39s
Serial   Phase #:29   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:30   [SDSPDB2] Files:1    Time: 1s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [SDSPDB2] Files:1    Time: 2s
Restart  Phase #:32   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:34   [SDSPDB2] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [SDSPDB2] Files:288  Time: 224s
Serial   Phase #:36   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:37   [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:38   [SDSPDB2] Files:2    Time: 18s
Restart  Phase #:39   [SDSPDB2] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [SDSPDB2] Files:3    Time: 146s
Restart  Phase #:41   [SDSPDB2] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [SDSPDB2] Files:13   Time: 274s
Restart  Phase #:43   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:44   [SDSPDB2] Files:11   Time: 55s
Restart  Phase #:45   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:46   [SDSPDB2] Files:3    Time: 4s
Restart  Phase #:47   [SDSPDB2] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [SDSPDB2] Files:1    Time: 125s
Restart  Phase #:49   [SDSPDB2] Files:1    Time: 1s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [SDSPDB2] Files:1    Time: 8s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:52   [SDSPDB2] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [SDSPDB2] Files:2    Time: 1636s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:56   [SDSPDB2] Files:3    Time: 10s
Serial   Phase #:57   [SDSPDB2] Files:3    Time: 24s
Parallel Phase #:58   [SDSPDB2] Files:9    Time: 11s
Parallel Phase #:59   [SDSPDB2] Files:25   Time: 34s
Serial   Phase #:60   [SDSPDB2] Files:4    Time: 85s
Serial   Phase #:61   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:62   [SDSPDB2] Files:31   Time: 33s
Serial   Phase #:63   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:64   [SDSPDB2] Files:6    Time: 11s
Serial   Phase #:65   [SDSPDB2] Files:2    Time: 21s
Serial   Phase #:66   [SDSPDB2] Files:3    Time: 73s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:69   [SDSPDB2] Files:1    Time: 1s
Parallel Phase #:70   [SDSPDB2] Files:2    Time: 84s
Serial   Phase #:71   [SDSPDB2] Files:1    Time: 76s
Restart  Phase #:72   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:73   [SDSPDB2] Files:2    Time: 14s
Serial   Phase #:74   [SDSPDB2] Files:2    Time: 1s
*****************   Upgrading SDO   ****************
Restart  Phase #:75   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:77   [SDSPDB2] Files:1    Time: 76s
Serial   Phase #:78   [SDSPDB2] Files:1    Time: 47s
Restart  Phase #:79   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:80   [SDSPDB2] Files:1    Time: 52s
Restart  Phase #:81   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:82   [SDSPDB2] Files:3    Time: 239s
Restart  Phase #:83   [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:84   [SDSPDB2] Files:1    Time: 14s
Restart  Phase #:85   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:86   [SDSPDB2] Files:1    Time: 32s
Restart  Phase #:87   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:88   [SDSPDB2] Files:4    Time: 174s
Restart  Phase #:89   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:90   [SDSPDB2] Files:1    Time: 1s
Restart  Phase #:91   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:92   [SDSPDB2] Files:2    Time: 11s
Restart  Phase #:93   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:94   [SDSPDB2] Files:1    Time: 1s
Restart  Phase #:95   [SDSPDB2] Files:1    Time: 0s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:96   [SDSPDB2] Files:1    Time: 86s
Restart  Phase #:97   [SDSPDB2] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:98   [SDSPDB2] Files:1    Time: 5s
*************   Final Upgrade scripts   ************
Serial   Phase #:99   [SDSPDB2] Files:1    Time: 779s
*******************   Migration   ******************
Serial   Phase #:100  [SDSPDB2] Files:1    Time: 1s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:101  [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:102  [SDSPDB2] Files:1    Time: 9s
Serial   Phase #:103  [SDSPDB2] Files:1    Time: 59s
*****************   Post Upgrade   *****************
Serial   Phase #:104  [SDSPDB2] Files:1    Time: 13s
****************   Summary report   ****************
Serial   Phase #:105  [SDSPDB2] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:106  [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:107  [SDSPDB2] Files:1    Time: 7s
Serial   Phase #:108  [SDSPDB2] Files:1     Time: 0s

------------------------------------------------------
Phases [0-108]         End Time:[2018_07_29 03:22:15]
Container Lists Inclusion:[SDSPDB2] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 10603s [SDSPDB2]

 LOG FILES: (/u01/app/oracle/catupgrdsdspdb2*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/upg_summary.log

Total Upgrade Time:          [0d:2h:56m:43s]

     Time: 10608s For PDB(s)

Grand Total Time: 10608s

 LOG FILES: (/u01/app/oracle/catupgrd*.log)


Grand Total Upgrade Time:    [0d:2h:56m:48s]

Start upgraded pdb

Upgrade is complete, start the pdb

[oracle@sdsserver ~]$ sqlplus sys@sdspdb2 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jul 29 10:29:45 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> startup
Pluggable Database opened.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Run Post-upgrade script

Run the postupgrade_fixups.sql script generated in the first step.
Run utlrp to recompile invalid objects.

[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -c 'sdspdb2' -n 1 -e -b postfixups -d '''.''' /tmp/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/postfixups_catcon_2676.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/postfixups*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/postfixups_*.lst] files for spool files, if any

catcon.pl: completed successfully
[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -c 'sdspdb2' -n 1 -e -b comp -d '''.''' /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/comp_catcon_3476.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/comp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/comp_*.lst] files for spool files, if any

catcon.pl: completed successfully

And that’s it. The upgrade duration was longer than I would have hoped for but the manual steps were minimal and easy.

For Oracle documentation on these steps:
https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/manual-upgrade-scenarios-multitenant-architecture-oracle-databases.html#GUID-8F9AAFA1-690D-4F70-8448-E66D765AF136

Leave a Reply