Blog 
Manage Audit Data

Over the last couple of years I was asked repeatedly because the SYSTEM Tablespace is constantly growing. The reason is pretty simple: Since Oracle 11.2 DDL commands not executed with SYSDBA privilege and all logins (failed or successful) are logged in the AUD$ table. And against some documentation this table is located in the SYSTEM tablespace and not in SYSAUX.

SYSTEM Tablespace Size

Changing the Oracle parameter AUDIT_TRAIL=NONE will turn off this auditing but firstly this isn’t a dynamic parameter but the instances have to be bounced and secondly it might be quite useful to audit those actions.

Audit Tablespace

According to the application(s) the AUD$ table can grow rapidly so it should be moved into its own tablespace. Because you shouldn’t move data dictionary table directly (MOVE TABLESPACE) Oracle provides a package (dbms_audit_mgmt) to manage the AUD$ and FGA_LOG$ tables.

CREATE TABLESPACE AUDITTS    DATAFILE '/u02/oradata/HANNES/auditts01.dbf' size 100M    AUTOEXTEND ON NEXT 100M MAXSIZE 5000M; -- -- Initializing of the Audit Management -- BEGIN   dbms_audit_mgmt.init_cleanup(     AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std,     DEFAULT_CLEANUP_INTERVAL => 1 /*hours*/   ); END; / 

CAUTION! This procedure will move the AUD$ table to the SYSAUX tablespace first. So in the above case it will move 20 GB of data from SYSTEM to SYSAUX. That will make things even worse! So before executing this procedure you should delete trail records or better truncate the AUD$ table. As a best practice I would recommend that you execute this package right after the database creation for both standard auditing as well as fine grained auditing.

Now we can move the AUD$ table into its own tablespace AUDITTS. Again the package dbms_audit_mgmt will provide the right procedure:

BEGIN    dbms_audit_mgmt.set_audit_trail_location(     audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,     audit_trail_location_value => 'AUDITTS'); END; / 

Now that the table has been moved our problem should be resolved. But I would recommend that you think about how to maintain the trail records. The dbms_audit_mgmt has again the right procedure (create_purge_job) but the whole process is a bit more complex.

BEGIN    dbms_audit_mgmt.create_purge_job(     AUDIT_TRAIL_TYPE           => dbms_audit_mgmt.audit_trail_aud_std,     AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours*/,     AUDIT_TRAIL_PURGE_NAME     => 'Audit_Purge_Job',     USE_LAST_ARCH_TIMESTAMP    => TRUE   ); END; / 

A new scheduler job named “Audit Purge Job” will be created and executed once a day. The purpose of this job is to look for trail records that have been archived (USE_LAST_ARCH_TIMESTAMP). If the parameter has been set to “FALSE” all trail records will be removed.

Unfortunately this procedure does not work as expected. With the first execution via the Oracle scheduler an error message will be returned:

ORA-12012: error on auto execute of job "SYS"."AUDIT_PURGE_JOB" ORA-46258: Cleanup not initialized for the audit trail 

With the execution of this command via the scheduler the package has to be initialized again. You need to specify everything using the dbms_scheduler package or you can edit the procedure with Toad:

Toad Purge job

The next question is: how can we set the “last_arch_timestamp”? Oracle supposes that audit trail records are moved to a separate database from time to time to make them available for reporting. After the replication of those data the records will be marked “archived” and can be purged. For our “simple” auditing we need a second job running once a day and mark records “archived” which are older than 30 days.

BEGIN   dbms_scheduler.create_job (     job_name   => 'Audit_Archive_Timestamp',     job_type   => 'PLSQL_BLOCK',     job_action => 'BEGIN                          dbms_audit_mgmt.init_cleanup(                             AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,                             DEFAULT_CLEANUP_INTERVAL => 1);                          dbms_audit_mgmt.set_last_archive_timestamp(                             AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,                             LAST_ARCHIVE_TIME        => sysdate-31);                           dbms_audit_mgmt.deinit_cleanup(                             AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);                      END;',      start_date => to_date('2015-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'),      repeat_interval => 'FREQ=HOURLY;INTERVAL=24',      enabled    =>  TRUE,     comments   => 'Create Archive timestamp'   ); END; / 

This job calls the procedure set_last_archive_timestamp and marks trail records as archived. Like with the purge job it is again necessary to initialize the process first.

Now the management of audit trail has been finished and we can deinitialize the management:

BEGIN    dbms_audit_mgmt.deinit_cleanup(       AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std    ); END; / 

Crosscheck

The following views might help to check if auditing is enabled and if the procedures or jobs are executed correctly.

 

SELECT audit_option, success, failure FROM dba_stmt_audit_opts; AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- --------- CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS col JOB_NAME format a30 col JOB_FREQUENCY format a40 SELECT job_name,job_status,audit_trail,job_frequency FROM dba_audit_mgmt_cleanup_jobs; JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY ------------------------------ -------- ---------------------------- ----------------------- DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24 SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; JOB_NAME NEXT_RUN_DATE STATE ENABL ------------------------------ ----------------------------------- --------------- ----- AUDIT_ARCHIVE_TIMESTAMP 08.01.15 00:30:00,000000 +01:00 SCHEDULED TRUE DAILY_AUDIT_PURGE_JOB 08.01.15 09:16:30,800000 +01:00 SCHEDULED TRUE

But you might want to look for the audit trail information. The following example lists all logons to the database. The return code shows that some of the logins were not successful.

 SELECT os_username, username, userhost, extended_timestamp, action, action_name, returncode as return FROM dba_audit_trail WHERE action_name = 'LOGON' ORDER BY TIMESTAMP desc;  OS_USERNAME       USERNAME USERHOST               EXTENDED_TIMESTAMP               ACTION ACTION_NAM  RETURN ----------------- -------- ---------------------- -------------------------------- ------ ---------- ------- Johannes Ahrends  SYSTEM   CARAJANDB\CDB-JA       08.01.15 12:11:58,150458 +01:00     100 LOGON            0 oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:37,148850 +01:00     100 LOGON            0 oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:32,535973 +01:00     100 LOGON         1017 oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:10:16,180199 +01:00     100 LOGON         1017 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:33,723828 +01:00     100 LOGON            0 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,224429 +01:00     100 LOGON        28000 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,736211 +01:00     100 LOGON            0 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,430337 +01:00     100 LOGON            0 oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:31,497521 +01:00     100 LOGON            0 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,588672 +01:00     100 LOGON            0 oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,712832 +01:00     100 LOGON            0 oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:10,574229 +01:00     100 LOGON            0 oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:05,167148 +01:00     100 LOGON        28000 oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,565045 +01:00     100 LOGON            0 oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,491996 +01:00     100 LOGON            0 

Conclusion

Latest your SYSTEM tablespace grew more than 2 GB I would suggest to have a look at the space usage of the AUD$ or FGA_LOG$ table and to move those tables into a separate tablespace (with FGA_LOG$ theAUDIT_TRAIL_TYPE = dbms_audit_mgmt.audit_trail_fga_std). The problem is that you are probably not able to shrink the SYSTEM (or SYSAUX) tablespace because other objects might have created extents at the end of the datafile.

Currently I’m working on the new unified auditing in Oracle 12c. So I guess one of my next blogs will be about those new features.

4 comments on “Manage Audit Data

Hello Brother,
i wonder to know is there any way to disable SYSMAN,DBSNMP AUDITING?
as far as i know the command NOAUDIT all by SYSMAN should disable it! but even i did the command but alot of record is coming from SYSMAN!

sorry overlooked your question. Actually I haven’t tried yet but if you are already on 12.2 (and not 12.1) I would recommend using unified auditing as it is able to filter by role, user, etc.

Hi Johannes,
Thank you for your article. Could you comment the picture from TOAD. Is this a right code? dbms_audit_mgmt.CREATE_PURGE_JOB in the another job? May be dbms_audit_mgmt.CLEAN_AUDIT_TRAIL should be instead it?

You are correct. The purge job ist of course not necessary – but it works as it calls the clean_audit_trail procedure.

Leave a Reply

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

What can CarajanDB do for you?