Roll Forward your Standby Oracle RAC database

Due to network issues or any other temporary issues, the synchronization between your Primary and Standby database might not be in SYNC.

Here we would discuss, how to Roll Forward your Standby Database in Oracle RAC to ensure syncup with the Primary Database.

Please click on the INDEX and browse for more interesting posts.

Below are the steps to follow :

 

1. Verify GAP

On Primary

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

——— ——————– —————-

INFRA       READ WRITE           PRIMARY

SQL> select max(sequence#) from v$archived_log where archived=’YES’;

 

MAX(SEQUENCE#)

————–

        167955

On Standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

———      ——————–           —————-

INFRA       MOUNTED              PHYSICAL STANDBY

We don’t have archive logs from 167804 to 167955. Now how to recover standby database without rebuild from scratch? 

2. Stop Redo Transfer (On Primary)

SQL> show parameter log_archive_dest_state_2 

NAME                                 TYPE        VALUE

———————————– ———– ——————————

log_archive_dest_state_2  string      ENABLE

SQL> alter system set log_archive_dest_state_2=’DEFER’ scope=both; 

System altered. 

SQL> show parameter log_archive_dest_state_2 

NAME                                 TYPE        VALUE

———————————– ———– ——————————

log_archive_dest_state_2   string      DEFER

3. Find current SCN from Standby

SQL> SELECT CURRENT_SCN FROM V$DATABASE; 

CURRENT_SCN

———–

5988311909

4. Take RMAN Incremental from SCN (On Primary) with control file backup

[oracle@INFRA RMAN]$RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup incremental from SCN 5988311909 database tag=’STANDBY_TODAY_REFRESH’ format ‘/RMAN/database_%d_%u_%s’;
BACKUP CURRENT CONTROLFILE for standby format ‘/RMAN/ctstdy_%d_t%t_s%s_p%p’;
release channel c1;
release channel c2;
release channel c3;
}

6. Transfer Backup to standby
7. Stop Recovery and Shutdown Standby

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
INFRA MOUNTED PHYSICAL STANDBY

SQL> alter database recover managed standby database cancel;

Database altered.

[oracle@INFRA]$ srvctl stop database -d INFRA_STBY

8. Open the database in no mount mode and restore the control file

[oracle@INFRA]$ srvctl start database -d INFRA_STBY -startoption NOMOUNT

RMAN> restore standby controlfile from ‘/RMAN/backup_standby/ctstdy_INFRA_t1059612733_s131008_p1’; 

9. Mount Standby Database and catalog backup pieces on standby

RMAN > Alter database mount;

RMAN> catalog backuppiece ‘/RMAN/backup_standby/database_INFRA_0tto3o79_29’;

10. Recover standby

RMAN> RECOVER DATABASE FROM TAG STANDBY_TODAY_REFRESH NOREDO;

11. Enable log shipping from both primary nodes

SQL> alter system set log_archive_dest_state_2=’ENABLE’ scope=both; 

Database altered.

12. Enable MRP from single standby node

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

Database altered. 

13. Check the synchronization with Primary

SQL> SELECT A.THREAD#,
B.LAST_SEQ,
A.APPLIED_SEQ,
to_char(A.LAST_APP_TIMESTAMP ,’DD-MON-YYYY HH24:MI:SS’) “Applied Time”,
B.LAST_SEQ – A.APPLIED_SEQ ARC_DIFF
FROM ( SELECT THREAD#,
MAX (SEQUENCE#) APPLIED_SEQ,
MAX (NEXT_TIME) LAST_APP_TIMESTAMP
FROM GV$ARCHIVED_LOG
WHERE APPLIED = ‘YES’
GROUP BY THREAD#) A,
( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
FROM GV$ARCHIVED_LOG
GROUP BY THREAD#) B
WHERE A.THREAD# = B.THREAD#;

 

You will be all set now and your Standby database should be in sync with Primary