--环境SQL>select* from v$version where rownum=1; BANNER--------------------------------------------------------------------------------Oracle Database11g Enterprise EditionRelease11.2.0.1.0-64bitProduction SQL> show parameter audit NAMETYPEVALUE------------------------------------ ----------- ------------------------------audit_file_deststring/home/oraprod/app/product/11.2.0/dbhome_1/rdbms/audit audit_sys_operationsbooleanFALSE audit_syslog_levelstringaudit_trailstringDB--此值为当前Oracle 11gR2缺省配置--从下面的查询中可以看出，当前的审计位于system表空间SQL> col segment_nameFORa10 SQL>SELECTowner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =AUD$; OWNER SEGMENT_NA TABLESPACE_NAME------------------------------ ---------- ------------------------------SYS AUD$ SYSTEM

新增1个表空间用于存储审计日志 SQL> CREATE tablespace audit_data datafile /home/oracle/app/oradata/orcl/audit01.dbf2SIZE100M autoextendONNEXT50M; SQL> @tbs_free.sql TABLESPACE_NAME USED (MB FREE (MB TOTAL (M PER_FR------------------------------ -------- -------- -------- ------AUDIT_DATA11,1991,200100% SYSAUX1,133771,2106% SYSTEM1,875151,8901%-- 设定审计数据寄存表空间SQL>BEGIN2DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(3AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,4AUDIT_TRAIL_LOCATION_VALUE =>AUDIT_DATA5);6END;7/BEGIN* ERROR at line1: ORA-46267: Insufficient spaceinAUDIT_DATA tablespace, cannot complete operation ORA-06512: at"SYS.DBMS_AUDIT_MGMT", line1576ORA-06512: at line2-- 毛病提示，虽然我们使用了自动扩大表空间，照旧提示空间不够-- 查看当前审计数据大小，以下为1152MBSQL>selectsegment_name,bytes/1024/1024from dba_segments where segment_name=AUD$; SEGMENT_NAME BYTES/1024/1024------------------------- ---------------AUD$1152-- 下面调剂数据文件大小SQL> alter database datafile /home/oracle/app/oradata/orcl/audit01.dbf resize1200m; Database altered.-- 再次设定审计数据寄存表空间OKSQL>BEGIN2DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(3AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,4AUDIT_TRAIL_LOCATION_VALUE =>AUDIT_DATA5);6END;7/ PL/SQLproceduresuccessfully completed. Elapsed:00:02:23.10--全部进程花费了2m23s，主要是期间进行了数据搬迁SQL>SELECTowner,segment_name,tablespace_name FROM dba_segments WHERE segment_name =AUD$; OWNER SEGMENT_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------SYS AUD$ AUDIT_DATA SQL> @tbs_free.sql TABLESPACE_NAME USED (MB FREE (MB TOTAL (M PER_FR------------------------------ -------- -------- -------- ------AUDIT_DATA1,153471,2004% SYSAUX1,143671,2106% SYSTEM7241,1661,89062%-- 从上面的这个查询可以看出，原来位于system表空间的AUD$被迁移到了AUDIT_DATA-- 相应地AUDIT_DATA表空间已使用增加，而SYSTEM表空间使用率降落-- 查看审计数据字典配置信息SQL> col PARAMETER_NAMEFORa30 SQL> col PARAMETER_VALUEFORa15 SQL> col AUDIT_TRAILFORa20 SQL>SELECTPARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL2FROM DBA_AUDIT_MGMT_CONFIG_PARAMS3WHERE audit_trail =STANDARDAUDIT TRAIL; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL------------------------------ --------------- --------------------DB AUDIT TABLESPACE AUDIT_DATA STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE10000STANDARD AUDIT TRAIL

通过这个进程设定清除间隔 SQL>BEGIN2DBMS_AUDIT_MGMT.init_cleanup(3audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,4default_cleanup_interval =>120/* hours */);5END;6/ PL/SQLproceduresuccessfully completed.-- 下面严验证审计日志清除是不是已开启SQL> SET SERVEROUTPUTONSQL>BEGIN2IFDBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)THEN3DBMS_OUTPUT.put_line(YES);4ELSE5DBMS_OUTPUT.put_line(NO);6ENDIF;7END;8/ YES PL/SQLproceduresuccessfully completed. SQL>selectsegment_name,bytes/1024/1024from dba_segments where segment_name=AUD$; SEGMENT_NAME BYTES/1024/1024------------------- ---------------AUD$1152SQL>selectLeshami As author,http://blog.csdn.net/leshami as Blog from dual; AUTHOR BLOG------- ----------------------------Leshami http://blog.csdn.net/leshami SQL>selectcount(*) from AUD$; COUNT(*)----------5908086SQL>selectmin(ntimestamp#) from aud$; MIN(NTIMESTAMP#)---------------------------------------------------------------------------20-AUG-1406.11.09.901253AM-- 设定归档间隔SQL>BEGIN2DBMS_AUDIT_MGMT.set_last_archive_timestamp(3audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,4last_archive_time => SYSTIMESTAMP-10);5END;6/ PL/SQLproceduresuccessfully completed--查看设定的归档间隔SQL>SELECT* FROM dba_audit_mgmt_last_arch_ts; AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS-------------------- ------------ ---------------------------------------------------------------------------STANDARD AUDIT TRAIL009-OCT-1501.27.17.000000PM +00:00--通过调用DBMS_AUDIT_MGMT.clean_audit_trail进行手动清算审计日志BEGINDBMS_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_trail Thisproceduredeletes audit trail records. The CLEAN_AUDIT_TRAILprocedureisusually calledafterthe SET_LAST_ARCHIVE_TIMESTAMPProcedurehas been usedtoset the last archived timestampforthe audit records.--也能够通过创建1个purge Job来进行清算已归档的历史审计记录SQL>BEGIN2DBMS_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 => TRUE7);8END;9/ PL/SQLproceduresuccessfully completed.-- 本次测试使用了job进行清算，注，上面的purge job 并不是使用DBMS_SCHEDULER.CREATE_JOB创建-- 履行job用于清算归档，通过视察，由于redo log size为50MB，切换较为频繁，花费了19分钟-- 同时伴随有Checkpoint not complete等待事件，可见redo size太小SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name =>SYS.DAILY_AUDIT_PURGE_JOB); PL/SQLproceduresuccessfully completed. Elapsed:00:19:26.38SQL>selectcount(*) from AUD$; COUNT(*)----------12--经查看，清算后空间并没有释放SQL>selectsegment_name,bytes/1024/1024from dba_segments where segment_name=AUD$; SEGMENT_NAME BYTES/1024/1024------------------------------ ---------------AUD$1152SQL> alter table sys.aud$ shrink space cascade; alter table sys.aud$ shrink space cascade * ERROR at line1: ORA-10636: ROW MOVEMENTisnotenabled SQL> 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>selectsegment_name,bytes/1024/1024from dba_segments where segment_name=AUD$; SEGMENT_NAME BYTES/1024/1024-------------------- ---------------AUD$.0625