With Oracle 12c the SQL commands belonging to one patch are not installed by catbundle.sqlbut by the “datapatch” tool, which is located in the OPatch directory. Other than catbundle.sql datapatch checks before if the requirement for the installation are met and if it is actually necessary running those SQL commands. It is also being checked if the database is in upgrade mode. The result of the “SQL Apply” can queried in the view dba_registry_sqlpatch, i.e. like this:
SQL> SELECT patch_id, version, status, bundle_id, bundle_series FROM dba_registry_sqlpatch;
If you have a closer look on datapatch, you notice that it is link to the sqlpatch script in the $ORACLE_HOME/sqlpatch directory. And this script loads a Perl script called sqlpatch.pl.
In the sqlpatch directory you find subdirectories with all the patch numbers and in these (or in another subdirectory) the apply and the rollback SQL script for the particular patch.
Until now this is not that exciting and this simple patching works like this:
- Stop the database
- Execute opatch apply
- Start the database
- Execute datapatch [-verbose]
Alternatively you can also execute opatchauto, as this runs both the opatch and the datapatch subsequently.
This first looks quite easy and well considered and it actually works pretty well. But there is a twist: When upgrading a database with the DBUA the datapatch is not run automatically. Mike Dietrich already pointed out those issues in his blog (and I put it down as well in this blog: Oracle 12c DBUA and Datapatch).
Until now I thought that this issue only occurs with the DBUA, but that is not the case. You see that when creating a database from a template with the DBCArunning datapatch can be necessary as the template does not already include the patches. Still it is not clear why that is necessary when creating a database entirely new with a “Custom Database”, too. I thought the “wrong” SQL scripts would be replaced by new ones. That is not the case!
This means you must always load datapatch after creating a database, to make sure the database itself and not only the software is “patched”.
My Oracle Support says this: “It’s not a bug, it’s a feature.” (read at Bug 19920083: DBCA NOT RUNNING DATAPATCH AFTER DATABASE IS CREATED).
You can again check the successful installation of the patches in the view dba_registry_sqlpatch, i.e. like this:
SQL> SELECT patch_id, version, status, bundle_id, bundle_series FROM dba_registry_sqlpatch; PATCH_ID VERSION STATUS BUNDLE_ID BUNDLE_SERIES ---------- -------------------- --------------- ---------- ---------------- 20831110 126.96.36.199 SUCCESS 4 PSU
At this point I can just recommend to run this SELECT command in every 12c database to make sure the patches are not only installed but also applied in the database.
With a multitenant database datapatch is run for all pluggable databases automatically, which are opened for writing, and for the PDB$SEED as well. Of course, that means you have to ensure that all PDBs are opened. But you do not need to run datapatch for PDBs that you create afterwards from the template.
With PSU 5 from October 2015 the patch will be automatically installed while creating a new database. So you do not need to run datapatch. Unfortunately this is no longer true with the PSU 160119 from January 2016. I this case the PSU 5 will be automacially applied but not the actual PSU. So my advice is:
don’t rely on the installation but run datapatch after every installation or upgrade of a 12c database!