By using the following steps, you can control whether the standby database is synchronized with primary:
V$ARCHIVED_LOG
view for the archived and applied sequences.For the last archived sequence, use the following:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) -------------- 145
For the last applied sequence, use the following:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES'; MAX(SEQUENCE#) -------------- 144
From the preceding two queries, we see that the latest sequence, 145
, 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.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 140 YES 141 YES 142 YES 143 YES 144 YES 145 IN-MEMORY
The log sequence 145
is still being shipped.
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) -------------- 145
Perform log switches several times and check.
SQL> alter system switch logfile; System altered. SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) -------------- 148
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APPLIED ---------- --------- 143 YES 144 YES 145 YES 146 YES 147 YES 148 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 IN-MEMORY
, or YES
if that logfile has been applied.