--環境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