To maintain the space and performance, We are scheduling the job which will be remove the audit records older then 30 days from the database.
Because database audit trails stored in the SYSAUX tablespace, they can fill it up and could start affecting other database operations that rely on the SYSAUX tablespace. In the eventuality of SYSAUX tablespace becoming full, audit record write will spillover to OS audit files and post a message to ALERT LOG so that corrective action can be initiated. If the OS file system space also becomes full, all database operations will start to fail.
Step-1 Login to the database and check the any of the job is existing related to purge the auditing records
SELECT OWNER, JOB_NAME, JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME like '%PURGE%'; -------------OR------------------
SELECT OWNER, JOB_NAME, JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME like '%UNIFIED%'; -------------OR------------------
SELECT OWNER, JOB_NAME, JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME like '%AUDIT%';
Step-2 Schedule the purge audit records older then 30 days for Unified auditing Trail
BEGIN DBMS_SCHEDULER.CREATE_JOB ( JOB_NAME => 'PURGE_UNIFIED_AUDIT_JOB', JOB_TYPE => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,SYSTIMESTAMP-30); DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => TRUE); END;', START_DATE => '', REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=0; BYSECOND=0;', END_DATE => NULL, ENABLED => TRUE, COMMENTS => 'Pruge unified audit trail older then 30 days.'); END; /
Step-3 SCheck the job scheduled or not from the database shown in below snap
Thank you.......Purging the audit records older then 30 days job has been scheduled........stay and connected.
Caution: It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
A. Rawat
Email: 88arawat@gmail.com