Meanwhile all customers applying Oracle databases should work on switching their database to Oracle 12c. While for Standard Edition Users there still is the restriction that only version 12.1.0.1 is available, Enterprise Edition Users can apply version 12.1.0.2. It is matter of course that besides the Basic release the current PSU (Patch Set Unit) is loaded.
Performing an installation last week as part of a project, I noticed a small but important commit:
If you perform the Upgrade from 11g to 12c with the dbua (Database Upgrade Assistant), the command datapatch is not performed automatically (even though the documentation says so explicitly). This leads to the software being up-to-date (at the time of posting this blog 12.1.0.2.4, i.e. including PSU from June 2015), but not is the database.
Find more information on datapatch here: Oracle Premier Support – Oracle Database Support News – Issue November, 2014 Volume 46 (Doc ID 1954478.1).
Example
An example for the upgrade of the “WOLFGANG” database from 11.2.0.4 to 12.1.0.2.5 clarifies this:
If all requirements for upgrading are met (i.e. clearing the recycling-bin), the upgrade will be done in around 30 to 45 minutes.
But what does this mean to the Upgrade of the Database?
First everything looks allright:
SELECT * FROM v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0
But what about the PSU?
SQL> SELECT patch_id, version, status, bundle_id, bundle_series
FROM dba_registry_sqlpatch;
no rows selected
That should actually look differentely!
In the log file of the upgrade ($ORACLE_BASE/cfgtoollogs/dbua/WOLFGANG/upgrade1/catupgrd0.log) you find this information:
... ------------------------------------------------------ Phases [0-73] End Time:[2015_07_22 10:24:42] ------------------------------------------------------ Grand Total Time: 1177s catuppst.sql unable to run in Database: REASON: catuppst.sql unable to run -x option has been turned on LOG FILES: (catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/WOLFGANG/upgrade/upg_summary.log Grand Total Upgrade Time: [0d:0h:19m:37s] stdout from running datapatch to install upgrade SQL patches and PSUs: SQL Patching tool version 12.1.0.2.0 on Wed Jul 22 10:22:58 2015 Copyright (c) 2015, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6067_2015_07_22_10_22_58/sqlpatch_invocation.log Connecting to database...OK Bootstrapping registry and package to current versions...done Determining current state...done Current state of SQL patches: Bundle series PSU: ID 4 in the binary registry and not installed in the SQL registry <---- Adding patches to installation queue and performing prereq checks... Installation queue: Nothing to roll back Nothing to apply <---- SQL Patching tool complete on Wed Jul 22 10:23:19 2015 stderr from running datapatch to install upgrade SQL patches and PSUs: Function created. Elapsed: 00:00:00.03 Oracle Database 12.1 Post-Upgrade Status Tool 07-22-2015 10:24:24 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:15:33 Oracle Workspace Manager VALID 12.1.0.2.0 00:00:50 Oracle XML Database VALID 12.1.0.2.0 00:01:21 Final Actions 00:01:27 Total Upgrade Time: 00:20:42
Even though it was recognized that there is a PSU, the patching ends with “Nothing to apply“.
If you are aware of this commit, you can quite easily install the patch subsequently via datapatch:
cd $ORACLE_HOME/OPatch
oracle@mozart[WOLFGANG]% ./datapatch
SQL Patching tool version 12.1.0.2.0 on Wed Jul 22 14:20:37 2015
Copyright (c) 2015, Oracle. All rights reserved.
Connecting to database...OK
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
Nothing to roll back
The following patches will be applied:
20831110 (Database Patch Set Update : 12.1.0.2.4 (20831110))
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
SQL Patching tool complete on Wed Jul 22 14:20:56 2015
You should always use this command to check if the database is up-to-date with the PSU:
SQL> SELECT patch_id, version, status, bundle_id, bundle_series
FROM dba_registry_sqlpatch;
PATCH_ID VERSION STATUS BUNDLE_ID BUNDLE_SERIES
---------- -------------------- --------------- ---------- ------------------------------
20831110 12.1.0.2 SUCCESS 4 PSU
Get more information at the Oracle Support and, of course, from “Mr. Upgrade” Mike Dietrich at https://blogs.oracle.com/UPGRADE/entry/dbua_and_datapatch_some_things.
Godspeed your upgrade and have fun with Oracle 12c!