Shulou(Shulou.com)06/01 Report--

Versions of Oracle above 10.2.0.5 can use DBMS_AUDIT_MGMT to clean up audit

1. Check whether audit has been opened.

SQL > show parameter audit_trail

NAME TYPE VALUE

Audit_trail string DB, EXTENDED

2. Create audit tablespace

Create tablespace ts_audit datafile'/ oradata/ora10g/audit01.dbf' size 4000m autoextend off

3. Set the cleaning frequency

BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD

Default_cleanup_interval = > 12 / hours /)

END

/

4. Migrate AUD$ from SYSTEM tablespace to new tablespace

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD

Audit_trail_location_value = > 'TS_AUDIT')

END

/

5. Set last archive timestamp and keep it for 30 days.

BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD

Last_archive_time = > SYSDATE-30)

END

/

6. Set last archive timestamp job and keep it for 30 days

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

Job_name = > SET_AUDIT_ARCHIVE_TIMESTAMP'

Job_type = > 'PLSQL_BLOCK'

Job_action = > 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME = > SYSDATE-30); END;'

Start_date = > sysdate

Repeat_interval = > 'FREQ=HOURLY;INTERVAL=24'

Enabled = > TRUE

Comments = > 'set last archive timestamp'

);

END

/

7. Define automatic cleaning job

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD

Audit_trail_purge_interval = > 24 / hours /

Audit_trail_purge_name = > 'AUDIT_CLEANUP_JOB'

Use_last_arch_timestamp = > TRUE)

END

/

8. Check the execution of cleaning job. Delete_count should not be 0.

SQL > select * from dba_audit_mgmt_clean_events

AUDIT_TRAIL RAC_INSTANCE CLEANUP_TIME DELETE_COUNT WAS

STANDARD AUDIT TRAIL 0 16-MAY-19 07.18.59.937786 AM + 00:00 460 NO

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.