以下内容测试版本为oracle 19c，其他版本可能会略有不同。最好是在创建数据库之后就进行设置，审计数据越多迁移会越麻烦，还可能影响业务。
编号 | 需求项 | 需求细节内容 | 说明 | |
准备工作 | 1 | 检查是否打开审计 | show parameter audit | audit_trail为NONE则未开启 |
2 | 检查审计表现在所在表空间 | SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; | TABLE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- AUD$ SYSTEM FGA_LOG$ SYSTEM | |
3 | 检查审计相关表数据量 | select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$'); | 如果AUD$很大，迁移期间其他进程会被enq: ZA - add std audit table partition等待事件阻塞，需要先导出该表数据然后truncate | |
4 | 创建新审计表空间 | create tablespace TBAUDIT datafile size 1g autoextend on next 100m maxsize 30g; | 注意有没有启用OMF show parameter create | |
5 | 检查当前数据库的失效对象 | select OWNER,OBJECT_NAME,OBJECT_TYPE,status,TIMESTAMP,LAST_DDL_TIME from dba_objects where STATUS ='INVALID'; | 注意迁移后新增了哪些失效对象 | |
迁移审计表 | 6 | 迁移AUD$表 | BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'TBAUDIT'); END; / | .0625M约8s |
7 | 迁移FGA_LOG$表 | BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'TBAUDIT'); END; / | .0625M约7s | |
8 | 检查迁移结果 | SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$','FGA_LOG$') ORDER BY table_name; | TABLE_NAME TABLESPACE_NAME -------------------------------------------------------------------------------- AUD$ TBAUDIT FGA_LOG$ TBAUDIT | |
初始化清理对象和间隔（168h） | 9 | AUD$表 | BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 168 ); END; / | 验证： set lines 800 COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A20 COLUMN audit_trail FORMAT A20 SELECT * FROM dba_audit_mgmt_config_params WHERE PARAMETER_NAME = 'DEFAULT CLEAN UP INTERVAL'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- -------------------- DEFAULT CLEAN UP INTERVAL 168 FGA AUDIT TRAIL DEFAULT CLEAN UP INTERVAL 168 STANDARD AUDIT TRAIL |
10 | FGA_LOG$表 | BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, default_cleanup_interval => 168 ); END; / | ||
验证审计日志清除是否已开启 | 11 | 验证审计日志清除是否已开启 | SET SERVEROUTPUT ON BEGIN IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD) THEN DBMS_OUTPUT.put_line('YES'); ELSE DBMS_OUTPUT.put_line('NO'); END IF; END; / | 输出应为YES |
设置审计信息保留时间（90天） | 12 | AUD$表 | BEGIN DBMS_AUDIT_MGMT.set_last_archive_timestamp( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, last_archive_time => SYSTIMESTAMP-90); END; / | |
13 | FGA_LOG$表 | BEGIN DBMS_AUDIT_MGMT.set_last_archive_timestamp( audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std, last_archive_time => SYSTIMESTAMP-90); END; / | ||
查看审计数据最后归档时间 | 14 | 查看审计数据最后归档时间 | SELECT * FROM dba_audit_mgmt_last_arch_ts; | 只有归档的数据才能删除 |
schedule及job设置 | 15 | 创建清理的schedule | BEGIN DBMS_AUDIT_MGMT.create_purge_job( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_purge_interval => 168 /* hours */, audit_trail_purge_name => 'PURGE_STD_AUDIT_TRAILS', use_last_arch_timestamp => TRUE); END; / | |
16 | 修改清理job运行时间 | BEGIN SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'AUDSYS.PURGE_STD_AUDIT_TRAILS' ,attribute => 'START_DATE' ,value => TO_TIMESTAMP_TZ('2019/11/28 02:05:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr') ); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'AUDSYS.PURGE_STD_AUDIT_TRAILS' ,attribute => 'REPEAT_INTERVAL' ,value => 'FREQ=WEEKLY; BYDAY=SAT' ); END; / | 设置为每日凌晨2点5分 | |
17 | 创建schedule每天设置保留时间为90天前 | BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,start_date => TO_TIMESTAMP_TZ('2018/12/19 01:05:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr') ,repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,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 => SYSTIMESTAMP-90); DBMS_AUDIT_MGMT.set_last_archive_timestamp( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, last_archive_time => SYSTIMESTAMP-90); END;' ,comments => NULL ); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'RESTARTABLE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'LOGGING_LEVEL' ,value => SYS.DBMS_SCHEDULER.LOGGING_OFF); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'MAX_FAILURES'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'MAX_RUNS'); BEGIN SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'STOP_ON_WINDOW_CLOSE' ,value => FALSE); EXCEPTION -- could fail if program is of type EXECUTABLE... WHEN OTHERS THEN NULL; END; SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'JOB_PRIORITY' ,value => 3); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'SCHEDULE_LIMIT'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD' ,attribute => 'AUTO_DROP' ,value => TRUE); SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'); END; / | ||
18 | 检查2个schedule是否设置 | SELECT owner,job_name,run_count,next_run_date FROM DBA_SCHEDULER_JOBS WHERE job_name IN ('PURGE_STD_AUDIT_TRAILS','MOVE_LAST_TIMESTAMP_FORWARD'); | 检查运行时间是否是每天的凌晨1点5分和2点5分 | |
检查是否有新增的审计相关失效对象 | 19 | 如果跟AUDIT相关的，需重编译一下 | COLUMN OBJECT_NAME FORMAT A30 COLUMN OBJECT_TYPE FORMAT A20 COLUMN status FORMAT A20 COLUMN TIMESTAMP FORMAT A26 COLUMN LAST_DDL_TIME FORMAT A26 select OWNER,OBJECT_NAME,OBJECT_TYPE,status,TIMESTAMP,LAST_DDL_TIME from dba_objects where STATUS ='INVALID'; | alter view sys.DBA_FGA_AUDIT_TRAIL compile; alter view sys.DBA_COMMON_AUDIT_TRAIL compile; alter view SYS.FGA_LOG$FOR_EXPORT compile; alter public synonym DBA_COMMON_AUDIT_TRAIL compile; alter public synonym DBA_FGA_AUDIT_TRAIL compile; --for CDB alter view sys.CDB_FGA_AUDIT_TRAIL compile; alter view sys.CDB_COMMON_AUDIT_TRAIL compile; alter PACKAGE DBMS_AUDIT_UTIL compile; alter PUBLIC SYNONYM CDB_FGA_AUDIT_TRAIL compile; alter PUBLIC SYNONYM CDB_COMMON_AUDIT_TRAIL compile; alter PUBLIC SYNONYM DBMS_AUDIT_UTIL compile; |
保留备用 | 1 | 反向初始化方法 | exec sys.DBMS_AUDIT_MGMT.deinit_cleanup(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD); | |
2 | exec sys.DBMS_AUDIT_MGMT.deinit_cleanup(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD); | |||
3 | 删除清理schedule | exec sys.DBMS_AUDIT_MGMT.drop_purge_job('PURGE_STD_AUDIT_TRAILS'); | ||
4 | 删除重置时间的schedule | exec SYS.DBMS_SCHEDULER.DROP_JOB(job_name => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'); |