一.背景

客户反馈有个数据库归档日志生成量比平时多了几倍，需要分析一下是什么导致的。

二.排查步骤

2.1.通过查看日志切换得到异常时间

这里分享两个脚本：
来源：Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In RAC (Doc ID 2373477.1)。

set linesize 200 pagesize 1000
column day format a3
column total format 9999
column h00 format 999
column h01 format 999
column h02 format 999
column h03 format 999
column h04 format 999
column h04 format 999
column h05 format 999
column h06 format 999
column h07 format 999
column h08 format 999
column h09 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column h24 format 999
break on report
compute max of "total" on report
compute max of "h00" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report

REM Script to Report the Redo Log Switch History

alter session set nls_date_format='DD MON YYYY';
select thread#, trunc(completion_time) as "date", to_char(completion_time,'Dy') as "Day", count(1) as "total",
sum(decode(to_char(completion_time,'HH24'),'00',1,0)) as "h00",
sum(decode(to_char(completion_time,'HH24'),'01',1,0)) as "h01",
sum(decode(to_char(completion_time,'HH24'),'02',1,0)) as "h02",
sum(decode(to_char(completion_time,'HH24'),'03',1,0)) as "h03",
sum(decode(to_char(completion_time,'HH24'),'04',1,0)) as "h04",
sum(decode(to_char(completion_time,'HH24'),'05',1,0)) as "h05",
sum(decode(to_char(completion_time,'HH24'),'06',1,0)) as "h06",
sum(decode(to_char(completion_time,'HH24'),'07',1,0)) as "h07",
sum(decode(to_char(completion_time,'HH24'),'08',1,0)) as "h08",
sum(decode(to_char(completion_time,'HH24'),'09',1,0)) as "h09",
sum(decode(to_char(completion_time,'HH24'),'10',1,0)) as "h10",
sum(decode(to_char(completion_time,'HH24'),'11',1,0)) as "h11",
sum(decode(to_char(completion_time,'HH24'),'12',1,0)) as "h12",
sum(decode(to_char(completion_time,'HH24'),'13',1,0)) as "h13",
sum(decode(to_char(completion_time,'HH24'),'14',1,0)) as "h14",
sum(decode(to_char(completion_time,'HH24'),'15',1,0)) as "h15",
sum(decode(to_char(completion_time,'HH24'),'16',1,0)) as "h16",
sum(decode(to_char(completion_time,'HH24'),'17',1,0)) as "h17",
sum(decode(to_char(completion_time,'HH24'),'18',1,0)) as "h18",
sum(decode(to_char(completion_time,'HH24'),'19',1,0)) as "h19",
sum(decode(to_char(completion_time,'HH24'),'20',1,0)) as "h20",
sum(decode(to_char(completion_time,'HH24'),'21',1,0)) as "h21",
sum(decode(to_char(completion_time,'HH24'),'22',1,0)) as "h22",
sum(decode(to_char(completion_time,'HH24'),'23',1,0)) as "h23"
from
v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time), to_char(completion_time, 'Dy') order by 2,1;

REM Script to calculate the archive log size generated per day for each Instances.

select THREAD#, trunc(completion_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024) as GB
, trunc(sum(blocks*block_size)/1024/1024) as MB
, sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time)
order by 2,1
;


Output:  

THREAD#    date        Day total  h00  h01  h02  h03  h04  h05  h06  h07  h08  h09  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
---------- ----------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
         1 17 MAR 2018 Sat    47    0    0    0    0    0    0    0    0    1    0   44    1    0    0    0    0    0    0    0    0    0    1    0    0
         2 17 MAR 2018 Sat   133    0    0    0    0    1    0    0    0    0    2  128    1    0    0    0    0    0    0    1    0    0    0    0    0
         1 18 MAR 2018 Sun    10    0   10    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
         2 18 MAR 2018 Sun    33    0   33    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
                            ----      ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
maximum                      133        33    0    0    1    0    0    0    1    2    12   8    1    0    0    0    0    0    0    1    0    0    1    0 

THREAD#    DATE              NUM          GB         MB         KB
---------- ----------- ---------- ---------- ---------- ----------
         1 17 MAR 2018         47          0         63    64939.5
         2 17 MAR 2018        133          6       6403    6557111
         1 18 MAR 2018         10          0          0         50
         2 18 MAR 2018         33          1       1616  1654888.5
                       ---------- ---------- ---------- ----------
sum                           223          7       8082    8276989

2.2.通过logminer分析归档日志内容从而确定异常

2.2.1.使用脚本创建相关的包

@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。
第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

2.2.2.指定要分析的日志文件

exec sys.dbms_logmnr.add_logfile(logfilename => ‘+DATA/hrac/archivelog/thread_1_seq_647.13901.1100781339’,options => dbms_logmnr.new);

2.2.3.使用本地的在线数据字典分析归档日志

exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

2.2.4.查询分析出来的归档日志内容

–统计最大修改量的Schema
select seg_owner,count(*) from v$logmnr_contents group by seg_owner;
–把所有的修改输出出来
spool sql_redo.log
select count(1),substr(sql_redo,1,30) from v$logmnr_contents group by substr(sql_redo,1,30) order by count(1) desc ;
spool off

2.2.5.增加别的日志文件

exec sys.dbms_logmnr.add_logfile(logfilename=>’+DATA/hrac/archivelog/thread_1_seq_647.13901.1100781339’);
exec sys.dbms_logmnr.add_logfile(logfilename=>’+DATA/hrac/archivelog/thread_1_seq_647.13901.1100781339’);

2.2.6.结束分析归档日志

exec sys.dbms_logmnr.end_logmnr;

2.3.根据分析内容确定异常应用

当时根据分析出来的日志内容看到，异常快速增长的原因是一个新建的用户，频繁的对一个表进行delete和insert操作，进一步跟业务沟通之后得出是一个周期性的操作，每隔一段时间做一次，协商之后把该业务放到晚上去执行。

三.总结

当遇到日志切换频繁的时候，我们可以做如下检查

3.1.检查redo大小

select group#,thread#,status,bytes/1024/1024 M from v$log;

3.2.检查参数

SQL> show parameter ARCHIVE_LAG_TARGET

NAME TYPE VALUE

archive_lag_target integer 0
这个参数一般在DG环境中设置，是个动态参数，当然，在非DG环境中也可以设置，作用是固定间隔切换日志。

3.3.logminger分析归档日志内容

3.4.拷贝asm归档日志到文件系统

有时候我们需要拷贝asm里面的归档文件到文件系统，可以用如下方法。

create or replace directory source_dir as '+FRA/jhemr/archivelog';
create or replace directory data_dir as '/data/expdp/';

begin
dbms_file_transfer.copy.file(
source_directory_object=>'source_dir',
source_file_name=>'',
destination_directory_object=>'data_dir',
destination_file_name=>'');
end;
/


begin
dbms_file_transfer.copy_file(
source_directory_object=>'source_dir',
source_file_name=>'2_21665_1074345238.dbf',
destination_directory_object=>'data_dir',
destination_file_name=>'2_21665_1074345238.dbf');
end;
/