Upgrade 12.1.0.1 Oracle Multitenant database to 12.1.0.2

Overiew

  • Basically there are two techniques to upgrade an Oracle Multitenant environment:
    • Everything at Once
    • One at a Time — via unplug/plug
  • Even plugin a 12.1.0.1 PDB into 12.1.0.2 CDB will take some time the concept of installing the CDB 12.1.0.2 first has some advantages:
    • Intensive testing can occur with that CDB 12.1.0.2 which will become our procution database later
    • If during the UPGRADE some problems happens despite intensive testing we can easily swich back to our 12.1.0.1 env.
    • In that case of switch back you simply need to plugin your PDB to your CDB 12.1.0.1
  • This BLOG covers  use the unplug/plug approach 

Install 12.1.0.2 software  ( no impact to our production env )

Create new ORACLE_HOME on all custer nodes and install 12.1.0.2 software 
[root@gract1 ~]# mkdir -p  /u01/app/oracle/product/12102/racdb
[root@gract1 ~]# chown oracle:oinstall  /u01/app/oracle/product/12102/racdb
[root@gract1 ~]# chmod 775 /u01/app/oracle/product/12102/racdb

Run OUI and install 12.0.1.2 by using software ONLY option 
[oracle@gract1 database]$ pwd
/media/sf_Kits/12.1.0.2/database
[oracle@gract1 database]$ ./runInstaller
  --> Select database software only 
   --> Select RAC database

Run any fixup scripts if there are fixable errors
[root@gract1 ~]# /tmp/CVU_12.1.0.2.0_oracle/runfixup.sh
All Fix-up operations were completed successfully.

[root@gract1 ~]# ssh gract2
[root@gract2 ~]# /tmp/CVU_12.1.0.2.0_oracle/runfixup.sh
All Fix-up operations were completed successfully.

[root@gract1 ~]# ssh gract3
[root@gract3 ~]# /tmp/CVU_12.1.0.2.0_oracle/runfixup.sh
All Fix-up operations were completed successfully.

Create a new CDB database ( no impact to our production env )

After OUI finishes run /u01/app/oracle/product/12102/racdb/root.sh on all nodes 
--> Use dbca an crate an new database
  --> Create new database
   --> Advanced mode 
    --> RAC Database - Admin managed
     --> Create an empty Container database : dbname : cdbn

Run preupgrade scripts in 12.1.0.1 PDB (  no impact to our production env )

Copy preupgrd.sql and utluppkg.sql from the rdbms/admin directory of the new Oracle home where you installed Oracle Database 
12c to a directory that is accessible when you connect to your source database, which is the database to be upgraded. 
Preferably, this should be a temp directory.
Now copy preupgrd.sql and utluppkg.sql from the new home into a temp directory eg. /tmp and run the preupgrd.sql. 

[oracle@gract1 UPGRADE]$ cp  /u01/app/oracle/product/12102/racdb/rdbms/admin/preupgrd.sql .
[oracle@gract1 UPGRADE]$ cp  /u01/app/oracle/product/12102/racdb/rdbms/admin/utluppkg.sql .

Switch  to your  source 12.1.0.1 Oracle Home.
 - When running preupgrd.sql in a CDB, make sure all the PDBs are opened. 
 - To open all the PDBs:

$ sqlplus  sys/sys@cdb as sysdba
SQL>  alter pluggable database all open;
SQL>  select INST_ID, CON_ID, DBID, CON_UID, GUID, NAME, OPEN_MODE, RESTRICTED  from gv$pdbs where NAME='PDB1';
   INST_ID     CON_ID        DBID    CON_UID GUID                 NAME    OPEN_MODE  RES
---------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---
     1        3 3362522988 3362522988 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO
     2        3 3362522988 3362522988 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO
     3        3 3362522988 3362522988 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO

Run preugrd script on PDB1 and prepare for plugin later 
[oracle@gract1 UPGRADE]$ sqlplus sys/sys as sysdba
SQL> alter session set container=PDB1;  
SQL> @preupgrd
           ====>> PRE-UPGRADE RESULTS for PDB1 <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
 /u01/app/oracle/cfgtoollogs/cdb/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
 /u01/app/oracle/cfgtoollogs/cdb/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
 /u01/app/oracle/cfgtoollogs/cdb/preupgrade/postupgrade_fixups.sql
Summary from preupgrade.log 
[Pre-Upgrade Recommendations] : as SYSDBA run : EXECUTE dbms_stats.gather_dictionary_stats;
[Post-Upgrade Recommendations] : as SYSDBA run : EXECUTE dbms_stats.gather_dictionary_stats;

MANUAL ACTION SUGGESTED
 After your database is upgraded and open in normal mode you must run 
 rdbms/admin/catuppst.sql which executes several required tasks and completes
 the upgrade process.

 You should follow that with the execution of rdbms/admin/utlrp.sql, and a
 comparison of invalid objects before and after the upgrade using
 rdbms/admin/utluiobj.sql

 If needed you may want to upgrade your timezone data using the process
 described in My Oracle Support note 1509653.1

Unplug PDB database and plugin into your new 12.1.0.2 CDB ( Downtime starts here !!)

SQL> alter sesstion set container=CDB$ROOT; 
SQL> alter pluggable database PDB1 close immediate instances=all;
SQL> alter pluggable database PDB1 unplug into '/home/oracle/RAC/UPGRADE/pdb1.xml' ;
Pluggable database altered.

Connect to new CDB cd2
SQL> alter session set container=CDB$ROOT;
SQL>  SET SERVEROUTPUT ON
      DECLARE
              compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
              pdb_descr_file => '/home/oracle/RAC/UPGRADE//pdb1.xml',
              pdb_name => 'PDB1')
              WHEN TRUE THEN 'YES' ELSE 'NO'
      END;
            BEGIN
            DBMS_OUTPUT.PUT_LINE(compatible);
            END;
            /
NO  
PL/SQL procedure successfully completed.
--> Compatibiltiy  Check will result in "NO" - but obviously the plugin operation will work

Check pdb_plug_in_violations
SQL> select message, status from pdb_plug_in_violations where type like '%ERR%';
MESSAGE                                                        STATUS
--------------------------------------------------------------------------------------------------- ---------
PDB's version does not match CDB's version: PDB's version 12.1.0.0.0. CDB's version 12.1.0.2.0.      PENDING
APEX mismatch: PDB installed version 4.2.0.00.27 CDB installed version 4.2.5.00.08                   PENDING

As we use the same ASM datafile we don't need to use file_name_convert running create pluggable database  
SQL> create pluggable database pdb1 using '/home/oracle/RAC/UPGRADE/pdb1.xml'; 
Pluggable database created.
      Here a sample with file_name_convert
      SQL>  create pluggable database pdb1 using '/stage/pdb1.xml' file_name_convert=('/oradata/CDB1/pdb1', '/oradata/CDB2/pdb1');

Open database with UPGRADE 
SQL>  alter pluggable database PDB1 open upgrade;
Warning: PDB altered with errors.

Note the follwing step will take some time :
[oracle@gract1 racdb]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c 'PDB1' catupgrd.sql
Argument list for [/u01/app/oracle/product/12102/racdb/rdbms/admin/catctl.pl]
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Input Directory       d = /u01/app/oracle/product/12102/racdb/rdbms/admin
...
Display Phases        y = 0
Child Process         I = 0

catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle

Analyzing file /u01/app/oracle/product/12102/racdb/rdbms/admin/catupgrd.sql
Log files in /u01/app/oracle/product/12102/racdb
catcon: ALL catcon-related output will be written to catupgrd_catcon_22075.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus        = 1
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count     = 0
New SQL Process Count = 1
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1
PDB Inclusion:[PDB1] Exclusion:[]
Starting
[/u01/app/oracle/product/12102/racdb/perl/bin/perl /u01/app/oracle/product/12102/racdb/rdbms/admin/catctl.pl -d 
          /u01/app/oracle/product/12102/racdb/rdbms/admin -c 'PDB1' -I -i pdb1 -n 2 catupgrd.sql]
Argument list for [/u01/app/oracle/product/12102/racdb/rdbms/admin/catctl.pl]
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Input Directory       d = /u01/app/oracle/product/12102/racdb/rdbms/admin
...
Display Phases        y = 0
Child Process         I = 1
catctl.pl version: 12.1.0.2.0
Oracle Base           = /u01/app/oracle
Analyzing file /u01/app/oracle/product/12102/racdb/rdbms/admin/catupgrd.sql
Log files in /u01/app/oracle/product/12102/racdb
catcon: ALL catcon-related output will be written to catupgrdpdb1_catcon_22568.lst
catcon: See catupgrdpdb1*.log files for output generated by scripts
catcon: See catupgrdpdb1_*.lst files for spool files, if any
Number of Cpus        = 1
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: 91s   PDB1
Serial   Phase #: 1 Files: 5     Time: 254s  PDB1
Restart  Phase #: 2 Files: 1     Time: 0s    PDB1
....
Serial   Phase #:73 Files: 1     Time: 0s    PDB1
Grand Total Time: 7957s PDB1
LOG FILES: (catupgrdpdb1*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12102/racdb/cfgtoollogs/cdbn/upgrade/upg_summary.log
Total Upgrade Time:          [0d:2h:12m:37s]
     Time: 7965s For PDB(s)
Grand Total Time: 7965s 
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time:    [0d:2h:12m:45s]

Check Upgrade Log
[root@gract1 var]# more /u01/app/oracle/product/12102/racdb/cfgtoollogs/cdbn/upgrade/upg_summary.log
Oracle Database 12.1 Post-Upgrade Status Tool           08-10-2014 21:27:56
                             [PDB1:3]
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
Oracle Server                          UPGRADED      12.1.0.2.0  00:34:31
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:21:14
Oracle Real Application Clusters          VALID      12.1.0.2.0  00:00:06
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:07:25
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:01:41
Oracle OLAP API                           VALID      12.1.0.2.0  00:04:31
Oracle Label Security                     VALID      12.1.0.2.0  00:00:36
Oracle XDK                                VALID      12.1.0.2.0  00:06:49
Oracle Text                               VALID      12.1.0.2.0  00:01:08
Oracle XML Database                       VALID      12.1.0.2.0  00:03:09
Oracle Database Java Packages             VALID      12.1.0.2.0  00:01:40
Oracle Multimedia                         VALID      12.1.0.2.0  00:11:09
Spatial                                UPGRADED      12.1.0.2.0  00:15:27
Oracle Application Express                VALID     4.2.5.00.08  00:11:23
Oracle Database Vault                     VALID      12.1.0.2.0  00:03:03
Final Actions                                                    00:02:26
Post Upgrade                                                     00:00:18
Total Upgrade Time: 02:07:09 [PDB1]
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.42

Execute POST upgragde scripts
SQL> @/u01/app/oracle/cfgtoollogs/cdb/preupgrade/postupgrade_fixups.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

Check upgrade status 
Verify object status 
SQL>  @?/rdbms/admin/utluiobj.sql
Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 08-11-2014 08:36:02
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
                           Owner                     Object Name                     Object Type
.
PL/SQL procedure successfully completed.
Check upgrade status 
SQL> select owner,count(*) from dba_objects where status !=  'VALID'  group by owner; 
no rows selected
SQL> select comp_name,version,status from dba_registry;

Verify PDB mount status :
SQL> select INST_ID, CON_ID, DBID, CON_UID, GUID, NAME, OPEN_MODE, RESTRICTED  from gv$pdbs where NAME='PDB1';
   INST_ID     CON_ID        DBID    CON_UID GUID                 NAME    OPEN_MODE  RES
---------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---
     1        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO
     2        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    MOUNTED
     3        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    MOUNTED

Mount PDB clusterwide
SQL>  alter pluggable database pdb1 open instances=all;
Pluggable database altered.
SQL> select INST_ID, CON_ID, DBID, CON_UID, GUID, NAME, OPEN_MODE, RESTRICTED  from gv$pdbs where NAME='PDB1';
   INST_ID     CON_ID        DBID    CON_UID GUID                 NAME    OPEN_MODE  RES
---------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---
     1        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO
     3        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO
     2        3 3362522988 1064331803 FFE30B05B94B1D25E0436F01A8C05EFE PDB1    READ WRITE NO

After all PDBs are migrated consider to drop the old 12.1.0.1 CDB,PDB including software release
Before dropping Check new datafiles in use: 
SQL> select file_name, con_id from cdb_data_files;
FILE_NAME                                                 CON_ID
---------------------------------------------------------------------------------------------------- ----------
+DATA/CDBN/DATAFILE/system.302.855237255                                      1
+DATA/CDBN/DATAFILE/sysaux.303.855237163                                      1
+DATA/CDBN/DATAFILE/undotbs1.316.855237383                                    1
+DATA/CDBN/DATAFILE/users.318.855237381                                       1
+DATA/CDBN/DATAFILE/undotbs2.297.855237793                                      1
+DATA/CDBN/DATAFILE/undotbs3.298.855237797                                      1
+DATA/CDBN/FFE30B05B94B1D25E0436F01A8C05EFE/DATAFILE/system.324.855252739                      3
+DATA/CDBN/FFE30B05B94B1D25E0436F01A8C05EFE/DATAFILE/sysaux.325.855252377                      3
+DATA/CDBN/FFE30B05B94B1D25E0436F01A8C05EFE/DATAFILE/users.326.855251235                      3

OLD CDB datafiles 
[grid@gract1 ~]$ asmcmd ls -l DATA/CDB/DATAFILE/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    SYSAUX.283.854809845
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    SYSTEM.273.854810097
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    UNDOTBS1.279.854810307
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    UNDOTBS2.294.854810989
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    UNDOTBS3.286.854810995
DATAFILE  MIRROR  COARSE   AUG 10 13:00:00  Y    USERS.295.854810303

[grid@gract1 ~]$ asmcmd  ls -l  DATA/CDB/FFE30B05B94B1D25E0436F01A8C05EFE//DATAFILE/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   AUG 10 18:00:00  Y    SYSAUX.278.854811821
DATAFILE  MIRROR  COARSE   AUG 10 18:00:00  Y    SYSTEM.272.854811641
DATAFILE  MIRROR  COARSE   AUG 10 17:00:00  Y    USERS.270.854812071

--> After verifying that our new CDB cdbn does not reference an old  file form 12.1.0.1
    you can detele your 12.1.0.1 CDB,PDBs and software kit

 

Reference

  • https://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_one_at_a
  • https://blogs.oracle.com/UPGRADE/entry/upgrade_pdbs_everything_at_once1   

     Thx Mike for above  articles – they helpled me a lot !!

Leave a Reply

Your email address will not be published. Required fields are marked *