Physical Standby Swithover_status as UNRESOLVABLE GAP

On Data Guard site, I can see that Archive logs are copying but not applying to Physical Standby Database. When I query I see that SWITCHOVER_STATUS showing as “UNRESOLVABLE GAP”

SQL> select OPEN_MODE,LOG_MODE,DATABASE_ROLE, switchover_status from v$database;

OPEN_MODE LOG_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ------------------------------ ---------------------------------------------
MOUNTED ARCHIVELOG PHYSICAL STANDBY UNRESOLVABLE GAP

I didn’t see any errors when we query v$archive_Dest_status;
SQL> select DEST_NAME, ERROR from v$archive_Dest_status;

But when we query V$ARCHIVE_GAP we can see archive log Gap

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 47402 47409

I don’t see missing archive logs in the archive log destination, but when I query v$managed_standby I can see that MRP0 processes waiting for archive log sequence 47402

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS THREAD# SEQUENCE#
------------------------------------ ------------------------------------------------ ---------- ----------
MRP0 WAIT_FOR_GAP 1 47402

If the archive logs on the Physical Standby site removed by mistake then you can restore using RMAN
RMAN> restore archivelog from logseq 47402 until logseq 47409;

If you want to restore archive logs into different destination other than default use below command.
RMAN> set archivelog destination to '/tmp/archive_restore';

In case, if you need to restore specific archive log then use below command
RMAN> restore archivelog from logseq=47402;

Once the archive logs are restored, it will apply to standby site. In case if you have restored to non-default destination then you need to copy the archive logs into default destination.

If the archive logs are not applying on Physical Standby site then shut down and open the Physical Standby in recovery mode again. In case if the archive logs are missing and cannot able to restore from backup then you might get below error message. 

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 2.0737E+10 bytes
Fixed Size 9923356 bytes
Variable Size 8680473632 bytes
Database Buffers 2040487392 bytes
Redo Buffers 6859032 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Note that with missing archive logs you cannot able to recover the database.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE