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.
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.
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
- Upgrade PDBs with RELOCATE – How does it work?
- Cloning with Pluggable Databases in Oracle 18c
- Cloning a PDB from Oracle 12.1 to Oracle 12.2 – Shared Undo Case
- Patching/Upgrading Time Zone in PDBs
–Mike
Hi Mike, great approach – any chance you have an idea when AutoUgrade will support this?
Hi Jeannette,
thanks – and unfortunately there are a lot of open projects we are working on right now (e.g. “Unplug/plug/upgrade”). I fear, using an online clone for testing isn’t on the list right now. But I will carry it forward to the team for sure.
Thanks and kind regards – and take care please!
Mike
Thanks for your answer Mike. We are working with a business scenario where this method could also apply to production upgrades (of which we have 100s). We have a fallback scenario which is broken if we unplug/plug/upgrade. This is where the online clone option comes in handy. This is our pitched scenario:
1. Run AutoUpgrade analyse for PDBx in CDBoldVersion and fix issues reported for PDBx
2. Clone PDBx over database link to PDBy in CDBnewVersion and start PDBy in upgrade mode
3. Run AutoUpgrade on PDBy in CDBnewVersion
4. Any errors in step 2 or 4, open op for continued work in PDBx, else drop PDBx
5. If no errors open op for continued work in PDBy
Kind regards and take care as well !
Hi Jeannette,
you are the 2nd one proposing this as the better scenario (online clone instead of relocate), and I agree with you.
Especially the fact that the relocate – as soon as you start the relocated PDB in STARTUP UPGRADE mode – drops the source whereas the clone leaves the source makes it the better solution.
Thanks for your suggestion.
And please take care, too!!! And enjoy the Easter weekend!
Cheers,
Mike
Hi Mike,
in the real world Data Guard is often used. The 19c therefore already has one standby db and no Active Data Guard is used. Do you have already plans for such an article?
Frank
Hi Frank,
at the moment not – there are so many different topics on my list right now … I will blog more about DG once AutoUpgrade fully handles everything.
Cheers,
Mike
Hello Mike,
Very useful demo. Can you have a similar clone for CDB1 PDB1 which has TDE wallet enable on Oracle 12.2 and clone to CDB2 on Oracle 19c.
Thank you
Hi Emma,
working on it.
Example:
create pluggable database DEV from DEV@clone_link file_name_convert=(‘CDB1’, ‘CDB2’) KEYSTORE IDENTIFIED BY *********;
Cheers,
MIke
Mike,
Thanks for the detailed steps. I ran into Bug 29469563 cloning database(target 19.7) on OCI with TDE. After applying the patch it ran fine.
Regards
Sri
Thanks Sri!
Cheers,
Mike