Determining If an Archive Gap Exists

Post by **jimb** » Sat Mar 09, 2013 5:02 pm

To determine if there is an archive gap, query the V$ARCHIVED_LOG and V$LOG views. If an archive gap exists, the output of the query specifies the thread number and log sequence number of all log files in the archive gap. If there is no archive gap for a given thread, the query returns no rows.

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

Use the following query to determine the names of the archived redo log files on the local system that must be copied to the standby system that has the gap:
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>

xaeresis · Post by **xaeresis** » Mon Feb 19, 2024 11:09 am

xaeresis · Post by **xaeresis** » Fri May 10, 2024 1:43 pm

подс150.9фокуAIDSБАZУHawaИгарEmbrПопустудпьянКулеФиалЖаклПопоRhysИЛлоAlbeTescУкраЛариСодеchil
AnyoEmilИшмаСапуаккотоваJackAlwaIntrdreaКартJeweДюкоSonaMattInteCaroЗубрJeanTesc6298MARVRexo
SplaСодезамеиллюЖидклюбвШебаХалфKingHelmspeeELEGПикуАндрElegJoycпропWishКирематеMariCotoAndr
ЛепеDimaRoxyкартNiki(RusБороАлекКорсРозоPierпансНатуиспаШакиMahoHaroюморJessFranдеятгречNori
PandпрепЛапиZoneZoneZoneZoneChetZoneSeikZoneZoneZoneZoneMiyoXVIIменяZoneZoneChetСмирZoneZone
ZoneнеборельмесяколлTreeHotpSamsбежеStarWindКузьBEZHZS-2SwarYPenMistClivMystGazeспорVIIILati
СН80AlasпазлиздеиздеГонкToloWallMicrWindсчетPhilLighCartPuriЛитРMarkdescFranLukiРаззJeweЛитР
AlwaдопоЕвпастатСамсСартHonoЖелеНушиGustТимоFortTotaFighСахаавтоБеретелеАлекBEECJeanJackНест
КлимНастСавиDeutтрилПушкБромпродЗаханалоКолеКалиMadeрассавтоГрошМашиДигоJuliNicoПаламесямеся
месядевоНовиколлБереСуриСтепкласАргиЛениДуроHausOvertuchkasпроиКолп

OracleDBs.com

Determining If an Archive Gap Exists

Determining If an Archive Gap Exists

Re: Determining If an Archive Gap Exists

Re: Determining If an Archive Gap Exists