Logical Standby Recovery Stalled - Problem due to Sequence Reset or SEQ$ table

Changes:

A sequence was reset or decremented on your primary instance and now apply process is not able to progress on logical standby database or apply process is not applying logs on logical standby.

 

Symptoms:

ORA-16127 : stalled waiting for additional transactions to be applied
ORA-16121 : applying transaction with commit SCN 0xnnnn.nnnnnnnnn
ORA-16124 : transaction n n nnnnnnn is waiting on another transaction

Applier processes are halted/slowed down while working on below SQL query:
SQL ID : 4m7m0t6fjcs5x
Text:
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1

In case you are wondering how to get SQL query, use SERIAL# column from V$LOGSTDBY view to get serial# of applier session, then obtain SQL_ID from V$SESSION using it. Get Full Query Here

There are probable bugs listed on My Oracle Support for this issue, would suggest to review these in case it helps you, in my case it did not helped.

Bug 9486060 - Many "update seq$" SQLs generated / spin on logical standby
Bug 6265559 - Apply spins after sequence altered at primary
Bug 9906543 - Logical standby Apply lag due to apply slaves synchronizing a sequence (superceded patch of above two)

Solution that worked:

1. Stop logical standby apply:
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
OR Force Stop / Abort logical standby apply:
ALTER DATABASE ABORT LOGICAL STANDBY APPLY;

2. Disable Guard at Session level
ALTER SESSION DISABLE GUARD;

3. Drop Sequence that was reset on primary (drop from standby only!)
DROP SEQUENCE <owner>.<sequence_name>;

4. Start Apply and Enable Guard
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
ALTER SESSION ENABLE GUARD;

 This would discard all the changes automatically for the sequence and apply will resume.Wait for logical standby to be re synchronized.

Now Steps to Recreate the Sequence:
1. Stop logical standby apply

2. Disable Guard at Session level

3. Get DDL from Primary
Set Long 99999
Select dbms_metadata.get_ddl('SEQUENCE','&sequence_name','&owner') From Dual;

4. Create Sequence
Use DDL statement from above statement's output to create sequence on standby with correct START WITH value

5. Start Apply and Enable Guard

Cheers!
Anurag

#sequence #reset  #ORA-16127 #ORA-16121 #ORA-16124 #logical #standby #seq$