Dienstag, 12. Dezember 2017

manage ORACLE_HOMEs and patches in a centralised way

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.

Kommentar veröffentlichen