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:
- DBCA 12c and “datapatch.pl” – things to know (Aug 17, 2915)
- Does DBCA execute “datapatch” in Oracle 12.2? (May 25, 2017)
Does the DBCA execute “datapatch” when you create a new database
It varies. For Oracle 18.104.22.168 and 22.214.171.124 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 126.96.36.199 and Oracle 188.8.131.52 always run:
But for database environments since Oracle Database 184.108.40.206 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?