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

A few weeks ago I did blog about the DBUA (Database Upgrade Assistant) not executing ‘datapatch’ (i.e. not applying the SQL changes involved with a SPU/PSU/BP) automatically:

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

For DBUA, please note that this behavior DOES NOT APPLY to command line upgrades done with catctl.pl – as you can see from this somewhat disturbing messages during the upgrade in phase 65 and phase 69 (which are not errors but just informational messages for datapatch’s execution):

Datapatch phases command line upgrade catctl.pl

But afterwards I have learned that things are worse.
The same behavior is true when you create a database.

Not a typo.
You create a fresh database with DBCA (Database Configuration Assistant), you are a honest customer, you have followed our advice and applied the most recent PSU (or SPU or BP) into your Oracle Home. You don’t even deploy one of the preconfigured databases but use the CREATE CUSTOM DATABASE option of DBCA. And the database will run from the patched home – but the SQL changes haven’t been applied to it.

Reason?

DBCA does not call ‘datapatch’ for database changes.

I consider this even worse than the DBUA behavior as the person who upgrades a database in most cases is aware of the future home. But the person who either deploys a new database or asks for one to be deployed is often not identical with the person who did patch the homes.

And there’s no warning displayed yet nor (afaik) is there a MOS note available talking about it.

How do you fix the issue?

After creating a new database make sure to run:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

and double check with DBA_REGISTRY_SQLPATCH view:

select  PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
from DBA_REGISTRY_SQLPATCH
order by BUNDLE_SERIES;

Actually to be 100% you may find some patch information in DBA_REGISTRY_SQLPATCH showing that the JAVAVM patch has been applied in case you’ve installed the Combo version of the PSU. But you’ll miss the database changes.

Important Note

This misbehavior applies to all databases in Oracle Database 12.1.0.2, regardless of which PSU or BP you are using as unless for the DBUA the issue has not been fixed. Please be aware!

Related Blog Posts datapatch.pl:

Oracle Database 12.2.0.1

Please see:

Does DBCA execute “datapatch” in Oracle 12.2?

–Mike


Addition – Sept 18, 2015:

Please be aware that the same thing happens on ODA (Oracle Database Appliance) with the oakcli. This will be fixed in the version 12.1.2.5 (ODA/oakcli)

26 thoughts on “DBCA 12c and “datapatch.pl” – things to know

  1. Hello Mike,

    Executing "datapatch" after a new Oracle 12c DB creation was also the conclusion of a SR which I created in December 2014 (FYI, SR 3-9964155761). During resolution of this SR the Oracle support engineer also stated that "datapatch" should be executed separately after a new DB was created.

    He also told that this behavior is due to 2 unpublished bugs. He could not tell me by when this behavior would be fixed (= no longer executing "datapatch" seperately after creation of a new 12c DB) as he stated the following : "As of now, it has been not decided in which version this will be fixed. Once done, this information will be updated in the PSU readmes."

    Within this SR it was also stated that "datapatch" does not need be executed seperately after a manual DB upgrade to Oracle 12c as it would be executed automatically as part of the upgrade script catctl.pl.

    I really find it a pity that this very important information for all Oracle customers is not put somewhere in a MOS note (ex. "Note 1683799.1 : 12.1.0.2 Patch Set – Availability and Known Issues" or "Note 1609718.1 : Datapatch Known Issues").

    Luckily there are still people like you with this fantastic blog in order to spread the word ;-).

    Greetings,

    Chris

  2. Chris,

    I appologize that this isn’t in a MOS Note yet. But my colleagues are working on this – a MOS Note should be available soon.

    And sorry for this inconvenience – I just learned this as well a few weeks ago.

    You are right – this does not happen with the command line upgrade with catctl.pl – but it does happen in any case where a graph tool is involved as the change from catpatch.sql or catbundle.sql being purely SQL scripts to the PERL mechanism of datapatch came too late for the GUI folks to embed something to call PERL scripts as well.

    This all will be fixed in the next major release of Oracle (12c Release 2).

    Thanks
    Mike

  3. I would rather check the applied patches in the dba_registry_sqlpatch view. I am not sure if the DBA_SQL_PATCHES will have information about the applied patch.

    Cheers,
    Gleb

  4. In 12c DBUA notes, this information has been added
    Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)

  5. Hello Mike,

    nice to hear about this, our upgrade was six months ago…
    Can I start datapatch against a running production database, or is a downtime needed?

    Thanks
    Sven

  6. Actually, command line upgrades are affected as well, if you do not use OS authentication. Apparently, datapatch is not able to execute in non-OS authentication mode. See MOS note 1635007.1.

  7. Hello Mike,

    In your post of 09/09/2015 you answered that "datapatch" does not require downtime on the question from Sven who asked whether datapatch can be executed on a running production database, or whether a downtime is needed. In fact the database might need to be started in "upgrade" mode as one of the post-SQL patches to be applied by "datapatch" might require this (ex. in case of a Patch Set Update). In this case there will thus be a small downtime needed for the running production database as in order to be able to execute "datapatch" it needs to be stopped and restarted in "upgrade" mode which also means that only users with the privilege "restricted session" can connect to the DB. This is described on Metalink in 1585822.1 – Datapatch: Database 12c Post Patch SQL Automation.

    Greetings,

    Chris

  8. Hello Mike,

    In your post of 09/09/2015 you answered that "datapatch" does not require downtime on the question from Sven who asked whether datapatch can be executed on a running production database, or whether a downtime is needed. In fact the database might need to be started in "upgrade" mode as one of the post-SQL patches to be applied by "datapatch" might require this (ex. in case of a Patch Set Update). In this case there will thus be a small downtime needed for the running production database as in order to be able to execute "datapatch" it needs to be stopped and restarted in "upgrade" mode which also means that only users with the privilege "restricted session" can connect to the DB. This is described on Metalink in 1585822.1 – Datapatch: Database 12c Post Patch SQL Automation.

    Greetings,

    Chris

  9. Hi Chris,

    you are referring to this note:
    https://support.oracle.com/epmos/faces/DocumentDisplay?id=1585822.1

    It says:
    "If a patch requires upgrade mode startup, datapatch enforces that via a pre-req check. To overcome this error, DB/PDB needs to be re-started in Upgrade/Migrate mode to complete patching. Please note that when there are a number of simultaneous patch applications pending, datapatch will fail the upgrade mode pre-req check if any of the pending patches requires upgrade mode startup. Any patches not requiring upgrade mode can be installed by invoking datapatch with the –apply bug # option "

    But this is valid for the OJVM patches only, not for the regular database patches as they are always rolling applicable. For this topic (OJVM) I can refer to this blog post:
    https://blogs.oracle.com/security/entry/october_2014_critical_patch_update

    So basically my answer to Sven was directed to database-only patches.

    Cheers 🙂
    Mike

  10. Hi Mike,
    thanks for your heads up!

    When I ran ./datapatch -verbose against a previously created database using RDBBMS 12.1.0.2.0 + "Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)" + "Database Patch Set Update : 12.1.0.2.4 (20831110)" + Sub-patch 20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)" + Sub-patch 19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)" its output told me that I had to start the database in UPGRADE mode, which means (afaik) that the database has to be shutdown’d and restarted with UPGRADE flag. That would incur downtime, wouldn’t it?

    Best regards,
    Andre

  11. Andre,

    yes this is expected because of the OJVM patch included. "Database PSU 12.1.0.2.4, Oracle JavaVM Component (Jul2015)"

    If you’d like to avoid this restart then please use the PSU *without* the OJVM.

    Oracle Support Document 20831110.8 (Bug 20831110 – 12.1.0.2.4 (Jul 2015) Database Patch Set Update (DB PSU)) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20831110.8
    is the same PSU but *without* the OJVM patch – and thus "rolling installable" – it requires no restart of the database nor does it require "upgrade mode".

    Hope this helps – thanks
    Mike

  12. Hi Mike,

    On september 18th you mention that this also applies to oakcli on the ODA’s, but that it is fixed with 12.1.2.5.

    I have updated multiple ODA’s to that version but cannot see that the patches have been applied on the databases. The query you mention:

    select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
    from DBA_REGISTRY_SQLPATCH
    order by BUNDLE_SERIES;

    Returns no rows.

    If I’m correct it means that I still should run:
    cd $ORACLE_HOME/OPatch
    ./datapatch -verbose

    That probably means that patches AFTER applying ODA Patch 12.1.2.5.0 will not have this issue anymore?

    Regards and thank you for your great work on database upgrades. It saves me time again and again.

    Patrick

  13. Patrick,

    it should be fixed with 12.1.0.2.5 – but Roy and I have seen other issues with datapatch not updating the patch information correctly.

    I hate to say this but please refer to Oracle Support via MOS as they need to know if something is not updated correctly in the patch view.

    Thanks
    Mike

  14. Hi,

    I can confirm that this is fixed now in version 12.1.0.2.6 of the ODA software, since below is the result after creating a database using the oakcli tool

    SQL> l
    1 select PATCH_ID, PATCH_UID, VERSION, STATUS, DESCRIPTION
    2 from DBA_REGISTRY_SQLPATCH
    3* order by BUNDLE_SERIES
    SQL> /

    PATCH_ID PATCH_UID VERSION STATUS
    ———- ———- ——————– —————
    DESCRIPTION
    ——————————————————————————–
    21948354 19553095 12.1.0.2 SUCCESS
    Database Patch Set Update : 12.1.0.2.160119 (21948354)

    SQL>

  15. Hi Mike,

    what if installed the RDBMS binaries then applied the lastest PSU before creating any database , do i still need to run datapatch ?

    Regards,
    Sam

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

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

  18. Hi Mike,

    as things seem to have changed with Oracle 12c Release 2, maybe it’s time for an update of this post (or a new post 🙂 )

    Regards
    Markus

    • Hi Markus,

      I haven’t tried it with 12.2.0.1’s DBCA yet. Will post an update as soon as the first BP is available for 12.2. 😉

      Thanks for the reminder,
      Mike

  19. Pingback: Does DBCA execute "datapatch" in Oracle Database 12.2? – Upgrade your Database - NOW!

Leave a Reply

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