Scheduler Job for OS Audit Trail Cleanup Routine

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;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s