Oldest redo log sequence required by MINE

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