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 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
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
Hi Mike,
first of all it is amazing how such an important fact is so poorly documented; so many thanks for pointing out this issue. However, I still do not understand why you have to run datapatch after creating a database from scripts with patched binaries. In your example one would think the relevant create package script in OH/rdbms/admin must also have been patched and thus loaded – or is that assumption incorrect?
Paul,
in my case I had to run it after I applied the patches. But not directly after creating the databases. And not after an upgrade. Both, at least in most versions, trigger the patch scripts via datapatch. Check the blog for:
https://mikedietrichde.com/2017/05/25/dbca-execute-datapatch-oracle-database-12-2/
https://mikedietrichde.com/2015/08/17/dbca-12c-and-datapatch-pl-things-to-know/
https://mikedietrichde.com/2015/07/20/dbua-12c-and-datapatch-pl-things-to-know/
https://mikedietrichde.com/2015/08/17/dbca-12c-and-datapatch-pl-things-to-know/
Cheers,
Mike
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 ?
What is 11.2.0.4, Roberto 😉 ? Just kidding 😉
In 11.2.0.4 the mechanism was different – you can start catbundle.sql directly from SQL Plus – and the same applies to DBCA and DBUA (in theory) 😉
Let me verify it – building a fresh 11.2.0.4 database in my environment now.
One thing I can say for sure:
As soon as you use a prebuild SEED database (General Purpose, OLTP or DWH) you’ll have to execute it as the database will be simply copied. This is fast but does not add any treatments automatically.
Cheers,
Mike
Roberto,
please see here:
https://mikedietrichde.com/2018/04/25/do-you-have-to-execute-catbundle-sql-when-you-create-a-new-11g-database/
Cheers and thanks for the question!
Mike
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!
Thanks Oscar!!!
Cheers,
Mike
Thanks Mike for clarifying … I do run my script …. so will have to add datapatch.
Regards
Atul
Hi Mike,
We are planning to upgrade 11.2.0.4 DBs to 12.1.0.2 using silent DBUA method.
Since we installed 12.1 Home without any DBs running out of it currently, we took this chance to apply required SPUs etc. since we don’t impact any services out of that home.
Now my Question is when we run actual Upgrade to 12.1 using Slient DBUA method, Do we still need to run datapatch AFTER upgrade is done or DBUA handles that for us ?
Thanks
Jalpan
Jalpan,
please see here:
https://mikedietrichde.com/2015/07/20/dbua-12c-and-datapatch-pl-things-to-know/
Cheers,
Mike
Hi Mike,
You are awesome. Great article.
Thanks for super quick reply with your busy time.
Also, such facts are documented on MOS or somewhere as many like me are not lucky enough to find this blog 🙂 ?
PS: Will surely catch you if and when you are in India for OUG sessions.
Jalpan,
thanks for your feedback. And glad if I could help.
Some of the information is not in MOS notes – that’s why I write about it from time to time 😉
Cheers,
Mike
Hi Mike,
I have installed EBS R12.2.0 on windows 2012 R2.I have done express intallation of Vision instance.As a first step, i am apply database patch 27440294, recommended by ETCC.Opatch suceeded but i am unable to run datapatch.It failing with error “catconInit: database is not open on the default instance catconInit failed, exiting” I have set Oracle_SID=VIS. also SET PERL5LIB=set PATH=%ORACLE_HOME%\perl\bin;%PATH% ;
E:\oracle\VIS\fs1\EBSapps\10.1.2\bin;E:\oracle\VIS\fs1\EBSapps\10.1.2\jdk\jre\bin\classic;E:\oracle\VIS\fs1\EBSapps\10.1.2\jdk\jre\bin;E:\oracle\VIS\fs1\EBSapps\10.1.2\jlib;E:\oracle\VIS\fs2\EBSapps\10.1.2\bin;E:\oracle\VIS\fs2\EBSapps\10.1.2\jdk\jre\bin\classic;E:\oracle\VIS\fs2\EBSapps\10.1.2\jdk\jre\bin;E:\oracle\VIS\fs2\EBSapps\10.1.2\jlib;E:\oracle\VIS\12.1.0\bin;C:\cygwin64\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;E:\oracle\VIS\fs2\FMW_Home\webtier\bin;E:\oracle\VIS\fs2\FMW_Home\webtier\opmn\bin;E:\oracle\VIS\fs2\FMW_Home\webtier\opmn\lib;E:\oracle\VIS\fs2\FMW_Home\webtier\perl\bin;E:\oracle\VIS\fs1\FMW_Home\webtier\bin;E:\oracle\VIS\fs1\FMW_Home\webtier\opmn\bin;E:\oracle\VIS\fs1\FMW_Home\webtier\opmn\lib;E:\oracle\VIS\fs1\FMW_Home\webtier\perl\bin;E:\oracle\vis\12.1.0\opatch.
FYI, my database is non container database.Also i was able to rollback one of the patch conflict to patch 27440294.
Can you please me to solve problem?
here is list of command executed on command prompt
E:\>CD ORACLE\VIS\12.1.0\BIN
E:\oracle\VIS\12.1.0\BIN>SET ORACLE_SID=VIS
E:\oracle\VIS\12.1.0\BIN>SQLPLUS / AS SYSDBA
SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 2 05:18:06 2018
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 3046080 bytes
Variable Size 469763392 bytes
Database Buffers 1090519040 bytes
Redo Buffers 13729792 bytes
Database mounted.
Database opened.
SQL> QUIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64
bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
E:\oracle\VIS\12.1.0\BIN>SET PERL5LIB= set PATH=%ORACLE_HOME%\perl\bin;%PATH%
E:\oracle\VIS\12.1.0\BIN>datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue Oct 2 05:22:32 2018
Copyright (c) 2012, 2018, Oracle. All rights reserved.
Log file for this invocation: E:\oracle\VIS\12.1.0\cfgtoollogs\sqlpatch\sqlpatch
_3688_2018_10_02_05_22_32\sqlpatch_invocation.log
Connecting to database…OK
catconInit failed, exiting
Please refer to MOS Note 1609718.1 and/or the invocation log
E:\oracle\VIS\12.1.0\cfgtoollogs\sqlpatch\sqlpatch_3688_2018_10_02_05_22_32\sqlp
atch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Tue Oct 2 05:22:35 2018
E:\oracle\VIS\12.1.0\BIN>Sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 2 05:28:05 2018
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> select open_mode from V$database;
OPEN_MODE
——————–
READ WRITE
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64
bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
E:\oracle\VIS\12.1.0\BIN>datapatch -verbose -debug
SQL Patching tool version 12.1.0.2.0 Production on Tue Oct 2 05:33:06 2018
Copyright (c) 2012, 2018, Oracle. All rights reserved.
initialize entry
params_ref: $VAR1 = ‘Data::Dumper’;
$VAR2 = \{
‘verbose’ => 1,
‘debug’ => 1
};
Creating invocation log dir E:\oracle\VIS\12.1.0\cfgtoollogs\sqlpatch\sqlpatch_3
828_2018_10_02_05_33_06
Log file for this invocation: E:\oracle\VIS\12.1.0\cfgtoollogs\sqlpatch\sqlpatch
_3828_2018_10_02_05_33_06\sqlpatch_invocation.log
SQL Patching arguments:
debug: 1
verbose: 1
force: 0
prereq: 0
upgrade_mode_only:
oh:
bundle_series:
ignorable_errors:
bootstrap:
skip_upgrade_check:
userid:
pdbs:
user_pdbs:
noqi:
Connecting to database…OK
not container database!
printing pdbs data
$VAR1 = ‘Data::Dumper’;
$VAR2 = {
‘undef’ => {
‘startup_mode’ => ‘READ WRITE’,
‘pdb_name’ => undef,
‘bootstrap_log’ => undef
}
};
$VAR1 = ‘Data::Dumper’;
$VAR2 = [
undef
];
catconInit failed, exiting
initialize complete, final configuration:
pdb_list:
apply_list:
rollback_list:
upgrade_mode_only:
force: 0
prereq_only: 0
user_oh:
bundle_series:
verbose: 1
debug: 1
database name: VIS
finalize entry
Please refer to MOS Note 1609718.1 and/or the invocation log
E:\oracle\VIS\12.1.0\cfgtoollogs\sqlpatch\sqlpatch_3828_2018_10_02_05_33_06\sqlp
atch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Tue Oct 2 05:33:07 2018
E:\oracle\VIS\12.1.0\BIN>
Hi Vipul,
this is clearly something you need to open an SR for.
Sorry that I can’t help but the reason you pay for a Support contract is that Support will assist you to sort this out.
Cheers,
Mike
Hi,
Do you know if there is any way to avoid line such as :
“Patch Description: ORA-600[PITL1] ON UPDATE TO COMPRESSED BLOCK WITH FIX FOR BUG 28364411 INSTALLED”
in alert.log, because ORA-600 is trapped as a real error by automatic checker soft…
Thanks for your reply
Laurent
Laurent,
I don’t think that there’s any way to prevent this. At least not that I have heard of any. But I see your issue 🙁
Cheers,
Mike
Do you have to re-run datapatch on any new PDB you created afterwards?
No – datapatch did patch the PDB$SEED – and thus, every new PDB has the changes as it gets provisioned as a clone with additions from the PDB$SEED.
Cheers,
Mike
This is an old post but I think my question is relevant here.
How soon does datapatch have to be run after running off the patched software ? This is an issue for me as I have some advanced queueing jobs running in my database, and I have found that I need to stop those before running datapatch.
If I do not stop them, datapatch will appear to run, but the jobs are affected and they, although still running, do not process the data in their incoming stream correctly.
So I am wondering, is it OK to run for some time, say 24 hours or even 7 days, without running datapatch although the software has been patched.
Hi Andy,
actually this is very hard to answer. At first, nothing usually brakes when you don’t run datapatch. At least not as far as I have seen yet. We had customers which forgot to run it. This becomes obvious when you apply the next RU as it will complain about this mismatch.
There is no rule – and there won’t be any.
If you’d ask Support, they will tell you: It is part of the patching process, and hence must be run once the patch had been applied.
Cheers,
Mike
Hi Mike,
I have two instances under oracle_home. Do I need to run datapatch for each sid after applying the patch?
thanks
Hi Steve,
yes, you need to set the ORACLE_SID correctly, make sure the database (and all PDBs are open), and then execture “datapatch -verbose”. This will load the SQL and PL/SQL changes into the database you directed it to with ORACLE_SID.
Cheers,
Mike
sorry, check_patches script reports 403. Is there a simple way to check if the database needs datapatch run?
Best Regards,
Bartlomiej Sowa
Sorry – old link – I corrected it:
https://mikedietrichde.com/scripts/
Cheers.
Mike
When we execute datapatch -verbose in 12c after binary patch applied successfully , some of the databases executed quickly .but some of the databases it’s taking a long time to complete the postscript. here my question is do we have any query or way to find out how much time or how much % remaining to complete the postscript. I am asking approximate ETA to complete the script.
Hi Ahmed,
it would be fab if datapatch could foresee this – but I fear this is impossible.
There could be many reasons for longer runtimes.
In my experience, two factors can speed up the execution generally:
a) recompile beforehand with utlrp.sql
b) access the inventory with “select * from OPATCH_XML_INV;” beforehand
Cheers,
Mike
Hi Mike, how does it apply to autoupgrade to 19c?
I am installing new homes with the -applyRU option of runInstaller to provide a oracle home with the latest patch. Do I need to apply the datapatch manually after upgrade? Is it safe to repeat a datapatch just to be sure?
Lastly, is it advisable to autoupgrade from a patched oracle home or should it be patched after the database upgrade.
Hi Xabier,
you never need to run datapatch manually when you invoked AutoUpgrade. Only in the case that you switch the home manually, you need to invoke database, let’s say when you flip from 19.17.0 to 19.18.0.
Use AutoUpgrade always in its freshest edition from MOS Note:2485457.1 – Download AutoUpgrade
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
since it will be newer than the one you consumed with the patch.
Cheers
Mike