Sometimes Archive Gap is Just Temporary

  • by
As a DBA, you might see many gaps on the standby database in your career like this.
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1        113187         113187

Just don't rush to take some actions on archived logs, we can check the arrival of the log first for sure.
SQL> select status, archived, applied from v$archived_log where sequence# = 113187;

S ARC APP
- --- ---
A YES NO

As you can see, the log arrived and was archived, but where did the gap come from? The answer is simple.
SQL> select process, status, sequence#, block#, blocks from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG     113187     358401    2097150
RFS       WRITING          113189     165888    2097150
RFS       RECEIVING        113188    2097150    2097150

As you can see, the archived log is still applying and other logs are on the road. The bottom line is that any archived but not applied log is essentially regarded as a gap. So you can expect to see gaps more often, if your archived log is quite big (e.g. greater than 2GB). No bother to resolve it manually.

Leave a Reply

Your email address will not be published. Required fields are marked *