Oracle 审计表AUD$迁移表空间及创建清理job

深藏阁楼爱情的钟 2022-10-02 04:43 498阅读 0赞

Oracle 10.2.0.5以上的版本可以使用DBMS\_AUDIT\_MGMT清理audit  
1、查看audit是否已经打开  
SQL> show parameter audit\_trail

NAME TYPE VALUE

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

audit\_trail string DB, EXTENDED

2、创建audit tablespace  
create tablespace ts\_audit datafile '/oradata/ora10g/audit01.dbf' size 4000M autoextend off;  
3、设置清理频率

BEGIN  
DBMS\_AUDIT\_MGMT.INIT\_CLEANUP(  
audit\_trail\_type => DBMS\_AUDIT\_MGMT.AUDIT\_TRAIL\_AUD\_STD,  
default\_cleanup\_interval => 12 / *hours* /);  
END;  
/

4、将AUD$从SYSTEM表空间迁移到新的表空间  
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、设置last archive timestamp，保留30天  
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、设置last archive timestamp job，保留30天  
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、定义自动清理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、查看清理job执行情况，delete\_count应该不为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

转载于:https://blog.51cto.com/395138/2396005