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

18 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?

  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!

  4. 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

  5. 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

  6. 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

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.