Comparing Oracle database PSU patching with DBCA between 11.2.0.4 and 12.1.0.2

Whether you upgrade a database or patch a database with a given PSU two major components are modified:

  • the Oracle Database Home files (binaries, libraries, etc.) are updated
  • databases using the involved Oracle Database Home must be updated: dictionary objects are generally modified with the PSU SQL script.
  • If you use Database Creation Assistant (DBCA) to create a database using an Oracle Database home with a PSU patch, DBCA will apply the PSU SQL script to the newly created database.

    In this article I compare what DBCA is supposed to do and what it really does for 11.2.0.4 and 12.1.0.2 versions.

    I have used OTN Hands On Lab virtual machine where Oracle 11.2.0.4 PSU 8 and Oracle 12.1.0.2 PSU 5 are installed.

    Oracle Database 11.2.0.4

    Using Oracle 11.2.0.4 PSU 8 …

    $ $ORACLE_HOME/OPatch/opatch lspatches
    21352635;Database Patch Set Update : 11.2.0.4.8 (21352635)
    
    OPatch succeeded.
    $ 
    

    … I have created a new 11.2.0.4 database with DBCA:

    dbca -silent \
    -createDatabase \
    -templateName General_Purpose.dbc \
    -gdbName D112 \
    -sid D112 \
    -SysPassword oracle \
    -SystemPassword oracle \
    -emConfiguration NONE \
    -datafileDestination /oradata/ \
    -storageType FS \
    -characterSet AL32UTF8 \
    -memoryPercentage 20 \
     
    

    I have checked that PSU SQL script has been run on this new database:

    SYS@D112>select to_char(action_time,'DD-MON-YYYY HH24:MI') as action_time_2, action, namespace, version,  comments
      2  from dba_registry_history
      3  order by action_time;
    
    ACTION_TIME_2              ACTION     NAMESPACE  VERSION    COMMENTS
    -------------------------- ---------- ---------- ---------- --------------------
    24-AUG-2013 12:03          APPLY      SERVER     11.2.0.4   Patchset 11.2.0.2.0
    27-SEP-2016 16:02          APPLY      SERVER     11.2.0.4   PSU 11.2.0.4.8
    
    

    Oracle Database 12.1.0.2

    Using Oracle 12.1.0.2 PSU 5 …

    $ $ORACLE_HOME/OPatch/opatch lspatches
    21539301;
    21359755;Database Patch Set Update : 12.1.0.2.5 (21359755)
    
    OPatch succeeded.
    
    

    … I have created a new non container 12.1.0.2 database with DBCA:

    dbca -silent \
    -createDatabase \
    -templateName General_Purpose.dbc \
    -gdbName D121 \
    -sid D121 \
    -SysPassword oracle \
    -SystemPassword oracle \
    -emConfiguration NONE \
    -datafileDestination /oradata/ \
    -storageType FS \
    -characterSet AL32UTF8 \
    -memoryPercentage 20 \
    

    I have tried to check that SQL PSU script has been run on this new database but DBA_REGISTRY_HISTORY is empty:

    select to_char(action_time,'DD-MON-YYYY HH24:MI') as action_time_2, action, namespace, version,  comments
      2  from dba_registry_history
      3  order by action_time;
    
    no rows selected
    
    

    Actually there is a new view DBA_REGISTRY_SQLPATCH but it is also empty:

    SYS@D121>select to_char(action_time,'DD-MON-YYYY') as action_time_2, patch_id, patch_uid, action, version,  description
      2  from dba_registry_sqlpatch
      3  order by action_time;
    
    no rows selected
    
    

    Note that above documentation also says:

    Support note 1585822.1 “Datapatch: Database 12c Post Patch SQL Automation” at My Oracle Support at the following URL for more information about datapatch:


    This My Oracle Support note says that datapatch is the new tool to apply PSU SQL script to databases.

    I have run it:

    $ $ORACLE_HOME/OPatch/datapatch -verbose
    SQL Patching tool version 12.1.0.2.0 on Tue Sep 27 16:40:34 2016
    Copyright (c) 2015, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7377_2016_09_27_16_40_34/sqlpatch_invocation.log
    
    Connecting to database...OK
    Bootstrapping registry and package to current versions...done
    Determining current state...done
    
    Current state of SQL patches:
    Patch 21539301 ():
      Installed in the binary registry only
    Bundle series PSU:
      ID 5 in the binary registry and not installed in the SQL registry
    
    Adding patches to installation queue and performing prereq checks...
    Installation queue:
      Nothing to roll back
      The following patches will be applied:
        21359755 (Database Patch Set Update : 12.1.0.2.5 (21359755))
        21539301 ()
    
    Installing patches...
    Patch installation complete.  Total patches installed: 2
    
    Validating logfiles...
    Patch 21359755 apply: SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_D121_2016Sep27_16_40_56.log (no errors)
    Patch 21539301 apply: SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21539301/19298399/21539301_apply_D121_2016Sep27_16_41_01.log (no errors)
    SQL Patching tool complete on Tue Sep 27 16:41:10 2016
    

    I have checked again DBA_REGISTRY_SQLPATCH view:

    SYS@D121>select to_char(action_time,'DD-MON-YYYY') as action_time_2, patch_id, patch_uid, action, version,  description
      2  from dba_registry_sqlpatch
      3  order by action_time;
    
    ACTION_TIME_2          PATCH_ID  PATCH_UID ACTION     VERSION    DESCRIPTION
    -------------------- ---------- ---------- ---------- ---------- --------------------------------------------------
    27-SEP-2016            21359755   19194568 APPLY      12.1.0.2   Database Patch Set Update : 12.1.0.2.5 (21359755)
    27-SEP-2016            21539301   19298399 APPLY      12.1.0.2
    
    

    Now it looks OK.

    The fact that DBCA did not run datapatch is not a feature but simply a bug according to Mike Dietrich blog.

    This bug should only be fixed with Oracle 12.2.

    Conclusion

    Oracle Database version Is DBCA supposed to apply PSU SQL script ? Does DBCA actually apply PSU SQL script ? Dictionary view for PSU SQL script
    11.2.0.4 Yes Yes DBA_REGISTRY_HISTORY
    12.1.0.2 Yes NO DBA_REGISTRY_SQLPATCH

    So do not forget for a newly database created with DBCA:

  • to check the right PSU SQL script view
  • to run datapatch for a 12.1.0.2 database.
  • Advertisements
    Post a comment or leave a trackback: Trackback URL.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: