Blog Oracle 12c Datapatch

4. August 2015 Johannes Ahrends

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:

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]

done

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:

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.

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!

Johannes Ahrends

2 comments on “Oracle 12c Datapatch”

Vipul

Hi,
I have installed R12.2.0 on windows 2012 R2. I have installed vision instance using express option, on single node.Now i am upgrading to R12.2.7 . i have applied latest Opatch and database patch 21821214 successfully. Datapatch is failing with error “catconInit: database is not open on the default instance”.After days of struggle i found it is failing because of no PDB found.Datpatch -verbose -debug produced following information.
user_pdbs:
Connecting to database…OK
not container database!
$VAR1 = ‘Data::Dumper’;
$VAR2 = {
‘undef’ => {
‘startup_mode’ => ‘READ WRITE’,
‘pdb_name’ => undef,
‘bootstrap_log’ => undef
}
};
$VAR1 = ‘Data::Dumper’;
$VAR2 = [
undef
];

catconInit failed, exiting

when i ran
SQL> select * from v$pdbs;

no rows selected

SQL> select cdb from V$database;

CDB
—
NO
Default installation does not create anyy PDB. I am looking for your help for the this issue.
Why datapatch is looking for the PDB even though it found no CBD
Can you please let me know if i can run datapatch without looking for PDB?
Do i need to create PDB? and then run?
i appreciate for your suggestion on the issue
Regards
Vipul