Find SQL Query Being Applied by Logical Standby or Applier Process

Came-up with this query to find which SQL is being applied on a logical standby as I frequently need to check what tables are undergoing heavy DML and may need to be manually re-instantiated.

Oracle view V$LOGSTDBY contains SERIAL# of applier process, this can be joined with V$SESSION to find out SQL ID of SQL query being applied.

Below is a raw attempt to get this information, suggestions are most welcome to make this more efficient.

Set pages 1000 lines 200
Col status for a80
Col type for a15
Col sql_text for a51
SELECT distinct l.Pid, l.Status, l.Serial#, l.Type, substr(sq.Sql_text,1,50) sql_text
FROM V$LOGSTDBY l, V$SQL sq, V$SESSION s
Where l.serial#=s.serial#
and (s.sql_id=sq.sql_id or s.prev_sql_id=sq.sql_id);

I would advise to crosscheck number of actual applier processes using below query in case you are getting multiple results when both sql_id and prev_sql are matched for a session. I used OR condition to make it more informative, but feel free to modify as you need.

SELECT * FROM V$LOGSTDBY;

Cheers!
Anurag

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$