2017-12-15

don't look to close, it might change the results

We all know, if we try to look to close on some details, the results we get may change from the original observation.
This is also sometimes true for Oracle Trace events:
During analyses of a query with XMLTYPE which fails with ORA-12850 (in a 4 node RAC DB), Oracle Support asked for some trace files. the query itself is something like
SELECT XMLTYPE(
CURSOR( SELECT ...
FROM gv$lock l
JOIN gv$session s
... ) )


and the original error is
ORA-19202: Error occurred in XML processing 
ORA-12850: Could not allocate slaves on all specified instances: 4 needed, 1 allocated 
ORA-06512: at "SYS.XMLTYPE", line 343 
ORA-06512: at line 1 

But with a specific trace event:
alter session set events 'trace [SQL_Compiler.*] disk highest'; 

the error changes to
ORA-19202: Error occurred in XML processing 
ORA-00918: column ambiguously defined 
ORA-06512: at "SYS.XMLTYPE", line 343 
ORA-06512: at line 1 

As there are 2 different Errors now Oracle Support has more to analyze 😂

2017-12-14

oratop with TFA 12.2.1.3.0

We are facing an issue where Oracles Trace File Analyzer creates high load on some servers.
During the discussion with Oracle Support I installed the latest version of TFA which is 12.2.1.3.0 right now.
With this version, oratop - which oracle calls
oratop is a utility similar to the unix OS utility top which gives a overview of database performance and can be used in combination with top to get a more complete overview of system performance
did not work as expected but quit with
Oratop successfully tested. 
ohome /appl/oracle/product/rdbms_12102_Oct17BPJa 
ouser oracle 
osid TTT0091 
db_running 1 
running_local 1 
oversion 12.1.0.2.0 
After another SR , it was clear to be Bug 27103547 - ORATOP DOES NOT WORK IN TFA 12.2.1.3.0
The solution is simple:
In $GRID_HOME/tfa/`hostname`/tfa_home/bin/common/tfactlshare.pm
edit somewhere around line 9010
# Farm called 
if ( $tcase ) { 
if ( length $ohome && length $ouser && length $osid && 
$db_running && length $oversion ) { 
print "Oratop successfully tested.\n"; 
print "ohome $ohome\n"; 
print "ouser $ouser\n"; 
print "osid $osid\n"; 
print "db_running $db_running\n"; 
print "running_local $running_local\n"; 
print "oversion $oversion\n"; 
# exit 0; 
} else { 
print "Oratop failed.\n"; 
print "ohome $ohome\n"; 
print "ouser $ouser\n"; 
print "osid $osid\n"; 
print "db_running $db_running\n"; 
print "running_local $running_local\n"; 
print "oversion $oversion\n"; 
exit 1; 
} 
} # end if $tcase 
So the exit 0; is not executed anymore.
Now oratop works as expected - even with TFA 12.2.1.3.0

Update: 18-Dec-2017
A new version 12.2.1.3.1 is available - this Bug is fixed there.
thank you Sandesh Rao for informing me.





2017-12-12

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.

2017-12-06

I went to a conference - and all I can post about is this picture

I was on DOAG and UKOUG conferences this year and hold some presentations there.
Of course I attended even more presentations and learned a lot of new things.
But I did not blog about it for some good reasons:
The presentations I attended are done already. Which means the presenters did a lot of research and put it all into great presentations and abstracts - so why should I create a weak copy of their work?

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.
But these notes contains this important reminder: !no Blog! 
That's there for a good reason: We shared information in mutual private trust. There are often details about things (ideas, concepts, tools, features, ...) which are not complete yet, or never will be. Or only applicable in this very specific situation, but not used without proper care. Or many other good reasons.
And this is the real value for going to conferences! These discussions beside and after the presentations.
It's NOT the hunt for hidden information in general, but the possibility to get open minded conversations off the records.
It was great to meet all the old and new friends. I love this community where you are seen as a valuable member based on your contribution; questions are always accepted and not marked as "dumb" or "silly". And even (and special) in disagreement the discussions are on highest technical level, but never personal.

Writing that I have to thank DOAG and UKOUG for their great work running these conferences.