Blog 
Oracle 12c DBUA and Datapatch

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!