Today we are going to have look at the database security’s phase. In this phase, we are going to have look at Purging AUDIT TRAIL RECORDS
The parameterAUDIT_TRAIL_TYPE is specified using one of three constants.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.
If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.
Automated Purge with retention of 90 days.
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'archive_time', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90); DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90); DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90); DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;', end_date => NULL, enabled => TRUE, comments => 'last archive time.'); END; /
col owner for a20 select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- -------------------- ------------------------- --------------- 15-OCT-07 SYS AUD$ 0 20:03:01 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5); PL/SQL procedure successfully completed. Elapsed: 00:00:19.65 20:03:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- ---------- ------------------------- --------------- 15-OCT-07 SYS AUD$ 1982500 00:36:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 15-OCT-07 11.58.44.054379 PM 02:45:16 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 15-OCT-07 11.56.07.081514 PM Elapsed: 00:00:01.51 02:46:13 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -250); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 15-OCT-07 11.58.46.296859 PM Elapsed: 00:00:01.55 02:59:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 15-OCT-07 11.59.17.319628 PM 03:04:53 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000); 79572 rows deleted. Elapsed: 00:01:02.47 03:06:08 [SYS]>> commit; Commit complete. Elapsed: 00:00:00.02 03:06:38 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500); 64230 rows deleted. Elapsed: 00:00:56.64 03:07:58 [SYS]>> commit; Commit complete. Elapsed: 00:00:00.01 03:08:08 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -121); 39672 rows deleted. Elapsed: 00:00:46.57 03:09:07 [SYS]>> commit; Commit complete. Rebuilding Indexes ---------------------- 03:25:23 [SYS]>> select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name='AUD$' and owner='SYS'; OWNER INDEX_NAME INDEX_TYPE LAST_ANALYZED ------------ ---------------------------------------- ------------ --------------- SYS SYS_IL0000000375C00040$$ LOB SYS I_AUD1 NORMAL 15-OCT-07 -->If any Indexes listed above needs to rebuild indexes. 03:27:50 [SYS]>> alter index SYS.I_AUD1 rebuild online; Index altered. Elapsed: 00:00:05.39 03:35:59 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5); PL/SQL procedure successfully completed. 03:37:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- ------------ -------------------- --------------- 15-OCT-07 SYS AUD$ 123512
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
Anuradha’s LinkedIn: Anuradha’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
