/
Oldest redo log sequence required by MINE

Oldest redo log sequence required by MINE

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
1 min read

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