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

Webinar: Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: May 5th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

How to roll forward image copies using RMAN

Recovery Manager (RMAN) is a utility that can be used to backup and recovery your database and it simplifies backing up, restoring, and recovering database or database files

Oracle RMAN can be used to roll forward the copy to the point in time of the most recent level 1 incremental backup by applying level 1 incremental backups to an older image copy. This will help reducing recovery time. To roll forward processes all changes between SCN of original image and incremental backup are applied to the image copy.

When using Roll forward image no need to restore the database instead just switch to updated image copy this will reduce restore time and recovery will be minimal.

run {
allocate channel c1 device type disk;
recover copy of database with tag 'rman_incr_backup';
backup incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

The TAG is used to identify which incremental backups are applying to which image copies.

Note that when the backup runs first time there is no level 0 exists to apply incremental backups. Therefore, it will create level 0 image copy of the database. Going forward it will create level 1 incremental backup and will update previous image copy.

You can also use compression during roll forward image copies using below example

run {
allocate channel c1 device type disk;
recover copy of database with tag ‘rman_incr_backup';
backup as compressed backupset incremental level 1 for recover of copy with tag ‘rman_incr_backup' database;
}

In case if you need to switch the database to image copy you then “shutdown database” and “startup mount”

SQL> shutdown immediate;
SQL> startup nomount;

By using below RMAN command, you can switch the database to image copy

run {
allocate channel c1 device type disk;
switch database to copy;
recover database;
alter database open;
}

When using FRA (Fast Recovery Area) make sure you have has enough space to keep take full image copy database backup.

The Block change tracking will help to improve performance of the incremental backups and you can check the status and enable using below command. 

SQL> select status from v$block_change_tracking;
SQL> alter database enable block change tracking using file '/oracle/oradata/TESTDB/block_change_tracking_file.dbf' reuse;

You can use below command to disable Block change tracking

SQL> alter database disable block change tracking;

Thanks & Regards

http://oracleracexpert.com, Oracle ACE