Oracle 12c Datapatch

With Oracle 12c the SQL commands belonging to one patch are not installed by [inlinecode]catbundle.sql[/inlinecode]but by the “datapatch” tool, which is located in the OPatch directory. Other than [inlinecode]catbundle.sql[/inlinecode] 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 [inlinecode]dba_registry_sqlpatch[/inlinecode], 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 [inlinecode]sqlpatch[/inlinecode] script in the [inlinecode]$ORACLE_HOME/sqlpatch[/inlinecode] directory. And this script loads a Perl script called [inlinecode]sqlpatch.pl[/inlinecode].

In the [inlinecode]sqlpatch[/inlinecode] directory you find subdirectories with all the patch numbers and in these (or in another subdirectory) the [inlinecode]apply[/inlinecode] and the [inlinecode]rollback[/inlinecode] SQL script for the particular patch.

Until now this is not that exciting and this simple patching works like this:

1. Stop the database
2. Execute [inlinecode]opatch apply[/inlinecode]
3. Start the database
4. Execute [inlinecode]datapatch [-verbose][/inlinecode]
5. done

Alternatively you can also execute [inlinecode]opatchauto[/inlinecode], as this runs both the [inlinecode]opatch[/inlinecode] and the [inlinecode]datapatch[/inlinecode] 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 [inlinecode]datapatch[/inlinecode] 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 [inlinecode]DBCA[/inlinecode]running [inlinecode]datapatch[/inlinecode] 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 [inlinecode]datapatch[/inlinecode] 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 [inlinecode]dba_registry_sqlpatch[/inlinecode], 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 12.1.0.2             SUCCESS                  4 PSU

At this point I can just recommend to run this [inlinecode]SELECT[/inlinecode] command in every 12c database to make sure the patches are not only installed but also applied in the database.

With a multitenant database [inlinecode]datapatch[/inlinecode] 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 [inlinecode]datapatch[/inlinecode] for PDBs that you create afterwards from the template.

Addendum 2016-04-25

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!

Addendum 2020-03-31

Since Version 18 (not 12.2) Oracle changed the columns of the view dba_registry_sqlpatch. The above query will no longer work. Instead you can use a quey like this:

SQL> SELECT patch_id, patch_type, status, description
     FROM dba_registry_sqlpatch;

PATCH_ID PATCH_TYPE STATUS      DESCRIPTION
---------- ---------- ----------- --------------------------------------------------------
  29834717 RU         SUCCESS     Database Release Update : 19.4.0.0.190716 (29834717)
  30557433 RU         SUCCESS     Database Release Update : 19.6.0.0.200114 (30557433)