Upgrade Testing – Online Clone a PDB to 19c

In the past I wrote some articles about cloning PDBs to new environments. And I added a post about RELOCATE as well. But I realized that I have no specific blog post about a very typical scenario: Upgrade Testing – Online Clone a PDB to 19c.

Upgrade Testing - Online Clone a PDB to Oracle 19c

Photo by Jørgen Håland on Unsplash

My Setup

I have an Oracle 12.2.0.1 CDB with a PDB, and another Oracle 19.6.0 CDB. I will hot clone the PDB from 12.2.0.1 to the higher version CDB, and then upgrade the PDB.

Upgrade Testing - Online Clone a PDB to Oracle 19c

The motivation to do it this way is to allow testing for the application teams within my test environment being already on 19.6.0. As soon as AutoUpgrade supports this scenarios as well, you can automate this every night.

Hot Cloning

You can use Hot Cloning since the introduction of Local Undo with Oracle 12.2.0.1. Hence, if my source is at least Oracle 12.2.0.1 and if I use the default, Local Undo, this works. As usual, I show the sequence of actions:

CDB1 CDB2
Create a common user for the relocate operation and assign necessary privileges:

CREATE USER c##borg IDENTIFIED BY oracle DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO c##borg CONTAINER = ALL;
Create the public database link for the relocate operation:

CREATE public DATABASE link clonemypdb CONNECT TO c##borg IDENTIFIED BY oracle USING 'CDB1';
Initiate the relocation:

CREATE PLUGGABLE DATABASE PDB1 FROM PDB1@clonemypdb file_name_convert=('CDB1','CDB2');
Open the PDB:

ALTER PLUGGABLE DATABASE PDB1 OPEN UPGRADE;

The PDB will open with errors in RESTRICTED mode only.

Then I need to open PDB1 in CDB2 in UPGRADE mode because its dictionary is still an Oracle 12.2.0.1 dictionary whereas it operates now within a 19c database.

I need to upgrade it.

Upgrade of the PDB

This step is simple as I just avoid the preupgrade step. In my environment there are no extra tasks necessary for PDB1 before upgrading it.

Invoke the upgrade in the CDB2 environment:

$ dbupgrade -c "PDB1" -l /home/oracle/logs

Argument list for [/u01/app/oracle/product/19/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = PDB1
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = /home/oracle/logs
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: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217]


/u01/app/oracle/product/19/rdbms/admin/orahome = [/u01/app/oracle/product/19]
/u01/app/oracle/product/19/bin/orabasehome = [/u01/app/oracle/product/19]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19]

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

Log file directory = [/home/oracle/logs]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/catupgrd_catcon_17267.lst]

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

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


Number of Cpus        = 2
Database Name         = CDB2
DataBase Version      = 19.0.0.0.0
PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [2]
Concurrent PDB Upgrades defaulting to CPU Count [2]
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades               = 2
Generated PDB Inclusion:[PDB3]
CDB$ROOT  Open Mode = [OPEN]
Concurrent PDB Upgrades Reset           = 1

Start processing of PDBs (PDB1)
[/u01/app/oracle/product/19/perl/bin/perl /u01/app/oracle/product/19/rdbms/admin/catctl.pl -c 'PDB3' -l /home/oracle/logs -I -i pdb3 -n 2 /u01/app/oracle/product/19/rdbms/admin/catupgrd.sql]

Argument list for [/u01/app/oracle/product/19/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = PDB1
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = pdb1
Child Process                I = 1
Log Dir                      l = /home/oracle/logs
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: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.6.0.0.0DBRU_LINUX.X64_191217]


/u01/app/oracle/product/19/rdbms/admin/orahome = [/u01/app/oracle/product/19]
/u01/app/oracle/product/19/bin/orabasehome = [/u01/app/oracle/product/19]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19]

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

Log file directory = [/home/oracle/logs]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/catupgrdpdb3_catcon_17545.lst]

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

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


Number of Cpus        = 2
Database Name         = CDB2
DataBase Version      = 19.0.0.0.0
PDB3 Open Mode = [MIGRATE] 
Generated PDB Inclusion:[PDB1]
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB3]
    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-107]         Start Time:[2020_03_06 14:32:27]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [PDB1] Files:1    Time: 23s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [PDB1] Files:5    Time: 50s
Restart  Phase #:2    [PDB1] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [PDB1] Files:19   Time: 27s
Restart  Phase #:4    [PDB1] Files:1    Time: 2s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [PDB1] Files:7    Time: 22s
*****************   Catproc Start   ****************
Serial   Phase #:6    [PDB1] Files:1    Time: 18s
*****************   Catproc Types   ****************
Serial   Phase #:7    [PDB1] Files:2    Time: 13s
Restart  Phase #:8    [PDB1] Files:1    Time: 2s
****************   Catproc Tables   ****************
Parallel Phase #:9    [PDB1] Files:67   Time: 37s
Restart  Phase #:10   [PDB1] Files:1    Time: 1s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [PDB1] Files:1    Time: 64s
Restart  Phase #:12   [PDB1] Files:1    Time: 2s
**************   Catproc Procedures   **************
Parallel Phase #:13   [PDB1] Files:94   Time: 15s
Restart  Phase #:14   [PDB1] Files:1    Time: 1s
Parallel Phase #:15   [PDB1] Files:121  Time: 15s
Restart  Phase #:16   [PDB1] Files:1    Time: 2s
Serial   Phase #:17   [PDB1] Files:22   Time: 9s
Restart  Phase #:18   [PDB1] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [PDB1] Files:32   Time: 28s
Restart  Phase #:20   [PDB1] Files:1    Time: 2s
Serial   Phase #:21   [PDB1] Files:3    Time: 18s
Restart  Phase #:22   [PDB1] Files:1    Time: 2s
Parallel Phase #:23   [PDB1] Files:25   Time: 227s
Restart  Phase #:24   [PDB1] Files:1    Time: 2s
Parallel Phase #:25   [PDB1] Files:12   Time: 131s
Restart  Phase #:26   [PDB1] Files:1    Time: 1s
Serial   Phase #:27   [PDB1] Files:1    Time: 0s
Serial   Phase #:28   [PDB1] Files:3    Time: 8s
Serial   Phase #:29   [PDB1] Files:1    Time: 0s
Restart  Phase #:30   [PDB1] Files:1    Time: 2s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [PDB1] Files:1    Time: 6s
Restart  Phase #:32   [PDB1] Files:1    Time: 2s
Serial   Phase #:34   [PDB1] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [PDB1] Files:294  Time: 21s
Serial   Phase #:36   [PDB1] Files:1    Time: 0s
Restart  Phase #:37   [PDB1] Files:1    Time: 2s
Serial   Phase #:38   [PDB1] Files:6    Time: 8s
Restart  Phase #:39   [PDB1] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB1] Files:3    Time: 42s
Restart  Phase #:41   [PDB1] Files:1    Time: 2s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB1] Files:13   Time: 116s
Restart  Phase #:43   [PDB1] Files:1    Time: 1s
Parallel Phase #:44   [PDB1] Files:11   Time: 10s
Restart  Phase #:45   [PDB1] Files:1    Time: 1s
Parallel Phase #:46   [PDB1] Files:3    Time: 8s
Restart  Phase #:47   [PDB1] Files:1    Time: 1s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [PDB1] Files:1    Time: 16s
Restart  Phase #:49   [PDB1] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [PDB1] Files:1    Time: 9s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [PDB1] Files:1    Time: 7s
Restart  Phase #:52   [PDB1] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [PDB1] Files:2    Time: 23s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [PDB1] Files:1    Time: 1s
Serial   Phase #:56   [PDB1] Files:3    Time: 12s
Serial   Phase #:57   [PDB1] Files:3    Time: 9s
Parallel Phase #:58   [PDB1] Files:10   Time: 10s
Parallel Phase #:59   [PDB1] Files:25   Time: 12s
Serial   Phase #:60   [PDB1] Files:4    Time: 14s
Serial   Phase #:61   [PDB1] Files:1    Time: 0s
Serial   Phase #:62   [PDB1] Files:32   Time: 10s
Serial   Phase #:63   [PDB1] Files:1    Time: 0s
Parallel Phase #:64   [PDB1] Files:6    Time: 13s
Serial   Phase #:65   [PDB1] Files:2    Time: 21s
Serial   Phase #:66   [PDB1] Files:3    Time: 27s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [PDB1] Files:1    Time: 2s
Serial   Phase #:69   [PDB1] Files:1    Time: 8s
Parallel Phase #:70   [PDB1] Files:2    Time: 9s
Restart  Phase #:71   [PDB1] Files:1    Time: 2s
Parallel Phase #:72   [PDB1] Files:2    Time: 9s
Serial   Phase #:73   [PDB1] Files:2    Time: 8s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [PDB1] Files:1    Time: 1s
Serial   Phase #:76   [PDB1] Files:1    Time: 8s
Serial   Phase #:77   [PDB1] Files:2    Time: 8s
Restart  Phase #:78   [PDB1] Files:1    Time: 1s
Serial   Phase #:79   [PDB1] Files:1    Time: 8s
Restart  Phase #:80   [PDB1] Files:1    Time: 2s
Parallel Phase #:81   [PDB1] Files:3    Time: 9s
Restart  Phase #:82   [PDB1] Files:1    Time: 1s
Serial   Phase #:83   [PDB1] Files:1    Time: 8s
Restart  Phase #:84   [PDB1] Files:1    Time: 2s
Serial   Phase #:85   [PDB1] Files:1    Time: 8s
Restart  Phase #:86   [PDB1] Files:1    Time: 2s
Parallel Phase #:87   [PDB1] Files:4    Time: 9s
Restart  Phase #:88   [PDB1] Files:1    Time: 2s
Serial   Phase #:89   [PDB1] Files:1    Time: 8s
Restart  Phase #:90   [PDB1] Files:1    Time: 1s
Serial   Phase #:91   [PDB1] Files:2    Time: 8s
Restart  Phase #:92   [PDB1] Files:1    Time: 2s
Serial   Phase #:93   [PDB1] Files:1    Time: 6s
Restart  Phase #:94   [PDB1] Files:1    Time: 2s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [PDB1] Files:1    Time: 6s
Restart  Phase #:96   [PDB1] Files:1    Time: 2s
***********   Final Component scripts    ***********
Serial   Phase #:97   [PDB1] Files:1    Time: 8s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [PDB1] Files:1    Time: 122s
*******************   Migration   ******************
Serial   Phase #:99   [PDB1] Files:1    Time: 6s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [PDB1] Files:1    Time: 7s
Serial   Phase #:101  [PDB1] Files:1    Time: 4s
Serial   Phase #:102  [PDB1] Files:1    Time: 6s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [PDB1] Files:1    Time: 117s
****************   Summary report   ****************
Serial   Phase #:104  [PDB1] Files:1    Time: 6s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [PDB1] Files:1    Time: 6s
Serial   Phase #:106  [PDB1] Files:1    Time: 5s
Serial   Phase #:107  [PDB1] Files:1     Time: 0s

------------------------------------------------------
Phases [0-107]         End Time:[2020_03_06 14:58:28]
Container Lists Inclusion:[PDB1] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 1561s [PDB1]

 LOG FILES: (/home/oracle/logs/catupgrdpdb3*.log)

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

     Time: 1636s For PDB(s)

Grand Total Time: 1636s 

 LOG FILES: (/home/oracle/logs/catupgrd*.log)


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

Afterwards, I recompile.

sqlplus / as sysdba

SQL> alter pluggable database PDB1 open;
SQL> alter pluggable database PDB1 save state;
SQL> exit

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDB1' -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql

Done.

Finally, I can adjust the time zone of my upgraded PDB. As catcon.pl does not give direct output, I will have to check the log files afterwards. That’s a bit nasty.

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDB1' -l /home/oracle/logs -b utltz_upg_check -d $ORACLE_HOME/rdbms/admin utltz_upg_check.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/utltz_upg_check_catcon_31522.lst]

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

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

catcon.pl: completed successfully
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -c 'PDB1' -l /home/oracle/logs -b utltz_upg_apply -d $ORACLE_HOME/rdbms/admin utltz_upg_apply.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/utltz_upg_apply_catcon_31826.lst]

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

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

catcon.pl: completed successfully

Final check:

sqlplus / as sysdba
alter system set "_exclude_seed_cdb_view"=false scope=both;

select value$, con_id from containers(SYS.PROPS$) where NAME='DST_PRIMARY_TT_VERSION' order by 2;

VALUE$ CON_ID
------ ------
32	    1
32	    2
32	    3

All set!

Further Information and Links

–Mike

Share this: