Whether you upgrade a database or patch a database with a given PSU two major components are modified:
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.
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
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.
| 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: