standby out of sync – Oracle Data Point

Standby is out of Sync or GAP is not getting resolved on standby

It can happen due to several reasons like
1. Archived log has been deleted
2. Moved log file has corrupted
3. Any network issues

If it’s because of either 1 or 2 incremental backup from SCN is the best option to recover it. Suppose if we have proper archive logs exist on the primary database then FAL_SERVER, FAL_CLIENT will take care of the gap issue.

SQL> select name,database_role,switchover_status,protection_mode,force_logging from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE FOR
——— —————- ——————– ——————– —
PRD6A PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE YES

SQL>

Make sure all the logs upto the gap is applied using v$archived_log and applied=YES. APPLIED=NO means MRP process is not applying logs on the standby database even though all the logs are available in standby database except the gaps.

SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
34 YES 14-JUL-12 14-JUL-12 RFS A NO
35 YES 14-JUL-12 14-JUL-12 RFS A NO
36 YES 14-JUL-12 14-JUL-12 RFS A NO
37 YES 14-JUL-12 14-JUL-12 RFS A NO
38 YES 14-JUL-12 14-JUL-12 RFS A NO
39 YES 14-JUL-12 15-JUL-12 RFS A NO
40 YES 15-JUL-12 15-JUL-12 RFS A NO
41 YES 15-JUL-12 15-JUL-12 RFS A NO
42 YES 15-JUL-12 15-JUL-12 RFS A NO
43 YES 15-JUL-12 15-JUL-12 RFS A NO
44 YES 15-JUL-12 16-JUL-12 RFS A NO

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
45 YES 16-JUL-12 16-JUL-12 RFS A NO
71 NO 19-JUL-12 19-JUL-12 RFS A NO
73 NO 19-JUL-12 19-JUL-12 RFS A NO
75 NO 19-JUL-12 19-JUL-12 RFS A NO

SQL>

After 45 you will be able to see 71, archive log gap is 46-70. In this situation we can take the incremental backup from SCN

Take an Incremental backup of primary database using standby current SCN.

Get the current SCN from standby database
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)
—————————————-
7728610994157

Stop the MRP process (stop redo Apply)
SQL> alter database recover managed standby database cancel;

Database altered.

Make sure no MRP is working, from V$MANAGED_STANDBY
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY where PROCESS=’MRP0′;

no rows selected

Connect primary database and take the RMAN Incremental backup using below script.
$ rman target /

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Jul 29 05:15:21 2012

connected to target database: PRD6A (DBID=2752208625)

RMAN> BACKUP INCREMENTAL FROM SCN 7728610994157 DATABASE FORMAT ‘/db/flashback/rmanback/ForStandby_%U’ tag ‘STANDBY’;
RMAN>

Starting backup at 29-JUL-12

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
backup will be obsolete on date 05-AUG-12
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/db/prd6a/dbf1/datafile/doc_001.dbf
.
.
.
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_11nh9epm_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:35

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/rmanback/ForStandby_12nh9f29_1_1 tag=STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JUL-12

RMAN>

Copy this backup piece from primary server to standby server and catalog the pieces with standby controlfile.
RMAN> catalog backuppiece ‘/db/backup/ForStandby_11nh9epm_1_1’;

after this start the recovery on the sane session.
RMAN> recover database noredo;

allocated channel: disk1
channel disk1: SID=250 device type=DISK

allocated channel: disk2
channel disk2: SID=368 device type=DISK

Starting recover at 29-JUL-12
allocated channel: ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
….
..
..
.
channel ORA_DISK_1: reading from backup piece /db/backup/ForStandby_11nh9epm_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/db/backup/ForStandby_11nh9epm_1_1 tag=STANDBY
channel ORA_DISK_1: restore complete, elapsed time: 05:03:08
Finished recover at 29-JUL-12

Better to delete the backup from the standby using the TAG given while taking backup.

RMAN>delete backup tag ‘STANDBY’;

Start the managed recovery on standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Now check the database MRP is applying proper logs, It must solve the issue
SQL> SELECT SEQUENCE#,applied, FIRST_TIME, NEXT_TIME,REGISTRAR,status,END_OF_REDO_TYPE,DELETED FROM V$ARCHIVED_LOG;

46 YES 16-JUL-12 16-JUL-12 RFS A NO
47 YES 16-JUL-12 16-JUL-12 RFS A NO

SEQUENCE# APPLIED FIRST_TIM NEXT_TIME REGISTR S END_OF_RED DEL
———- ——— ——— ——— ——- – ———- —
48 YES 16-JUL-12 16-JUL-12 RFS A NO
49 YES 16-JUL-12 16-JUL-12 RFS A NO

select process,client_process,status,thread#,sequence#,block#,blocks,DELAY_MINS from v$managed_standby;

In case if it still persist then we need to re-create the controlfile of standby database from primary database.

Why we are recreating the controlfile?
Why because state of the database might not be changed while applying incremental backup. So that the database_scn
will be intact and scn for datafile were updated so that the database will look for old file copy.

Steps to re-create the controlfile [Refer note 734862.1 for details]

take the backup of control file from primary database
$:>rman target /
RMAN> backup current controlfile for standby;
Starting backup at 29-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=370 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUL-12
channel ORA_DISK_1: finished piece 1 at 29-JUL-12
piece handle=/db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp tag=TAG20120729T062512 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-12

Restore and start database using new controlfile

Copy this controlfile /db/flashback/PRD6A/backupset/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp from primary to standby and
restore the control file on standby.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> Shut immediate;

SQL> startup nomount;

Restore the controlfile using RMAN

rman target /
restore standby comtrolfile from ‘/db/backup/2012_07_29/o1_mf_ncnnf_TAG20120729T062512_818spbmh_.bkp’;

SQL> shut immediate;
SQL> startup mount;

Start the MRP.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Note: If we have different file location on primary and standby, then we need change the datafile
location in standby database.

use alter database command to change the file name in controlfile.

SQL> alter database rename file ‘<from file-name >’ to ‘<to file-name >’;

Once everything is fine, Make sure standby logfile exist both physically and on the controlfile too, then start
managed recovery using standby logfile.

In case still you have the problem with again few log file and those are existing on the standby server
then manually register it using the below command

SQL> alter database register logfile ”;

Check the MRP status now, we will be able to see it has changed from ARCHIVE_LOG_GAP to APPLYING_LOGS.