Thanks to Murthy who commented on this blog post and Jeannette Holland (SimCorp) who opened an SR resulting in an ER.

DBMS_QOPATCH in Multitenant

DBMS_QOPATCH will deliver useful information about installed patches only when executed within the CDB$ROOT. It has been designed this way for security reasons in Oracle Database 12.1 but I can easily see a need to check for installed patches within a PDB as well.

Testcase

I “borrowed” this test case from Jeannette’s SR:

SQL> COLUMN NAME FORMAT A8

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 NAME     CON_ID DBID       CON_UID    GUID
 -------- ------ ---------- ---------- ------------------
 CDB$ROOT      1 3424772713 1          47C8525C0DFE49...
 PDB$SEED      2 3983775695 3983775695 E6204BB1F6EB4F...
 MYPDB1        3 7270044002 7270044002 B975668B860049...
 MYPDB2        4 1943363979 1943363979 BCD7AAFAF3F641...

In a PDB:

ALTER SESSION SET container = myPDB;

Session altered.

SQL> select * from OPATCH_XML_INV ;
 ERROR:
 ORA-29913: error in executing ODCIEXTTABLEOPEN callout
 ORA-29400: data cartridge error
 KUP-04080: directory object OPATCH_LOG_DIR not found

no rows selected

SQL> select dbms_qopatch.get_opatch_install_info from dual;
 ERROR:
 ORA-20001: Latest xml inventory is not loaded into table
 ORA-06512: at "SYS.DBMS_QOPATCH", line 1986
 ORA-06512: at "SYS.DBMS_QOPATCH", line 133

In the CDB:

SQL> ALTER SESSION SET container = cdb$root;
Session altered.

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance>

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-2d1c0910-36ac-429b-98db-96a353d423b6</UId><targetTyp

Solution

There’s no solution available right now for Oracle Database 12.1.0.2. And this behavior does not seem to be documented yet. The SR resulted in an (unpublished) Enhancement Request. In a PDB the following workaround may help in Oracle Database 12.1.0.2:

 select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;

But this is not as fancy and easy to deal with as an API call to a DBMS package.

I tested in Oracle Database 12.2.0.1 – and there everything seems to work fine there 🙂

SQL>
 create pluggable database PDB3 admin user adm identified by adm
 file_name_convert=( '/u02/oradata/CDB2/pdbseed',
 '/u02/oradata/CDB2/pdb3');

Pluggable database created.

SQL> alter pluggable database pdb3 open;
Pluggable database altered.

SQL> alter session set container=pdb3;
Session altered.

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-3cb04a3a-3999-4767-86f1-bc845cab158e</UId><targetTyp

SQL> select * from OPATCH_XML_INV ;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<InventoryInstance> <ora

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

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

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

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB3                           READ WRITE NO

Cheers
–Mike