Archive for the ‘DataGuard’ Category

DATAGUARD:Verifying synchronization between the primary and standby databases
By using the following steps, you can control whether the standby database is synchronized with primary:
1. STANDBY DATABASE
For the last archived sequence, use the following:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
————–
195
For the last applied sequence, use the following:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
MAX(SEQUENCE#)
————–
194
From the preceding two queries, we see that the latest sequence, 195, is being archived or written into the standby redo logfiles. There’s expected to be a lag of one sequence between archived and applied columns.
2. STATUS OF THE LATEST LOG SEQUENCE
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
——— ——-
190       YES
191       YES
192       YES
193       YES
194       YES
195       IN-MEMORY
The log sequence 195 is still being shipped.
3. ON THE PRIMARY DATABASE QUERY FOR THE LAST ARCHIVED LOGFILE, PERFORM A COUPLE OF LOG SWITCHES AND THEN MONITOR IF THOSE ARCHIVES ARE TRANSPORTED AND APPLIED.
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
————–
195
Perform log switches several times and check.
SQL> alter system switch logfile;
System altered.
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
————–
198
4. On the standby query for new archived logfiles and applied archived logfiles, query if the new archive log sequences are applied on standby.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE#      APPLIED
————– ———
193            YES
194            YES
195            YES
196            YES
197            YES
198            YES
The APPLIED column on standby will be very helpful to determine which sequence is generated and which sequences are applied. In the previous scenario, the archives generated on primary and archives applied on standby have the same sequence number; hence, standby is synchronized with the primary database.
The value of the APPLIED column for the most recently received logfile will be INMEMORY, or YES if that logfile has been applied.