Do you have to execute “datapatch” when you create a new database?

Do you have to execute "datapatch" when you create a new database?Thanks to Connor McDonald (Mr. AskTom) I learned today that one topic is not clear from our patches readmes: Do you have to execute “datapatch” when you create a new database? And it’s true. It does not get explained in the readme anywhere.

Do you have to execute “datapatch” when you create a new database?

If you never applied an Oracle patch bundles you may ask now: What is “datapatch” doing. And the answer is very simple. When you apply a patch bundle usually the patch does not include only binary files but also SQL changes, for instance a new package. These changes need to be applied to all your databases using the new binaries of the patch bundle. The Oracle Database Update 18.2.0 from April 2018 for instance includes the following section in the readme:

3.4 Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of this patch in the Oracle Home 

You must execute the steps in Section 3.3.2, “Loading Modified SQL Files into the Database” (ie, run datapatch) for any new database.

But actually here I have to differ between two cases when I applied a patch bundle to a new Oracle home:

  • You’ll create a new database with Database Configuration Assistant (DBCA)
  • You’ll create a new database with your own scripts

I did blog in the past several times about this topic with DBCA already:

Does the DBCA execute “datapatch” when you create a new database

It varies. For Oracle 12.1.0.1 and 12.1.0.2 databases you must execute datapatch after a database creation. This is a common pitfall and didn’t get fixed (afaik) for Oracle 12.1.

Rule: For Oracle 12.1.0.1 and Oracle 12.1.0.2 always run:

$ORACLE_HOME/OPatch/datapatch -verbose

But for database environments since Oracle Database 12.2.0.1 you don’t need to run datapatch after a database creation. The DBCA handles this for you.

What happens if you create a database with scripts?

But the other case would be that you create a database with your own scripts. And in this case no automatic execution of datapatch happens. This will lead to a situation where you applied a patch to an Oracle home and then create a new database with your own scripts. But the database, even though using the patched binaries, won’t have the required scripts applied to it.

Rule: For all databases you create with your own scripts, always call datapatch -verbose after creation.

You may use my simple check_patches.sql script to verify if your database has gotten the correct script treatments.

Are you looking for information on DBCA and Oracle 11g instead?

–Mike

8 thoughts on “Do you have to execute “datapatch” when you create a new database?

  1. Most of my databases were upgraded from 11g, so this doesn’t apply.
    However I found one or two new databases which still missed the datapatch run.
    What does datapatch actually do besides updating the dba_registry_sqlpatch view?
    Are the patches “inactive” until you run datapatch?

    • Rodolfo,

      datapatch applies all SQL changes associated with a patch bundle.
      For instamce, a binary security fix in a patch bundle may require also that a DBMS package in the database gets rebuilt and exchanged. If you apply the binary only you may not have the patch working correctly. There are also fixes which apply only to things in the database (may apply to the DBMS_PACKAGE example as well). So you miss part of the patch. The binary patches are not inactive – but they may not work correctly.

      Cheers,
      Mike

  2. Hi

    What about 11.2.0.4 ?
    If you create a new database with yur own scripts after creation you need to run the catbundle ?

  3. Thanks for clearing this up, this post was so timely! Literally the day after I read this article, a co-worker asks me the exact question in the title. Thanks!

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree