This content has been marked as final. Show 3 replies
Hi,1 person found this helpful
generally this should be sufficient.
However if you use 11.2 and Active DataGuard you can force the synchronization:
alter session sync with primary
787789 wrote:Get the RESETLOGS_CHANGE# from the primary database:
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 ?
On the target physical standby database, identify any active standby redo logs (SRL’s)
SQL> SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT';
On the target physical standby database, identify maximum applied sequence number(s).
SQL> SELECT GROUP#, THREAD#, SEQUENCE# FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE' ORDER BY THREAD#,SEQUENCE#;
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.
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#;
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.
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
Thanks Levi for all the details - exactly what I needed