4. Februar 2014

SQL anstatt "opatch -lsinventory" - mit Oracle12c

SQL query instead of "opatch -lsinventory" - Oracle12c
Im neuen Datenbankrelease 12c gibt es, wie immer, einige neue Funktionen, die ein Schattendasein pflegen. Dazu gehört wohl auch das neue PL/SQL-Paket DBMS_QOPATCH. Es erlaubt das Auslesen des "Oracle-Inventory" mit SQL - möchte man sich über die installieren Patches informieren, muss man sich nun also nicht mehr am Betriebssystem einloggen - es geht ganz bequem mit SQL.
Mit ein paar Tricks ging das auch schon vor Oracle12c - so hatte ich bereits vor einigen Jahren ein Blog Posting veröffentlicht, welches die Vorgehensweise mit Hilfe eines BFILE und einigen XML-Funktionen erläutert. Nun ist das also out-of-the-box Teil der Datenbank - so weit, so gut.
Schauen wir mal ins Package DBMS_QOPATCH hinein. Es fällt recht schnell auf, dass fast alle Funktionen einen XMLTYPE zurückliefern ...
:
FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 PNUM                           VARCHAR2                IN
FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE
FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE
FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE
FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE
:
... was für ein schnelles Nachsehen nicht so angenehm ist.
SQL> select dbms_qopatch.GET_OPATCH_LSINVENTORY from dual;

GET_OPATCH_LSINVENTORY
-----------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850" standalone='yes'?>
<InventoryInstance>
  <oracleHome>
    <UId>OracleHome-75909c1d-7269-45d1-802e-75deb9104053</UId>
    <targetTypeId>oracle_home</targetTypeId>
    <patchingModel>oneoff</patchingModel>
    <path>/opt/oracle/product/12.1.0/db</path>
    <targetTypeId>oracle_home</targetTypeId>
    <inventoryLocation>/opt/oraInventory</inventoryLocation>
    <isShared>false</isShared>
  </oracleHome>
  <patches>
    <patch xmlns:xsi="http://www.w3.org/2001/XM
DBMS_QOPATCH bringt auch ein XSLT-Stylesheet mit (DBMS_QOPATCH.GET_OPATCH_XSLT), welches das XML in ein "plain text" Format umwandelt ...
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
--------------------------------------------------------------------------------
Oracle Home       : /opt/oracle/product/12.1.0/db
Inventory         : /opt/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
Oracle Universal Installer                                  12.1.0.1.0
Oracle USM Deconfiguration                                  12.1.0.1.0
Oracle Configuration Manager Deconfiguration                10.3.1.0.0
:
Aber etwas in einem Plain Text zu finden, ist nicht unbedings eleganter als in XML. Was wir wollen, wäre eine strukturierte Sicht auf die Informationen - schließlich sind wir in einer Datenbank. Zum Glück haben wir ja die SQL/XML-Funktionen, mit denen das Zerlegen des XML-Dokumentes ein Kinderspiel ist. Man muss sich "lediglich" mit den XML-Tags ein wenig auseinandersetzen - diese haben sich übrigens geändert, so dass die SQL-Kommandos im erwähnten Blog-Posting nicht mehr funktionieren. Aber fangen wir gleich an: Als erstes bauen wir eine View, welche die installierten Einzelpatches (oneoffs) anzeigt.
select
  patch_id,
  apply_time,
  rollbackable
from
  xmltable(
    '//patch'
    passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
      patch_id     number                   path 'patchID',
      apply_time   timestamp with time zone path 'appliedDate',
      rollbackable varchar2(10)             path 'rollbackable'
  );

  PATCH_ID APPLY_TIME                          ROLLBACKAB
---------- ----------------------------------- ----------
  16527374 08.11.13 14:02:02,000000 +01:00     true
  17027533 08.11.13 10:24:12,000000 +01:00     true
Schon mal gar nicht schlecht. Aber das XML enthält noch mehr Informationen; bspw. ist auch enthalten, welche Bugs ganz konkret mit den Patches gefixt wurden. Auch das lässt sich schnell aufbereiten ...
select
  patch_id,
  bug_id,
  bug_desc
from
  xmltable(
    '//patch'
     passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
     patch_id number path 'patchID',
     bugs xmltype path 'bugs/bug'
  ) p,
  xmltable(
    'bug'
    passing p.bugs
    columns
      bug_id number path '@id',
      bug_desc varchar2(500) path 'description'
  );

  PATCH_ID     BUG_ID BUG_DESC
---------- ---------- --------------------------------------------------------
  16527374   16527374 [12100-LIN64-130318]CDB ORA-07445 EM EXPRESS HOME GOT IO
  17027533   17034172 LX64_MAIN_CDB ORA-7445 R DUE TO RANDOM SGA CORRUPTION
  17027533   16694728 MSGQ LSM1 HITS ORA-600 [KJBLPGORM !ANTILOCK]
  17027533   16448848 LGSB WIDETAB ORA-600[17147] & ORA-600[600] & SQL
  17027533   16863422 ORA-600 [KXDAM_NOTIFY_CELL IOCTLFAILED] DURING RPM UPGRA
  17027533   16634384 LOGMINER BUILDER ASSERTS KRVUATLA20 AFTER UNREGISTERING 
         :          : :
Ob ein bestimmter Bug in meiner Datenbank nun gefixt ist oder nicht, dass findet man nun ganz einfach heraus - schließlich arbeiten wir mit SQL. Natürlich kann man sich mit diesen SQL-Anweisungen ganz einfach ein paar schöne Views bauen - ich denke an DBA_INSTALLED_PATCHES, DBA_FIXED_BUGS oder ähnliches.
The new database version Oracle12c contains, as always, many new functions which are not highlighted all around - in all the presentations and whitepapers. One of these is the new PL/SQL package DBMS_QOPATCH. It allows to read the Oracle Software Repository ("Oracle Inventory") with SQL. If you want to know which one-off patches are installed on the current system, you can now query this with SQL - logging into the operating system is no longer required.
Honestly, this was already possible before Oracle12c - some years ago, I publised a blog posting, which described how to achieve this with a BFILE and some SQL/XML functions. Now, we have this out-of-the-box - so far, so good.
Let's have a look at DBMS_QOPATCH. Most functions return XMLTYPE ...
:
FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 PNUM                           VARCHAR2                IN
FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE
FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE
FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE
FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE
:
... but dealing with XMLTYPE in order to see which patches have been installed, is a bit cumbersome.
SQL> select dbms_qopatch.GET_OPATCH_LSINVENTORY from dual;

GET_OPATCH_LSINVENTORY
-----------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850" standalone='yes'?>
<InventoryInstance>
  <oracleHome>
    <UId>OracleHome-75909c1d-7269-45d1-802e-75deb9104053</UId>
    <targetTypeId>oracle_home</targetTypeId>
    <patchingModel>oneoff</patchingModel>
    <path>/opt/oracle/product/12.1.0/db</path>
    <targetTypeId>oracle_home</targetTypeId>
    <inventoryLocation>/opt/oraInventory</inventoryLocation>
    <isShared>false</isShared>
  </oracleHome>
  <patches>
    <patch xmlns:xsi="http://www.w3.org/2001/XM
DBMS_QOPATCH also provides a stylesheet (DBMS_QOPATCH.GET_OPATCH_XSLT), which formats the information as a plain text document ...
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
--------------------------------------------------------------------------------
Oracle Home       : /opt/oracle/product/12.1.0/db
Inventory         : /opt/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
Oracle Universal Installer                                  12.1.0.1.0
Oracle USM Deconfiguration                                  12.1.0.1.0
Oracle Configuration Manager Deconfiguration                10.3.1.0.0
:
But finding something within this plain text would be still cumbersome. We want structured information - we are within a database.
Luckily, we can still use SQL/XML functions, work directly on the XML output and project all the information into columns of a SQL SELECT result. First, we want to see, which "oneoff" patches have been installed.
select
  patch_id,
  apply_time,
  rollbackable
from
  xmltable(
    '//patch'
    passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
      patch_id     number                   path 'patchID',
      apply_time   timestamp with time zone path 'appliedDate',
      rollbackable varchar2(10)             path 'rollbackable'
  );

  PATCH_ID APPLY_TIME                          ROLLBACKAB
---------- ----------------------------------- ----------
  16527374 08.11.13 14:02:02,000000 +01:00     true
  17027533 08.11.13 10:24:12,000000 +01:00     true
Looks good, doesn't it? Now we want to see the individual bugs which have been fixed by applying the patches ...
select
  patch_id,
  bug_id,
  bug_desc
from
  xmltable(
    '//patch'
     passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
     patch_id number path 'patchID',
     bugs xmltype path 'bugs/bug'
  ) p,
  xmltable(
    'bug'
    passing p.bugs
    columns
      bug_id number path '@id',
      bug_desc varchar2(500) path 'description'
  );

  PATCH_ID     BUG_ID BUG_DESC
---------- ---------- --------------------------------------------------------
  16527374   16527374 [12100-LIN64-130318]CDB ORA-07445 EM EXPRESS HOME GOT IO
  17027533   17034172 LX64_MAIN_CDB ORA-7445 R DUE TO RANDOM SGA CORRUPTION
  17027533   16694728 MSGQ LSM1 HITS ORA-600 [KJBLPGORM !ANTILOCK]
  17027533   16448848 LGSB WIDETAB ORA-600[17147] & ORA-600[600] & SQL
  17027533   16863422 ORA-600 [KXDAM_NOTIFY_CELL IOCTLFAILED] DURING RPM UPGRA
  17027533   16634384 LOGMINER BUILDER ASSERTS KRVUATLA20 AFTER UNREGISTERING 
         :          : :
Looking for a specific bug is now so easy, since this is a SQL result. Of course, these SQL queries could be extended very easily - all you have to do is to look into the XML documents in order to determine the XML tags actually being used. And in the next step we could create some views like "DBA_INST_PATCHES" or "DBA_FIXED_BUGS" ...

Keine Kommentare:

Beliebte Postings