We're here to help!

Oldest redo log sequence required by MINE

Follow
Description

The list obsolete redo command lists the oldest redo log sequence that is required by the MINE process on a restart. Because of long running transactions and or locks this can sometimes be quite a few hours back:

Solution
dbvrep> list obsolete redo
Thread 1 last obsolete sequence#: 661245 (19 hours and 4 minutes ago)

To determine which transactions are causing the oldest sequence to be required, the following SQL can be run:

select s.SID, s.SERIAL#, s.USERNAME, s.PROGRAM, t.START_TIME 
from v$transaction t, v$session s
where s.SADDR=t.SES_ADDR
order by t.START_TIME desc

Example output of the query:


SID        SERIAL#        USERNAME    PROGRAM            START_TIME 
---------- ---------- --------------- -------- -------------------- 
      840      45607 IE003858                     08/16/13 14:52:47 
      331      56253 IE003858                     08/16/13 14:42:00 
      599      32280 IE003858                     08/16/13 13:28:08 
       89      58824 IE003858                     08/16/13 13:22:45 
      219      48753 IE003858                     08/16/13 13:22:34

Next step:

Investigate why these transactions are still running. It could be that each session is blocking each other and holding the transactions open.

Question:

How does replicate get the "list obsolete redo" information? 

Answer:

Mine keeps track of open transactions + rows not assembled on log switch + LOBs not assembled on log switch. This gives us SCN/plog. Mine than uses SQL to convert it to redo sequences - and it can even decide that the query is too slow and that it should not do it on every log switch. 

Arjen Visser August 18, 2013 10:11

Have more questions? Submit a request

Comments