Oracle DBA – A lifelong learning experience

Issue with Datapatch – AKA SQL Patching Tool after cloning a database

Posted by John Hallas on February 23, 2015

There have been a few changes in the way patches are managed and monitored in 12c and whilst looking at this I found a potential problem that might occur when you clone or copy databases around, or even build them from a template file.

Firstly when you apply a PSU and run an opatch lsinventory command you now see a description of the patch rather than just a patch number – here showing that PSU 1 has been applied. This came in at 11.2.0.3 and in my opinion is really helpful.

 

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :
Patch  19303936     : applied on Wed Feb 18 15:59:10 GMT 2015
Unique Patch ID:  18116864
Patch description:  "Database Patch Set Update : 12.1.0.2.1 (19303936)"
   Created on 6 Oct 2014, 12:01:37 hrs PST8PDT

 

The major change when applying a PSU in 12c is the datapatch utility otherwise known as the SQL Patching Tool. This is run as a post PSU installation step and populates a new view dba_registry_sqlpatch when the sql part of the PSU has been run against each database belonging to the Oracle Home that has just been patched.

I give an example below after I have applied 12.1.0.2 PSU2 to a home and then ran datapatch against the L19 database using that home.

 

[L19]/app/oracle/product/12.1.0.2/dbhome_1/OPatch $./datapatch -verbose
SQL Patching tool version 12.2.0.0.0 on Thu Feb 19 15:42:14 2015
Copyright (c) 2014, Oracle.  All rights reserved.
 Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_1739.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done
Determining current state...done
 Current state of SQL patches:
Bundle series PSU:
  ID 2 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:
    19769480 (Database Patch Set Update : 12.1.0.2.2 (19769480))

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 19769480 apply: SUCCESS
  logfile: /app/oracle/cfgtoollogs/sqlpatch/19769480/18350083/19769480_apply_L19_2015Feb19_15_42_46.log (no errors)
SQL Patching tool complete on Thu Feb 19 15:43:14 2015

 

However I did come across a couple of situations where the datapatch did not work  – giving the errors below

 $./datapatch -verbose
SQL Patching tool version 12.2.0.0.0 on Thu Feb 19 07:41:13 2015
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_18850.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Bootstrapping registry and package to current versions...done

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'
for the complete error.
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 for information on how to resolve the above errors.
SQL Patching tool complete on Thu Feb 19 07:41:42 2015

The MoS note referred to provides a list of documents associated with Datapatch errors but in my case was not very helpful.

The recommended sql to run gave me a bit of a clue but the log files did not provide an answer.

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------
ORA-22285: non-existent directory or file for FILEEXISTS operation

The answer was related to the lack of the correct directories. Three new directories are created to support datapatch

OPATCH_SCRIPT_DIR
/app/oracle/product/12.1.0.2/dbhome_2/QOpatch

OPATCH_LOG_DIR
/app/oracle/product/12.1.0.2/dbhome_2/QOpatch

OPATCH_INST_DIR
/app/oracle/product/12.1.0.2/dbhome_2/OPatch

 

In my case the database had been built from a pre-provisioned clone of another database. The database had a different name for the O_H – /app/oracle/product/12.1.0.2/dbhome_1

So the error message was correct, the directories were pointing to a directory that did not exist and the fix was very easy

drop directory OPATCH_SCRIPT_DIR;
drop directory OPATCH_LOG_DIR;
drop directory OPATCH_INST_DIR;
create directory  OPATCH_SCRIPT_DIR as '/app/oracle/product/12.1.0.2/dbhome_1/QOpatch';
create directory OPATCH_LOG_DIR as '/app/oracle/product/12.1.0.2/dbhome_1/QOpatch';
create directory OPATCH_INST_DIR as '/app/oracle/product/12.1.0.2/dbhome_1/OPatch';

 

So if you are likely to be using different oracle home names across servers and are copying, cloning, duplicating, pre-provisioning databases around it is worth running a post run script to check that the OPATCH directories are pointing to the correct Oracle Home.

It might also be worth writing an OEM script to check an environment to ensure that the path in the ORACLE_HOME is repeated in the OPATCH entries in DBA_DIRECTORIES

Simon Pane at Pythian has written a good blog entry around datapatch but in particular I recommend the script he has provided to see where an OH has not had a PSU applied but a database has or when a database has been added to a OH but has not had datapatch run against it

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: