Using DBMS_QOPATCH; Oracle Database 12c has a PL/SQL - SQL interface for viewing patch information using a select statement instead a OS ORACLE opach command. This new interface view all the patch information available as part of the ,OPatch lsinventory -xml command conform the the OS Oracle opatch command. The package DBMS_QOPATCH accesses the Oracle Universal Installer (OUI) patch inventory in real time to provide patch and patch meta information.
Using DBMS_QOPATCH feature, users can do:
- Query what patches are installed from SQL*Plus.
- Write wrapper programs to create reports and do validation checks across multiple environments.
- Check patches installed on Oracle RAC nodes from a single location instead of having to log onto each one in turn.
Objects related to the name OPATCH
select object_name , object_type, owner from dba_objects where object_name like '%OPATCH%'
OBJECT_NAME OBJECT_TYPE OWNER ------------------------ ----------------- ------ OPATCH_LOG_DIR DIRECTORY SYS OPATCH_SCRIPT_DIR DIRECTORY SYS OPATCH_XML_INV TABLE SYS OPATCH_XINV_TAB TABLE SYS OPATCH_INST_JOB TABLE SYS OPATCH_INST_PATCH TABLE SYS OPATCH_NODE_ARRAY TYPE SYS DBMS_QOPATCH PACKAGE SYS DBMS_QOPATCH PACKAGE BODY SYS LOAD_OPATCH_INVENTORY JOB SYS
DBMS_QOPATCH PACKAGE - APIs/functions/procedures
DBMS_QOPATCH | ||
|---|---|---|
| Function/procedure Name | Parameters | Comment |
| GET_OPATCH_INSTALL_INFO | None | Provides ORACLE HOME details |
| SET_CURRENT_OPINST | None | Set current oracle instance to get the inventory |
| GET_OPATCH_LIST | None | Provides list of patches installed |
| IS_PATCH_INSTALLED | patch Number IN varchar2 | Provides information in the installed patch |
| GET_OPATCH_DATA | patch Number IN varchar2 | Provides top level patch info for patch |
| GET_OPATCH_BUGS | patch Number DEFAULT NULL IN varchar2 | Provides bugs list in a patch or all the patches |
| GET_OPATCH_FILES | patch Number IN varchar2 | Provides list of files modified by a patch |
| GET_OPATCH_COUNT | None | Provides installed patches total count |
| GET_OPATCH_PREQS | patch Number IN varchar2 | Provides prerequisite patches for a patch |
| GET_OPATCH_OLAYS | patch Number IN varchar2 | Provides overlay patches for a patch |
| PATCH_CONFLICT_DETECTION | fileName IN varchar2 | Detects patch conflicts for given file |
| GET_PENDING_ACTIVITY | None | Gets sql patch status of RAC/non-RAC |
| GET_OPATCH_XSLT | None | Gets the style-sheet for presentation same appearance as opatch text output command line |
| GET_OPATCH_LSINVENTORY | None | Gets the inventory in xml format |
| GET_SQLPATCH_STATUS | patch Number DEFAULT NULL IN varchar2 | Displays the sql patch status |
Link : Summary of DBMS_QOPATCH Subprograms
"OPATCH_XINV_TAB" External table with prerprocessed
Information about the external table with the external table prerprocessed module "OPATCH_XINV_TAB"select
dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') "OPATCH_XML_INV"
from dual;
OPATCH_XML_INV
-----------------------------------------------------------------
CREATE TABLE "SYS"."OPATCH_XML_INV"
( "XML_INVENTORY" CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
READSIZE 67108864
preprocessor opatch_script_dir:'qopiprep.bat'
BADFILE opatch_script_dir:'qopatch_bad.bad'
LOGFILE opatch_log_dir:'qopatch_log.log'
FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION
( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
)
)
REJECT LIMIT UNLIMITED
"qopiprep.bat" script on Operation System inside the Oracle Software tree
select * from dba_directories where directory_name like '%OPATCH%';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ------ ------------------ ------------------------------------------------ ------------- SYS OPATCH_LOG_DIR /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch 1 SYS OPATCH_SCRIPT_DIR /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch 1
HOST cat /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
#!/bin/sh
#
# $Header: rdbms/src/client/tools/qpinv/qopiprep.bat /st_rdbms_12.1.0.1/1 2013/01/26 05:16:37 tbhukya Exp $
#
# qopiprep.bat
#
# Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.
#
# NAME
# qopiprep.bat - bat file for preprocessor
#
# DESCRIPTION
# bat file for external table preprocessor.
#
# NOTES
# .
#
# MODIFIED (MM/DD/YY)
# tbhukya 01/25/12 - Bug 16076845
# tbhukya 09/23/12 - Creation
#
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
# Option: "-retry 0" avoids retries in case of locked inventory.
# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options
# ("-jdk" is another good candidate that may require configuration!).
# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory. With that information, the
# patches of non-RDBMS homes could be fetched.
$ORACLE_HOME/OPatch/opatch lsinventory -xml $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt
Using the package module DBMS_QOPATCH
SQL> select DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO from dual;
GET_OPATCH_INSTALL_INFO -------------------------------------------------------------------------------------------------------------------------------------------------------------------------<oracleHome><UId>OracleHome-609f621a-71a9-42c9-814f-beed236fe198</UId><targetTypeId>oracle_home</targetTypeId><patchingModel>oneoff</patchingModel><path>/u02/app/oracle/product/12.1.0/dbhome_1</path> <targetTypeId>oracle_home</targetTypeId><inventoryLocation>/u02/app/oraInventory</inventoryLocation><isShared>false</isShared></oracleHome>
Now in a SQL friendly way with xmltransform and functionDBMS_QOPATCH.GET_OPATCH_XSLT XMLTransform takes as arguments an XMLType instance and an XSL style sheet, which is itself a form of XMLType instance. It applies the style sheet to the instance and returns an XMLType.
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO,DBMS_QOPATCH.GET_OPATCH_XSLT) ----------------------------------------------------------------------------------------------------------------------------- Oracle Home : /u02/app/oracle/product/12.1.0/dbhome_1 Inventory : /u02/app/oraInventory
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
LINK : XMLTRANSFORM
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 -------------------------------------------------------------------------------- Oracle Home : /u02/app/oracle/product/12.1.0/dbhome_1 Inventory : /u02/app/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 Oracle RAC Deconfiguration 12.1.0.1.0 ... Oracle Database 12c 12.1.0.1.0 Oracle OLAP 12.1.0.1.0 Oracle Spatial and Graph 12.1.0.1.0 Oracle Partitioning 12.1.0.1.0 Enterprise Edition Options 12.1.0.1.0 Interim patches:
Important Note on Security
Note that any DBA, as well as any user with the CREATE ANY DIRECTORY or DROP ANY DIRECTORY or grant any object privilege also has full access to all directory objects. Therefore, those privileges should be used sparingly, if at all. DBMS_ADVISOR privilege does not require DB DIRECTORY privileges to WRITE directly to the OS user dbsnmp has this rights.LINK : Preprocess clause External Tables - ORACLE_LOADER Access Driver
No comments:
Post a Comment