8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Upgrade a PDB using Unplug/Plugin
Using the Database Upgrade Assistant (DBUA) against a container database (CDB) will upgrade all the associated pluggable databases (PDBs) also. If you don't want to commit to upgrading all the PDBs in one step, you can upgrade them individually, or a subset of the PDBs, using the unplug/plugin method.
This article describes the method for upgrading a PDB using the unplug/plugin method. It assumes you have the following container databases.
- cdb1 (12.1.0.1) : Containing pdb1, the PDB we wish to upgrade.
- cdb2 (12.1.0.2) : No PDBs, although that is not a prerequisite.
I'm purposely going to ignore the upgrade to APEX, as this issue is discussed here.
Related articles.
Prepare the PDB for Upgrade
The PDB must be prepared for upgrade, then unplugged from the source container databases.
Switch to the "cdb1" instance in the "12.1.0.1" environment.
export ORACLE_BASE=/u01/app/oracle export ORAENV_ASK=NO export ORACLE_SID=cdb1 . oraenv export ORAENV_ASK=YES sqlplus /nolog
From Oracle 12.2 onward the "preupgrd.sql" script has been removed and replaced by the "preupgrade.jar" file, which is run as follows. The "preupgrade.jar" file is shipped with the Oracle software, but you should really download the latest version from MOS 884522.1.
$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrade.jar TERMINAL TEXT -c "pdb1"
The resulting output is similar to the "preupgrd.sql" script, an example of which is shown below.
Run the "preupgrd.sql" script from the "12.1.0.2" home, not the current 12.1.0.1 home!
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
@/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in PDB1...
***************************************************************************
************************************************************
====>> ERRORS FOUND for PDB1 <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for PDB1 <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in PDB1 Completed.
***************************************************************************
***************************************************************************
***************************************************************************
SQL>
The output displays the generated scripts, including the "preupgrade.log" file. Both the log file and fixup scripts will be in the "$ORACLE_BASE/cfgtoollogs" directory or the "$ORACLE_HOME/cfgtoollogs" directory, depending on whether the $ORACLE_BASE has been specified or not. Run the fixup script and perform any manual tasks listed in the "preupgrade.log" file. These should be listed by the "preupgrade_fixups.sql" script also.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
@/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2015-02-16 09:40:04 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container PDB1
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 4.2.0.00.27 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
1 fixup routine generated an INFORMATIONAL message that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
SQL>
EXEC DBMS_STATS.gather_dictionary_stats;
Connect to the root container and unplug the PDB.
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE pdb1 CLOSE; ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml'; EXIT;
You have a couple of options about how to deal with the source pluggable database.
-- Option 1 -- 1) Drop the PDB, but keep the datafiles. DROP PLUGGABLE DATABASE pdb1 KEEP DATAFILES; -- 2) Copy the files to the destination location. -- 2) Manually clean up the files. -- Option 2 -- 1) Copy the files to the destination location. -- 2) Drop the PDB, including the datafiles, as they have already been copied to the destination. DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;
Upgrade the PDB
The PDB must be plugged into the destination CDB and upgraded.
Switch to the "cdb2" instance in the "12.1.0.2" environment.
export ORACLE_BASE=/u01/app/oracle export ORAENV_ASK=NO export ORACLE_SID=cdb2 . oraenv export ORAENV_ASK=YES sqlplus /nolog
Plugin the "pdb1" pluggable database into the "cdb2" container.
CONN / AS SYSDBA
CREATE PLUGGABLE DATABASE pdb1 USING '/tmp/pdb1.xml'
FILE_NAME_CONVERT=('/oradata/cdb1/pdb1', '/oradata/cdb2/pdb1');
ALTER PLUGGABLE DATABASE pdb1 OPEN UPGRADE;
Warning: PDB altered with errors.
SQL> EXIT;
Don't worry about the "Warning: PDB altered with errors." message at this point.
Run the "catupgrd.sql" script against the PDB. Notice the use of the "-c" flag to specify an inclusion list. If you were upgrading multiple PDBs, you could list them in a space-separated list so they are all upgraded in a single step.
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -c "pdb1" -l /tmp catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /tmp
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = pdb1
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /tmp
catcon: ALL catcon-related output will be written to /tmp/catupgrd_catcon_9258.lst
catcon: See /tmp/catupgrd*.log files for output generated by scripts
catcon: See /tmp/catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count = 0
New SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1] Exclusion:[]
Starting
[/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl catctl.pl -c 'PDB1' -l /tmp -I -i pdb1 -n 2 catupgrd.sql]
Argument list for [catctl.pl]
SQL Process Count n = 2
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /tmp
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = pdb1
Run in c = PDB1
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 1
catctl.pl version: 12.1.0.2.0
Oracle Base = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /tmp
catcon: ALL catcon-related output will be written to /tmp/catupgrdpdb1_catcon_9360.lst
catcon: See /tmp/catupgrdpdb1*.log files for output generated by scripts
catcon: See /tmp/catupgrdpdb1_*.lst files for spool files, if any
Number of Cpus = 2
SQL PDB Process Count = 2
SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1] Exclusion:[]
------------------------------------------------------
Phases [0-73]
Container Lists Inclusion:[PDB1] Exclusion:[]
Serial Phase #: 0 Files: 1 Time: 13s PDB1
Serial Phase #: 1 Files: 5 Time: 34s PDB1
Restart Phase #: 2 Files: 1 Time: 0s PDB1
Parallel Phase #: 3 Files: 18 Time: 11s PDB1
Restart Phase #: 4 Files: 1 Time: 0s PDB1
Serial Phase #: 5 Files: 5 Time: 14s PDB1
Serial Phase #: 6 Files: 1 Time: 7s PDB1
Serial Phase #: 7 Files: 4 Time: 6s PDB1
Restart Phase #: 8 Files: 1 Time: 0s PDB1
Parallel Phase #: 9 Files: 62 Time: 47s PDB1
Restart Phase #:10 Files: 1 Time: 0s PDB1
Serial Phase #:11 Files: 1 Time: 11s PDB1
Restart Phase #:12 Files: 1 Time: 0s PDB1
Parallel Phase #:13 Files: 91 Time: 8s PDB1
Restart Phase #:14 Files: 1 Time: 0s PDB1
Parallel Phase #:15 Files: 111 Time: 11s PDB1
Restart Phase #:16 Files: 1 Time: 0s PDB1
Serial Phase #:17 Files: 3 Time: 1s PDB1
Restart Phase #:18 Files: 1 Time: 0s PDB1
Parallel Phase #:19 Files: 32 Time: 19s PDB1
Restart Phase #:20 Files: 1 Time: 0s PDB1
Serial Phase #:21 Files: 3 Time: 6s PDB1
Restart Phase #:22 Files: 1 Time: 0s PDB1
Parallel Phase #:23 Files: 23 Time: 79s PDB1
Restart Phase #:24 Files: 1 Time: 0s PDB1
Parallel Phase #:25 Files: 11 Time: 34s PDB1
Restart Phase #:26 Files: 1 Time: 0s PDB1
Serial Phase #:27 Files: 1 Time: 0s PDB1
Restart Phase #:28 Files: 1 Time: 0s PDB1
Serial Phase #:30 Files: 1 Time: 0s PDB1
Serial Phase #:31 Files: 257 Time: 22s PDB1
Serial Phase #:32 Files: 1 Time: 0s PDB1
Restart Phase #:33 Files: 1 Time: 0s PDB1
Serial Phase #:34 Files: 1 Time: 1s PDB1
Restart Phase #:35 Files: 1 Time: 0s PDB1
Restart Phase #:36 Files: 1 Time: 0s PDB1
Serial Phase #:37 Files: 4 Time: 37s PDB1
Restart Phase #:38 Files: 1 Time: 0s PDB1
Parallel Phase #:39 Files: 13 Time: 51s PDB1
Restart Phase #:40 Files: 1 Time: 0s PDB1
Parallel Phase #:41 Files: 10 Time: 5s PDB1
Restart Phase #:42 Files: 1 Time: 0s PDB1
Serial Phase #:43 Files: 1 Time: 5s PDB1
Restart Phase #:44 Files: 1 Time: 0s PDB1
Serial Phase #:45 Files: 1 Time: 1s PDB1
Serial Phase #:46 Files: 1 Time: 1s PDB1
Restart Phase #:47 Files: 1 Time: 0s PDB1
Serial Phase #:48 Files: 1 Time: 164s PDB1
Restart Phase #:49 Files: 1 Time: 0s PDB1
Serial Phase #:50 Files: 1 Time: 33s PDB1
Restart Phase #:51 Files: 1 Time: 0s PDB1
Serial Phase #:52 Files: 1 Time: 38s PDB1
Restart Phase #:53 Files: 1 Time: 0s PDB1
Serial Phase #:54 Files: 1 Time: 44s PDB1
Restart Phase #:55 Files: 1 Time: 0s PDB1
Serial Phase #:56 Files: 1 Time: 58s PDB1
Restart Phase #:57 Files: 1 Time: 1s PDB1
Serial Phase #:58 Files: 1 Time: 73s PDB1
Restart Phase #:59 Files: 1 Time: 0s PDB1
Serial Phase #:60 Files: 1 Time: 88s PDB1
Restart Phase #:61 Files: 1 Time: 0s PDB1
Serial Phase #:62 Files: 1 Time: 117s PDB1
Restart Phase #:63 Files: 1 Time: 0s PDB1
Serial Phase #:64 Files: 1 Time: 0s PDB1
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib;
export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin
-I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl
-verbose -upgrade_mode_only -pdbs PDB1 > /tmp/catupgrdpdb1_datapatch_upgrade.log 2>
/tmp/catupgrdpdb1_datapatch_upgrade.err
returned from sqlpatch
Time: 13s PDB1
Serial Phase #:66 Files: 1 Time: 3s PDB1
Serial Phase #:68 Files: 1 Time: 3s PDB1
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib;
export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin
-I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs PDB1 >
/tmp/catupgrdpdb1_datapatch_normal.log 2> /tmp/catupgrdpdb1_datapatch_normal.err
returned from sqlpatch
Time: 8s PDB1
Serial Phase #:70 Files: 1 Time: 70s PDB1
Serial Phase #:71 Files: 1 Time: 6s PDB1
Serial Phase #:72 Files: 1 Time: 4s PDB1
Serial Phase #:73 Files: 1 Time: 0s PDB1
Grand Total Time: 1150s PDB1
LOG FILES: (catupgrdpdb1*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/cdb2/upgrade/upg_summary.log
Total Upgrade Time: [0d:0h:19m:10s]
Time: 1152s For PDB(s)
Grand Total Time: 1152s
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:19m:12s]
$
Start the PDB and recompile any invalid objects.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; STARTUP; @?/rdbms/admin/utlrp.sql
Run the "postupgrade_fixups.sql" script. Remember to perform any recommended manual steps.
@/u01/app/oracle/cfgtoollogs/cdb1/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2015-02-16 09:40:04 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
No fixup routines were executed.
**************************************************
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.
SQL>
EXECUTE DBMS_STATS.gather_fixed_objects_stats;
The PDB upgrade is now complete.
For more information see:
Hope this helps. Regards Tim...