Oracle故障处理-归档异常增长
数据库启用归档模式，主要是保证数据安全，但是如果归档增长过快，或者人员维护不合理，可能会导致归档文件把磁盘占满，最终数据库无法正常工作；
数据库归档增长异常，最终导致数据库无法使用，如何查找原因，解决问题呢？
1 当出现归档空间不足，首先需要通过扩空间或者移动(删除)部分归档文件释放空间，尽快让数据库正常工作；
2 数据库可用后，再去具体分析归档文件增长过快的原因；
归档空间满了，在删除归档之前需要确定归档所在目录（archive log list）；
如果归档文件放在默认的闪回区，必须通过RMAN的delete命令进行删除归档，或者直接通过命令扩大闪回区大小，不能通过操作系统命令直接删除闪回区下的归档文件；
如果归档文件存放路径是手动指定的其他目录，非闪回去，除了RMAN删除归档外，也可以通过操作系统命令移动或删除归档文件；
1 删除过期归档
删除过期(expired)的归档，释放空间；
RMAN> crosscheck archivelog all;
RMAN> list expired archivelog all;
RMAN> delete expired archivelog all;
删除指定时间归档
RMAN>delete archivelog until time 'sysdate-7';
删除废弃(obsolete)的归档，释放空间；
RMAN> report obsolete;
RMAN> delete obsolete;
扩大归档所在空间(闪回区)；
select dbid,name,log_mode from v$database;
SQL> archive log list;
SQL> show parameter db_recovery
select * From v$flash_recovery_area_usage;
修改闪回区大小：alter system set db_recovery_file_dest_size = 4G（更改大小）
删除部分归档后，数据库就可以正常工作了，这时需要具体分析归档过快的原因；
首先需要知道每天(每小时)归档产生频率和大小；
如果每天大多数归档文件都某个特定时间内产生的，那么可能是这段时间有定时JOB，或者计划任务，查看一下这些JOB和计划任务是否合理；
如果每天的每秒每分钟都在不停的产生归档，很可能产品或者数据库存在BUG，需要具体分析产生归档的SQL语句，才能和业务操作联系起来；
如果都是工作时间内产生的归档，可能是正常业务操作产生的归档，具体分析业务操作对应的表，SQL信息等，通常情况需要增加存储空间；
2 分析归档过快原因
查看归档参数频率
查看数据库JOB
查看计划任务
----查看数据库归档分布及频率
3 查找归档增长异常常见方法
一：日志挖掘
分析多个归档文件中SQL信息
1.Enable Supplemental Logging
2.Extract a LogMiner Dictionary (unless you plan to use the online catalog)
3.Specify Redo Log Files for Analysis
4.Start LogMiner
5.Query V$LOGMNR_CONTENTS
6.End the LogMiner Session
二：AWR报告
Segments by DB Blocks Changes结合TOP SQL进行分析
4 案例分析
归档异常增长案例一：
问题原因：11g数据库自动维护任务-段指导BUG导致归档增长过快。
现象：平时每天归档5G左右，突然有一天产生200多G归档。
分析过程：先通过SQL查看全天中每小时归档量，找出归档最集中的时间段，并收集这一时间段的AWR报告，或通过日志挖掘分析这一时间段的归档文件。
发现大多数归档文件生成时间特别集中，收集这段时间AWR报告即可。
通过AWR报告查找归档异常增长原因
查看问题期间AWR报告，发现有一条CTAS语句特别耗时
SQL语句如下：
1：call dbms_space.auto_space_advisor_job_proc ( )
2：create table “XXX".DBMS_TABCOMP_TEMP_UNCMP tablespace NNC_DATA02 as select *+ full(“CHENJCH".SM_PUB_FILESYSTEM)*/ * from “CHENJCH".SM_PUB_FILESYSTEM sample block( 41)
其中SM_PUB_FILESYSTEM表是一张附件表，包含blob字段，大小200多G，通过DBMS_SCHEDULER可知这条耗时耗空间的语句是Oracle自动执行到了。
解决方案：
11g数据库，自动维护任务-段指导 BUG导致归档过快，禁用段指导。
begin
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
end;
详细信息请参考：
How to Stop Creating Table DBMS_TABCOMP_TEMP_UNCMP? (Doc ID 1326118.1)
归档异常增长案例二：
问题原因：应用程序产品BUG导致归档增长异常。
问题现象：同事反馈，一个小系统，全库大小不到10G，但是启动归档模式后，发现每天产生的归档文件有300G大小。
分析过程：先通过SQL查看全天中每小时归档量，找出归档最集中的时间段，并收集这一时间段的AWR报告，或通过日志挖掘分析这一时间段的归档文件。
发现每小时每分钟都在不断生成大量归档文件。
查看AWR报告：发现产生归档的对象主要集中在ARAP_BALANCE2对象上。
查看TOP SQL，找出ARAP_BALANCE2相关DML或DDL语句。
每分钟都会执行大量如下SQL，通过arap_balance2表定位到具体的业务，和对应业务开发对接查找SQL产生的原因。
1.delete from arap_balance2 where dr = 9
2.update arap_balance2 set dr = 9
3.insert into arap_balance2 ( pk_balance, compondmd5, pk_group, pk_org, accperiod, pk_currtype, objtype, billclass, billstatus, effectstatus, customer, supplier, pk_deptid, pk_psndoc, def1, def2, def3, def4, def5, def6, accyear, quantity_de, quantity_cr, money_de, money_cr, local_money_de, local_money_cr, grouplocal_money_de, grouplocal_money_cr, globallocal_money_de, globallocal_money_cr ) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 )
解决方案：产品BUG，研发提供补丁，减少SQL执行次数。
归档异常增长案例三：
问题原因：数据库JOB执行太频繁；
问题现象：每分钟都有归档产生
平均每天产生60G归档文件
查看当前正在执行的SQL
Select * from v$sql where address in (select sql_address from v$session);
日志挖掘
查看数据库job
问题原因：
JOB每分钟执行一次，调用存储过程，频繁delete,insert大量数据，当存储过程在一分钟之内执行不完时，下一个JOB又开始调用存储过程，导致delete,insert每秒都在执行，造成归档疯涨；
解决方案：
客户反馈这个JOB对应的应用已经不用了，但是数据库JOB没有及时停掉，手动停掉这个JOB即可。
归档异常增长案例四：
问题原因：预算业务产生大量归档文件
查找原因：
一 查看归档频率及归档分布，查看JOB以及后台计划任务
二 日挖掘多个归档日志
三 找出执行次数多的SQL
四 分析SQL由来
五 跟踪表的变化
一 查看归档频率及归档分布，查看JOB以及后台计划任务
----查看数据库归档分布及频率
发现归档产生并没有集中在某一小时或某一小段时间内，可能和备份计划任务(0点)和JOB关系不大。
---查看后台job
select job,schema_user,last_date,next_date,broken,interval from dba_jobs;
二 日志挖掘多个归档日志
---查看每天归档大小
select sum(block_size * blocks) / 1024 / 1024 / 1024 "size(GB)",
to_char(first_time, 'yyyymmdd')
from v$archived_log
group by to_char(first_time, 'yyyymmdd')
order by 2 desc;
---查看归档大小及目录名
select block_size * blocks / 1024 / 1024 "size(M)",
name,
first_time,
next_time,
creator
from v$archived_log a
order by first_time desc;
---日志挖掘
(分析连续的几个归档)
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/archive/1_5673_863636484.dbf',Options=>dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> create table test1 as select * from v$logmnr_contents;
SQL> exec dbms_logmnr.end_logmnr;
---查看归档中执行次数多的SQL
select count(*),substr(sql_redo,1,100)
from test.test1
group by substr(sql_redo,1,100) order by 1 desc;
---查看sql_redo为空归档的分布
select count(*),data_obj# from test.test1 where sql_redo is null group by data_obj# order by 1 desc;
/*发现信息主要分布在两个data_obj#上*/
---通过data_obj#查询具体的对象
select * from dba_objects where data_object_id in ('92468','92467');
---查看所属段
select * from dba_segments
where segment_name in('SYS_LOB0000092466C00006$$','SYS_IL0000092466C00006$$');
---查询具体对象类型（OBJECT_TYPE 为LOB）
select * from dba_lobs where SEGMENT_NAME='SYS_LOB0000092466C00006$$';
---查询具体对象类型（OBJECT_TYPE 为index）
select * from dba_indexes where index_name='SYS_IL0000092466C00006$$';
---查看tb_taskshtmodel段大小(6.25M)
select bytes/1024/1024,a.* from
dba_segments a where owner='NC63PROD' and segment_name='TB_TASKSHTMODEL';
---查看tb_taskshtmodel表SHEETMODEL列的blob段SYS_LOB0000092466C00006$$大小(47G)
select bytes/1024/1024/1024,a.* from dba_segments a
where owner='NC63PROD' and segment_name='SYS_LOB0000092466C00006$$';
--查看tb_taskshtmodel表SHEETMODEL列的blob段SYS_LOB0000092466C00006$$对应段索引SYS_IL0000092466C00006$$的大小(70.5M)
select bytes/1024/1024,a.* from dba_segments a
where owner='NC63PROD' and segment_name='SYS_IL0000092466C00006$$';
----查看LOB字段大小分布
select max(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
min(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
avg(dbms_lob.getLength(sheetmodel) / 1024 / 1024)
from nc63prod.tb_taskshtmodel;
结论：tb_taskshtmodel表段6.25M，tb_taskshtmodel表SHEETMODEL列的blob段SYS_LOB0000092466C00006$$有47G，tb_taskshtmodel表SHEETMODEL列的blob段SYS_LOB0000092466C00006$$对应段索引SYS_IL0000092466C00006$$有70.5M。
---查看TB_TASKSHTMODEL数据量（27154）
select count(*) from nc63prod.TB_TASKSHTMODEL;
---tb_taskshtmodel表sheetmodel字段总大小(44.3G)
select sum(a) / 1024 / 1024 / 1024
from (select dbms_lob.getLength(sheetmodel) a
from nc63prod.tb_taskshtmodel);
---查看tb_taskshtmodel字段11月7日大小
select sum(a) / 1024 / 1024 / 1024
from (select dbms_lob.getLength(sheetmodel) a
from nc63prod.tb_taskshtmodel
where ts like '2015-11-07%');
如果有delete操作，会产生更多归档，
通过JOB来跟踪tb_taskshtmodel表每分钟数据量，从而粗略的推测出表插入和删除的大概操作次数。
---1
create table c_test(t_date date default sysdate,t_count number);
---2
create or replace procedure p_test as
begin
insert into c_test
select sysdate, count(1) from TB_TASKSHTMODEL;
commit;
end;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as nc63prod
SQL> variable job1 number;
SQL> begin
2 dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');
3 end;
4 /
PL/SQL procedure successfully completed
select job, what from dba_jobs;
SQL> begin
2 dbms_job.run(23);
3 end;
4 /
PL/SQL procedure successfully completed
select * from nc63prod.c_test order by 1 desc;
---查看nc63prod.TB_TASKSHTMODEL删除情况
select * from nc63prod.c_test a,nc63prod.c_test b
where a.t_date>b.t_date and a.t_count<b.t_count order by 3;
结论：nc63prod.TB_TASKSHTMODEL表24小时内插入18000行数据，删除583条数据
---删除JOB
SQL> begin
2 dbms_job.remove(23);
3 end;
4 /
PL/SQL procedure successfully completed
解决方案：
审计也会产生一部分归档文件，如果不使用审计，建议关闭。
1、查看审计功能是否开启
SQL> show parameter audit
---audit_trail DB
说明：VALUE值为DB，表面审计功能为开启的状态
2、关闭oracle的审计功能
SQL> alter system set audit_trail=none scope=spfile;
System altered.
3、重启数据库生效
SQL> shutdown immediate;
SQL> startup;
减少"NC63PROD"."TB_TASKSHTMODEL"表产生日志
具体操作：
(1) 表级别设置nologging
alter table NC63PROD.TB_TASKSHTMODEL nologging;
(2)开发人员将
insert into "NC63PROD"."TB_TASKSHTMODEL"
更改为
insert /*+ append */ into "NC63PROD"."TB_TASKSHTMODEL"
注意：append+nologing存在的风险，会导致rman恢复这个表时出现问题,需要充分评估是否有必要使用此方案。
