Managing many
ORACLE_HOMEs in a complex environment can be tricky, especially when questions about their patches & bugs or comparisons should be done.
It's required to understand the different types of patches available. This can be (according to the
documentation & my interpretation). Especially the differences between
SYSTEM PATCH (in our case only Bundle Patches),
COMPOSITE and
SINGLETON patches, and
BUGs - of course.
A way to handle all the information is to use the details provided by opatch and the patches, store them in some tables and afterwards query them.
For ORACLE_HOME related information, all the information required can be generated by a simple opatch command:
${ORACLE_HOME}/OPatch/opatch lsinventory -bugs_fixed -xml ${bugs_fixed_file}
This then needs to be uploaded into a table. For this purpose we use a
SQLcl inspired by
Kris Rices
SQLcl examples.
${SQLCL} "${conn_string}" << EOF
script
var fileName="${bugs_fixed_file}";
var theBlob = conn.createBlob();
var outBlob = theBlob.setBinaryStream(1);
var path = java.nio.file.FileSystems.getDefault().getPath(fileName);
theBlob.setBytes(1, java.nio.file.Files.readAllBytes(path));
// ctx.write("length: " + theBlob.length());
var HashMap = Java.type("java.util.HashMap");
bind_map = new HashMap();
bind_map.put("theblob", theBlob);
bind_map.put("name", "${name}");
bind_map.put("version", "${version}");
try {
var retval = util.execute("insert into oh_registry (name, version, bugs_fixed) values (:name, :version, blob2clob(:theblob))", bind_map);
// ctx.write(retval + "\n");
}
catch (e) {
ctx.write("\n\n ERROR:" + e + "\n\n");
}
sqlcl.setStmt("select name, version, dbms_lob.getlength(bugs_fixed) from oh_registry where name='${name}'");
sqlcl.run();
/
commit;
exit
EOF
(blob2clob is just a wrapper for DBMS_LOB.CONVERTTOCLOB)
With this information from several ORACLE_HOMEs, fancy stuff is possible with simple xml queries:
Which patches are different between 2 ORACLE_HOMEs?
select
sub1.patchid as OH1
, sub2.patchid as OH2
, NVL2(sub1.patchid
, NVL(sub1.patchdescription ,
(SELECT md.DESCRIPTION
FROM ORACLE_PATCHES_MANUALDESC md
WHERE md.patchnum = sub1.patchid)
)
, NVL(sub2.patchdescription ,
(SELECT md.DESCRIPTION
FROM ORACLE_PATCHES_MANUALDESC md
WHERE md.patchnum = sub2.patchid)
)
)
description
from
(select
xml1.patchid
, nvl(xml1.patchdescription, (select description from oracle_patches where patchnum=xml1.patchid)) patchdescription
from
oh_registry
, XMLTable(
'/InventoryInstance/patches/patch' PASSING xmltype(bugs_fixed)
COLUMNS
patchID number path 'patchID'
, patchDescription varchar2(64) path 'patchDescription'
) xml1
where
name='${home1}') sub1
full outer join
(select
xml2.patchid
, nvl(xml2.patchdescription, (select description from oracle_patches where patchnum=xml2.patchid)) patchdescription
from
oh_registry
, XMLTable(
'/InventoryInstance/patches/patch' PASSING xmltype(bugs_fixed)
COLUMNS
patchID number path 'patchID'
, patchDescription varchar2(64) path 'patchDescription'
) xml2
where
name='${home2}') sub2 on (sub1.patchid = sub2.patchid)
where
(sub1.patchid is null
or sub2.patchid is null) or ( ${full_precidate} != 0)
order by
sub1.patchid
, sub2.patchid
;
which can show results like
rdbms_12102_Oct17BPJa_beta2 rdbms_12102_Oct17BPJa_beta5 DESCRIPTION
--------------------------- --------------------------- -------------------------------------------------------------------------------------
19450139 KN LNX PERFORMANCE ISSUE WHEN RUNNING GATHER TABLE STATS WITH INCREMENTAL STATS
21385422 12C DB ORA-46264 DURING DBMS_AUDIT_MGMT OPERATION
26988490 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.171017 FOR BUGS 19450139 20807398
So between these 2 ORACLE_HOMEs, 1 patch disappeared and 2 new came in.
A similar query can provide the delta of BUGs:
select
sub1.bug as OH1
, sub2.bug as OH2
, case
when sub1.bug is null then sub2.description
else sub1.description
end description
from
(select
xml1.bug
, xml1.description
from
oh_registry
, XMLTable(
'//bug' PASSING xmltype(bugs_fixed)
COLUMNS
bug number path '@id'
, description varchar(64) path 'description'
) xml1
where
name='${home1}') sub1
full outer join
(select
xml2.bug
, xml2.description
from
oh_registry
, XMLTable(
'//bug' PASSING xmltype(bugs_fixed)
COLUMNS
bug number path '@id'
, description varchar(64) path 'description'
) xml2
where
name='${home2}') sub2 on (sub1.bug = sub2.bug)
where
( sub1.bug is null
or sub2.bug is null ) or ( ${full_precidate} != 0)
order by
sub1.bug
, sub2.bug
;
rdbms_12102_Oct17BPJa_beta2 rdbms_12102_Oct17BPJa_beta5 DESCRIPTION
--------------------------- --------------------------- ----------------------------------------------------------------
20807398 ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 20465582
21385422 12C DB ORA-46264 DURING DBMS_AUDIT_MGMT OPERATION
21529241 DBMS_STATS ORA-06502 PL/SQL NUMERIC OR VALUE ERROR
This shows quite good the difference between BUGs and Patches.
Also to query all ORACLE_HOMEs which contain a specific BUG is possible:
Inthis case, I'm querying for
Bug:22652097
WITH dim_1
AS (SELECT name as OH_NAME,
xml1.patchid,
Nvl(xml1.patchdescription, (SELECT description
FROM oracle_patches_manualdesc
WHERE patchnum = xml1.patchid))
patchdescription,
bugs
FROM oh_registry,
XMLTABLE( '/InventoryInstance/patches/patch' passing xmltype(bugs_fixed)
COLUMNS
patchid NUMBER path 'patchID'
, bugs xmltype path 'bugs'
, patchdescription
VARCHAR2(64) path 'patchDescription' ) xml1
-- WHERE name = 'ebs_rdbms_12102_Apr17b_1'
)
SELECT dim_1.OH_NAME as NAME,
patchid,
patchdescription,
bug,
bugdescription
FROM dim_1,
XMLTABLE( '//bug' passing bugs COLUMNS
bug NUMBER path '@id'
, bugdescription VARCHAR(64) path 'description'
)
WHERE bug= to_number('${bug}')
ORDER BY dim_1.OH_NAME,
patchid,
bug;
NAME PATCHID PATCHDESCRIPTION BUG BUGDESCRIPTION
-------------------------------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
rdbms_12102_Apr17BPJa 25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 186 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Apr17BPJb 25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 186 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Apr17BPJc 25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 186 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Apr17BPJd 25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 186 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Aug17BPJa_beta2 27011973 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170718 FOR BUGS 261 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Jan17BPJa 25635590 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170117 FOR BUGS 189 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa_beta1 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa_beta2 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa_beta3 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa_beta4 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJa_beta5 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
rdbms_12102_Oct17BPJb_beta1 26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470) 22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATIS
So a lot of useful queries can be done when information regarding ORACLE_HOMEs is stored in a RDBMS.
Similar things can be done for patches as well. patches contain a lot of useful information. Those are stored in
inventory.xml,
actions.xml and if they exist in
bundle.xml, and any
README* files. If these files are loaded into tables similar to the method shown above, they can be used to generate even more insight. Some care must be taken as SYSTEM and COMPOSITE patches contain other patches which must be processed as well (together with their dependencies). We normalized the xml files to several tables during the load for easier/faster queries.
It's possible to search for all patches which will fix a given bug:
BUGNUM DESCRIPTION
---------- -----------------------------------------------------------------------------
22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES
single patches
PATCHNUM DESCRIPTION
---------- ------------------------------------------------------------------------------
26886035 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 21156276 22652097
27011973 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170718 FOR BUGS 26165305 19450139
25304579
26022324
26166813
26816088
25929584 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170418 FOR BUGS 18650065 18961555
26165162
22652097 PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES
25635590 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170117 FOR BUGS 18961555 19450139
26165461
26757390
26898071 MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170718 FOR BUGS 21156276 22652097
26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470)
14 rows selected.
composite patches
PATCHNUM DESCRIPTION
---------- ----------------------------------------------------------------------------------
26717470 Database Bundle Patch : 12.1.0.2.171017 (26717470)
bundle patches
PATCHNUM DESCRIPTION
---------- ----------------------------------------------------------------------------------
26635880 Oracle® Database Patch 26635880 - Database Proactive Bundle Patch 12.1.0.2.171017
Or the difference between 2 merge patches:
26886035_BUGS 25929584_BUGS BUG_DESCRIPTION
------------- ------------- -------------------------------------------------------------------------------
21156276 ORA-00600 [QESDPSIGERROR], [ADAPTIVE JOIN RESOLUTION FAILED], [394]
18961555 STATIC PL/SQL BASELINE REPRODUCTION BROKEN BY FIX 18020394
19450139 KN LNX PERFORMANCE ISSUE WHEN RUNNING GATHER TABLE STATS WITH INCREMENTAL STATS
20508819 WRONG RESULTS OR ORA-7445 WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE
20807398 ORA-00600 [KGL-HASH-COLLISION] WITH FIX TO BUG 20465582
21529241 DBMS_STATS ORA-06502 PL/SQL NUMERIC OR VALUE ERROR
21826068 WRONG RESULTS STILL WHEN _OPTIMIZER_AGGR_GROUPBY_ELIM=TRUE
22324460 ORA-600 QKSANGETTEXTSTR 1 AND CORRUPT PLANS WITH SPM
22913528 WRONG RESULTS WITH PARTITION PRUNING AND MIN/MAX SCANS
18650065 WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS
21171382 AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY
Using the available information about ORACLE_HOMEs and Patches together it's even possible to check if a patch can be applied to an ORACLE_HOME, or where they conflict.
...
conflicts on:
ACTION ARGUMENTS
--------------- ----------------------------------------------------------------------------------------------------
copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb
1 row selected.
conflict details:
PATCHDESC ACTION ARGUMENTS
-------------------------------------------------- --------------- --------------------------------------------------
20925154 - ORA-39126 WORKER UNEXPECTED FATAL ERRO copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb
25740076 - copy %ORACLE_HOME%/rdbms/admin/prvtbpw.plb
2 rows selected.
In this case the Patch:25740076 with
<oracle .rdbms.dbscripts="" opt_req="O" version="12.1.0.2.0">
<copy file_name="rdbms/admin/prvtbpm.plb" name="prvtbpm.plb" path="%ORACLE_HOME%/rdbms/admin">
<copy file_name="rdbms/admin/prvtkupc.plb" name="prvtkupc.plb" path="%ORACLE_HOME%/rdbms/admin">
<copy file_name="rdbms/admin/prvtbpw.plb" name="prvtbpw.plb" path="%ORACLE_HOME%/rdbms/admin">
conflicts with the
ORACLE_HOME which contains
<patchid>25740076</patchid>
whith
<patchactions>
<patchaction><archive name="libserver12.a" object_name="lib/libserver12.a/kupv.o" path="%ORACLE_HOME%/lib"></archive></patchaction>
<patchaction><make change_dir="%ORACLE_HOME%/rdbms/lib" make_file="ins_rdbms.mk" make_target="ioracle"></make></patchaction>
<patchaction><copy file_name="rdbms/admin/prvtbpm.plb" name="prvtbpm.plb" path="%ORACLE_HOME%/rdbms/admin"></copy></patchaction>
<patchaction><copy file_name="rdbms/admin/prvtkupc.plb" name="prvtkupc.plb" path="%ORACLE_HOME%/rdbms/admin"></copy></patchaction>
<patchaction><copy file_name="rdbms/admin/prvtbpw.plb" name="prvtbpw.plb" path="%ORACLE_HOME%/rdbms/admin"></copy></patchaction>
...
My Oracle Support comes to the same conclusion in it's tool to check for conflicts.
Now oratop works as expected - even with TFA 12.2.1.3.0 
Beside these presentations I had a lot of talks to several people. We shared war stories, created concepts for new solutions over a beer and also discussed hidden, or at least not so well known "features". I took notes for some of these discussions for later analysis.