应用Logmnr发现那些操作产生归档
日期：2014-05-16 浏览次数：20593 次
使用Logmnr发现那些操作产生归档
最近某套库2号节点的归档异常的多，660M的redo log，平均3分钟切换一次，产生大量归档。NBU调度归档备份也成为瓶颈,IO等待相当严重，通过日志挖掘，然后和应用沟通，解决了部分问题。
开始查看，发现参数utl_file_dir没有设置，修改这个参数要重启数据库。很显然，我们繁忙的生产库没有合理的理由是不可能重启的。9i后可以使用数据字典在线分析。
SELECT supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui FROM V$DATABASE;
查看所有值均为NO,那么为了得到所有的DML语句，我们首先要执行如下操作：
alter database add supplemental log data;
alter database add supplemental log data(primary key,unique index)columns;
对归档进行分析：
FROM v$logmnr_contents group by substr(sql_redo,1,45)
ORDER BY 1 desc;
对归档进行分析：
BEGIN
SYS.DBMS_LOGMNR.add_logfile
('/smarch2/arch_2_283525_706494229.arc',
SYS.DBMS_LOGMNR.NEW
);
END;
BEGIN
SYS.DBMS_LOGMNR.start_logmnr
(options=>SYS.DBMS_LOGMNR.dict_from_online_catalog
+ SYS.DBMS_LOGMNR.committed_data_only
);
END;SELECT count(*),substr(sql_redo,1,45)FROM v$logmnr_contents group by substr(sql_redo,1,45)
ORDER BY 1 desc;
COUNT(*) SUBSTR(SQL_REDO,1,45)
150319 commit;
150315 set transaction read write;
35635 insert into "ICDMIP"."T_MIP_OPERATIONPROCESS"
26169 update "ICDMIP"."T_MIP_OPERATIONPROCESS" set
26124 insert into "ICDMIP"."T_MIP_INTERFACELOG"("SE
26100 update "ICDMIP"."T_MIP_BIZRESULT" set "BOSSST
25962 insert into "ICDMIP"."T_MIP_MSGLOG"("SERIALID
19150 update "ICDMIP"."T_MIP_SERVICE_SESSION" set "
9545 insert into "MIPMSP"."T_MS_REC_MSG"("MSGID","
9527 update "MIPMSP"."T_MS_REC_MSG" set "PICKTIME"
9527 insert into "MIPMSP"."T_MS_REC_MSG_HIS"("MSGI
9527 delete from "MIPMSP"."T_MS_REC_MSG" where "MS
9501 insert into "ICDMIP"."T_MIP_SERVICE_SESSION"(
9500 insert into "ICDMIP"."T_MIP_BIZRESULT"("INANI
9452 delete from "ICDMIP"."T_MIP_SERVICE_SESSION"
9450 update "ICDMIP"."T_MIP_BIZRESULT" set "BIZRES
8213 delete from "MIPMSP"."T_MS_SEND_TASK" where "
8213 insert into "MIPMSP"."T_MS_MEDIA_TASK"("MONTH
8196 update "MIPMSP"."T_MS_SEND_TASK" set "EXECUTE
8160 insert into "MIPMSP"."T_MS_SEND_TASK"("MONTHD
7305 delete from "MIPMSP"."T_MS_SEND_STATUS" where
7226 update "MIPMSP"."T_MS_MEDIA_TASK" set "DELIVE
5349 insert into "MIPMSP"."T_MS_SEND_STATUS"("MEDI
4940 insert into "ICDMIP"."T_MIP_APS_MEDIATASK"("M
2901 update "MIPMSP"."T_MS_MEDIA_TASK" set "CONTEN
1176 insert into "ICDMIP"."T_MIP_APS_UPSMSLOG"("MO
1016 select * from "MIPMSP"."T_MS_MUTEX" where ROW
389 insert into "ICDMIP"."T_MIP_SPCANCELLOG"("LOG
233 insert into "ICDMIP"."T_MIP_AWARDCODE"("AWARD
67 update "ICDMIP"."T_MIP_INTERFACELOG" set "OUT
65 update "ICDMIP"."T_MIP_MSGLOG" set "MESSAGE"
65 update "ICDMIP"."T_MIP_INTERFACELOG" set "INP
61 insert into "MIPMSP"."T_MS_RATE"("RATENAME","
50 insert into "ICDMIP"."T_MIP_COMFIRMTEMPTABLE"
42 delete from "MIPMSP"."T_MS_LONG_MSG" wh
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
