Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch

Posted in: Technical Track

Background

Oracle Database 12c brings us many new features including: the long needed ability to run OPatch and query the patch software installed in the Oracle Home programatically through SQL using the new DBMS_QOPATCH package.

If you’re a DBA working in an environment where patching consistently among databases is important, this is a welcomed enhancement. Sometimes one-off critical bug fix patches are important in the environment. Other times regular and consistent application of the quarterly PSUs is important to the organization (i.e. for regulatory compliancy). For sake of illustration I’m going to use the later as my need case for the duration of this article as I’ve regularly run into the situation where clients need PSUs applied properly, regularly, and consistently to their databases.

As a recap, many Oracle Patches including PSUs require a two-step process to implement properly. First the software change needs to be applied to the associated Oracle Home via the OPatch utility. Secondly it needs to be installed into all databases running in that Oracle Home (possibly updating internal packages and objects).

With Oracle 11g the latest patch is applied within the database using:

SQL> @catbundle.sql psu apply

Oracle 12c introduces a new utility called “datapatch” which replaces the need to run the 11g command shown above.

As with Oracle 11g you first install the patch into the Oracle Home with all services & databases down. But with Oracle Database 12c after restarting the database simply run datapatch from the OPatch directory:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

 

Given that patching is a two-step process, some common DBA questions are:

1) Has a patch been installed in a given database?
2) Has a patch been installed into the $ORACLE_HOME via Opatch but not one or more of the databases?
3) Is the patch in the database but not the $ORACLE_HOME?

You may be thinking that #3 isn’t very likely but the reality is that it does happen. Circumstances that lead to a database being at a higher patch level than the Oracle Home include:

A) If the database was create from an RMAN duplicate from a home with a patch to one without.
B) A data guard standby switch-over or fail-over where the primary Oracle home was patched but the standby home was missed.
C) A 12c PDB that was unplugged from a patched CDB and plugged into an unpatched CDB.
D) The patch was uninstalled via Opatch from the Oracle home without uninstalling from the database (unlikely).

Since patching is a two-step process, historically answering questions such as these is also a two step process requiring the DBA to query the Oracle Home inventory and the database and to manually correlate and reconcile the results. This is cumbersome, prone to human errors, and not scalable due to the manual component.

But with Oracle 12c and the new DBMS_QOPATCH package, both can be queried programatically and joined using SQL allowing the DBA to easily report on questions like 1, 2, and 3 above via SQL queries, custom extensions to monitoring tools, reporting programs, etc. Or just to report accurately on patch deployments for security compliance reports, general database security assessments, or database health checks.

This is actually quite a significant breakthrough with regards to patch management!

 

How it works

The first thing to note is that in Oracle Database 12c there are some new Directory Objects installed by default:

SQL> select owner, directory_name, directory_path from dba_directories
  2  where directory_name like 'OPATCH%' order by 2;

OWNER        DIRECTORY_NAME       DIRECTORY_PATH
------------ -------------------- --------------------------------------------------------------------------------
SYS          OPATCH_INST_DIR      /u01/app/oracle/product/12.1.0/dbhome_1/OPatch
SYS          OPATCH_LOG_DIR       /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch
SYS          OPATCH_SCRIPT_DIR    /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch

SQL>

 

Two of those point to a new QOpatch directory which contains a batch file which runs the OPatch utility:

SQL> !ls /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch
qopatch_log.log  qopiprep.bat

SQL> !cat /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
#!/bin/sh
#
# $Header: rdbms/src/client/tools/qpinv/qopiprep.bat /unix/2 2013/04/04 20:59:27 tbhukya Exp $
#
# qopiprep.bat
#
# Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      qopiprep.bat - bat file for preprocessor
#
#    DESCRIPTION
#      bat file for external table preprocessor.
#
#    NOTES
#      .
#
#    MODIFIED   (MM/DD/YY)
#    tbhukya     04/03/13 - Bug 16226172 : Forward merge of fix 16076845
#    tbhukya     09/23/12 - Creation
#
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH

# Option: "-retry 0" avoids retries in case of locked inventory.

# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options
# ("-jdk" is another good candidate that may require configuration!).

# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory.  With that information, the
# patches of non-RDBMS homes could be fetched.


$ORACLE_HOME/OPatch/opatch lsinventory -xml  $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt

 

The description text in this Oracle provided file states that it’s a batch file used for external table preprocessing. The preprocessor clause was added in Oracle 11g release 2 (and back-ported to Oracle11gR1) to allow for execution of an external script/file to process data before reading it in the database via an external table. A further explanation of this feature is beyond the scope of this article.

Consequently we know an external table is involved and it’s not too hard to find and understand that:

SQL> select owner, table_name from dba_external_tables where table_name like 'OPATCH%' order by 1,2;

OWNER        TABLE_NAME
------------ --------------------
SYS          OPATCH_XML_INV

SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."OPATCH_XML_INV"
   (    "XML_INVENTORY" CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
      DISABLE_DIRECTORY_LINK_CHECK
      READSIZE 8388608
      preprocessor opatch_script_dir:'qopiprep.bat'
      BADFILE opatch_script_dir:'qopatch_bad.bad'
      LOGFILE opatch_log_dir:'qopatch_log.log'
      FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        xml_inventory    CHAR(100000000)
      )
        )
      LOCATION
       ( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
       )
    )
   REJECT LIMIT UNLIMITED


SQL>

 

Hence we can understand the underlying mechanics of this new feature. It’s based on an external table using a directory object running an external batch file (through the external table preprocessor option) which then runs the actual OPatch utility.

And we can query that external table directly if we want though we’ll get an XMLTYPE result:

SQL> select * from OPATCH_XML_INV;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance> <ora
cleHome> <UId>OracleHome-0243b34c-d8db-43f7-946b-589110de0ef9</UId> <targetTypeI
d>oracle_home</targetTypeId> <inventoryLocation>/u01/app/oraInventory</inventory
Location>
...
(output truncated)

 

Using DBMS_QOPATCH

Oracle gives us a wrapper API to this new external table (OPATCH_XML_INV) with DBMS_QOPATCH to make working with the output more manageable.

For example we can start with some basic information on the inventory using DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO :

SQL> set heading off long 50000
SQL> select dbms_qopatch.get_opatch_install_info from dual;

<oracleHome><UId>OracleHome-a59380fa-5f8e-42df-b624-282f0189ec93</UId><targetTyp
eId>oracle_home</targetTypeId><inventoryLocation>/u01/app/oraInventory</inventor
yLocation><isShared>false</isShared><patchingModel>oneoff</patchingModel><path>/
u01/app/oracle/product/12.1.0/dbhome_1</path><targetTypeId>oracle_home</targetTy
peId></oracleHome>

SQL>

 

But as we see the output still isn’t easy on the eyes. Fortunately the XML stylesheet is also presented using DBMS_QOPATCH.GET_OPATCH_XSLT function. Hence combining the two gives a much more readable output:

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) from dual;


Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory


SQL>

 

The DBMS_QOPATCH package provides many other useful functions and procedures. For example, to see if and when a specified patch was installed, or just to see the list of all of the patches installed by bug number:

SQL> select xmltransform(dbms_qopatch.is_patch_installed('19303936'), dbms_qopatch.get_opatch_xslt) from dual;


Patch Information:
         19303936:   applied on 2014-12-20T13:54:54-07:00


SQL> select xmltransform(dbms_qopatch.get_opatch_bugs, dbms_qopatch.get_opatch_xslt) from dual;


  Bugs fixed:
          19157754  18885870  19303936  19708632  19371175  18618122  19329654
19075256  19074147  19044962  19289642  19068610  18988834  19028800  19561643
19058490  19390567  18967382  19174942  19174521  19176223  19501299  19178851
18948177  18674047  19723336  19189525  19001390  19176326  19280225  19143550
18250893  19180770  19155797  19016730  19185876  18354830  19067244  18845653
18849537  18964978  19065556  19440586  19439759  19024808  18952989  18990693
19052488  19189317  19409212  19124589  19154375  19279273  19468347  19054077
19048007  19248799  19018206  18921743  14643995

SQL>

 

Or to run the equivalent of “opatch lsinventory” but from SQL instead of the OS:

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;


Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------

Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                                    12.1.0.2.0
Installed Products ( 135)

Oracle Database 12c                                         12.1.0.2.0
Java Development Kit                                        1.6.0.75.0
...
(output truncated)

 

Putting it all together

The examples above are interesting but to make it truly useful for patch application querying, monitoring, and reporting we need join the output of DBMS_QOPATCH (which is showing us what’s installed into the Oracle Home) with the new 12c view DBA_REGISTRY_SQLPATCH (which shows us what patches are applied to the database).

Prior to Oracle 12c to list the PSUs installed into the Oracle home we might use:

SQL> !$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'DATABASE PSU|DATABASE PATCH SET UPDATE'
19303936 19303936 Sat Dec 20 13:54:54 MST 2014 DATABASE PATCH SET UPDATE 12.1.0.2.1 (OCT2014)

SQL>

 

I obtained that result by shelling out from a sqlplus session running on the database server. But likely a centralized SQL based monitoring or reporting tool won’t have that ability. A client-server database connection won’t be able to run OPatch easily and hence the DBMS_QOPATCH API is required.

To extract the same information from the DBMS_QOPATCH package we need to parse the resulting XMLTYPE output:

SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2  select x.*
  3    from a,
  4         xmltable('InventoryInstance/patches/*'
  5            passing a.patch_output
  6            columns
  7               patch_id number path 'patchID',
  8               patch_uid number path 'uniquePatchID',
  9               description varchar2(80) path 'patchDescription',
 10               applied_date varchar2(30) path 'appliedDate',
 11               sql_patch varchar2(8) path 'sqlPatch',
 12               rollbackable varchar2(8) path 'rollbackable'
 13         ) x;

  PATCH_ID  PATCH_UID
---------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
APPLIED_DATE                   SQL_PATC ROLLBACK
------------------------------ -------- --------
  19303936   18116864
Database Patch Set Update : 12.1.0.2.1 (19303936)
2014-12-20T13:54:54-07:00      true     true


SQL>

 

This is a great result: Information on the PSUs installed into the Oracle Home through SQL and returned to us in an easy to read and easy to work with tabular form!

To make this information even more useful we need to join to the new DBA_REGISTRY_SQLPATCH view which replaces the DBA_REGISTRY_HISTORY view with respect to which patches have been applied in the database:

SQL> select patch_id, patch_uid, version, status, description
  2  from dba_registry_sqlpatch
  3  where bundle_series = 'PSU';

  PATCH_ID  PATCH_UID VERSION              STATUS
---------- ---------- -------------------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
  19303936   18116864 12.1.0.2             SUCCESS
Database Patch Set Update : 12.1.0.2.1 (19303936)


SQL>

 

Joining these two outputs allows us to easily write queries that report on the PSU patches deployed and ones only partially deployed (whether in the Oracle Home but not the database or vice versa):

SQL> --
SQL> --   List of PSUs applied to both the $OH and the DB
SQL> --
SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2  select x.patch_id, x.patch_uid, x.rollbackable, s.status, x.description
  3    from a,
  4         xmltable('InventoryInstance/patches/*'
  5            passing a.patch_output
  6            columns
  7               patch_id number path 'patchID',
  8               patch_uid number path 'uniquePatchID',
  9               description varchar2(80) path 'patchDescription',
 10               rollbackable varchar2(8) path 'rollbackable'
 11         ) x,
 12         dba_registry_sqlpatch s
 13   where x.patch_id = s.patch_id
 14     and x.patch_uid = s.patch_uid
 15     and s.bundle_series = 'PSU';

  PATCH_ID  PATCH_UID ROLLBACK STATUS
---------- ---------- -------- ---------------
DESCRIPTION
--------------------------------------------------------------------------------
  19303936   18116864 true     SUCCESS
Database Patch Set Update : 12.1.0.2.1 (19303936)


SQL> --
SQL> --   PSUs installed into the $OH but not applied to the DB
SQL> --
SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2  select x.patch_id, x.patch_uid, x.description
  3    from a,
  4         xmltable('InventoryInstance/patches/*'
  5            passing a.patch_output
  6            columns
  7               patch_id number path 'patchID',
  8               patch_uid number path 'uniquePatchID',
  9               description varchar2(80) path 'patchDescription'
 10         ) x
 11  minus
 12  select s.patch_id, s.patch_uid, s.description
 13    from dba_registry_sqlpatch s;

no rows selected

SQL> --
SQL> --   PSUs applied to the DB but not installed into the $OH
SQL> --
SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
  2  select s.patch_id, s.patch_uid, s.description
  3    from dba_registry_sqlpatch s
  4  minus
  5  select x.patch_id, x.patch_uid, x.description
  6    from a,
  7         xmltable('InventoryInstance/patches/*'
  8            passing a.patch_output
  9            columns
 10               patch_id number path 'patchID',
 11               patch_uid number path 'uniquePatchID',
 12               description varchar2(80) path 'patchDescription'
 13         ) x;

no rows selected

SQL>

Simple queries such as those three are what can be incorporated into monitoring scripts and reports.

Some other DBMS_QOPATCH functions worth trying include:

set heading off long 50000 pages 9999 lines 180 trims on tab off
select xmltransform(dbms_qopatch.get_opatch_count, dbms_qopatch.get_opatch_xslt) from dual;
select xmltransform(dbms_qopatch.get_opatch_list, dbms_qopatch.get_opatch_xslt) from dual;
select xmltransform(dbms_qopatch.get_pending_activity, dbms_qopatch.get_opatch_xslt) from dual;

set serverout on
exec dbms_qopatch.get_sqlpatch_status;

 

Back-porting to Oracle Database 11g

An interesting question is: “can we back port this approach to 11g” and the answer is “absolutely“!

First of all, we need to create the directory object, external table, and OS batch script. For simplicity I’m keeping the name and structure of each the same as in 12c but of course you can adjust them if desired.

Using DBMS_METADATA.GET_DDL on an Oracle 12c database I get the necessary DDL to add the directory objects and external tables to an 11g database (note that I’ve updated the Oracle Home path but that’s the only change):

CREATE OR REPLACE DIRECTORY "OPATCH_LOG_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';
CREATE OR REPLACE DIRECTORY "OPATCH_SCRIPT_DIR" AS '/u01/app/oracle/product/11.2.0/dbhome_1/QOpatch';

CREATE TABLE "SYS"."OPATCH_XML_INV"
   (    "XML_INVENTORY" CLOB
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
      DISABLE_DIRECTORY_LINK_CHECK
      READSIZE 8388608
      preprocessor opatch_script_dir:'qopiprep.bat'
      BADFILE opatch_script_dir:'qopatch_bad.bad'
      LOGFILE opatch_log_dir:'qopatch_log.log'
      FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
      MISSING FIELD VALUES ARE NULL
      REJECT ROWS WITH ALL NULL FIELDS
      (
        xml_inventory    CHAR(100000000)
      )
        )
      LOCATION
       ( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
       )
    )
   REJECT LIMIT UNLIMITED;

 

Next I need to copy the preprocessor batch script into the 11g home (which can be copied from another machine if necessary):

!cp -r /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch /u01/app/oracle/product/11.2.0/dbhome_1/QOpatch

 

Now I don’t want to install the DBMS_QOPATCH package into a different version of the database. It’s Oracle supplied “wrapped” code meaning I can’t modify it and it probably has 12c dependencies meaning it would be invalid in Oracle 11g. Instead I performed a SQL Trace on the DBMS_QOPATCH.GET_OPATCH_LSINVENTORY package executing on the 12c database and from the resulting trace file I see that all it’s doing is:

INSERT INTO OPATCH_XINV_TAB(XML_INVENTORY) SELECT * FROM OPATCH_XML_INV
SELECT XMLTYPE(XML_INVENTORY) FROM OPATCH_XINV_TAB
DELETE FROM OPATCH_XINV_TAB

 

Not exactly complicated. Hence using the directory objects and external table created above I can execute the following on the 11g database:

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

SQL> select XMLTYPE(XML_INVENTORY) patch_output from OPATCH_XML_INV;

PATCH_OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="US-ASCII"?>
<INVENTORY isStandAlone="false">
 <HEADER>
 <ORACLE_HOME>/u01/app/oracle/product/11.2.0/dbhome_1</ORACLE_HOME>
 <CENTRAL_INVENTORY>/u01/app/oraInventory</CENTRAL_INVENTORY>
 <OPATCH_VERSION>11.2.0.3.6</OPATCH_VERSION>
 <OUI_VERSION>11.2.0.4.0</OUI_VERSION>
 <OUI_LOCATION>/u01/app/oracle/product/11.2.0/dbhome_1/oui</OUI_LOCATION>
 <LOG>/u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-1
2-23_15-08-04PM_1.log</LOG>
 </HEADER>
...
(output truncated)

 

So as we can see, it’s working perfectly back-ported to 11g.

However again the XML output isn’t really useful for me, I’m more interested in what PSUs have been installed. It’s easy to check that using opatch if I’m on the server:

SQL> !$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'DATABASE PSU|DATABASE PATCH SET UPDATE'
18522509   18522509  Sun Sep 21 20:58:00 MDT 2014   DATABASE PATCH SET UPDATE 11.2.0.4.3 (INCLUDES CPU
18031668   18031668  Sun Sep 21 20:57:48 MDT 2014   DATABASE PATCH SET UPDATE 11.2.0.4.2 (INCLUDES CPU
17478514   17478514  Sun Sep 21 20:57:38 MDT 2014   DATABASE PATCH SET UPDATE 11.2.0.4.1 (INCLUDES CPU

 

But again what if I’m not on the server, want to do this programatically through SQL, build it into a monitoring query or a security compliance report, etc?

Again we can query the XMLTYPE data and get exactly what we want. Specifically:

SQL> with a as (select XMLTYPE(XML_INVENTORY) patch_output from OPATCH_XML_INV)
  2  select x.* from a, xmltable(
  3     'INVENTORY/HOST/HOME/ONEOFF_LIST/INTERIM_PATCH/oneoff_inventory/base_bugs/bug'
  4     passing a.patch_output columns
  5     --row_number for ordinality,
  6     bug_number number path '@number',
  7     bug_description varchar2(256) path '@description'
  8  ) x
  9  where regexp_like(bug_description, '(DATABASE PSU|DATABASE PATCH SET UPDATE)','i')
 10  order by bug_number;

BUG_NUMBER BUG_DESCRIPTION
---------- ------------------------------------------------------------
  17478514 DATABASE PATCH SET UPDATE 11.2.0.4.1 (INCLUDES CPUJAN2014)
  18031668 DATABASE PATCH SET UPDATE 11.2.0.4.2 (INCLUDES CPUAPR2014)
  18522509 DATABASE PATCH SET UPDATE 11.2.0.4.3 (INCLUDES CPUJUL2014)

SQL>

 

Voila! By copying the technique used by the Oracle 12c database we’ve now performed an OPatch query from SQL against an 11g database. Just took about 2 minutes of setup.

Again this can be super handy if PSU patching is a regular activity for you or if you have to produce security compliance reports.

Unfortunately though Oracle 11g records less data in DBA_REGISTRY_HISTORY than 12c has in DBA_REGISTRY_SQLPATCH:

SQL> select comments, action_time from dba_registry_history
  2  where bundle_series like '%PSU' order by action_time;

COMMENTS                       ACTION_TIME
------------------------------ ------------------------------
PSU 11.2.0.4.3                 23-SEP-14 09.21.34.702876 AM

SQL>

 

Hence joining the two is more challenging but certainly possible. The data (in this case the string “11.2.0.4.3”) is present in both the DBA_REGISTRY_HISTORY and my XQuery output from my back-ported OPATCH_XML_INV external table output.

 

Conclusion

The new DBMS_QOPATCH API is a fantastic improvement with Oracle Database 12c when it comes to patch management (querying/reporting/monitoring). And by understanding the concepts and components added to 12c they can be manually back-ported to 11g. Both using DBMS_QOPATCH and even the back-port to 11g is actually pretty easy. In fact the hardest part for me (not being very familiar with XQuery) was coming up with the query to parse the XMLTYPE data.

 

References

http://docs.oracle.com/database/121/ARPLS/d_qopatch.htm

Oracle Recommended Patches — Oracle Database (Doc ID 756671.1)

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)

email

Interested in working with Simon? Schedule a tech call.

About the Author

Simon describes himself as a technology enthusiast who is passionate about collecting and sharing interesting database tips. If you want to see his eyes light up, let him teach you something new. Based out of Calgary, Alberta, Simon is known for his contributions to various online Oracle communities, and being very thorough in his work. A self-proclaimed stereotypical Canadian, Simon can be found watching hockey with his family in his spare time.

19 Comments. Leave new

Thank you for this valuable article.
Foued

Reply

Thanks Simon,

great and very useful article to make patch management again a little bit more easier.

M.

Reply
Suresh Karthikeyan
January 30, 2015 12:13 am

Good article, Thanks Simon!

Reply

Thank you for detailed examples and scripts. Thanks, Vineela

Reply

Also, thank you for the reference note “How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration (Doc ID 278641.1)”…clears my doubts about applying rdbms patch in Oracle production which has a physical standby. Thanks, Vineela

Reply

Glad it helped.

Reply
Janice Toews
March 2, 2015 12:55 pm

Thanks, Simon;

Great article.

Reply

Nice article, but I am finding the enhancement quite buggy and a bit of a headache:

BUG 20284155 – DATAPATCH HIT CAN’T DETERMINE CURRENT OPATCH STATU ON 1 OF 2 DBS

Reply
Zane Appel
May 7, 2015 3:12 pm

This is very useful for a current problem I have. Thank you. There is one small typo in your query for “PSUs applied to the DB but not installed into the $OH”. It has an extra comma at the end.

Reply
Simon Pane
May 7, 2015 3:41 pm

Thanks for pointing out the extra comma typo Zane. Seemed to be from pasting into the blogging software. I’ve corrected it now.

Reply

I did an interesting test where I patched a CDB with one of the PDBs not open. The patching succeeds but when you try to open the unpatched PDB the following error is returned:

SQL> alter pluggable database all open;

Warning: PDB altered with errors.

SQL>

A little investigation shows that the unpatched PDB (called “MY_TEST_PDB”) is open but in restricted session mode:

SQL> SELECT * FROM pdb_plug_in_violations;

TIME NAME CAUSE TYPE ERROR_NUMBER LINE
—————————- —————————— ———— ——— ———— ———-
MESSAGE
————————————————————————————————————————
STATUS ACTION
——— ————————————————–
14-MAY-15 01.54.39.373958 PM MY_TEST_PDB SQL Patch ERROR 0 1
PSU bundle patch 1: Installed in Database Patch Set Update : 12.1.0.2.1 (19303936) but not in the CDB.
PENDING Call datapatch to install in the PDB or the CDB

SQL> SELECT s.con_id, s.patch_id, s.patch_uid, s.description
2 FROM cdb_registry_sqlpatch s order by 1,2;

CON_ID PATCH_ID PATCH_UID
———- ———- ———-
DESCRIPTION
—————————————————————————————————-
1 19303936 18116864
Database Patch Set Update : 12.1.0.2.1 (19303936)

3 19303936 18116864
Database Patch Set Update : 12.1.0.2.1 (19303936)

4 19303936 18116864
Database Patch Set Update : 12.1.0.2.1 (19303936)

SQL> SELECT c.inst_id, p.con_id, pdb_name, open_mode, status, logging, force_logging,
2 force_nologging, restricted, recovery_status, total_size, open_time
3 FROM dba_pdbs p, gv$containers c
4 WHERE p.con_id = c.con_id
5 ORDER BY p.con_id;

INST_ID CON_ID PDB_NAME OPEN_MODE STATUS LOGGING FOR FOR RES RECOVERY TOTAL_SIZE OPEN_TIME
———- ———- ———————— ———- ——— ——— — — — ——– ———- ——————————–
1 2 PDB$SEED READ ONLY NORMAL LOGGING NO NO NO ENABLED 838860800 14-MAY-15 01.54.04.004 PM -06:00
1 3 PLUG1 READ WRITE NORMAL LOGGING NO NO NO ENABLED 969932800 14-MAY-15 01.54.21.636 PM -06:00
1 4 PLUG2 READ WRITE NORMAL LOGGING NO NO NO ENABLED 969932800 14-MAY-15 01.54.21.636 PM -06:00
1 5 MY_TEST_PDB READ WRITE NORMAL LOGGING NO NO YES ENABLED 859832320 14-MAY-15 01.54.21.646 PM -06:00

SQL> connect scott/tiger@MY_TEST_PDB
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL>

Running datapatch again patches the PDB but doesn’t automatically disable restricted session. Instead I need to do that manually:

$ ./datapatch -verbose -pdbs MY_TEST_PDB
SQL Patching tool version 12.2.0.0.0 on Thu May 14 14:21:01 2015
Copyright (c) 2014, Oracle. All rights reserved.

Connecting to database…OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_11330.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 1 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks…
Installation queue:
For the following PDBs: MY_TEST_PDB
Nothing to roll back
The following patches will be applied:
19303936 (Database Patch Set Update : 12.1.0.2.1 (19303936))

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

Validating logfiles…
Patch 19303936 apply (pdb MY_TEST_PDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/19303936/18116864/19303936_apply_CDB1_MY_TEST_PDB_2015May14_14_21_12.log (no errors)
SQL Patching tool complete on Thu May 14 14:21:13 2015
$

In sqlplus:

SQL> SELECT restricted FROM v$containers WHERE con_id = 5;

RES

YES

SQL> alter session set container = MY_TEST_PDB;

Session altered.

SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> SELECT restricted FROM v$containers WHERE con_id = 5;

RES

NO

SQL> SELECT s.con_id, s.patch_id, s.patch_uid, s.description FROM cdb_registry_sqlpatch s order by 1,2;

CON_ID PATCH_ID PATCH_UID DESCRIPTION
———- ———- ———- —————————————————————————————————-
5 19303936 18116864 Database Patch Set Update : 12.1.0.2.1 (19303936)

SQL>

And finally, the message still does appear in PDB_PLUG_IN_VIOLATIONS however the STATUS column has been updated to “RESOLVED”.

Similar situations are described in MOS note: 1635482.1

Reply

This is useful. Thanks.

Reply
H_name M_name
January 29, 2016 5:07 am

Thank you Simon, a fantastically good article!

Reply

Thank you , This is a great article

Reply

Thank you, nice arcticle.

I’ve checked my DB … and I found out that some of patches has not been applied into database but there ar installed in OH.
How I can load them manually ?

I did:
– installed SW 12.1.0.2.0
– installed
Patch 21948354 – Database Patch Set Update 12.1.0.2.160119 (Includes CPUJan2016)
Patch 22139226 – Oracle JavaVM Component 12.1.0.2.160119 Database PSU (Jan2016)
– then I created DB by DBCA …
– and then I loaded SQL files ..
startup upgrade
./datapatch -verbose

with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.*,s.status, s.action
from a,
xmltable(‘InventoryInstance/patches/*’
passing a.patch_output
columns
patch_id number path ‘patchID’,
— patch_uid number path ‘uniquePatchID’,
description varchar2(80) path ‘patchDescription’,
applied_date varchar2(21) path ‘appliedDate’,
sql_patch varchar2(8) path ‘sqlPatch’,
rollbackable varchar2(8) path ‘rollbackable’,
action varchar2(8) path ‘Action’
) x,
dba_registry_sqlpatch s; 2 3 4 5 6 7 8 9 10 11 12 13 14 15

PATCH_ID DESCRIPTION APPLIED_DATE SQL_PATC ROLLBACK ACTION STATUS ACTION
———- ——————————————————————————– ——————— ——– ——– ——– ————— —————
22139226 Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016) 2016-02-22T13:26:48Z true true SUCCESS APPLY
21948354 Database Patch Set Update : 12.1.0.2.160119 (21948354) 2016-02-22T13:21:32Z true true SUCCESS APPLY
21359755 Database Patch Set Update : 12.1.0.2.5 (21359755) 2016-02-22T13:21:27Z true true SUCCESS APPLY
20831110 Database Patch Set Update : 12.1.0.2.4 (20831110) 2016-02-22T13:21:24Z true true SUCCESS APPLY
20299023 Database Patch Set Update : 12.1.0.2.3 (20299023) 2016-02-22T13:21:22Z true true SUCCESS APPLY
19769480 Database Patch Set Update : 12.1.0.2.2 (19769480) 2016-02-22T13:21:19Z true true SUCCESS APPLY
22139226 Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016) 2016-02-22T13:26:48Z true true SUCCESS APPLY
21948354 Database Patch Set Update : 12.1.0.2.160119 (21948354) 2016-02-22T13:21:32Z true true SUCCESS APPLY
21359755 Database Patch Set Update : 12.1.0.2.5 (21359755) 2016-02-22T13:21:27Z true true SUCCESS APPLY
20831110 Database Patch Set Update : 12.1.0.2.4 (20831110) 2016-02-22T13:21:24Z true true SUCCESS APPLY
20299023 Database Patch Set Update : 12.1.0.2.3 (20299023) 2016-02-22T13:21:22Z true true SUCCESS APPLY
19769480 Database Patch Set Update : 12.1.0.2.2 (19769480) 2016-02-22T13:21:19Z true true SUCCESS APPLY

— List of PSUs applied to both the $OH and the DB
PATCH_ID PATCH_UID ROLLBACK STATUS DESCRIPTION
———- ———- ——– ————— ——————————————————————————–
21948354 19553095 true SUCCESS Database Patch Set Update : 12.1.0.2.160119 (21948354)

— PSUs installed into the $OH but not applied to the DB
PATCH_ID PATCH_UID DESCRIPTION
———- ———- —————————————————————————-
19769480 18350083 Database Patch Set Update : 12.1.0.2.2 (19769480)
20299023 18703022 Database Patch Set Update : 12.1.0.2.3 (20299023)
20831110 18977826 Database Patch Set Update : 12.1.0.2.4 (20831110)
21359755 19194568 Database Patch Set Update : 12.1.0.2.5 (21359755)

Thanks.

Reply

But it should be OK:

oracle@fradzb09008:/oracle/fctoolq/product/12.1.0.2/db_1/OPatch[FCTOOLQ]> ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Feb 25 06:04:20 2016
Copyright (c) 2015, Oracle. All rights reserved.

Log file for this invocation: /oracle/fctoolq/cfgtoollogs/sqlpatch/sqlpatch_29068_2016_02_25_06_04_20/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 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
Installed in the binary registry only
Bundle series PSU:
ID 160119 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:
22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016))
21948354 (Database Patch Set Update : 12.1.0.2.160119 (21948354))

Installing patches…
Patch installation complete. Total patches installed: 2

Validating logfiles…
Patch 22139226 apply: SUCCESS
logfile: /oracle/fctoolq/cfgtoollogs/sqlpatch/22139226/19729684/22139226_apply_FCTOOLQ_2016Feb25_06_04_51.log (no errors)
Patch 21948354 apply: SUCCESS
logfile: /oracle/fctoolq/cfgtoollogs/sqlpatch/21948354/19553095/21948354_apply_FCTOOLQ_2016Feb25_06_04_51.log (no errors)
SQL Patching tool complete on Thu Feb 25 06:04:55 2016

Reply

It shows that you have the most recent patch applied within the DB. Normally just check from the documentation that it’s cumulative. In which case it’s likely just a DB reporting issue on the cumulative patch (not showing the previous patches in the registry).

However the generic answer to the question is of course: “run datapatch”.

Reply
Nenad Noveljic
March 7, 2016 7:22 am

Thanks for this awesome overview Simon!
I would like to warn of bugs we can hit if we start datapatch concurrently on multiple databases.

Reply
Daniel Bogesdorfer
November 4, 2016 9:28 am

Simon – The readme for the patch states two different things. One is to “ensure all database instances” are started and the other is “for each database instance” . If I have 4 instances running out a an Oracle home how do I patch each database?

Here’s the Oracle Docs
The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.
1.Start the OracleService and Oracle Listener Services if already not started. Ensure that all database instances running out of the ORACLE_HOME that you just patched are started.

2.For each database instance running on the Oracle home being patched, run the datapatch utility as described in Table 2.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *