Rolling forward a standby database using RMAN incremental backup when datafile is added to primary and archivelogs missing at primary
What Is an Archive Gap:-
An archive gap is a range of archived redo logs created whenever the standby system is unable to receive the next archived redo log generated by the primary database. For example, an archive gap occurs when the network becomes unavailable and automatic archiving from the primary database to the standby database stops. When the network is available again, automatic transmission of the redo data from the primary database to the failed standby database resumes.
Causes:-
The steps in this section can used to resolve problems if a physical standby database has
1) Loss of Archive log
2) Corrupted Archive Redo Data
3) Unresolvable Archive GAP’s
4) Out of Retention policy WorkArounds:-
1) If archive exist on primary Troubleshoot the issue, It will automatically shipped if the archive is not corrupted.
2) If archive is corrupted at OS level, if we do have backup, Then Restore archivelogs from Backup.
3) Incremental SCN for standby/ Roll Forward.
On Standby
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
SQL> select min(checkpoint_change#) from v$datafile_header;
CHECKPOINT_CHANGE#
---------------------
3162298
NOTE :- In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may be behind.
Check Datafiles added
On Primary check if any new datafile is added in regards to scn
SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <SCN_NUMBER_FROM_STEP 2>;
Using rman, create backup of missing datafiles and an incremental backup using the SCN derived in the previous step
RMAN> backup datafile 5, 6, 7, 8 format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup incremental from SCN 3162298 database format '/tmp/ForStandby_%U' tag 'FORSTANDBY';
RMAN> backup current controlfile for standby format '/tmp/ForStandbyCTRL.bck';
NOTE:- if there is now rows selected from Primary check 1, please skip backup datafile step
Tranfer files from primary to standby:-
scp /tmp/ForStandby_* texas:/tmp
On Standby restore new controlfile and catalog the backup transfered
RMAN> shutdown;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/tmp/ForStandbyCTRL.bck';
RMAN> alter database mount;
RMAN> CATALOG START WITH '/tmp/ForStandby';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/ForStandby
List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/ForStandby_2lkglss4_1_1
File Name: /tmp/ForStandby_2mkglst8_1_1
restore missing datafiles:-
run
{
set newname for datafile X to '+DISKGROUP';
set newname for datafile Y to '+DISKGROUP';
set newname for datafile Z to '+DISKGROUP';
etc.
restore datafile x,y,z,....;
}
Check Datafiles location on Standby database
spool datafile_names_step8.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
As the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
List of Files Unknown to the Database
=====================================
File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773
File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773
File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333
File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333
File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335
Once all files have been cataloged, switch the database to copy:
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333"
datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335"
datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"
Recover the STANDBY database with the cataloged incremental backup:
RMAN> RECOVER DATABASE NOREDO;
starting recover at 03-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=28 dev
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/mystd/datafile/system.297.688213333
destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335
destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 03-JUN-09
Clear all log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Start Managed Recovery Processes
SQL> ALTER DATABASE RECOVER MANAGED STANDBY USING CURRENT LOGFILE DISCONNECT FROM SESSION;