Do SET_AUDIT_TRAIL_LOCATION before INIT_CLEANUP

This is all old stuff, but I want to record a simple thing I found. I was following Oracle’s support document for setting up audit table cleanup using the DBMS_AUDIT_MGMT package. I used this document:

SCRIPT: Basic example to manage AUD$ table with dbms_audit_mgmt (Doc ID 1362997.1)

This is a very helpful document, but the example script runs DBMS_AUDIT_MGMT.INIT_CLEANUP before it runs DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION and it moves the audit tables SYS.AUD$ first to the SYSAUX tablespace and then to a newly created AUDIT_DATA tablespace. My simple thought is to run SET_AUDIT_TRAIL_LOCATION first to move SYS.AUD$ to AUDIT_DATA and then run INIT_CLEANUP which leaves SYS.AUD$ in AUDIT_DATA. Nothing monumental, but it seems more efficient to move the audit table once.

I did a couple of quick tests on an 18c database to demonstrate that SYS.AUD$ only moves once with SET_AUDIT_TRAIL_LOCATION first.

Test1: Follow the order in the Oracle document:

Before starting:

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSTEM

Create tablespace:

SQL> CREATE TABLESPACE AUDIT_DATA LOGGING
DATAFILE '/oracle/db01/DBA18C/dbf/audit_data_1.dbf'
SIZE 100M
AUTOEXTEND OFF;  2    3    4

Tablespace created.

Do INIT:

SQL> BEGIN
  2  IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
  3  (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
  4  THEN
  5  dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
  6  DBMS_AUDIT_MGMT.INIT_CLEANUP(
  7  audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
  8  default_cleanup_interval => 24*7);
  9  else
 10  dbms_output.put_line('Cleanup for STD was already initialized');
 11  end if;
 12  end;
 13  /
Calling DBMS_AUDIT_MGMT.INIT_CLEANUP

PL/SQL procedure successfully completed.

Table in SYSAUX:

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSAUX

Set the new table location:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  3  audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
  4  audit_trail_location_value => 'AUDIT_DATA') ;
  5  end;
  6  /

Table is in AUDIT_DATA (moved twice SYSTEM->SYSAUX->AUDIT_DATA):

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
AUDIT_DATA

Test2: Reverse the order in the Oracle document:

First, I restored my database to its original condition:

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSTEM

After creating the tablespace again, I ran set the trail location and the table is now in AUDIT_DATA:

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
AUDIT_DATA

Next, I do the init and the table does not move:

SQL> select
  2  tablespace_name
  3  from dba_tables
  4  where
  5  owner='SYS' and
  6  table_name='AUD$';

TABLESPACE_NAME
------------------------------
AUDIT_DATA

So, I am not sure why Oracle’s document has you do INIT_CLEANUP before SET_AUDIT_TRAIL_LOCATION but it seems more efficient to do them in the reverse order and move SYS.AUD$ once, from SYSTEM to AUDIT_DATA.

Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Do SET_AUDIT_TRAIL_LOCATION before INIT_CLEANUP

  1. Tim N. says:

    Hi,

    I left a comment about this audit subject buried in a script some years ago.
    In that era 12c was release the code was developed against.

    [snip]
    conditionally enable audit init when –audit-trail-cleanup-interval > 0 and move audit storage objects
    to sysaux tablespace. functionality to support relocating audit storage objects outside of sysaux
    tablespace is not provided to keep audit objects self contained within SYS+SYSAUX tablespaces and avoid
    complications for RMAN DUPLICATE/RECOVER point-in-time and EXPDP scenarios. 2017/10/08
    [end snip]

    “… enable audit init …” refers to dbms_audit_mgmt.init_cleanup(). I failed to capture the supporting URLs.

    https://support.oracle.com/knowledge/Oracle%20Database%20Products/2256158_1.html

    Current day google hit (above) speaks to possible rman or expdp problems which may arise when system objects (AUD$) are not self-contained in SYSTEM+SYSAUX tablespaces. Something to keep in mind when AUD$ lives outside of SYSTEM+SYSAUX.

    Looping back to your thoughts about ordering of AUD$ movement. I recall the the oracle philosophy is purge audit data first (init cleanup), then move the location. This applied to the widest audience as most people would be moving AUD$ tables containing audit records, then change the location. I believe dbms_audit_mgmt uses [ alter table … move … ] behind the curtain. A smaller cleaned AUD$ table moves faster, uses less resources, and less exposure to possible resources related failure.

    For new databases, or playing around, whichever order works. For me, fewer API calls equals less checking for failures.

    My $0.02, Tim…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.