Standby roll forward using RMAN incremental backup

Last week I came across a scenario where a standby database went out of synch with the primary and there was a big gap of archive logs to be applied. Instead of rebuilding the standby database from scratch or restoring and applying a zillion archives, we can make use of a feature available from the version 10.2 onwards called the standby roll forward that uses RMAN incremental backup to apply the changes on the standby.

For databases on non-ASM, it should work with the first 4 steps as indicated below. For databases on ASM, we need to perform some more steps.

To keep it simple, I have outlined the steps below and have attached the logs for reference.

1) Identify the last SCN on the Standby, take the minimum value returned by the below queries for further steps.

SQL>
col current_scn for 99999999999999999999999
col int_scn for 99999999999999999999999

select current_scn FROM V$DATABASE;
select min(fhscn) int_scn from x$kcvfh;  

CURRENT_SCN
-----------------
47987632

2) Take an incremental backup on the primary database from this SCN to the current point.
The incremental backup files will be generated on the filesystem under the directory “/ora001/backup/” in the example.
Need to make sure that there is sufficient space in the filesystem to hold the backup. In this case, the backup files sized nearly 1.2G.

RMAN> CONFIGURE CHANNEL c1 DEVICE TYPE DISK FORMAT   '/ora001/backup/std_%U';
RMAN> BACKUP INCREMENTAL FROM SCN 47987632 DATABASE;

3) SCP the backup files to the server where the standby database is running and catalog the backup.

RMAN> CATALOG START WITH '/ora001/backup/std_%U';

This makes the controlfile aware of the incremental backup.

4) Recover the standby database.

RMAN>
RECOVER DATABASE NOREDO;

5) Check the archive gap and the SCN to see if the DB has applied the changes.

SQL>
col CURRENT_SCN for 99999999999999999999999
select * from v$archive_gap;
select current_scn from v$database;

The standby is in good shape if the SCN returned is a bigger value and there are no gaps.

—- Steps above should be sufficient for non-ASM databases; continue the steps below for databases on ASM. —-
(It was found that the SCN was not as expected and the steps below were done)

5) Cancel the database recovery at the standby site.

SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6) Take the controlfile backup again on primary database and SCP it over to the standby server.

RMAN>
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/ora001/ctrl_bkp.bak';

7) Take pfile, controlfile backups and note the current controlfile location on the standby and shutdown the standby database.

SQL>
select name  from v$controlfile;
shut immediate;
startup nomount;

8) Restore the standby controlfile created in step 6 on the standby site.

RMAN>
RESTORE STANDBY CONTROLFILE FROM '/ora001/ctrl_bkp.bak';
sql 'alter database mount';

9) At this point, the controlfiles on the standby database will have the datafiles pointed to the locations on primary. This needs to be updated so that they point to the right datafiles.

(For eg: On primary, it would be something like ‘+DATA01_R5/prod’ and on standby, the structure will be something like ‘+DATA01_R5/stby’)

RMAN>
CATALOG START WITH '+DATA01_R5/stby';

(May need to repeat this if there are files in multiple diskgroups)

10) Imp: If there are datafiles created on the primary database after the last SCN on standby (the one on step 1), we’ll need to backup the datafile(s), catalog the backup, do the restore of those file(s) too. The files can be identified with the below SQL:

SQL>
SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 47987632;

11) Perform the switch so that the controlfile points to the datafiles under the standby directory.

RMAN>
SWITCH DATABASE TO COPY;

12) Clear redo log groups on the standby.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

13) Check the SCN on the standby and start the managed recovery. The SCN should be number much higher than that was on step 1 and there shouldn’t be no archive gaps.

SQL>
select current_scn from v$database;
select * from v$archive_gap;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

14) Do a couple of log switches on the primary and verify from the alert log that the standby is catching up with the primary.

15) Verify the standby redologs and other settings as required depending on the configuration and make sure the standby is in good shape.

Share

1 comment to Standby roll forward using RMAN incremental backup

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>