Rolling Forward Standby Database When Archive Log is Missing
Oracle Dataguard is technology of choice for maintaining disaster recovery site. Over past few releases it has matured significantly and with data guard broker and grid control it is really easy to setup, maintain and perform switchovers.
To setup a standby you have to clone the primary database, after that depending on type of physical standby data guard you choose, either redo logs or archived logs are transferred to standby site and applied.
Standby data guard is quite resilient in terms of catching up with primary database. If network is down for some time, standby falls behind primary but as network becomes available standby requests missing logs and continues to apply them. It may take some time to catch up the primary depending on amount of logs generated during network disruption.
If for some reason some of the archived logs are missing then you may think you will have to redo the standby setting from scratch by cloning the database again. But there is good news for you, you can roll forward standby to recover up to current SCN of primary without applying missing archived logs.
The basic idea is to capture status of current standby database, then compare it with primary database and gather incremental change and apply it on standby.
Here are the step by step instructions:
1. Shutdown standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
2. Rename existing control files in standby
3. Start a SQL session on the primary database and create the control file for the standby database:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
4. Copy the standby control file to all control files locations in standby server.
5. Connect to the standby database and mount (but do not open) the standby database:
SQL> STARTUP MOUNT;
6. If data file locations are different in primary and standby, verify that spfile has db_file_name_convert defined.
SQL> SHOW PARAMETER CONVERT;
7. If convert parameter is missing, set it and shutdown the database and startup mount again.
SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='H:\MYDB\SYSTEM01.DBF', 'H:\MYDB\SYS\SYSTEM01.DBF','M:\MYDB\UNDOTBS01.DBF','I:\MYDB\UNDO\UNDOTBS01.DBF' SCOPE=SPFILE;
8. Connect to the recovery catalog and the standby database as the target, and manually catalog the standby datafiles as datafile copies
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB
RMAN>CATALOG DATAFILECOPY
'H:\MYDB\SYS\SYSTEM01.DBF',
'I:\MYDB\UNDO\UNDOTBS01.DBF',
'H:\MYDB\DATA1\MYSCHEMA_DATA01.DBF',
'I:\MYDB\DATA2\MYSCHEMA_DATA02.DBF',
'I:\MYDB\IDX1\MYSCHEMA_INDX01.DBF',
'H:\MYDB\IDX2\MYSCHEMA_INDX02.DBF',
'I:\MYDB\DATA2\TOOLS01.DBF',
'H:\MYDB\SYS\SYSAUX01.DBF'
LEVEL 0 TAG 'STBY';
9. Exit RMAN and start it again then connect to the recovery catalog and the primary database as the target, and create an incremental level 1 backup using the datafile copies as the parent level 0.
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@MYDB
RMAN> RUN {
ALLOCATE CHANNEL DSK1 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK2 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK3 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK4 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
BACKUP AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL 1 TAG 'STBY' FOR RECOVER OF COPY WITH TAG 'STBY' DATABASE;
RELEASE CHANNEL DSK1;
RELEASE CHANNEL DSK2;
RELEASE CHANNEL DSK3;
RELEASE CHANNEL DSK4;
}
9. Exit RMAN
10. Copy the newly created backup piece to the same location on the standby system
COPY D:\BACKUP\MYDB\STDBY\*.* \\STDBYSERVER\D$\BACKUP\MYDB\STDBY
10. To minimize the possibility of any other programs accessing Oracle files, stop Oracle agents and remove broker configuration as well.
11. Remove broker configuration using DGMGRL
DGMGRL> REMOVE CONFIGURATION;
12. Start RMAN and connect to the recovery catalog and the standby database as the target, and roll the datafile copies forward
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANREP
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB
RMAN>RUN {
SQL 'ALTER DATABASE MOUNT';
ALLOCATE CHANNEL DSK1 TYPE DISK;
ALLOCATE CHANNEL DSK2 TYPE DISK;
ALLOCATE CHANNEL DSK3 TYPE DISK;
ALLOCATE CHANNEL DSK4 TYPE DISK;
RECOVER COPY OF DATABASE WITH TAG 'STBY';
RELEASE CHANNEL DSK1;
RELEASE CHANNEL DSK2;
RELEASE CHANNEL DSK3;
RELEASE CHANNEL DSK4;
}
13. Using SQL Plus at standby database,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP NOMOUNT;
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
14. Verify that the database started without any error - check alert log file.
15. Start the Oracle agents and upload agent.
16. Once standby server appears online in Grid, setup the data guard configuration.
17. Verify that the logs are getting copied and applied.
18. Connect to RMAN and standby database and delete the incremental backup
RMAN>DELETE BACKUP TAG 'STBY';
RMAN>CHANGE COPY LIKE 'D:\BACKUP\MYDB\STDBY\%' UNCATALOG;
This has proved to be a life saver for me when some archived log files disappeared from primary database and I was worried that I have to start from scratch. It has been tested in Oracle 10g, and it should work in 11g as well. You may even chose to use this if standby is far behind primary after a long network disruption, thus avoiding long archive log transfer and apply process to sync up standby with primary.
To setup a standby you have to clone the primary database, after that depending on type of physical standby data guard you choose, either redo logs or archived logs are transferred to standby site and applied.
Standby data guard is quite resilient in terms of catching up with primary database. If network is down for some time, standby falls behind primary but as network becomes available standby requests missing logs and continues to apply them. It may take some time to catch up the primary depending on amount of logs generated during network disruption.
If for some reason some of the archived logs are missing then you may think you will have to redo the standby setting from scratch by cloning the database again. But there is good news for you, you can roll forward standby to recover up to current SCN of primary without applying missing archived logs.
The basic idea is to capture status of current standby database, then compare it with primary database and gather incremental change and apply it on standby.
Here are the step by step instructions:
1. Shutdown standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
2. Rename existing control files in standby
3. Start a SQL session on the primary database and create the control file for the standby database:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'stbycf.ctl';
4. Copy the standby control file to all control files locations in standby server.
5. Connect to the standby database and mount (but do not open) the standby database:
SQL> STARTUP MOUNT;
6. If data file locations are different in primary and standby, verify that spfile has db_file_name_convert defined.
SQL> SHOW PARAMETER CONVERT;
7. If convert parameter is missing, set it and shutdown the database and startup mount again.
SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='H:\MYDB\SYSTEM01.DBF', 'H:\MYDB\SYS\SYSTEM01.DBF','M:\MYDB\UNDOTBS01.DBF','I:\MYDB\UNDO\UNDOTBS01.DBF' SCOPE=SPFILE;
8. Connect to the recovery catalog and the standby database as the target, and manually catalog the standby datafiles as datafile copies
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB
RMAN>CATALOG DATAFILECOPY
'H:\MYDB\SYS\SYSTEM01.DBF',
'I:\MYDB\UNDO\UNDOTBS01.DBF',
'H:\MYDB\DATA1\MYSCHEMA_DATA01.DBF',
'I:\MYDB\DATA2\MYSCHEMA_DATA02.DBF',
'I:\MYDB\IDX1\MYSCHEMA_INDX01.DBF',
'H:\MYDB\IDX2\MYSCHEMA_INDX02.DBF',
'I:\MYDB\DATA2\TOOLS01.DBF',
'H:\MYDB\SYS\SYSAUX01.DBF'
LEVEL 0 TAG 'STBY';
9. Exit RMAN and start it again then connect to the recovery catalog and the primary database as the target, and create an incremental level 1 backup using the datafile copies as the parent level 0.
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANDB
RMAN>CONNECT TARGET SYS/PASSWORD@MYDB
RMAN> RUN {
ALLOCATE CHANNEL DSK1 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK2 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK3 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
ALLOCATE CHANNEL DSK4 TYPE DISK FORMAT 'D:\BACKUP\MYDB\STDBY\INC_MYDB_%U_%S_%T.BAK';
BACKUP AS COMPRESSED BACKUPSET
INCREMENTAL LEVEL 1 TAG 'STBY' FOR RECOVER OF COPY WITH TAG 'STBY' DATABASE;
RELEASE CHANNEL DSK1;
RELEASE CHANNEL DSK2;
RELEASE CHANNEL DSK3;
RELEASE CHANNEL DSK4;
}
9. Exit RMAN
10. Copy the newly created backup piece to the same location on the standby system
COPY D:\BACKUP\MYDB\STDBY\*.* \\STDBYSERVER\D$\BACKUP\MYDB\STDBY
10. To minimize the possibility of any other programs accessing Oracle files, stop Oracle agents and remove broker configuration as well.
11. Remove broker configuration using DGMGRL
DGMGRL> REMOVE CONFIGURATION;
12. Start RMAN and connect to the recovery catalog and the standby database as the target, and roll the datafile copies forward
RMAN>CONNECT CATALOG RMANUSER/PASSWORD@RMANREP
RMAN>CONNECT TARGET SYS/PASSWORD@STDBYMYDB
RMAN>RUN {
SQL 'ALTER DATABASE MOUNT';
ALLOCATE CHANNEL DSK1 TYPE DISK;
ALLOCATE CHANNEL DSK2 TYPE DISK;
ALLOCATE CHANNEL DSK3 TYPE DISK;
ALLOCATE CHANNEL DSK4 TYPE DISK;
RECOVER COPY OF DATABASE WITH TAG 'STBY';
RELEASE CHANNEL DSK1;
RELEASE CHANNEL DSK2;
RELEASE CHANNEL DSK3;
RELEASE CHANNEL DSK4;
}
13. Using SQL Plus at standby database,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP NOMOUNT;
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
14. Verify that the database started without any error - check alert log file.
15. Start the Oracle agents and upload agent.
16. Once standby server appears online in Grid, setup the data guard configuration.
17. Verify that the logs are getting copied and applied.
18. Connect to RMAN and standby database and delete the incremental backup
RMAN>DELETE BACKUP TAG 'STBY';
RMAN>CHANGE COPY LIKE 'D:\BACKUP\MYDB\STDBY\%' UNCATALOG;
This has proved to be a life saver for me when some archived log files disappeared from primary database and I was worried that I have to start from scratch. It has been tested in Oracle 10g, and it should work in 11g as well. You may even chose to use this if standby is far behind primary after a long network disruption, thus avoiding long archive log transfer and apply process to sync up standby with primary.