Friday, December 11, 2020

Cleanup of Audit Trail in Oracle

 

Its been a while, since I blogged. Lot of things are going on like most of us. 

Hopefully , in the coming weeks and 2021 onwards, things go smooth

It is recommended that we move  objects related to audit trail ( Unified Audit Trail / FGA Audit Trail , ... ) to a different tablespace rather than default tablespace. We can use dbms_audit_mgmt package to perform the move.

In the following example , we moved the audit related objects to a different tablespace TS_AUD_TRAIL


SQL> Select table_name , tablespace_name

  2  from dba_tables where table_name in ( 'AUD$' , 'FGA_LOG$')

  3  ;

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

FGA_LOG$                       SYSAUX

AUD$                           SYSAUX

 

SQL> begin

  2    dbms_audit_mgmt.set_audit_trail_location( audit_trail_type =>dbms_audit_mgmt.audit_trail_aud_std   ,audit_trail_location_value => 'TS_AUD_TRAIL' ) ;

  3  end ;

  4  /

 

PL/SQL procedure successfully completed.

 

SQL> begin

       dbms_audit_mgmt.set_audit_trail_location( audit_trail_type =>dbms_audit_mgmt.audit_trail_fga_std   ,audit_trail_location_value => 'TS_AUD_TRAIL' ) ;

     end;

/

 

PL/SQL procedure successfully completed.

 

SQL> Select table_name , tablespace_name

     from dba_tables where table_name in ( 'AUD$' , 'FGA_LOG$')

     /

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

FGA_LOG$                       TS_AUD_TRAIL

AUD$                           TS_AUD_TRAIL

 

Also , it is better to do a clean up of these records as it is expected to grow. This is important , if we don't move these tables from the default tablespace. There are few other bugs that also causes SYSAUX to grow.
Of course , we need to make sure that we are in compliance with the corporate's retention policy . 
Management of audit trail can be either be manual or automatic. 

To manually clear the audit trail , we can follow the following steps. 
We will first check , when was the last time , we did manual cleanup. 

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

no rows selected


DBA_AUDIT_MGMT_LAST_ARCH_TS shows that there was no previous cleanup. 
Now , I want to purge any records before Jan 1st 2020. For this , I set the date in DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP and then perform the clean up.

SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  4      LAST_ARCHIVE_TIME => '01-JAN-2020');
  5  END;
  6  /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

After the clean up , we re run the query to list the contents from DBA_AUDIT_MGMT_LAST_ARCH_TS. Now , it has the entry .

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL          RAC_INSTANCE
-------------------- ------------
LAST_ARCHIVE_TS
---------------------------------------------------------------------------
DATABASE_ID CONTAINER_GUID
----------- ---------------------------------
UNIFIED AUDIT TRAIL             0
01-JAN-20 12.00.00.000000 AM +00:00
5553077069 733F2AB490104389843DFGFADFDS


SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      LAST_ARCHIVE_TIME => '01-JAN-2020');
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 177
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 941
ORA-06512: at line 2


Elapsed: 00:00:00.01
SQL> SELECT JOB_NAME, JOB_STATUS, AUDIT_TRAIL from DBA_AUDIT_MGMT_CLEANUP_JOBS;

no rows selected

Elapsed: 00:00:00.00
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 24);
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.32
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4      LAST_ARCHIVE_TIME => '01-JAN-2020');
  5  END;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.clean_audit_trail(
  3     audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4     use_last_arch_timestamp => TRUE);
  5  END;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.70
SQL>


By performing clean up of these audit trail , we can keep the tablespace's storage under control. 
Comments welcome. 

 






No comments:

Post a Comment