审计信息清理及审计表迁移时遇到的坑

        晚上十点三十五分左右，客户的业务无法访问数据库，至于报错是什么，忘记询问了。这是个坏习惯，应该一开始就询问，这样子可以最快找到解决问题的方案。

        一开始客户联系我，让我帮忙检查一下数据库的状态，我首先查询了监听服务（lsnctl status），因为我所接触的大部分故障都是由于监听问题导致的。此处，监听服务正常。

        查看alert日志，发现system表空间无法扩展，一般而言，system表空间会自动管理，不会需要太多空间（此处尚需要进行研究）。首先将system表空间数据文件的扩展方式修改可自动的。

        接下来联想到数据库是否开启审计，通过以下命令确认

show parameter audit

        发现audit_trail为DB（即将audit trail记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息），即已经开启审计，查询aud$表，发现有5亿条数据(select count(*) from sys.aud$;)，保留了一年多的数据

        占据空间大约50G(select bytes/1024/1024/1024 from dba_segments where segment_name='AUD$';)

        建议客户删除审计信息，客户决定保留半年，并配置清除审计信息的job。

        决定使用DBMS_AUDIT_MGMT设置定期清理任务，首先需要初始化

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval => 12 /* hours */);
END;
/

        初始化的时候，如果aud$没有进行迁移的话，会默认迁移到SYSAUX上，由于空间不足，报错。

        尝试手动迁移:

alter table sys.aud$ move tablespace SYSAUX;
alter table sys.aud$ move lob(sqlbind) store as (tablespace SYSAUX);
alter table sys.aud$ move lob(sqltext) store as (tablespace SYSAUX);
alter index sys.I_AUD1 rebuild tablespace SYSAUX;

        坑来了，以上脚本从boss的文档而来，手动迁移的时候，业务无法访问，同时导致system表空间数据文件出现碎片，无法回收，并且产生了大量的归档，把磁盘挤爆了，心跳加速系列，赶紧删已备份的归档，空间仍旧不足，移动归档到emc上面，移动失败，由于emc是通过网络连接的。

        但是move完表sys.aud$，movelob字段的时候也花费了很多时间。客户联系我，备份已经跑起来，带宽基本被耗尽，是无法移动归档的，除非停止备份。

        建议客户删除aud$，毕竟aud$只记录了记录登录信息，无法审计违法操作。客户认同方案。

        此时alter table sys.aud$ move lob(sqltext) store as (tablespace SYSAUX);的操作被我中断，直接TRUNCATE TABLE AUD$;这个过程很快,基本不产生redo。

        然后回收高水位，清理空间

alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;
alter table sys.aud$ disable row movement;

       再次手动迁移，这次很快，这时已经接近凌晨三点，由此可见，50G大小的表aud$迁移耗时相当长，同时产生了巨量归档，平时一天三四十G的归档，当晚产生了300多G的归档，不知道是否触发bug,此处需要对alter table XX move tablespace XX进行研究，这个过程到底发生了什么？

alter table sys.aud$ move tablespace SYSAUX;
alter table sys.aud$ move lob(sqlbind) store as (tablespace SYSAUX);
alter table sys.aud$ move lob(sqltext) store as (tablespace SYSAUX);
alter index sys.I_AUD1 rebuild tablespace SYSAUX;

　　此次今晚告一段落，暂不进行aud$清理job设置，整理了一下迁移的最佳步骤具体步骤,应该如下：

#1.建议客户删除aud$，如果有需要，expdp导出保留，不然可能遇到以上问题
expdp USERID = "/ as sysdba" table=’SYS.AUD$’ log=expdp_aud_20190107.log dumpfile=expdp_aud_20190107.dmp

#删除aud$表
TRUNCATE TABLE AUD$;

#收缩HVM，清理空间
alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;
alter table sys.aud$ disable row movement;

#2.使用DBMS_AUDIT_MGMT迁移aud$表到sysaux，不影响业务，手动时业务无法访问
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'SYSAUX');
END;
/

#3.初始化
BEGIN
      sys.DBMS_AUDIT_MGMT.init_cleanup(
      audit_trail_type         => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval => 24);
END;
/

#4.验证是否初始化
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

#5.设置定时清理job
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   last_archive_time => systimestamp-30,	                 --保留最近30天审计日志
   rac_instance_number => 1);					 --对于保存在DB中的审计或者单实例审计，rac_instance_number可忽视
END;
/

#6.创建定时清理job
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,	--此次是假设审计日志保存在DB中。如果是保存在OS中，应该是audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS（12c默认audit_sys_operations为true，sys的审计会存放在audit_file_dest）
  audit_trail_purge_interval => 24 /* hours */,  		--每24小时运行一次
  audit_trail_purge_name => 'Purge_AUD$',
  use_last_arch_timestamp => TRUE);				--使用前面设置的last_archive_time保存策略，FALSE则为删除全部审计日志
END;
/

#7.运行定时清理任务
exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.Purge_AUD$');

总结：

1. 不要手动迁移aud$，影响业务
2. aud$最好删除，不然迁移很慢，还会出现大量归档，如果要保留，使用expdp导出
3. 备份过程不要用mv移动归档，会导致备份失败，mv是先cp后rm,cp失败后，仍旧rm，出现了归档丢失，备份失败，后期客户决定删除归档。