3 Replies Latest reply: May 24, 2011 2:53 AM by 790792 RSS

    Check status of sync to standby

    790792
      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-Oracle
          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
            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
              Thanks Levi for all the details - exactly what I needed