Identify the log files in the archive gap
Query the V$ARCHIVED_LOG and V$LOG views on the standby database. For example, the following query shows there is a difference in the RECD and SENT sequence numbers for the destination specified by DEST_ID=2, indicating that there is a gap:
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';
Code: Select all
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
3167 3184
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 3167 and 3184;
Code: Select all
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 3167 and 3184;
NAME
--------------------------------------------------------------------------------
+FLASH/standby/archivelog/2013_02_20/thread_1_seq_3168.28802.807892291
+FLASH/standby/archivelog/2013_02_22/thread_1_seq_3169.46762.808061921
+FLASH/standby/archivelog/2013_02_24/thread_1_seq_3170.20612.808195285
+FLASH/standby/archivelog/2013_02_25/thread_1_seq_3171.46864.808355303
+FLASH/standby/archivelog/2013_02_27/thread_1_seq_3172.10707.808528225
+FLASH/standby/archivelog/2013_03_02/thread_1_seq_3173.46916.808965693
+FLASH/standby/archivelog/2013_03_04/thread_1_seq_3174.46876.809158847
+FLASH/standby/archivelog/2013_03_05/thread_1_seq_3175.31410.809242417
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3177.46841.809322189
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3178.52870.809322191
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3179.46932.809322193
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3176.46776.809322189
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3180.46881.809323059
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3181.46880.809323061
+FLASH/standby/archivelog/2013_03_06/thread_1_seq_3182.52927.809329391
+FLASH/standby/archivelog/2013_03_07/thread_1_seq_3183.39022.809453485
16 rows selected.
SQL>