This discussion is archived
3 Replies Latest reply: May 24, 2011 12:53 AM by 790792 RSS

Check status of sync to standby

790792 Newbie
Currently Being Moderated
Hi Guys

Whats the best way to check that your standby DB is in sync with the Primary ?

select current_scn from v$database;

Then compare the SCN numbers and ensure they are the same ?

Also archive logs need to be confirmed that they are being SHIPPED and APPLIED on standby ?

Is that enough to conclude that Standby is in sync ?

Thanks
Steve
  • 1. Re: Check status of sync to standby
    Sebastian Solbach (DBA Community) Guru
    Currently Being Moderated
    Hi,

    generally this should be sufficient.
    However if you use 11.2 and Active DataGuard you can force the synchronization:

    alter session sync with primary

    See here:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/manage_ps.htm#SBYDB4780

    Regards
    Sebastian
  • 2. Re: Check status of sync to standby
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,
    787789 wrote:
    Whats the best way to check that your standby DB is in sync with the Primary ?
    select current_scn from v$database;
    Then compare the SCN numbers and ensure they are the same ?
    Get the RESETLOGS_CHANGE# from the primary database:
    SQL> SELECT RESETLOGS_CHANGE#
    FROM V$DATABASE_INCARNATION
    WHERE STATUS = 'CURRENT';
    On the target physical standby database, identify any active standby redo logs (SRL’s)
    SQL> SELECT GROUP#, THREAD#, SEQUENCE#
    FROM V$STANDBY_LOG
    WHERE STATUS = 'ACTIVE'
    ORDER BY THREAD#,SEQUENCE#;
    On the target physical standby database, identify maximum applied sequence number(s).
    SQL> SELECT THREAD#, MAX(SEQUENCE#)
    FROM V$LOG_HISTORY
    WHERE RESETLOGS_CHANGE#=< resetlogs_change# from the primary V$DATABASE_INCARNATION.RESETLOGS_CHANGE# >
    GROUP BY THREAD#;
    The last SEQUENCE# for each THREAD# from V$LOG_HISTORY on the target physical standby database should be close (the difference in log sequences < 3) to the SEQUENCE# for each THREAD# from V$THREAD on the primary database.

    Also archive logs need to be confirmed that they are being SHIPPED and APPLIED on standby ?
    -- Verify that the primary has archived a log for the thread(s);
    -- On the primary database issue the following;
    SQL> SELECT THREAD#, MAX(SEQUENCE#)
    FROM V$LOG_HISTORY
    WHERE RESETLOGS_CHANGE# =
    (SELECT RESETLOGS_CHANGE#
    FROM V$DATABASE_INCARNATION
    WHERE STATUS = 'CURRENT')
    GROUP BY THREAD#;
    -- Verify that the last sequence# received and the last sequence# applied to standby 
    -- database.
    select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
    from (select thread# thrd, max(sequence#) almax
          from v$archived_log
          where resetlogs_change#=(select resetlogs_change# from v$database)
          group by thread#) al,
         (select thread# thrd, max(sequence#) lhmax
          from v$log_history
          where first_time=(select max(first_time) from v$log_history)
          group by thread#) lh
    where al.thrd = lh.thrd;
    >
    Is that enough to conclude that Standby is in sync ?
    I recommend you use the Data Guard command-line interface (DGMGRL) to manage, and monitor a broker configuration.
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm

    You can use this:
    *Script to Collect Data Guard Physical Standby Diagnostic Information [ID 241438.1]*

    *11.2 Data Guard Physical Standby Switchover Best Practices using SQL*Plus [ID 1304939.1]*

    Monitoring Primary, Physical Standby, and Snapshot Standby Databases
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/manage_ps.htm#i1005610

    Regards,
    Levi Pereira
  • 3. Re: Check status of sync to standby
    790792 Newbie
    Currently Being Moderated
    Thanks Levi for all the details - exactly what I needed

Legend

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