Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle audit table AUD$ migrate tablespace and create cleanup job

2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report