DBUA 12c and “datapatch.pl” – things to know

For clarification:
The following blog post applies to upgrades to Oracle 12.1 done by DBUA only whenever a SPU/PSU/BP is installed into the 12.1 home prior to the upgrade (which I’d highly recommend as patching before upgrade saves you headache after upgrade!).

Update [Dec 10, 2015]:
The required patch is included in  12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct 2015) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs.

Update:
For Oracle Database 12.2 and newer please see:
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/


Two customers independently reported last week that they have doubts on DBUA’s ability to apply the required SQL changes associated with CPU/SPU or PSU.

First of all, let me tell you that this is not an issue when you do a command line upgrade to Oracle Database 12c with catctl.pl – you’ll only need to take care when using the DBUA.

One claimed that this feature alongside with datapatch.pl had been announced a while back:

Oracle Premier Support – Oracle Database Support News Issue November, 2014 Volume 46 (Doc ID 1954478.1)

Which Patching Tools uses Datapatch ?

  • Opatchauto   
    • OPatchAuto calls datapatch automatically to complete post patch actions upon installation of the binary patch and restart of the database.
  • Enterprise Manager Cloud Control   
    • Starting version 12.1 EMCC now calls datapatch to complete post patch actions upon any 12c or later database restart
  • Upgrade   
    • Catctl.pl and DBUA now call Datapatch during the upgrade process
  • OPatch  
    • Datapatch integration with OPatch is not possible as OPatch is executed when the database is down and datapatch requires the database to be open to complete its activity.

The other customer provided all the logfiles – and I print the important logs only with the interesting part marked in RED:

==========================================
Contents of catupgrd_datapatch_upgrade.log
==========================================

SQL Patching tool version 12.2.0.0.0 on Tue Jul 14 13:10:39 2015

Copyright (c) 2014, Oracle. All rights reserved.

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Patch 19282028 (Database PSU 12.1.0.2.1, Oracle JavaVM Component ():

Installed in the binary registry only

Bundle series PSU:

ID 1 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 Tue Jul 14 13:10:57 2015

=============================================
Contents of sqlpatch_catcon__catcon_22773.lst
=============================================

catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts

catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

catconInit: start logging catcon output at 2015-07-14 13:10:39

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Ok, so it seems to be true that DBUA did not apply the post upgrade SQL changes associated with the most recent PSU.

DBUA 12c

Now digging a bit deeper we could solve the puzzle.

The DBUA uses the “catctl.pl -x” option executing catuppst.sql (the post upgrade script) in a separate step whereas on the command line catctl.pl will execute catuppst.sql by default (tracked with bug19990037). The DBUA uses catcon.pl instead to execute catuppst.sql. In previous releases this was not an issue as catbundle.sql got automatically executed as part of catuppst.sql. But as datapatch.pl is a PERL script, and a PERL script cannot be run from within a SQL script, catuppst.sql can no longer call the post-patching activities. The DBUA in 12.1.0.2 misses this action as a separate task.

Summary and Solution

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

And again, this is only necessary when you used the DBUA for a database upgrade. This step is not required for the command line upgrade. This will be fixed in an upcoming release of the DBUA.

If you are in doubt whether the DBUA or the command line upgrade had been used, unfortunately you won’t find any indication inside the database. But look into $ORACLE_BASE/cfgtoollogs/dbua/logs – if the “dbua” directory exists, the DBUA had been used. If not than the command line upgrade had been processed.

Related Blog Posts datapatch.pl:

–Mike

Share this: