This discussion is archived
3 Replies Latest reply: Apr 26, 2012 7:07 PM by mseberg RSS

Best practice on using Flashback and Logical Standby

user11957149 Explorer
Currently Being Moderated
Hello,

I'm testing a fail-back scenario where I first need to activate a logical standby, then do some dummy transactions before I flashback this db and resme the redo apply. Here is what the steps look like:

1)     Ensure logical standby is in-sync with primary
2)     Enable flashback on standby
3)     Create a flashback guaranteed restore point
4)     Defer log shipping from primary
5)     Activate the logical standby so it’s fully open to read-write
6)     Dummy activities against the standby (which is now fully open)
7)     Flashback the database to the guaranteed checkpoint
8)     Resume log shipping on primary
9)     Resume redo apply on secondary

In the end, i can see the log shipping is happening but the logical standby does not apply any of these..and there is no error in the alert log on Standby side. But the following query could explains why the standby is idle:

SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

TYPE HIGH_SCN STATUS
----------- --------------- --------------------------------------------------
COORDINATOR ORA-16240: Waiting for log file (thread# 2, sequence# 0)

ORA-16240: Waiting for log file (thread# string, sequence# string)
Cause: Process is idle waiting for additional log file to be available.
Action: No action necessary. This informational statement is provided to record the event for diagnostic purposes.


I dont understand why it's looking for sequence #0 after the flashback.

Thanks for the help.
  • 1. Re: Best practice on using Flashback and Logical Standby
    mseberg Guru
    Currently Being Moderated
    Hello;

    I hesitate to answer your question because you are not doing a good job of keeping the forum clean :

    Total Questions: 13 (13 unresolved)

    Please consider closing some of you old answered questions and rewarding those who helped you.

    No action necessary.

    Do you really have a thread 2? ( Redo thread number )

    Quick check
    select applied_scn,latest_scn from v$logstdby_progress;
    Use the DBA_LOGSTDBY_LOG View if you don't have a thread 2 then the sequence# is meaningless.
    COLUMN DICT_BEGIN FORMAT A10;
    
    SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,
    TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# AS THR# FROM DBA_LOGSTDBY_LOG
    ORDER BY SEQUENCE#;
    Logical Standby questions are difficult, not a lot of them out there I'm thinking.

    Check

    http://docs.oracle.com/cd/E14072_01/server.112/e10700/manage_ls.htm

    "Waiting On Gap State" ( However I still believe you don't have a 2nd thread# )

    OR

    http://psilt.wordpress.com/2009/04/29/simple-logical-standby/



    Best Regards

    mseberg

    Edited by: mseberg on Apr 26, 2012 5:13 PM
  • 2. Re: Best practice on using Flashback and Logical Standby
    user11957149 Explorer
    Currently Being Moderated
    There is thread 2. See query below. The sequence of CURRENT just doesnt move forward...

    SQL> SELECT THREAD#, L.SEQUENCE#, L.FIRST_TIME,
    (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES' WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY THREAD#, SEQUENCE#;
    2
    THREAD# SEQUENCE# FIRST_TIM APPLIED
    ---------- ---------- --------- -------
    1 2738 25-APR-12 CURRENT
    1 2739 25-APR-12 NO
    1 2740 25-APR-12 NO
    1 2741 25-APR-12 NO
    1 2742 25-APR-12 NO
    1 2743 25-APR-12 NO
    1 2744 25-APR-12 NO
    1 2745 26-APR-12 NO
    1 2746 26-APR-12 NO
    1 2747 26-APR-12 NO
    1 2748 26-APR-12 NO
    2 2661 25-APR-12 YES
    2 2662 26-APR-12 NO
    2 2663 26-APR-12 NO
    2 2664 26-APR-12 NO
    2 2665 26-APR-12 NO
    2 2666 26-APR-12 NO
    2 2667 26-APR-12 NO
    2 2668 26-APR-12 NO
  • 3. Re: Best practice on using Flashback and Logical Standby
    mseberg Guru
    Currently Being Moderated
    You are correct. ( I would have bet against that and lost! ).

    Thank you for cleaning up your old questions too.

    OK, in this case the alert log(s) are you best friend. There should be something or something else which gives a strong clue to the issue in one of both of them.

    You should be able to find the last statement SQL Apply tried to apply using :
    SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE
    FROM DBA_LOGSTDBY_EVENTS
    WHERE EVENT_TIME =
    (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
    You see if any activity is occurring by
    SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, NEWEST_SCN, NEWEST_TIME FROM DBA_LOGSTDBY_PROGRESS;
    The SCN should change if SQL Apply is working.
    SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
    Hopefully I have no typos in the above as it is very late here.


    Best Regards

    mseberg

    Edited by: mseberg on Apr 26, 2012 8:59 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points