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

Share this: