手动清理Oracle审计记录

1、第 PAGE17 页 共 NUMPAGES17 页手动清理Oracle审计记录 手动清理 Oracle 审计记录 Oracle 数据库审计功能非常强大，通常包括标准审计(包括用户级审计和系统级审计)和细粒度审计。尽管如此，一不小心就容易造成性能问题。同时会把系统表空间给撑爆。下面的内容描述的是如何将审计从系统表空间剥离以及清理 Oracle 审计记录，供大家参考。一、审计的相关配置 -环境SQL select _from v$version where rownum=1;BANNEROracle Database 11g Enterprise Edition Release 11.2.0.1.

2、0 - 64bit ProductionSQL show parameter auditNAMETYPEVALUE audit_file_deststring.0/dbhome_1/rdbms/auditaudit_sys_operationsbooleanFALSEaudit_syslog_levelstringaudit_trailstringDB-此值为当前 Oracle 11gR2 缺省配置-从下面的查询中可以看出，当前的审计位于 system 表空间SQL col segment_name FOR a10SQL SELECT owner,segment_name,tablespace

3、_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.dbf2SIZE 100M autoe_tend ON NE_T 50M;TABLESPACE_NAMEUSED (MB FREE (MB TOTAL (M PER_FR AUDIT_DATA11,

4、1991,20_100 %SYSAU_1,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 line 1:ORA-46267: Insufficient space in AUDIT_DATA tablespa

5、ce, cannot pleteoperationORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 1576ORA-06512: at line 2- 错误提示，尽管我们使用了自动扩展表空间，依旧提示空间不够- 查看当前审计数据大小，如下为 1152MBSQL select segment_name,bytes/1024/1024 from dba_segments where segment_name=AUD$;SEGMENT_NAMEBYTES/1024/1024 AUD$1152- 下面调整数据文件大小SQL alter database datafil

6、e /home/oracle/app/oradata/orcl/audit01.dbf resize 120_m;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/-整个过程花费了 2m23s，主要是期间进行了数据搬迁SQL SELECT owner,segme

7、nt_name,tablespace_name FROM dba_segments WHERE segment_name =AUD$;OWNERSEGMENT_NAMETABLESPACE_NAME SYSAUD$AUDIT_DATATABLESPACE_NAMEUSED (MB FREE (MB TOTAL (M PER_FR AUDIT_DATA1,153471,20_4 %SYSAU_1,143671,2106 %SYSTEM7241,1661,89062 %- 从上面的这个查询可以看出，原来位于 system 表空间的 AUD$被迁移到了 AUDIT_DATA- 相应地 AUDIT_D

8、ATA 表空间已使用增加，而 SYSTEM 表空间使用率下降- 查看审计数据字典配置信息SQL col PARAMETER_NAME FOR a30SQL col PARAMETER_VALUE FOR a15SQL col AUDIT_TRAIL FOR a20SQL SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL2FROM DBA_AUDIT_MGMT_CONFIG_PARAMS3WHERE audit_trail = STANDARD AUDIT TRAIL;PARAMETER_NAMEPARAMETER_VALUE AUDIT_T

9、RAIL DB AUDIT TABLESPACEAUDIT_DATASTANDARD AUDIT TRAILDB 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/SQL procedure successfully plet

10、ed. - 下面严验证审计日志清除是否已开启SQL SET SERVEROUTPUT ONSQL BEGIN2IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN3DBMS_OUTPUT.put_line(YES);4ELSE5DBMS_OUTPUT.put_line(NO);6END IF;7END;8/YESPL/SQL procedure successfully pleted. SQL select segment_name,bytes/1024/1024 from dba

11、_segments where segment_name=AUD$;SEGMENT_NAMEBYTES/1024/1024 AUD$1152SQL select Leshami As author,leshami as Blog from dual;AUTHORBLOG Leshami leshamiSQL select count(_) from AUD$;COUNT(_)5908086SQL select min(ntimest#) from aud$;MIN(NTIMEST#)20-AUG-14 06.11.09.901253 AM- 设定归档间隔SQL BEGIN2DBMS_AUDIT

12、_MGMT.set_last_archive_timest(3audit_trail_type= DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,4last_archive_time = SYSTIMEST-10);5END;6/PL/SQL procedure successfully pleted-查看设定的归档间隔SQL SELECT _FROM dba_audit_mgmt_last_arch_ts;AUDIT_TRAILRAC_INSTANCE LAST_ARCHIVE_TS STANDARD AUDIT TRAIL09-OCT-15 01.27.17.000

13、000 PM +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_timest = TRUE);END;/This procedure deletes audit trail records.The CLEAN_AUDIT_TRAIL procedure is usually called after theSET_LAST

14、_ARCHIVE_TIMEST Procedure has been used to set the last archived timest for the audit records. -也可以通过创建一个 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= Da

15、ily_Audit_Purge_Job,6USE_LAST_ARCH_TIMEST= TRUE7 );8END;9/PL/SQL procedure successfully pleted. - 本次测试使用了 job 进行清理，注，上面的 purge job 并非使用 DBMS_SCHEDULER.CREATE_JOB 创建- 执行 job 用于清理归档，通过观察，由于 redo log size 为 50MB，切换较为频繁，花费了 19 分钟- 同时伴随有 Checkpoint not plete 等待事件，可见 redo size 过小SQL e_ec DBMS_SCHEDULER.RU

16、N_JOB(job_name = SYS.DAILY_AUDIT_PURGE_JOB);SQL select count(_) from AUD$;COUNT(_)12-经查看，清理后空间并没有释放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 cas

17、cade_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四、小结 a、对于 Oracle 11g，审计功能默认被开启，因此如果在