代码改变世界

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

2019-01-07 17:44  AllegroCantabile  阅读(1357)  评论(0编辑  收藏  举报

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

        一开始客户联系我,让我帮忙检查一下数据库的状态,我首先查询了监听服务(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,出现了归档丢失,备份失败,后期客户决定删除归档。