ORACLE 12c PDB Upgrade
This document explains how to upgrade one
particular PDB in CDB environment from 12.1.0.1 to 12.1.0.2
Example:
Suppose Oracle 12.1.0.1 software installed and
created CDB environment with multiple PDBS.
We need to upgrade only one or few
PDBS upgrade.
As per upgrade process , install new oracle
version and create CDB with empty.
Current Oracle HOME
/u01/app/oracle/product/12.1.0.1, here CDB name is OR12CDB and pluggable
database names are pdb1 and pdb2.
New Oracle HOME
/u01/app/oracle/product/12.1.0.2, here CDB name is O12CR1.
CDB
upgradation will not there because CDB
will never store user data. While installing new oracle home we can
create new CDB .
As per
upgrade process, we will unplug the PDB and plug into new CDB and run the
script to convert. Here we will upgrade
PDB1 database.
Step1 : Stop the application and take Database
Backup
Disable
monitoring,cronjobs ,database jobs and etc.
Step2: Prepare the PDB for
Upgrade
The PDB must be prepared for upgrade, then
unplugged from the source container databases.
Switch to the "OR12CDB" instance in
the "12.1.0.1" environment.
Set the oracle environment variable.
$. Oraenv
ORACLE_SID[name] OR12CDB
sqlplus /nolog
connect / as sysdba
copy the preupgrade.sql , utluppkg.sql script from latest oracle home to
/home/oracle/upgrade folder.
Script will located in
/u01/app/oracle/product/12.1.0.2/rdbms/admin
Login to OR12CDB database
Sql>CONN / AS SYSDBA
SQL> select name,open_mode,cdb from
v$database; --CDB
NAME OPEN_MODE CDB
--------- -------------------- ---
OR12CDB READ WRITE YES
SQL> select comp_name,status,version from
dba_registry; --CDB
COMP_NAME
STATUS VERSION
----------- ------------------------------
Oracle XML Database VALID
12.1.0.1.0
Oracle Database Catalog Views VALID 12.1.0.1.0
Oracle Database Packages and Types VALID 12.1.0.1.0
SQL>
select count(*) from dba_objects where status='INVALID'; --CDB
COUNT(*)
----------
0
Note: run utlrp.sql script to make valid, run
respective component script to make VALID on CDB.
Set the PDB1
SQL>ALTER SESSION SET CONTAINER=pdb1;
Spool the log
SQL> select count(*) from dba_objects where
status='INVALID'; --PDB1
COUNT(*)
----------
133
SQL> @?/rdbms/admin/utlrp.sql --PDb1
SQL> select count(*) from dba_objects where
status='INVALID'; --PDB1
COUNT(*)
----------
0
SQL> select comp_name,status,version from
dba_registry; --PDB1
COMP_NAME STATUS VERSION
------------------------------ -----------
------------------------------
Oracle XML Database VALID 12.1.0.1.0
Oracle Database Catalog Views
VALID 12.1.0.1.0
Oracle Database Packages and Types
VALID 12.1.0.1.0
If any component is INVALID or RELOADED then make it VALID status.
SQL>spool /home/oracle/upgrade/preupgrade.log
--under PDB1
SQL>@/home/oracle/upgrade/preupgrd.sql --PDB1
Output will be
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/OR12CDB/preupgrade/preupgrade.log
2. Execute in the SOURCE
environment BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/OR12CDB/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW
environment AFTER upgrade:
/u01/app/oracle/cfgtoollogs/OR12CDB/preupgrade/postupgrade_fixups.sql
************************************************************
**************************************************************************
Pre-Upgrade Checks in PDB1
Completed.
**************************************************************************
**************************************************************************
**************************************************************************
SQL>
Please check the preupgrade.log for any activity do it. Then run
preupgrade_fixups.sql script.Make a note from preupgrade.log, what things we
have to do after upgradation
CONN / AS SYSDBA to OR12CDB
SQL>select comp_name,status,version from
dba_registry --CDB1
SQL>ALTER SESSION SET CONTAINER=pdb1;
SQL>select count(*) from dba_objects where
status=’INVALID’ --PDB1
SQL>?/rbdms/admin/utlrp.sql --PDB1
SQL>@/home/oracle/upgrade/preupgrade_fixups.sql --PDB1
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;
Here it
shows to collect dictionary stats. Sometimes it may tell to increase tablespace
(system,undo,sysaux and temp), parameter value changes and etc. Best practice
always says that make 1G free space in system,sysaux ,undo and temp.
Fix it.
Step 3: Unplug the PDB1 from OR12CDB
Connect
to the root container and unplug the PDB.
CONN / AS SYSDBA --connect
to OR12CDB --CDB1
ALTER PLUGGABLE DATABASE pdb1 CLOSE; --CDB1
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/home/oracle/upgrade/pdb1.xml'; --CDB1
EXIT;
Note: we are upgrading within the server only, no need to
copy anything
Step4: Upgrade the PDB
The PDB must be plugged into the destination CDB
and upgraded.
Switch to the "O12CR1" instance in the
"12.1.0.2" environment.
[oracle@localhost preupgrade]$ . oraenv --New CDB
ORACLE_SID = [OR12CDB] ? O12CR1
SQL>connect /as sysdba
Plugin the "pdb1" pluggable database
into the "O12CR1" container.
CREATE PLUGGABLE DATABASE pdb1 USING
'/tmp/pdb1.xml' nocopy; --NEW CDB
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 /u01/app/oracle/product/12.1.0.2
$ORACLE_HOME/perl/bin/perl
catctl.pl -c "pdb1" -l /tmp catupgrd.sql
--oracle user level
Argument list for
[catctl.pl]
SQL Process Count n = 0
….
Run in c = pdb1
…
catctl.pl version:
12.1.0.2.0
Oracle Base = /u01/app/oracle
….
CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1]
Exclusion:[]
…….
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/O12CR1/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 -- O12CR1 CDB
SQL>ALTER SESSION SET
CONTAINER=pdb1;
SQL>STARTUP;
@?/rdbms/admin/utlrp.sql
Run the "postupgrade_fixups.sql"
script. Remember to perform any recommended manual steps.
Connect to PDB1
SQL>@/u01/app/oracle/cfgtoollogs/OR12CDB/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;
SQL>@? /rdbms/admin/catuppst.sql
Now check
Select banner from v$version;
Select comp_name,status,version from dba_registry;
Check the connectivity.
Once application validation is good start monitoring,
change the oracle home path for this pdb jobs script, remove comment from cron
jobs etc..
Please update your comments on dbaclass4u@gmail.com
