oracle审计表迁移&备份清理

============ oracle审计表迁移到新的表空间 ============
前言

oracle数据库开启审计功能后会占用大量的SYSTEM系统表空间，要么定时对审计表进行清理，要么对系统表空间进行扩容（不建议）

1. 检查审计表现在所在表空间
   SQL> 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

2)检查审计表的当前数据量
select segment_name,bytes/1024/1024 size_in_mb from dba_segments where segment_name in (‘AUD$’,‘FGA_LOG$’);

SEGMENT_NAME SIZE_IN_MB

FGA_LOG$ 10380

AUD$ 4413

3. 创建新表空间 （审计用）
   SQL>create tablespace audit_tbs datafile size 16G autoextend on;

Tablespace created.

4. 迁移存储表
   使用存储过程：DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION

#迁移aud$

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,–this moves table AUD$
audit_trail_location_value => ‘AUDIT_TBS’);
END;
/
PL/SQL procedure successfully completed.

#迁移FGA_LOG$表

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,–this moves table FGA_LOG$
audit_trail_location_value => ‘AUDIT_TBS’);
END;
/
PL/SQL procedure successfully completed.

5. 检查迁移结果
   SQL> SELECT table_name, tablespace_name FROM dba_tables
   WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’) ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME

AUD$ AUDIT_TBS
FGA_LOG$ AUDIT_TBS

#对于没有特别要求的可以直接对审计表进行清理

SQL>truncate table sys.aud$;

#对于有要求保留审计日志的可以进行定期备份后再清理

nohup exp \'/ as sysdba\' rows=y indexes=n compress=n buffer=65536 file=web_audit_20190516.dmp log=exp_web_audit_20190516.log tables=sys.aud &