Hello all!
As you all know, most of OS files generated by oracle, like traces and logs can be cleared/managed using ADRCI. However, more than once, I saw filesystem get full of Audit Trail files.
In general, I see DBAs and companies implementing shell scripts to house keep those files. Usually some variation of the find with rm I posted some weeks ago. However, we have a very good “official” solition for this, using the DBMS_AUDIT_MGMT.
Basically we can create Scheduler Jobs in our databases to keep track on this. The advantages are seveal. The job clear in both nodes, this keep all Oracle information correctly updated, you may receive OEM notification for failure if monitoring jobs, etc.
You can see more detaiils here in MOS Note 731908.1 New Feature DBMS_AUDIT_MGMT to Manage and Purge Audit Information.
How I did?
1. Initial Cleanup to clear files older than a week.
2. Set last archive to a week ago.
3. Created Purge Job, purging older than a week
As per:
BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_OS, default_cleanup_interval => 24*7); end; / begin DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, last_archive_time => sysdate - 30); end; / 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; /
Complete example, in my case:
SQL> show parameter audit; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/oemrepo/ adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 SQL> set lines 150 pages 350 col parameter_name format a35 col parameter_value format a35 col audit_trail format a35 select parameter_name, parameter_value, audit_trail from dba_audit_mgmt_config_params where parameter_name like 'AUDIT FILE%';SQL> SQL> SQL> SQL> 2 PARAMETER_NAME PARAMETER_VALUE 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 AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL 6 rows selected. SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS; no rows selected SQL> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS; no rows selected SQL> 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 DB AUDIT TABLESPACE SYSAUX UNIFIED 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 AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL 14 rows selected. SQL> BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_OS, default_cleanup_interval => 24*7); end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> SQL> begin DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, last_archive_time => sysdate - 30); end; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> 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; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed. SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS; JOB_NAME -------------------------------------------------------------------------------- JOB_STAT AUDIT_TRAIL -------- ---------------------------- JOB_FREQUENCY -------------------------------------------------------------------------------- USE JOB_CON --- ------- STANDARD_OS_AUDIT_TRAIL_PURGE ENABLED OS AUDIT TRAIL FREQ=HOURLY;INTERVAL=168 YES CURRENT SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS; AUDIT_TRAIL RAC_INSTANCE -------------------- ------------ LAST_ARCHIVE_TS --------------------------------------------------------------------------- DATABASE_ID CONTAINER_GUID ----------- --------------------------------- OS AUDIT TRAIL 1 04-OCT-16 08.10.55.000000 PM -04:00 1582224112 26C061B3C8346D29E053D17BF00AE837 SQL> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS; AUDIT_TRAIL RAC_INSTANCE ---------------------------- ------------ CLEANUP_TIME --------------------------------------------------------------------------- DELETE_COUNT WAS ------------ --- OS AUDIT TRAIL 1 04-NOV-16 12.11.20.768819 AM +00:00 424 NO SQL> 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 where job_name='STANDARD_OS_AUDIT_TRAIL_PURGE';SQL> 2 OWNER -------------------------------------------------------------------------------- JOB_NAME -------------------------------------------------------------------------------- LAST_DATE_TIME NEXT_DATE_TIME ----------------- ----------------- JOB_ACTION -------------------------------------------------------------------------------- SYS STANDARD_OS_AUDIT_TRAIL_PURGE 03-11-16 20:11:20 10-11-16 19:11:20 BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE, 1); END;
