Physical Standby Database Lags Far Behind the Primary Database

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

Note:

The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an un resolvable archive gap.

1. On the standby database, stop the managed recovery process (MRP):

2.  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. On the standby database, find the SCN which will be used for the incremental backup at the primary database:

4.  SQL> SELECT CURRENT_SCN FROM V$DATABASE;

5. In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

6.  RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>

7.  DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

Note:

RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:

o    The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database

o    The backup is not cataloged at the source database

o    The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.

o    You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database.

8. Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:

9.  SCP /tmp/ForStandby_* standby:/tmp

10. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

11.  RMAN> CATALOG START WITH '/tmp/ForStandby';

12. Recover the standby database with the cataloged incremental backup:

13.  RMAN> RECOVER DATABASE NOREDO;

14. In RMAN, connect to the primary database and create a standby control file backup:

15.  RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

16. Copy the standby control file backup to the standby system. For example:

17.  SCP /tmp/ForStandbyCTRL.bck standby:/tmp

18. Shut down the standby database and startup nomount:

19.  RMAN> SHUTDOWN;

20.  RMAN> STARTUP NOMOUNT;

21. In RMAN, connect to standby database and restore the standby control file:

22.  RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

23. Shut down the standby database and startup mount:

24.  RMAN> SHUTDOWN;

25.  RMAN> STARTUP MOUNT;

26. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

27.  RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 

28.  RMAN> SWITCH DATABASE TO COPY;

29. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.
30. On the standby database, clear all standby redo log groups (there may be more than 3):

31.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

32.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

33.  SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

34. On the standby database, restart Flashback Database:

35.  SQL> ALTER DATABASE FLASHBACK OFF;

36.  SQL> ALTER DATABASE FLASHBACK ON;

37. On the standby database, restart MRP:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14239/scenarios.htm#CIHEGFEG