Troubleshooting High Redo Generation Issues (Doc ID 782935.1)

In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterInformation in this document applies to any platform. ***Checked for relevance on 02-Jul-2016*** This technique can be used for all versions irrespective of platform. GOALPurpose of this document is to have a checklist for troubleshooting the high redo generation issues. SOLUTIONHigh redo generation can be of two types: SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> select trunc(first_time, 'HH') , count(*) 2 from v$loghist 3 group by trunc(first_time, 'HH') 4 order by trunc(first_time, 'HH'); TRUNC(FIRST_TIME,'HH COUNT(*) -------------------- ---------- 25-MAY-2008 20:00:00 1 26-MAY-2008 12:00:00 1 26-MAY-2008 13:00:00 1 27-MAY-2008 15:00:00 2 28-MAY-2008 12:00:00 1 <- Indicate 1 log switch from 12PM to 1PM. 28-MAY-2008 18:00:00 1 29-MAY-2008 11:00:00 39 29-MAY-2008 12:00:00 135 29-MAY-2008 13:00:00 126 29-MAY-2008 14:00:00 135 <- Indicate 135 log switches from 2-3 PM. 29-MAY-2008 15:00:00 112 -- Create an AWR snapshot when you are able to reproduce the issue: SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); -- After 30 minutes, create a new snapshot: SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); -- Now run $ORACLE_HOME/rdbms/admin/awrrpt.sql SQL> connect perfstat/<Password> SQL> execute statspack.snap; -- After 30 minutes SQL> execute statspack.snap; SQL> @?/rdbms/admin/spreport -- Use the DBMS_LOGMNR.ADD_LOGFILE procedure to create the list of logs to be analyzed: SQL> execute DBMS_LOGMNR.ADD_LOGFILE('<filename>',options => dbms_logmnr.new); SQL> execute DBMS_LOGMNR.ADD_LOGFILE('<file_name>',options => dbms_logmnr.addfile); -- Start the logminer SQL> execute DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); SQL> select operation,seg_owner,seg_name,count(*) from v$logmnr_contents group by seg_owner,seg_name,operation;
col program for a10 col username for a10 select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value from v$session a, v$statname b, v$sesstat c where b.STATISTIC# =c.STATISTIC# and c.sid=a.sid and b.name like 'redo%' order by value;
REFERENCESNOTE:1507157.1 - Master Note: Troubleshooting Redo Logs and Archiving |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 凌晨三点救火实录:Java内存泄漏的七个神坑,你至少踩过三个!
2020-04-02 How to List the Objects with Stale Statistics Using dbms_stats.gather_schema_stats options=>'LIST STALE' (Doc ID 457666.1)
2020-04-02 check whether trace enabled