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.


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

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

  1. Markus,

    I updated the post – it is relevant to every upgrade to Oracle 12.1 with DBUA as soon as there’s a SPU/PSU/BP installed into the target home before upgrade (which I’d highly recommend as patching before upgrade saves headache after upgrade).

    Cheers
    Mike

  2. Good question, Kais 🙂

    Inside the DB you won’t get any indication whether the DBUA or catctl.pl directly have been used. But the log files will tell you.

    Look into:
    $ORACLE_BASE/cfgtoollogs/dbua/logs

    If this directory ("dbua") is there the DBUA had been used.

    Cheers
    Mike

  3. Hi Mike,

    I would like also to share some info on this subject.
    During upgrade tests which I performed, the new Db home was v. of 12.1.0.2 + PSU4(July, 2015). After upgrade was completed the stauts of the database query showed that datapatch run partially. The PSU was a combo PSU(Db and Java VM components) and the post patch actions related to Java VM were implemented, but related to DB PSU part were not. simple query of dba_registry_sqlpatch showed this. I think the reason was that, JVM PSU post patch steps require the db to be opened in upgrade mode, that is why dbua is obliged to run it. but for implementing DB PSU post patch part, we should run datapatch after the upgrade. The readme note of corresponding Db and Java VM PSU’s also says that. for upgraded databases, there are no post patch steps needed for Java VM patch. But for DB PSU patch , they do required.

    Regards,
    Ehtiram

  4. Mike,
    I need help. I have been tasked with creating a script that will upgrade an Oracle 11.2.0.3 database to 12.1.0.2 on Windows.

    Below is an outline of what I am doing:
    1.Pre-upgrade steps
    2.Backup database
    3.Switch the DB home to Oracle 12
    4.Upgrade DB (using catctl.pl)
    5.DBAU moved DB files using RMAN (I would just shut it down and move the files in the OS)
    6.Recreate control file
    7.Post upgrade steps (using catcon.pl)

    So far, I have not been able to run catctl.pl successfully. Here is the error:
    Serial Phase #: 0 Files: 1 next_proc: total processes (4) != number of li
    ve processes (3); giving up

    Oracle docs, blogs, and the Oracle community all provide different parameters for calling catclt.pl. I even ran DBUA and checked its logs to see how it ran catctl.pl and got yet another set of parameters.

    Help?

    JimW

  5. Jim,

    this is from my colleague Joe:

    This usually means that a SQLPlus processes has died. Phase 0 is where we do a lot of checking to make sure you can do the upgrade. If there is an error in catupstr.sql will terminate the SQL process and will cause this to happen in catctl.pl. catcon.pm will recognized that one process has gone away and terminate. Tell them to check his log files to see what is going on. You may see and error like the following in catupgrd0.log.

    ERROR at line 1:
    ORA-01722: invalid number

    Documentation should be in the log file on what to do about it like. Documentation would look something like this.

    DOC
    #######################################################################
    #######################################################################
    The following error is generated if (1) the old release uses a time
    zone file version newer than the one shipped with the new oracle
    release and (2) the new oracle home has not been patched yet:

    SELECT TO_NUMBER(‘MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME’)
    *
    ERROR at line 1:
    ORA-01722: invalid number

    o Action:
    Shutdown database ("alter system checkpoint" and then "shutdown abort").
    Patch new ORACLE_HOME to the same time zone file version as used
    in the old ORACLE_HOME.

    #######################################################################
    #######################################################################
    #

    Could you check the logs please?

    Cheers
    Mike

  6. Exactly the same problem we had today. We upgraded to 12.1.0.1.5 (12.1 software pre-patched with PSU5), but got ORA-600 bug that is supposedly fixed in PSU5.

    On a further investigation, we’ve noticed PSU wasn’t listed in registry$sqlpatch, so we ran
    ./datapatch -verbose
    and that fixed it.

    Regards,
    Tanja

  7. Tanja,

    patch is included in the 12.1.0.2BP13 but not 12.1.0.2.PSU5 (Oct) – it will be included in the 12.1.0.2.PSU6 (Jan 2016) and all following PSUs. Too late for you but I’ll update the above blog post as well with this information.

    Mike

  8. Hi,

    Just wanted to let you know, that I have just done an upgrade with dbua (using silent upgrade) to an oracle home with the Jan 2016 PSU and datapatch info was still missing in upgraded database.
    So it seems that the patch was not included in the Jan 2016 PSU bundle:

    [oracle@inframan ~]$ opatch lspatches
    21948354;Database Patch Set Update : 12.1.0.2.160119 (21948354)

    OPatch succeeded.

    SQL> select
    patch_id,
    patch_uid,
    version, status,
    description
    from
    dba_registry_sqlpatch
    order
    by bundle_series
    ;
    2 3 4 5 6 7 8 9 10
    no rows selected

  9. Hi Mike
    I have applied PSU 22291141 to my Oracle 12c DB version 12.1.0.2.0 using opatch apply but am having problems running datapatch, I get the error:
    Queryable inventory could not determine the current opatch status.
    Execute ‘select dbms_sqlpatch.verify_queryable_inventory from dual’

    and when running qopatch queries such as the following I get these errors:
    SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
    ERROR:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00229: input source is empty
    ORA-06512: at "SYS.XMLTYPE", line 272
    ORA-06512: at line 1
    ORA-06512: at "SYS.DBMS_QOPATCH", line 1293

    no rows selected

    SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;
    ERROR:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00229: input source is empty
    ORA-06512: at "SYS.XMLTYPE", line 272
    ORA-06512: at line 1
    ORA-06512: at "SYS.DBMS_QOPATCH", line 1293

    no rows selected

    SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;
    ERROR:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00229: input source is empty
    ORA-06512: at "SYS.XMLTYPE", line 272
    ORA-06512: at line 1
    ORA-06512: at "SYS.DBMS_QOPATCH", line 285
    ORA-06512: at line 1

    I have been unable to find a solution as to what is the problem here, have you come across these issues at all and could you please advise any possible solution?

  10. Hi Mike

    I actually solved it based on another one of your blogs, looks like I had the wrong version of OPatch!! Thanks anyway for the reply!
    Catherine

  11. Hi Mike,

    I have the following issue. Database is running in 12.1.0.2.4; next to this we installed the PSU Jul2016 12c release. I want to apply the PSU on the database. Restart database in the PSU release and apply the psu:
    datapatch -apply 23054246 (Database PSU) -bundle_series -force -verbose.

    The result is that the apply ends with errors due to the fact that DVSYS is not installed. We are not using DataVault but due to this the datapatch ends in error and in the dba_registry_sqlpatch the status is "ended with errors".

    I already created SR3-13523140392, resulted in spin off 3*13506939601 but I did not get any usefull feedback till now….

    So apparently the datapatch script is trying to patch the DVSYS schema, but if not present, it results in an error.
    PSU has been applied to the rest of the db, but final result is 100% clean.

    Any idea?

  12. Thanks – I checked your SRs and there are fundamental incorrect assumptions in there: DVSYS is NOT present if you haven’t installed DV. None of my databases has DVSYS.

    Datapatch shouldn’t be an issue as for bundle patches we have the component information from bundledata.xml,
    so we have always been able to only install if the component was ok in the registry.

    If I get your case right (but I haven’t seen this by myself – and I patched my environments in July with the PSU (11.2.0.4) and the BP (12.1.0.2) you have no DV or DVSYS but datapatch -verbose fails.

    I can’t solve this – you’ll have to work with Support until this is fixed and you have a final confirmation.

    Cheers
    Mike

  13. Marvin,

    thanks for the hint – and I wasn’t aware of the new " -skip_upgrade_check" flag.

    I can only assume that the UPGRADE mode requirements comes in because of the OJVM patch included in the fix applied.

    And I can only recommend you to file an SR so these things get logged.

    Cheers
    Mike

  14. Pingback: No OS Authentication? datapatch will fail in every upgrade | Upgrade your Database - NOW!

  15. Pingback: DBCA 12c and “datapatch.pl” – things to know | Upgrade your Database - NOW!

Leave a Reply to guest Cancel reply

Your email address will not be published. Required fields are marked *