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

Posted on July 20, 2015 by DBUA Patch Recommendation 33

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:

Tags:

You may also like...

33 Responses

  1. Markus says:
    July 21, 2015 at 07:25

    Hi,

    Is this also relevant for database version upgrades with DBUA or for applying PSU/CPU’s only?

    Regards,
    Markus

    Reply
  2. Mike says:
    July 21, 2015 at 07:52

    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

    Reply
  3. Kais says:
    July 22, 2015 at 06:06

    Hi Mike ,
    How to know which tool used for upgrade
    to 12.1.0.2 ?
    Thanks.
    Kais

    Reply
  4. Mike says:
    July 22, 2015 at 07:38

    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

    Reply
  5. Ehtiram says:
    August 17, 2015 at 10:12

    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

    Reply
  6. guest says:
    September 5, 2015 at 17:19

    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

    Reply
  7. Mike says:
    September 7, 2015 at 07:45

    Jim,

    I’ll come back later to you – waiting on feedback from a colleague.

    Cheers
    Mike

    Reply
  8. Mike says:
    September 8, 2015 at 20:20

    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

    Reply
  9. Tanja Injac says:
    December 3, 2015 at 21:57

    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

    Reply
  10. Mike says:
    December 4, 2015 at 08:15

    Tanja,

    sorry for this inconvenience – I know that this is really a terrible issue.

    Thanks for your comment!!!

    Cheers
    Mike

    Reply
  11. Mike says:
    December 10, 2015 at 09:11

    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

    Reply
  12. Freek says:
    April 6, 2016 at 17:04

    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

    Reply
  13. Catherine says:
    June 3, 2016 at 09:36

    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?

    Reply
  14. Mike Dietrich says:
    June 15, 2016 at 14:42

    @Catherine

    Catherine,

    does this help you:
    Oracle Support Document 20284155.8 (Bug 20284155 – Datapatch fails as it cannot determine the current opatch status) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20284155.8

    Otherwise please check with an SR with Oracle Support as there are several issues with datapatch as far as I can see.

    Cheers
    Mike

    Reply
  15. Catherine says:
    June 15, 2016 at 14:46

    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

    Reply
  16. guest says:
    October 27, 2016 at 16:56

    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?

    Reply
  17. Mike says:
    October 28, 2016 at 09:13

    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

    Reply
  18. Mike says:
    January 26, 2017 at 16:42

    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

    Reply
  19. Liron Amitzi says:
    February 11, 2018 at 06:26

    Hey Mike,
    I’m upgrading from 12.1 to 12.2 now and have the same question. The even more complex scenario is that I also have JVM in the database. I installed clean 12.2, and originally thought about installing the latest RU + OJVM patches and then upgrade the database. According to this DBUA won’t run datapatch (or maybe it’s fixed in 12.2), in any case, because I need to install both RU (27105253 in my case) and OJVM (27001739) and both ask for datapatch script, I suspect they overwrite each other’s script. I will install the RU and upgrade the database and then install the OJVM, but I wish Oracle would solve that, or at least document this somehow.
    Thanks
    Liron

    Reply
    • Liron Amitzi says:
      February 11, 2018 at 08:26

      Hi Mike,
      Just a quick update, it seems that DBUA 12.2 runs datapatch. After the upgrade I saw that the DBUA executed the datapatch for the RU (in the logs and in the database). Then I installed the OJVM patch and executed the datapatch for it.
      Liron

      Reply
      • Mike.Dietrich says:
        February 18, 2018 at 17:37

        Hi Liron,

        sorry for the late reply – I was out-of-office the past week.
        And yes, since the patch level I mentioned in the blog post (and this includes Oracle 12.2) the DBUA does run datapatch.pl (and the same applies to DBCA as well). The problem existed only in Oracle 12.1.0.2 with no patch or a patch level below the mentioned one. And generally for DBCA in Oracle 12.1.0.2 (it didn’t execute datapatch).

        Regarding RU+OJVM no worries – the sql scripts are the ones which matter – and datapatch executes them regardless of which patch you install first.

        Cheers,
        Mike

        Reply
  20. Sunil says:
    February 26, 2018 at 23:19

    We have 12.1.0.2 databases running on Windows 2008 /2012 servers.

    While applying PSUs, datapatch returns NOTHING To APPLY.
    DO we need some specific permissions to run datapatch on Windows

    Reply
    • Mike.Dietrich says:
      February 28, 2018 at 08:36

      Sunil,

      at first I believe you had applied Bundle Patches and not PSUs. But regardless of it, there were issues with datapatch not rolling back things correctly. Actually my ref customer, Universal Investment, had a similar issue with some 2017 Bundles – and I saw it with my own eyes.

      PLEASE log an SR for it. Support needs to know about this and may have a solution (manual treatment of the patch views in the database).

      Thanks,
      Mike

      Reply
  21. Michael Hüttner says:
    May 25, 2018 at 13:09

    Hello Sunil,
    we have 12102 and 12201 both on Windows Server 2012R2. datapatch ends with
    Nothing to roll back
    Nothing to apply for CDB and PDBs. Oracle-Support is struggling with our SR since the beginning of february.
    No solution so far. Until now i found out that “%ORACLE_HOME%\QOPatch\qopiprep.bat” does not execute even with full rights for everybody. As a consequence of this
    SYS@SID> select * from OPATCH_XML_INV; brings –> no rows selected
    Applocker is not responsible for the blocking of the bat-file and we are currently inspecting our GPOs and local user rigths to find out what prevents qopiprep.bat from executing.
    I’d like to know if you could solve your problem and what it was all about.

    On a standalone Windows10-notebook with no Domain-GPOs both oracle-versions work without any error.
    The whole (very annoying) case prevents us from upgrading our production-systems.

    Thanks, Michael

    Reply
  22. Sunil says:
    August 23, 2018 at 15:46

    Michael,

    After working with Microsoft, we found that
    Oracle database datapatch is not compatible with Windows Software Restriction Policy (SRP). The work around is to remove such policies from the registry.

    Reply
  23. parjesh Dutt says:
    September 3, 2019 at 16:43

    Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /home/oracle//catupgrd_datapatch_upgrade.log 2> /home/oracle//catupgrd_datapatch_upgrade.err
    returned from sqlpatch
    Time: 49s
    Serial Phase #:66 Files: 1 Time: 111s
    Serial Phase #:68 Files: 1 Time: 0s
    Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH; LIBPATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LIBPATH; LD_LIBRARY_PATH_64=/u01/app/oracle/product/12.1.0.2/db_1/lib; export LD_LIBRARY_PATH_64; DYLD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib; export DYLD_LIBRARY_PATH; /u01/app/oracle/product/12.1.0.2/db_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin -I /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /home/oracle//catupgrd_datapatch_normal.log 2> /home/oracle//catupgrd_datapatch_normal.err
    returned from sqlpatch
    Time: 13s
    Serial Phase #:70 Files: 1 Time: 25s
    Serial Phase #:71 Files: 1 Time: 0s
    Serial Phase #:72 Files: 1 Time: 0s
    Serial Phase #:73 Files: 1 Time: 3s

    Grand Total Time: 3101s

    LOG FILES: (catupgrd*.log)

    Grand Total Upgrade Time: [0d:0h:51m:41s]

    Reply
  24. parjesh Dutt says:
    September 3, 2019 at 16:48

    below error in catupgrd.log

    stderr from running datapatch to install upgrade SQL patches and PSUs:
    catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_25952838.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
    stdout from running datapatch to install non-upgrade SQL patches and PSUs:
    SQL Patching tool version 12.2.0.0.0 on Tue Sep 3 11:07:19 2019
    Copyright (c) 2014, Oracle. All rights reserved.

    Connecting to database…Database connect failed with:
    ORA-01089: immediate shutdown or close in progress – no operations are permitted (DBD ERROR: OCISessionBegin)

    Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.

    SQL Patching tool complete on Tue Sep 3 11:07:19 2019
    stderr from running datapatch to install non-upgrade SQL patches and PSUs:
    Can’t call method “prepare” on an undefined value at /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin/../../sqlpatch/sqlpatch.pm line 741.

    –however upgrade is successful none of the components are invalid but the registry$history doesn’t show the upgrade status.

    Reply
  25. parjesh says:
    September 4, 2019 at 12:49

    Thanks Mike
    After applying the latest july’19 BP problem is fixed.

    Regards
    Parjesh Dutt

    Reply
  26. parjesh Dutt says:
    September 4, 2019 at 12:52

    One Query Mike

    I believe now no need to execute #65 and #69 again.
    Please confirm,

    Regards
    Parjesh

    Reply
    • Mike.Dietrich says:
      September 4, 2019 at 19:48

      Parjesh,

      you can run “datapatch -verbose” manually from the $ORACLE_HOME/OPatch directory – it will tell you if additional changes are necessary.
      That’s enough 🙂

      Cheers,
      Mike

      Reply
  1. No OS Authentication? datapatch will fail in every upgrade | Upgrade your Database - NOW!
    April 1, 2017

    […] DBUA 12c and “datapatch.pl” – things to know (Jul 20, 2015) https://mikedietrichde.com/2015/07/20/dbua-12c-and-datapatch-pl-things-to-know/ […]

  2. DBCA 12c and “datapatch.pl” – things to know | Upgrade your Database - NOW!
    April 7, 2017

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,328 other subscribers

Follow me on Twitter