数据库的审计信息一般较重，尤其在打开登录审计后，所有 session 的登录和退出都会在审计表中留有记录，由于表空间或者存储的限制，如果不能写入审计记录，数据库执行需要有审计的操作将被挂起，比如打开了 CREATE SESSION 审计，由于空间限制普通用户将不能登录数据库。需要有关办法来灵活的管理数据库的审计记录空间。 # 环境检查 ### 查看保留时间 ```sql select min(NTIMESTAMP#) from aud$; ``` ### 查看归档时间戳 ```sql select * from DBA_AUDIT_MGMT_LAST_ARCH_TS; ``` ### 查看空间使用情况 ```sql col owner for a20 col segment_name for a50 SELECT OWNER, SEGMENT_NAME, segment_type, TABLESPACE_NAME, BYTES/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('AUD$','FGA_LOG$'); ``` ### 查看是否有索引 ```sql SELECT I.INDEX_NAME, I.INDEX_TYPE, I.TABLE_OWNER, I.TABLE_NAME, I.UNIQUENESS, I.TABLESPACE_NAME, C.COLUMN_NAME, C.COLUMN_POSITION, C.COLUMN_LENGTH FROM DBA_INDEXES I, DBA_IND_COLUMNS C WHERE I.INDEX_NAME = C.INDEX_NAME AND I.TABLE_NAME in ('AUD$','FGA_LOG$'); ``` ### 查看审计表高水位 ```sql SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED, BLOCKS * 8 / 1024 TRUE, (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB FROM DBA_TABLES WHERE TABLE_NAME in ('AUD$','FGA_LOG$') AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100; ``` ### 查看回收作业 ```sql select * from DBA_AUDIT_MGMT_CLEANUP_JOBS; col owner format a6 col job_name format a20 col comments format a20 col program_owner format a6 col program_name format a15 col job_type format a12 col state format a9 col job_action format a100 col schedule_name format a20 col schedule_type format a14 col repeat_interval format a20 col last_start_date format a25 col last_run_duration format a25 col next_run_date format a25 SELECT OWNER, JOB_NAME, COMMENTS, PROGRAM_OWNER, PROGRAM_NAME, JOB_TYPE, STATE, JOB_ACTION, SCHEDULE_NAME, SCHEDULE_TYPE, REPEAT_INTERVAL, LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME=(SELECT JOB_NAME FROM DBA_AUDIT_MGMT_CLEANUP_JOBS) OR JOB_ACTION like '%SET_LAST_ARCHIVE_TIMESTAMP%'; ``` # 清理 针对已经分析过的老数据做自动清理，首先要初始化清理策略： ```plsql begin dbms_audit_mgmt.init_cleanup( audit_trail_type => dbms_audit_mgmt.audit_trail_db_std, default_cleanup_interval => 24 ); end; / ``` - audit_trail_type：指要设置的清理类型，这里是 audit_trail_db_std，标准的数据库审计跟踪，即 aud$ 表。其他清理类型详情请见下表： - audit_trail_aud_std：The standard AUD$ audit trail in the database - audit_trail_fga_std：The FGA_LOG$ table, for Fine Grained Auditing - audit_trail_db_std：Both standard and FGA audit trails - audit_trail_os：The OS audit trail - audit_trail_xml：The XML audit trail - audit_trail_files：Both OS and XML audit trails - audit_trail_all：All of the above - default_cleanup_interval：这个值表示每隔多少时间执行清理任务，24 即 24 小时。 ### 半自动清理（手动设置清理的时间） 我们需要设置一个过程参数 SET_LAST_ARCHIVE_TIMESTAMP，它的作用是告诉清理进程一个审计归档时间戳。 ```plsql begin dbms_audit_mgmt.set_last_archive_timestamp( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, last_archive_time => to_timestamp('2021-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), rac_instance_number => null ); end; / ``` - audit_trail_type：要清理的审计类型，如 audit_trail_aud_std - last_archive_time：最后一次归档时间，可以手工设置 - rac_instance_number：RAC 的话可以指定实例号 有了归档时间后，就可以执行真正的清理程序了。 ```plsql begin dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, use_last_arch_timestamp => TRUE ); end; / ``` - audit_trail_type 表示清理的审计类型 - use_last_arch_timestamp：TRUE，表示用最后的归档时间，如果为 false，将会清理掉 audit_trail_aud_std 类型的所有审计信息。 ### 自动清理 第一个任务：自动产生清理时间点 ```plsql BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'AUDPURGE', 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-180); END;', start_date => sysdate, repeat_interval => 'FREQ=HOURLY;INTERVAL=24', enabled => TRUE, comments => 'Create an archive timestamp' ); END; / ``` 第二个任务：根据时间点自动清理 ```plsql 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 => 'DAILY_AUD_PURGE', USE_LAST_ARCH_TIMESTAMP => TRUE ); END; / ``` ### 清理任务优化 到此数据库审计自动清理就可以实现了，但是从数据库的性能考虑，如果您的数据库很大，我们就得考虑数据的审计清理的性能，oracle11g 也提供了一个很好的解决方法，设置清理批处理时 commit 行数。如下： ```plsql begin dbms_audit_mgmt.set_audit_trail_property( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, audit_trail_property => dbms_audit_mgmt.db_delete_batch_size, audit_trail_property_value => 100000); end; / ``` - audit_trail_type 清理类型 - audit_trail_property 设置删除批处理 - audit_trail_property_value 表示每次 commit 行数 100000。 ### 空间回收 由于清理操作为 delete 操作，而非 truncate，所以要定时收缩一下表。 ```sql ALTER TABLE SYS.AUD$ ENABLE ROW MOVEMENT; ALTER TABLE SYS.AUD$ SHRINK SPACE CASCADE; ALTER TABLE SYS.AUD$ DISABLE ROW MOVEMENT; EXEC dbms_stats.gather_table_stats('SYS','AUD$'); ALTER TABLE SYS.FGA_LOG$ ENABLE ROW MOVEMENT; ALTER TABLE SYS.FGA_LOG$ SHRINK SPACE CASCADE; ALTER TABLE SYS.FGA_LOG$ DISABLE ROW MOVEMENT; EXEC dbms_stats.gather_table_stats('SYS','FGA_LOG$'); ```