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.
