Oracle 数据库审计功能非常强大，通常包括标准审计(包括用户级审计和系统级审计)和细粒度审计。尽管如此，一不小心就容易造成性能问题。同时会把系统表空间给撑爆。下面的内容描述的是如何将审计从系统表空间剥离以及清理Oracle审计记录，供大家参考。一、审计的相关配置–环境SQL> select * from v$version where rownum=1;BANNER——————————————————————————–Oracle Database – 64bit ProductionSQL> show parameter auditNAMETYPEVALUE———————————— ———– ——————————audit_file_deststring/home/oraprod/app/product/11.2.0/dbhome_1/rdbms/auditaudit_sys_operationsbooleanFALSEaudit_syslog_levelstringaudit_trailstringDB –此值为当前Oracle 11gR2缺省配置–从下面的查询中可以看出，当前的审计位于system表空间SQL> col segment_name FOR a10SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =’AUD$’;OWNERSEGMENT_NA TABLESPACE_NAME—————————— ———- ——————————SYSAUD$SYSTEM二、修改审计存储表空间新增一个表空间用于存储审计日志SQL> CREATE tablespace audit_data datafile ‘/home/oracle/app/oradata/orcl/audit01.dbf’ M;SQL> @tbs_free.sqlTABLESPACE_NAMEUSED (MB FREE (MB TOTAL (M PER_FR—————————— ——– ——– ——– ——AUDIT_DATA,200 100 %SYSAUX,210 6 %SYSTEM,890 1 %– 设定审计数据存放表空间 SQL> BEGIN 2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 3AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4AUDIT_TRAIL_LOCATION_VALUE => ‘AUDIT_DATA’ 5 ); 6 END; 7 /BEGIN*ERROR at line 1:ORA-46267: Insufficient space in ‘AUDIT_DATA’ tablespace, cannot completeoperationORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 1576ORA-SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’;SEGMENT_NAMEBYTES/1024/1024————————- —————AUD$1152– 下面调整数据文件大小SQL> alter database datafile ‘/home/oracle/app/oradata/orcl/audit01.dbf’ resize 1200m;Database altered.– 再次设定审计数据存放表空间OK SQL> BEGIN 2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 3AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4AUDIT_TRAIL_LOCATION_VALUE => ‘AUDIT_DATA’ 5 ); 6 END; 7 /PL/SQL procedure successfully completed.Elapsed: 00:02:23.10–整个过程花费了2m23s，主要是期间进行了数据搬迁SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =’AUD$’;OWNERSEGMENT_NAMETABLESPACE_NAME—————————— —————————— ——————————SYSAUD$AUDIT_DATASQL> @tbs_free.sqlTABLESPACE_NAMEUSED (MB FREE (MB TOTAL (M PER_FR—————————— ——– ——– ——– ——AUDIT_DATA,200 4 %SYSAUX,210 6 %SYSTEM,890 62 %SQL> col PARAMETER_NAME FOR a30SQL> col PARAMETER_VALUE FOR a15SQL> col AUDIT_TRAIL FOR a20SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL 2 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 3 WHERE audit_trail = ‘STANDARD AUDIT TRAIL’;PARAMETER_NAMEPARAMETER_VALUE AUDIT_TRAIL—————————— ————— ——————–DB AUDIT TABLESPACEAUDIT_DATASTANDARD AUDIT TRAILDB AUDIT CLEAN BATCH SIZE10000STANDARD AUDIT TRAIL三、清除审计记录通过这个过程设定清除间隔SQL> BEGIN 2 DBMS_AUDIT_MGMT.init_cleanup( 3audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 4default_cleanup_interval => 120 /* hours */); 5 END; 6 /PL/SQL procedure successfully completed.– 下面严验证审计日志清除是否已开启SQL> SET SERVEROUTPUT ONSQL> DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN 3DBMS_OUTPUT.put_line(‘YES’); DBMS_OUTPUT.put_line(‘NO’); ; 7 END; 8 /YESPL/SQL procedure successfully completed.SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’;SEGMENT_NAMEBYTES/1024/1024——————- —————AUD$1152SQL> select ‘Leshami’ As author,’http://blog.csdn.net/leshami’ as Blog from dual;AUTHOR BLOG——- —————————-Leshami SQL> select count(*) from AUD$; COUNT(*)———- 5908086SQL> select min(ntimestamp#) from aud$;MIN(NTIMESTAMP#)AM– 设定归档间隔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 => SYSTIMESTAMP-10); 5 END; 6 /PL/SQL procedure successfully completed–查看设定的归档间隔SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;AUDIT_TRAILRAC_INSTANCE LAST_ARCHIVE_TS——————– ———— —————————————————————————STANDARD AUDIT TRAILPM +DBMS_AUDIT_MGMT.clean_audit_trail( audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp => TRUE);END;/DBMS_AUDIT_MGMT.clean_audit_trailThis procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.–也可以通过创建一个purge Job来进行清理已归档的历史审计记录SQL> BEGIN 2 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( 3AUDIT_TRAIL_TYPE=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */, 5AUDIT_TRAIL_PURGE_NAME=> ‘Daily_Audit_Purge_Job’, 6USE_LAST_ARCH_TIMESTAMP => TRUE 7 ); 8 END; 9 /PL/SQL procedure successfully completed.SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name => ‘SYS.DAILY_AUDIT_PURGE_JOB’);PL/SQL procedure successfully completed.Elapsed: 00:19:26.38SQL> select count(*) from AUD$; COUNT(*)SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’;SEGMENT_NAMEBYTES/1024/1024—————————— —————AUD$1152SQL> alter table sys.aud$ shrink space cascade;alter table sys.aud$ shrink space cascade*ERROR at line 1:ORA-10636: ROW MOVEMENT is not enabledSQL> alter table sys.aud$ enable row movement;Table altered.SQL> alter table sys.aud$ shrink space cascade;Table altered.SQL> alter table sys.aud$ disable row movement;Table altered.– 下面的查询可以看到，空间已经被释放SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’;SEGMENT_NAMEBYTES/1024/1024——————– —————AUD$.0625四、小结生活若剥去了理想、梦想、幻想，那生命便只是一堆空架子
手动清理Oracle审计记录
