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 7 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.
