归档日志异常增长排查（哪些对象在redo中出现的频率太高）

1. 我们以一个归档为例，生成 dump 文件：

sqlplus / as sysdba
oradebug setmypid
oradebug tracefile_name
alter system dump logfile '+archdg/rptdb/archivelog/2026_08_24/thread_2_seq_12309.298.1242122429';
alter system dump logfile '+archdg/rptdb/archivelog/2026_08_24/thread_2_seq_12308.494.1242121853';
alter system dump logfile '+archdg/rptdb/archivelog/2026_08_24/thread_2_seq_12307.1227.1242121235';
alter system dump logfile '+archdg/rptdb/archivelog/2026_08_24/thread_2_seq_12306.1488.1242120627';

2. 执行下面的命令，然后检查这个文件：

grep OBJ: /u01/app/oracle/diag/rdbms/rptdb/rptdb1/trace/rptdb1_ora_1725137.trc|awk -F"OBJ:" '{print $2}'|awk '{print $1}' | sort |uniq -c |sort -n -r
grep OBJ: /u01/app/oracle/diag/rdbms/rptdb/rptdb1/trace/rptdb1_ora_1750288_1.trc|awk -F"OBJ:" '{print $2}'|awk '{print $1}' | sort |uniq -c |sort -n -r
grep OBJ: /u01/app/oracle/diag/rdbms/rptdb/rptdb1/trace/rptdb1_ora_1750288_4.trc|awk -F"OBJ:" '{print $2}'|awk '{print $1}' | sort |uniq -c |sort -n -r
grep OBJ: /u01/app/oracle/diag/rdbms/rptdb/rptdb1/trace/rptdb1_ora_1750288_5.trc|awk -F"OBJ:" '{print $2}'|awk '{print $1}' | sort |uniq -c |sort -n -r

redo中出现的次数 对象id
212354 85627
156410 863078
56110 1153251
45058 5353115
37005 5352992
36083 5353113

redo中出现的次数 对象id
216663 85627
161212 5352431
142255 5352430
137832 5352433
137027 5352432
15321 5352774

redo中出现的次数 对象id
204428 85627
125598 845627
84290 5352620
82796 5352619
77515 5352621
56202 1153251

redo中出现的次数 对象id
378406 85627
212001 5352807
204178 5352806
192339 5352808
121357 5352809

SELECT owner,
object_name,
object_type,
object_id
FROM dba_objects
WHERE object_id = 1153251;

3. [2]会列出相关的 Object ID 信息，然后根据 Object ID 确认对象：

另外，我们可以通过 LogMiner 分析一个 REDO，确认是否有发现：

BEGIN
DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '+archdg/rptdb/archivelog/2026_08_24/thread_2_seq_12309.298.1242122429',
OPTIONS => DBMS_LOGMNR.NEW
);
END;
/

BEGIN
DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '<归档文件全路径2>',
OPTIONS => DBMS_LOGMNR.ADDFILE
);

DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '<归档文件全路径3>',
OPTIONS => DBMS_LOGMNR.ADDFILE
);
END;
/

BEGIN
DBMS_LOGMNR.START_LOGMNR(
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);
END;
/

create table t_redo nologging as
with t1 as
(select rs_id,
ssn,
rbablk,
rbabyte,
rbablk * 512 + rbabyte curpos,
lead(rbablk * 512 + rbabyte, 1, rbablk * 512 + rbabyte) over(partition by substr(rs_id, 1, 9) order by rbasqn, rbablk, rbabyte, scn, redo_value) nextpos,
lead(rbablk * 512 + rbabyte, 1, rbablk * 512 + rbabyte) over(partition by substr(rs_id, 1, 9) order by rbasqn, rbablk, rbabyte, scn, redo_value) - (rbablk * 512 + rbabyte) redo_size,
operation,
data_obj#,
redo_value,
client_id,
timestamp
from v$logmnr_contents t1)
select rs_id,
ssn,
rbablk,
rbabyte,
curpos,
nextpos,
redo_size,
data_obj#,
operation
from t1;

select data_obj# as object_id , sum(redo_size) as redo_size from t_redo group by data_obj# order by 2;

BEGIN
DBMS_LOGMNR.END_LOGMNR;
END;
/