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 133In 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
Good work