Changing AUDIT tablespace and create a purge job in 11g

This post is also available in: Português

Oracle Database 11g comes with some audit options turned on by default, which over time can exhaust the SYSTEM tablespace without the DBA knowledge. However, this same version of database now offers some DBMS packages that can greatly facilitate the life of the administrator who wants to migrate audit to another tablespace or change the retention period of the data. Until version 10gR2, it was necessary to make this change in a more archaic way, which can be checked at Doc ID 1019377.6 on Oracle Support.

To begin, make sure your audit options are turned on.

In my case, I activated for all sys operations and configured to be saved also the run SQL (DB_EXTENDED). This can vary from case to case. Place the desired values ​​and restart your database.

SQL> SHOW parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u02/admin/rmanbkp/adump
audit_syslog_level                   string
audit_sys_operations                 boolean     TRUE
audit_trail                          string      DB_EXTENDED

Let's first find in which tablespace are the audit tables.

SQL> SELECT OWNER,
  2         SEGMENT_NAME,
  3         TABLESPACE_NAME
  4  FROM   DBA_SEGMENTS
  5  WHERE  SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');

OWNER                          SEGMENT_NAME                                                                     TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS                            FGA_LOG$                                                                         SYSTEM
SYS                            AUD$                                                                             SYSTEM

Now let's check if the automatic purge infrastructure process of the audit data has already been initialized.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_OUTPUT.put_line('YES');
  4    ELSE
  5      DBMS_OUTPUT.put_line('NO');
  6    END IF;
  7  END;
  8  /

NO

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE                                                              SYSAUX                                                                           STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE                                                              SYSAUX                                                                           FGA AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL

10 rows selected

If it is already enabled, skip to the next step. Otherwise, we will then initialize the infrastructure and set the cleaning interval (not the retention interval) for every 1 day (24 hours).

SQL> BEGIN
  2    SYS.dbms_audit_mgmt.init_cleanup(
  3      audit_trail_type         => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  4      default_cleanup_interval => 24 /* hours */); -- PARAMETER NOT USED ON 11GR2 (FUTURE USE)
  5  END;
  6  /

PL/SQL procedure successfully completed

Rechecking.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_OUTPUT.put_line('YES');
  4    ELSE
  5      DBMS_OUTPUT.put_line('NO');
  6    END IF;
  7  END;
  8  /

YES

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE                                                              SYSAUX                                                                           STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE                                                              SYSAUX                                                                           FGA AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                                                        24                                                                               STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                                                        24                                                                               FGA AUDIT TRAIL

12 rows selected

Note the appearance of the last 2 rows with the value "DEFAULT CLEAN UP INTERVAL" set to 24 hours.
The next step is the creation of a unique tablespace to store audit data:

SQL> CREATE TABLESPACE AUDTBS DATAFILE '/u02/oradata/ORACL/audtbs01.dbf' SIZE 128M
  2  AUTOEXTEND ON NEXT 64M MAXSIZE 2G
  3  NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created

Now we will move the tables AUD$ and FGA_LOG$ to the new tablespace created. It is recommended that these tables are empty to optimize the process. Truncate if possible or leave your data and wait.

SQL> BEGIN
  2   SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  3      audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      audit_trail_location_value => 'AUDTBS');
  5  END;
  6  /

PL/SQL procedure successfully completed

SQL>
SQL> BEGIN
  2    SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(
  3      audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
  4      audit_trail_location_value => 'AUDTBS');
  5  END;
  6  /

PL/SQL procedure successfully completed

Time to see if the tables were really moved. Check if the values ​​of the parameter "DB AUDIT TABLESPACE" in the first query has been changed.

SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME                                                                   PARAMETER_VALUE                                                                  AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE                                                              AUDTBS                                                                           STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE                                                              AUDTBS                                                                           FGA AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            OS AUDIT TRAIL
AUDIT FILE MAX SIZE                                                              10000                                                                            XML AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                OS AUDIT TRAIL
AUDIT FILE MAX AGE                                                               5                                                                                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                                                        10000                                                                            FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                                                         1000                                                                             XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                                                        24                                                                               STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL                                                        24                                                                               FGA AUDIT TRAIL

12 rows selected

SQL>
SQL> SELECT OWNER,
  2         SEGMENT_NAME,
  3         TABLESPACE_NAME
  4  FROM   DBA_SEGMENTS
  5  WHERE  SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');

OWNER                          SEGMENT_NAME                                                                     TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS                            FGA_LOG$                                                                         AUDTBS
SYS                            AUD$                                                                             AUDTBS

Now let's create a new Scheduler that will daily mark all audit records prior to 90 days as filed. Thus, the cleaning process can clean them.

SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_JOB (
  3      job_name   => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP',
  4      job_type   => 'PLSQL_BLOCK',
  5      job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => SYSDATE-90); END;',
  6      start_date => sysdate,
  7      repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
  8      enabled    =>  TRUE,
  9      comments   => 'Create an archive timestamp'
 10    );
 11  END;
 12  /

PL/SQL procedure successfully completed

Checking if the job was created.

SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';

OWNER                          JOB_NAME                       JOB_STYLE   JOB_CREATOR                    JOB_TYPE         JOB_ACTION                                                                       START_DATE                                                                       REPEAT_INTERVAL                                                                  ENABLED STATE           SYSTEM NLS_ENV                                                                          COMMENTS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SYS                            DAILY_AUDIT_ARCHIVE_TIMESTAMP  REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00                                                  FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp

Finally, we will create the process to daily clean the records that were marked by the previous job.

SQL> BEGIN
  2    SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  3      AUDIT_TRAIL_TYPE           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
  5      AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
  6      USE_LAST_ARCH_TIMESTAMP    => TRUE
  7    );
  8  END;
  9  /

PL/SQL procedure successfully completed

Checking if the job was created.

SQL> SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME                                                                         JOB_STATUS AUDIT_TRAIL                  JOB_FREQUENCY
-------------------------------------------------------------------------------- ---------- ---------------------------- --------------------------------------------------------------------------------
DAILY_AUDIT_PURGE_JOB                                                            ENABLED    STANDARD AUDIT TRAIL         FREQ=HOURLY;INTERVAL=24

SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';

OWNER                          JOB_NAME                       JOB_STYLE   JOB_CREATOR                    JOB_TYPE         JOB_ACTION                                                                       START_DATE                                                                       REPEAT_INTERVAL                                                                  ENABLED STATE           SYSTEM NLS_ENV                                                                          COMMENTS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SYS                            DAILY_AUDIT_ARCHIVE_TIMESTAMP  REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00                                                  FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp
SYS                            DAILY_AUDIT_PURGE_JOB          REGULAR     SYS                            PLSQL_BLOCK      BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE);  END;                          22/05/13 13:30:13,003284 AMERICA/RECIFE                                          FREQ=HOURLY;INTERVAL=24                                                          TRUE    SCHEDULED       TRUE   NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Audit clean job = 'Daily_Audit_Purge_Job'

Now, I recommend removing all of the default audit options of Oracle and reapplying them only for executions "Whenever Successful", not commands that execution failed for lack of privileges.

SQL> noaudit all;
SQL> noaudit all privileges;
SQL> noaudit exempt access policy;

Noaudit succeeded
Noaudit succeeded
Noaudit succeeded

sql> audit all by access whenever successful; -- Add 27 AUDIT options
sql> audit alter any table by access whenever successful;
sql> audit create any table by access whenever successful;
sql> audit drop any table by access whenever successful;
sql> audit create any procedure by access whenever successful;
sql> audit drop any procedure by access whenever successful;
sql> audit alter any procedure by access whenever successful;
sql> audit grant any privilege by access whenever successful;
sql> audit grant any object privilege by access whenever successful;
sql> audit grant any role by access whenever successful;
sql> audit audit system by access whenever successful;
sql> audit create external job by access whenever successful;
sql> audit create any job by access whenever successful;
sql> audit create any library by access whenever successful;
sql> audit create public database link by access whenever successful;
sql> audit exempt access policy by access whenever successful;
sql> audit alter user by access whenever successful;
sql> audit create user by access whenever successful;
sql> audit role by access whenever successful;
sql> audit create session by access whenever successful;
sql> audit drop user by access whenever successful;
sql> audit alter database by access whenever successful;
sql> audit alter system by access whenever successful;
sql> audit alter profile by access whenever successful;
sql> audit drop profile by access whenever successful;

Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded
Audit succeeded

Verifying..

SQL> SELECT  * FROM DBA_STMT_AUDIT_OPTS order by 3;

USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
                                                              ALTER ANY PROCEDURE                      BY ACCESS  NOT SET
                                                              ALTER ANY TABLE                          BY ACCESS  NOT SET
                                                              ALTER DATABASE                           BY ACCESS  NOT SET
                                                              ALTER PROFILE                            BY ACCESS  NOT SET
                                                              ALTER SYSTEM                             BY ACCESS  NOT SET
                                                              ALTER USER                               BY ACCESS  NOT SET
                                                              CLUSTER                                  BY ACCESS  NOT SET
                                                              CONTEXT                                  BY ACCESS  NOT SET
                                                              CREATE ANY JOB                           BY ACCESS  NOT SET
                                                              CREATE ANY LIBRARY                       BY ACCESS  NOT SET
                                                              CREATE ANY PROCEDURE                     BY ACCESS  NOT SET
                                                              CREATE ANY TABLE                         BY ACCESS  NOT SET
                                                              CREATE EXTERNAL JOB                      BY ACCESS  NOT SET
                                                              CREATE PUBLIC DATABASE LINK              BY ACCESS  NOT SET
                                                              CREATE SESSION                           BY ACCESS  NOT SET
                                                              CREATE USER                              BY ACCESS  NOT SET
                                                              DATABASE LINK                            BY ACCESS  NOT SET
                                                              DIMENSION                                BY ACCESS  NOT SET
                                                              DIRECTORY                                BY ACCESS  NOT SET
                                                              DROP ANY PROCEDURE                       BY ACCESS  NOT SET
                                                              DROP ANY TABLE                           BY ACCESS  NOT SET
                                                              DROP PROFILE                             BY ACCESS  NOT SET
                                                              DROP USER                                BY ACCESS  NOT SET
                                                              EXEMPT ACCESS POLICY                     BY ACCESS  NOT SET
                                                              GRANT ANY OBJECT PRIVILEGE               BY ACCESS  NOT SET
                                                              GRANT ANY PRIVILEGE                      BY ACCESS  NOT SET
                                                              GRANT ANY ROLE                           BY ACCESS  NOT SET
                                                              INDEX                                    BY ACCESS  NOT SET
                                                              MATERIALIZED VIEW                        BY ACCESS  NOT SET
                                                              MINING MODEL                             BY ACCESS  NOT SET
                                                              NOT EXISTS                               BY ACCESS  NOT SET
                                                              PROCEDURE                                BY ACCESS  NOT SET
                                                              PROFILE                                  BY ACCESS  NOT SET
                                                              PUBLIC DATABASE LINK                     BY ACCESS  NOT SET
                                                              PUBLIC SYNONYM                           BY ACCESS  NOT SET
                                                              ROLE                                     BY ACCESS  NOT SET
                                                              ROLLBACK SEGMENT                         BY ACCESS  NOT SET
                                                              SEQUENCE                                 BY ACCESS  NOT SET
                                                              SYNONYM                                  BY ACCESS  NOT SET
                                                              SYSTEM AUDIT                             BY ACCESS  NOT SET
                                                              SYSTEM GRANT                             BY ACCESS  NOT SET
                                                              TABLE                                    BY ACCESS  NOT SET
                                                              TABLESPACE                               BY ACCESS  NOT SET
                                                              TRIGGER                                  BY ACCESS  NOT SET
                                                              TYPE                                     BY ACCESS  NOT SET
                                                              USER                                     BY ACCESS  NOT SET
                                                              VIEW                                     BY ACCESS  NOT SET

47 rows selected

SQL>

Done, now you no longer need to worry about the uncontrolled growth of your SYSTEM tablespace. The audit is isolated in a dedicated tablespace.

Have you enjoyed? Please leave a comment or give a 👍!

5 comments

1 pings

Skip to comment form

    • raj on August 18, 2017 at 13:31
    • Reply

    good documnet. it worked for me in my prod enviroenment.

    Thanks.

    • Vermilions on June 6, 2018 at 19:05
    • Reply

    do you know how to reset the PARAMETER_VALUE, I put 150 and I need this value set 24

    PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
    -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
    DB AUDIT TABLESPACE AUDTBS STANDARD AUDIT TRAIL
    DB AUDIT TABLESPACE AUDTBS FGA AUDIT TRAIL
    AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
    AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
    AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
    AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
    DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
    DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
    OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
    OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
    DEFAULT CLEAN UP INTERVAL here(24) STANDARD AUDIT TRAIL
    DEFAULT CLEAN UP INTERVAL here (24 )

    1. Which parameter value you need to change from 24 to 150?

  1. Great!!! Thanks for very clear explanation. Any way to take backup of aud$ table.

    • Pippo on November 25, 2024 at 13:54
    • Reply

    Hello, how can i change the default_cleanup_interval?

Leave a Reply

Your email address will not be published.