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
Hi Mike,
The lob segment of SYS.OPATCH_XINV_TAB seems to always increase by 16-20 MB at each re-start of the database instance, is this normal? We only notice this behaviour when a customer requested to shrink the LOB segment which is 200-MB+ on some databases. So we tested shrinking this on the Dev/Test database, and while it did shrink to 20-MB, each time we re-start the database, it grows by an average of 20MB. Same thing if we do a query like below:
set long 1000
select xmltransform(dbms_qopatch.get_opatch_lsinventory,
dbms_qopatch.get_opatch_xslt)
from dual ;
Just curious if this is a normal and expected behaviour for this table or for CLOB datatypes.
We have raised SR 3-29801908291 : Query on OPATCH_XINV_TAB’s LOB size post-upgrade and shrinking the LOB.
For the 11g database that we AutoUpgraded to 19c, it increase by 8-MB on each re-start?
I’m checking your SR right now, Edwin.
I was not aware of this LOB segment.
This is a good catch – it is HUGE in my environment, too. More or less 64MB in size. I’m quite surprised.
Using:
SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024) "LOB size (MB)"FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN
(
SELECT SEGMENT_NAME
FROM DBA_LOBS WHERE TABLE_NAME='OPATCH_XINV_TAB'
AND OWNER IN ('SYS')
)
GROUP BY OWNER,SEGMENT_NAME
;
I checked with the owners of opatch.
Let me know when you receive a useful answer via the SR.
Thanks
Mike