Rolling forward physical standby databases using RMAN incremental backup

Article2_RMAN_PITR

If having a data guard solution makes us confident with a straightforward backup solution in case of failure of the primary, it is required in the counterpart to make sure primary and standby databases are well-synchronized. And it may happen that, in some events like network failure, the standby lags far behind the primary. Depending on the size of the database, the standby can just be rebuilt or RMAN incremental backups can be applied to the standby.
It’s the later case, more useful for large databases, the following method describes here: it takes account of the lag in redo logs transport but also of gaps and missing data files which may have been created on the primary during the interruption.

Software releases used in this scenario: Oracle Database Enterprise Edition (11.2.0.3.5), Oracle Enterprise Linux 6 on virtual machines built with VMware Sphere v5.1.

 

1/ Preliminary steps

1.1 – Stop the recovery process on the standby.

If a data guard Broker is in use, disable the configuration for this standby:

DGMGRL> DISABLE CONFIGURATION <stdby_db>;

and stop recovery using SQL*Plus:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

1.2- Gather data guard synchronization data

–          Determine the lowest SCN on the standby using these 2 queries:

SQL> select database_role, current_scn from v$database;
DATABASE_ROLE       CURRENT_SCN
———————-     —————–
PHYSICAL STANDBY   2861299

SQL> select min(checkpoint_change#) from v$datafile_header;
MIN(CHECKPOINT_CHANGE#)
———————————–
2861300

If the standby database is a RAC database, shutdown all instances except one.

 

–          On the primary, obtain the current SCN and check for data files changes which may have occurred since the lowest standby database SCN.

SQL> select current_scn from v$database;
CURRENT_SCN
———————-
2911795
SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 2861299;

FILE# NAME
———- ——————————————————-
8 +DATA/MPEG/datafile/app_idx.331.850747379
9 +DATA/MPEG/datafile/app_idx.330.850747635

To summarize, the standby is behind the primary (current SCN values) and 2 data files on the primary do not exist on the standby.

 

2/ Perform RMAN incremental backups

From previous section, backup of the missing data files and incremental backups of the primary database are required to roll forward the standby database as well as a backup of the current control file.

For ease of the procedure, a different naming prefix for the control file backup will be used from one of the 2 other backup types.

RMAN> backup datafile 8, 9 format ‘/home/oracle/bu_rman/StbyIncr_%U’ tag ‘STDBY_INCR’;

RMAN> backup incremental from scn 2861299 database format ‘/home/oracle/bu_rman/StbyIncr_%U’ tag ‘STDBY_INCR’;

RMAN> backup current controlfile for standby format   ‘/home/oracle/bu_rman/ctrl_for_stdby.bck’;

 

3/ Transfer the resulting backups on the standby server.

Using “scp” or other command tool suitable with the operating system.

 

4/ Recover the standby database

4.1 Restore the control file on the standby

RMAN> shutdown
RMAN> startup nomount
RMAN> restore standby controlfile from ‘/home/oracle/bu_rman/ctrl_for_stdby.bck’;

 

4.2/ Mount the standby

RMAN> alter database mount;

 

4.3/ Record the backups in the controlfile

RMAN> CATALOG START WITH ‘/home/oracle/bu_rman/StbyIncr’;

 

4.4/ Restore missing data files

RMAN> RUN
{
set newname for datafile 8 to ‘+DATA’;
set newname for datafile 9 to ‘+DATA’;
restore datafile 8,9;
}

 

4.5/ Catalog standby data files

This step is only required if directory names between the primary and the standby are different.

RMAN> CATALOG START WITH ‘+DATA/MPEG_S/datafile’;

searching for all files that match the pattern +DATA/MPEG_S/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/MPEG_S/DATAFILE/SYSTEM.257.832876067
File Name: +data/MPEG_S/DATAFILE/SYSAUX.258.832876067
File Name: +data/MPEG_S/DATAFILE/EXAMPLE.259.832876067
File Name: +data/MPEG_S/DATAFILE/UNDOTBS1.260.832876069
File Name: +data/MPEG_S/DATAFILE/UNDOTBS2.261.832876069
File Name: +data/MPEG_S/DATAFILE/USERS.262.832876071
File Name: +data/MPEG_S/DATAFILE/APP_DATA.265.850669305
Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

If directory name are different and you are not able to see “unknown”, it will be better to check if something has not been missed previously like the “standby” key word in step 4.1, so restart from there.

 

4.6/ Switch database to copy

to make the standby data files (recoverable) image copies:

RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy “+DATA/mpeg_s/datafile/system.257.832876067”
datafile 2 switched to datafile copy “+DATA/mpeg_s/datafile/sysaux.258.832876067”
datafile 3 switched to datafile copy “+DATA/mpeg_s/datafile/undotbs1.260.832876069”
datafile 4 switched to datafile copy “+DATA/mpeg_s/datafile/users.262.832876071”
datafile 5 switched to datafile copy “+DATA/mpeg_s/datafile/example.259.832876067”
datafile 6 switched to datafile copy “+DATA/mpeg_s/datafile/undotbs2.261.832876069”
datafile 7 switched to datafile copy “+DATA/mpeg_s/datafile/app_data.265.850669305”
datafile 8 switched to datafile copy “+DATA/mpeg_s/datafile/app_idx.266.850749695”
datafile 9 switched to datafile copy “+DATA/mpeg_s/datafile/app_idx.267.850749697”

 

4.7/ Recover the standby

with the incremental backups recorded in the control file in step 4.3.

RMAN> RECOVER DATABASE NOREDO;

 

4.8/ Clear standby redo logs.

 

SQL> select GROUP# from v$logfile where TYPE=’STANDBY’ group by GROUP#;
GROUP#
———-
5
6

SQL> BEGIN
FOR i IN 5..7 LOOP
EXECUTE IMMEDIATE (‘ALTER DATABASE CLEAR LOGFILE GROUP ‘||i);
END LOOP;
END;
/

PL/SQL procedure successfully terminated.

 

4.9/ Start the MRP process

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

At this point, the standby database is in sync with the primary and available again.

 

5/ Flashback mode.

Oups … you need to have your database in flashback mode: well, stop the recovery process as in step 1.1, issue the 2 following SQL commands:

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

Restart the MRP process as in step 4.9 and you’re back to relax…at least for a little while.

About Jean-yves Maltese

Jean-Yves Maltese has written 3 post in this blog.

Consultant Oracle senior chez EASYTEAM

Leave a Reply

%d bloggers like this: