Wednesday, June 1, 2016

ORACLE 12c Pluggable database Upgrade from 12.1.0.1 to 12.1.0.2

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