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 !!