Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: PL/SQL

Oracle 12c: queryable Opatch

One of a cool new feature in Oracle 12c is queryable Opatch. Oracle offers a set of tables and a PL/SQL package to query the Oracle Inventory.

With this feature, you can query the inventory to know if a one off patch has been deployed, which components have been deployed etc.

Tables related to this feature are:

  • OPATCH_XML_INV
  • OPATCH_XINV_TAB
  • OPATCH_INST_JOB
  • OPATCH_INST_PATCH

The OPATCH_XINV_TAB table is in fact external table preprocessed with specific script :

SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;

DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------

  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
The script is located in the path pointed by oracle directory OPATCH_SCRIPT_DIR.
On my server:
[oracle@oel63 ~]$ find $ORACLE_HOME -name "qopiprep.bat"
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
SQL> select * from dba_directories where directory_name='OPATCH_SCRIPT_DIR';

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH                                     ORIGIN_CON_ID
---------- ------------------------------ -------------------------------------------------- -------------
SYS        OPATCH_SCRIPT_DIR              /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch                1
The content of this script is pasted below:
#!/bin/sh
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
 
$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
This is the base of the feature which is accessed through a PL/SQL package DBMS_QOPATCH.
This package has many functions, for example:
  • IS_PATCH_INSTALLED
  • GET_OPATCH_LIST
  • GET_OPATCH_FILES
For example, if we execute the GET_OPATCH_BUGS (which provides bugs list in a specific patch or for all patches), the result is a XML output:
SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;

GET_OPATCH_BUGS
------------------------------------------------------------------------------------------------------------------------------------------------------
<bugInfo><bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><bug id="9448798"><UId>FlexibleDataType-957ebac1-ef13-462b-9157-b1e0cb711840</UId
><description>11202 dummy one-off for testing</description></bug></bugs></bugInfo>
This is not very user friendly even with a lightweight output … try to execute DBMS_QOPATCH.GET_OPATCH_LSINVENTORY function, there will be more stuff to analyze ;).
That’s why Oracle delivered a XSLT sheet to transform these outputs, you can get this sheet by executing the DBMS_QOPATCH.GET_OPATCH_XSLT function:
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       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/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
 .../...
Or with my example (bugs corrected by a specific or all OneOff patches) :
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_BUGS, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------

  Bugs fixed:
         9448798

Of course, you can write your own XSLT sheet, if you want more information. For example, I want the patch Id and the description of this one:

SQL> select xmltransform(dbms_qopatch.GET_OPATCH_BUGS,
  2  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  3  <xsl:template match="bug">
  4  <xsl:text>Patch Id= </xsl:text><xsl:value-of select="@id"/>
  5  <xsl:text>      Description:  </xsl:text> <xsl:value-of select="description"/>
  6  </xsl:template>
  7  </xsl:stylesheet>')
  8  from dual
  9  /

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,'<XSL:STYLESHEETVERSION="1.0"XMLNS:XSL="HTTP://WWW.W3.ORG/
----------------------------------------------------------------------------------------------------
Patch Id= 9448798      Description:  11202 dummy one-off for testing
Advertisements

Managing database audit Trail in Oracle 11gR2

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

– The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

– The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

– The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.

Generate a random date in Oracle

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let’s see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);
Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select * from t;
D
----------------------
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00