Resolve Gaps in Standby with incremental backup

Sometimes due to some activity or archive log header corruption, standby database will be in out of synch and gap will come. This can be verified from queries or alert logs of standby database.

Note: Primary instance is in RAC ( 2-node) and standby is in Stand-alone server.

Real-time steps:

Example: ( from alert log)

…………………………

…………………………

Mon Oct 10 02:20:17 2016

Media Recovery Log /u02/prod_archivelogs/2016_10_10/thread_1_seq_142321.362.924828783

Media Recovery Waiting for thread 2 sequence 51848

Fetching gap sequence in thread 2, gap sequence 51848-51849

FAL[client]: Error fetching gap sequence, no FAL server specified

…………………………

…………………………

Here one archive log is missing, i.e., thread_2_seq_51848.xxx

So, Now you verify whether you have that archive log in production or not. If available then copy and catlog/ register it to recover again. Otherwise you can take an incremental backup and recover it. Here are the following steps:

Steps:

1.   At standby check the current scn.

sql> select to_char(current_scn) from v$database;

   5967451101100

2. Create a standby control file from Primary Database.

sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/incremental_bkp/standby_control.ctl';

3. Copy the standby controlfile to standby server.

$ scp -r -p '/u02/incremental_bkp/standby_control.ctl' oracle@160.192.1.2:/u03/incremental_bkp

4. Take incremental backup on primary starting from scn# of standby database

rman> Backup incremental from SCN 5969890030000 database tag='FOR_STANDBY' format '/u02/incremental_bkp/%d_%t_%s_%p';

Note: Take scn some less value than standby.

5. Move the backup pieces to the standby host

6. Shut down the standby database and replace the current controlfile with new one.

            cp control01.ctl control01.ctl_bkp

            cp control02.ctl control01.ctl_bkp

            cp standby_control.ctl control01.ctl

            cp standby_control.ctl control02.ctl

7. Now mount the standby database.

            sql> startup nomount;

            sql> alter database mount standby database;

8. If Primary and standby datafiles location are different then go with below steps.

            rman> catalog start with '/u02/flash_recovery_area/PROD/ORADATA';

            rman> switch database to copy;

            Some time we can get below error while above command try to execute.

Err:

RMAN> switch database to copy;

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of switch to copy command at 05/30/2016 23:35:44

RMAN-06571: datafile 1 does not have recoverable copy

It happened if standby database is in different incarnation with primary.

In Standby:

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       PROD  2886743190       PARENT  1          25-MAR-16

2       2       PROD  2886743190       PARENT  5965541899890 27-MAR-16

4       4       PROD  2886743190       ORPHAN  5969888275728 30-MAY-16

3       3       PROD  2886743190       CURRENT 5969888309840 30-MAY-16

In primary:

RMAN> list incarnation;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       PROD  2886743190       PARENT  1          25-MAR-16

2       2       PROD  2886743190       CURRENT  5965541899890 27-MAR-16

NOW change the standby database incarnation which is there in primary.

In standby:

            RMAN> reset database to incarnation 2;

                        database reset to incarnation 2

            rman> switch database to copy;

            Now shutdown and mount the database again.

9.  Recover the standby database with incremental backup pieces.

            rman> crosscheck backup;

            rman> delete expired backup;

            rman> catalog start with ' /u03/incremental_bkp';

            rman> recover database noredo;

10. Now verify change in scn status.

            SQL> select to_char(current_scn) from v$database;

             CURRENT_SCN

                        ---------------------------

                        5970008638143

11. On the standby database, start the Managed Recovery Process.

            sql>alter database recover managed standby database disconnect from session;

Now recovery is started. MRP process also started. You can catalog the archivelogs. It will apply archivelogs automatically.