Until very recently I had really believed the marketing hype and sales pitch about how in 12c database upgrades are so much faster and easier than earlier releases – just unplug the PDB from one container and plug it in to another container and bingo you have an upgraded database!
Partly true …. maybe about 20%!
As Mike Dietrich from Oracle Corp. has rightly pointed out on his great blog (http://blogs.oracle.com/upgrade),it is not as straight forward as pointed out in slides seen I am sure by many of us at various Oracle conferences showcasing Oracle database 12c.
I tested out the upgrade of a PDB from version 12.1.0.1 to the latest 12c version 12.1.0.2 and here are the steps taken.
Note: If we are upgrading the entire CDB and all the PDB’s the steps would be different.
In this case we are upgrading just of the pluggable databases to a higher database software version.
Run the preupgrd.sql script and pre-upgrade fixup script
Connect to the 12.1.0.1 target database and run the preupgrd.sql script.
The source container database is cdb3 and the PDB which we are upgrading is pdb_gavin.
[oracle@edmbr52p5 ~]$ . oraenv
ORACLE_SID = [cdb1] ? cdb3
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1 is /u01/app/oracle
[oracle@edmbr52p5 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 21 10:49:21 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter session set container=pdb_gavin;
Session altered.
SQL> @?/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/u01/app/oracle/cfgtoollogs/cdb3/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/oracle/cfgtoollogs/cdb3/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/oracle/cfgtoollogs/cdb3/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
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.
1) Check Tag: OLS_SYS_MOVE
Check Summary: Check if SYSTEM.AUD$ needs to move to SYS.AUD$ before upgrade
Fixup Summary:
"Execute olspreupgrade.sql script prior to upgrade."
+++ Source Database Manual Action Required +++
You MUST resolve the above error prior to upgrade
************************************************************
The execution of the preupgrd.sql script will generate 3 separate files.
1)preupgrade.log
2)preupgrade_fixups.sql
3)postupgrade_fixups.sql
2)preupgrade_fixups.sql
3)postupgrade_fixups.sql
Let us examine the contents of the preupgrade.log file.
Oracle Database Pre-Upgrade Information Tool 08-21-2015 10:50:04
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
Database Name: CDB3
Version: 12.1.0.1.0
Compatible: 12.1.0.0.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V18
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]
**********************************************************************
[Component List]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] OPTION OFF
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> Oracle Label Security [upgrade] VALID
--> Oracle Database Vault [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Multimedia [upgrade] VALID
--> Oracle Spatial [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[ Unsupported Upgrade: Tablespace Data Supressed ]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
ERROR: --> SYSTEM.AUD$ (audit records) Move
An error occured retrieving a count from SYSTEM.AUD$
This can happen when the table has already been cleaned up.
The olspreupgrade.sql script should be re-executed.
WARNING: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_040200 has dependent objects.
**********************************************************************
[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 ^^^
**********************************************************************
[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 ^^^
**********************************************************************
************ Summary ************
1 ERROR exist that must be addressed prior to performing your upgrade.
2 WARNINGS that Oracle suggests are addressed to improve database performance.
0 INFORMATIONAL messages messages have been reported.
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 977512.1
***********************************
So as part of the pre-upgrade preparation we execute :
SQL> @?/rdbms/admin/olspreupgrade.sql and SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Unplug the PDB from the 12.1.0.1 Container Database
SQL> alter session set container=CDB$ROOT; Session altered. SQL> alter pluggable database pdb_gavin unplug into '/home/oracle/pdb_gavin.xml'; Pluggable database altered
Create the PDB in the 12.1.0.2 Container Database
[oracle@edmbr52p5 ~]$ . oraenv ORACLE_SID = [cdb2] ? cdb1 The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle [oracle@edmb]$ sqlplus sys as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 21 12:04:10 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> create pluggable database pdb_gavin 2 using '/home/oracle/pdb_gavin.xml' 3 nocopy 4 tempfile reuse; Pluggable database created..
Upgrade the PDB to 12.1.0.2
After the pluggable database has been created in the 12.1.0.2 container, we will open it with the UPGRADE option in order to run the catupgrd.sql database upgrade script.
We can see that we receive some errors which we can ignore safely as we are in the middle of an upgrade to the PDB.
SQL> alter pluggable database pdb_gavin open upgrade; Warning: PDB altered with errors. SQL> select message, status from pdb_plug_in_violations where type like '%ERR%'; MESSAGE -------------------------------------------------------------------------------- STATUS --------- Character set mismatch: PDB character set US7ASCII. CDB character set AL32UTF8. RESOLVED PDB's version does not match CDB's version: PDB's version 12.1.0.1.0. CDB's vers ion 12.1.0.2.0. PENDING
We now run the catctl.pl perl script and we specify the PDB name (if we were upgrading multiple PDBs hee we would separate each PDB name with a comma) – not that we are also running the upgrade in parallel.
[oracle@edm ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@edm admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c "PDB_GAVIN" -n 4 -l /tmp catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
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 = PDB_GAVIN
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_19456.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 = 8
Parallel PDB Upgrades = 2
SQL PDB Process Count = 2
SQL Process Count = 4
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1_1
PDB_GAVIN
PDB Inclusion:[PDB_GAVIN] Exclusion:[]
Starting
[/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl catctl.pl -c 'PDB_GAVIN' -n 2 -l /tmp -I -i pdb_gavin 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 = pdb_gavin
Run in c = PDB_GAVIN
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/catupgrdpdb_gavin_catcon_19562.lst
catcon: See /tmp/catupgrdpdb_gavin*.log files for output generated by scripts
catcon: See /tmp/catupgrdpdb_gavin_*.lst files for spool files, if any
Number of Cpus = 8
SQL PDB Process Count = 2
SQL Process Count = 2
[CONTAINER NAMES]
CDB$ROOT
PDB$SEED
PDB1_1
PDB_GAVIN
PDB Inclusion:[PDB_GAVIN] Exclusion:[]
------------------------------------------------------
Phases [0-73]
Container Lists Inclusion:[PDB_GAVIN] Exclusion:[]
Serial Phase #: 0 Files: 1 Time: 15s PDB_GAVIN
Serial Phase #: 1 Files: 5 Time: 107s PDB_GAVIN
Restart Phase #: 2 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #: 3 Files: 18 Time: 40s PDB_GAVIN
Restart Phase #: 4 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #: 5 Files: 5 Time: 43s PDB_GAVIN
Serial Phase #: 6 Files: 1 Time: 18s PDB_GAVIN
Serial Phase #: 7 Files: 4 Time: 11s PDB_GAVIN
Restart Phase #: 8 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #: 9 Files: 62 Time: 110s PDB_GAVIN
Restart Phase #:10 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:11 Files: 1 Time: 28s PDB_GAVIN
Restart Phase #:12 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:13 Files: 91 Time: 8s PDB_GAVIN
Restart Phase #:14 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:15 Files: 111 Time: 15s PDB_GAVIN
Restart Phase #:16 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:17 Files: 3 Time: 2s PDB_GAVIN
Restart Phase #:18 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:19 Files: 32 Time: 43s PDB_GAVIN
Restart Phase #:20 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:21 Files: 3 Time: 11s PDB_GAVIN
Restart Phase #:22 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:23 Files: 23 Time: 75s PDB_GAVIN
Restart Phase #:24 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:25 Files: 11 Time: 25s PDB_GAVIN
Restart Phase #:26 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:27 Files: 1 Time: 1s PDB_GAVIN
Restart Phase #:28 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:30 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:31 Files: 257 Time: 29s PDB_GAVIN
Serial Phase #:32 Files: 1 Time: 0s PDB_GAVIN
Restart Phase #:33 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:34 Files: 1 Time: 3s PDB_GAVIN
Restart Phase #:35 Files: 1 Time: 0s PDB_GAVIN
Restart Phase #:36 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:37 Files: 4 Time: 62s PDB_GAVIN
Restart Phase #:38 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:39 Files: 13 Time: 33s PDB_GAVIN
Restart Phase #:40 Files: 1 Time: 0s PDB_GAVIN
Parallel Phase #:41 Files: 10 Time: 5s PDB_GAVIN
Restart Phase #:42 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:43 Files: 1 Time: 7s PDB_GAVIN
Restart Phase #:44 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:45 Files: 1 Time: 1s PDB_GAVIN
Serial Phase #:46 Files: 1 Time: 0s PDB_GAVIN
Restart Phase #:47 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:48 Files: 1 Time: 71s PDB_GAVIN
Restart Phase #:49 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:50 Files: 1 Time: 9s PDB_GAVIN
Restart Phase #:51 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:52 Files: 1 Time: 41s PDB_GAVIN
Restart Phase #:53 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:54 Files: 1 Time: 51s PDB_GAVIN
Restart Phase #:55 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:56 Files: 1 Time: 36s PDB_GAVIN
Restart Phase #:57 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:58 Files: 1 Time: 37s PDB_GAVIN
Restart Phase #:59 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:60 Files: 1 Time: 48s PDB_GAVIN
Restart Phase #:61 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:62 Files: 1 Time: 112s PDB_GAVIN
Restart Phase #:63 Files: 1 Time: 0s PDB_GAVIN
Serial Phase #:64 Files: 1 Time: 1s PDB_GAVIN
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only -pdbs PDB_GAVIN > /tmp/catupgrdpdb_gavin_datapatch_upgrade.log 2> /tmp/catupgrdpdb_gavin_datapatch_upgrade.err
returned from sqlpatch
Time: 3s PDB_GAVIN
Serial Phase #:66 Files: 1 Time: 1s PDB_GAVIN
Serial Phase #:68 Files: 1 Time: 12s PDB_GAVIN
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -pdbs PDB_GAVIN > /tmp/catupgrdpdb_gavin_datapatch_normal.log 2> /tmp/catupgrdpdb_gavin_datapatch_normal.err
returned from sqlpatch
Time: 3s PDB_GAVIN
Serial Phase #:70 Files: 1 Time: 30s PDB_GAVIN
Serial Phase #:71 Files: 1 Time: 4s PDB_GAVIN
Serial Phase #:72 Files: 1 Time: 3s PDB_GAVIN
Serial Phase #:73 Files: 1 Time: 0s PDB_GAVIN
Grand Total Time: 1155s PDB_GAVIN
LOG FILES: (catupgrdpdb_gavin*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/cdb1/upgrade/upg_summary.log
Total Upgrade Time: [0d:0h:19m:15s]
Time: 1156s For PDB(s)
Grand Total Time: 1156s
LOG FILES: (catupgrd*.log)
Grand Total Upgrade Time: [0d:0h:19m:16s]
[oracle@edmbr52p5 admin]$
Run the post upgrade steps
We then start the PDB and run the post-upgrade steps which includes recompiling all the invalid objects and also gathering fresh statistics on the fixed dictionary objects.
That completes the PDB upgrade – not quite a simple plug and unplug!!
SQL> startup;
Pluggable Database opened.
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-08-21 12:35:42
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2015-08-21 12:36:02
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Database user "SYS", database schema "APEX_040200", user# "98" 12:36:13
...Compiled 0 out of 3014 objects considered, 0 failed compilation 12:36:13
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 12:36:13
...Completed key object existence check 12:36:13
...Setting DBMS Registry 12:36:13
...Setting DBMS Registry Complete 12:36:13
...Exiting validate 12:36:13
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_GAVIN READ WRITE
No comments:
Post a Comment