Monday, February 9, 2015

DELETE OS AUDIT FILES IN ORACLE



[atoorpu@ORACLE1 adump]$ pwd
/u01/app/oracle/admin/ORCL/adump
[atoorpu@ORACLE1 adump]$ ls -1 /u01/app/oracle/admin/ORCL/adump | wc -l
22273
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
11363
[atoorpu@ORACLE1 adump]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 9 10:40:58 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

If the audit files are in the database (sys.aud$). They can be cleaned up using:

SQL> DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);

If the audit files are in the OS. They can be cleaned up using:
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  

PL/SQL procedure successfully completed.

The CLEAN_AUDIT_TRAIL procedure is the basic mechanism for manually purging the audit trail. It accepts two parameters.

AUDIT_TRAIL_TYPE: The audit trail whose timestamp is to be set (Constants). Only individual audit trails are valid, not the constants that specify multiples.

Types :
AUDIT_TRAIL_XML  ---- For Auditing on XML (XML files)
AUDIT_TRAIL_OS   --- For Auditing on OS (text files)
AUDIT_TRAIL_AUD_STD  --- For Standard Auditing

USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

 Lets see if the Last  Archive TS is  set for OS Audit files.


SQL> set pagesize 150
set linesize 150
col last_archive_ts format a40
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;


AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00



 You can also set the Last  Archive TS  if it is not set 
(in below it will set OS_AUDIT TS to sydate-45) :

SQL> BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-45);
END;
/  

PL/SQL procedure successfully completed.


 Now Lets confirm the last Archive Time stamp in DB.

SQL> COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;SQL> SQL> SQL>

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ----------------------------------------
STANDARD AUDIT TRAIL            0 23-DEC-14 03.34.45.000000 PM +00:00
OS AUDIT TRAIL                  1 26-DEC-14 10.43.05.000000 AM -06:00

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
   END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[atoorpu@ORACLE1 adump]$ ls -lrt *aud | wc -l
1602
[atoorpu@ORACLE1 adump]$

No comments:

Post a Comment