8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Multitenant : Unplug/Plugin

This article describes the method for upgrading a PDB using the unplug/plugin method. It assumes you have the following container databases.

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';
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...

Back to the Top.