USE DBMS_AUDIT_MGMT FOR PURGING AUDIT FILES AT OS LEVEL

Posted on January 20, 2012 by Anand

Few of the times we have scenario where db logon fails with ORA-09925: Unable to create audit trail file.Mostly a weekly crontab would have been set to delete the trace files, audit files etc from the server. From 11gR2 oracle introduced and started supporting dbms_audit_mgmt for managing the various audit trail types like database audit trails, operating system (OS) audit trails, and XML audit trails.

03:38:43 SYS@matrix1 > SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME                TABLESPACE_NAME
------------------------- -------------------------
AUD$                      SYSTEM
FGA_LOG$                  SYSTEM

select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');03:39:14 SYS@matrix1 >

SEGMENT_NAME              SIZE_IN_MEGABYTES
------------------------- -----------------
AUD$                                    128
FGA_LOG$                              .0625

2 rows selected.

Elapsed: 00:00:00.26
03:39:15 SYS@matrix1 >
03:39:16 SYS@matrix1 > @table_info
Enter value for table_name: aud$
old   9: WHERE  table_name  like UPPER('%&table_name%')
new   9: WHERE  table_name  like UPPER('%aud$%')

TABLE_NAME                     OWNER      TABLESPACE_NAME             NUM_ROWS LAST_ANALYZED               AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------------------- ---------- --------------------------- ----------- ---------- ------------
AUD$                           SYS        SYSTEM                        630545 14-JAN-12-10:01:47                  157      15204            0

1 row selected.
07:14:09 SYS@matrix1 > select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

no rows selected

Elapsed: 00:00:00.06
07:16:51 SYS@matrix1 > select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

no rows selected
07:16:59 SYS@matrix1 > select * 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

Current the adump has *.aud file from 25Sept in the audit_file_dest location.

(matrix1) /u01/app/oracle/admin/matrix> du -sh *
91M     adump
(matrix1) /u01/app/oracle/admin/matrix/adump> ls -lrt *aud | wc -l
58375

To setup the audit file deletion at OS level —

1. set init cleanup –This procedure sets up the audit management infrastructure and a default cleanup interval for the audit trail records. If the audit trail tables are in the SYSTEM tablespace, then the procedure moves them to the SYSAUX tablespace.

 
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_OS,
default_cleanup_interval => 24*7); -->The default time interval, in hours, after which the cleanup procedure should be called.
end;
/

2. set last archive timestamp –> This procedure sets a timestamp indicating when the audit records were last archived. The audit administrator provides the timestamp to be attached to the audit records.

As i want to retain 30days of audit files on OS i use AUDIT_TRAIL_OS and set last_archive_time => sysdate – 30.If the audit location is not shared between the nodes, specify the rac_instance_number parameter.

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => sysdate - 30,
rac_instance_number => 1 ); 
end;
/

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => sysdate - 30,
rac_instance_number => 2 ); 
end;
/

3. setup a purge –> This procedure creates a purge job for periodically deleting the audit trail records.This procedure carries out the cleanup operation at intervals specified by the user. It calls the CLEAN_AUDIT_TRAIL Procedure to perform the cleanup operation.As i want the job to be run weekly so AUDIT_TRAIL_PURGE_INTERVAL => 24*7.

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
AUDIT_TRAIL_PURGE_INTERVAL => 24*7,
AUDIT_TRAIL_PURGE_NAME => 'Standard_OS_Audit_Trail_Purge',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Now, using the oracle views lets check —

07:42:19 SYS@matrix1 > select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME                            JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY
----------------------------------- -------- ---------------------------- -------------------------
STANDARD_OS_AUDIT_TRAIL_PURGE       ENABLED  OS AUDIT TRAIL               FREQ=HOURLY;INTERVAL=168

07:43:17 SYS@matrix1 > select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
OS AUDIT TRAIL                  1 20-DEC-11 07.41.08.000000 AM -06:00
OS AUDIT TRAIL                  2 20-DEC-11 07.41.52.000000 AM -06:00

2 rows selected.
07:59:56 SYS@matrix1 > select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

AUDIT_TRAIL                  RAC_INSTANCE CLEANUP_TIME                                                                DELETE_COUNT WAS
---------------------------- ------------ --------------------------------------------------------------------------- ------------ ---
OS AUDIT TRAIL                          1 19-JAN-12 01.43.03.761395 PM +00:00                                                 1003 NO
OS AUDIT TRAIL                          2 19-JAN-12 01.43.03.811156 PM +00:00                                                 1000 NO
OS AUDIT TRAIL                          1 19-JAN-12 01.43.12.972868 PM +00:00                                                 1000 NO
.............................

08:07:15 SYS@matrix1 > select OWNER,JOB_NAME,to_char(last_start_date,'DD-MM-YY HH24:MI:SS') Last_date_time, to_char(next_run_date,'DD-MM-YY HH24:MI:SS') Next_Date_Time, JOB_ACTION from DBA_SCHEDULER_JOBS;

OWNER      JOB_NAME                       LAST_DATE_TIME       NEXT_DATE_TIME       JOB_ACTION
---------- ------------------------------ -------------------- -------------------- --------------------------------------------------------------------------------
SYS        STANDARD_OS_AUDIT_TRAIL_PURGE  19-01-12 05:42:18    26-01-12 05:42:18    BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE);  END;
..........

I believe its a better and easier way for purging the audit files.To explore more check out the reference.

Reference – http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_audit_mgmt.htm#BABFHEFH