Standby out of SYNC with Primary

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Oracle 10g Dataguard – Sync Standby database

One of the major problems being faced by DBAs managing Physical Standby Databases is to keep the standby database in sync with Primary Database.

There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons:

1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.

2. It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the methods to roll forward the physical standby database to have it in sync with the primary database.

The above situation can be tacked, without rebuild in Oracle 10g environment.

To Check GAP:

On Primary:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') DATE FROM DUAL;

SQL> SELECT MAX(SEQUENCE#) "LAST_LOG_GENERATED" FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED='YES';

SQL> SELECT DISTINCT SEQUENCE# "LAST SEQUENCE GENERATED", THREAD# "THREAD"
     FROM V$ARCHIVED_LOG
     WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
     ORDER BY 1;

On Standby:

SQL> SELECT MAX(SEQUENCE#) "LAST_LOG_APPLIED" FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED='YES';

SQL> SELECT ARCH.THREAD# "THREAD", ARCH.SEQUENCE# "LAST SEQUENCE RECEIVED", APPL.SEQUENCE# "LAST SEQUENCE APPLIED"
     FROM
      (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
      (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
     WHERE ARCH.THREAD# = APPL.THREAD#
     ORDER BY 1;
   
SQL> SELECT PROCESS,STATUS,SEQUENCE# FROM V$MANAGED_STANDBY;

SQL> SELECT SEQUENCE#, APPLIED, STATUS FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 4/24 ORDER BY SEQUENCE#;
 
Steps to bring the standby database in sync with Primary Database is listed below.

1. Find the current SCN of standby database.

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN 
------------
164793543008

2. On the primary database – create the needed incremental backup from the above SCN

$rman nocatalog target /

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 164793543008 DATABASE FORMAT '/ora/backup/incr_20121218_%U' tag 'INCR STANDBY';

3. Create a new standby controlfile from production

SQL> alter database create standby crontrolfile '/ora/backup/standbycontolfile.ctl';

4.Cancel managed recovery on standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5. Move your incremental backup from (2) to the standby server (empty folder) and catalog it

$rman nocatalog target /

rman> catalog start with '/ora/backup/incr_20121218';

6. Recover your standby from the incremental backup

rman> recover database noredo;

rman> DELETE BACKUP TAG 'INCR STANDBY';

7. Shutdown the standby and replace the controlfile with the one you backup in (3)

8.Startup the standby in mount state

SQL> startup mount

9. Crosscheck if the datafiles are showing the correct location after replacing the controlfile on standby taken from primary

SQL> select name from v$datafile;

/*As the controlfile has been restored from the primary, cross check if the datafiles location are different in primary and standby. For example, if the priamry datafiles are located at '/ora/primary_db/' and standby datafiles are at '/ora/standby_db/', the new controlfile has the datafiles location as '/ora/primary_db/DATAFILE'. We need to rename all the datafiles to reflect the correct location. */

10.  If the datafile location of the primary and standby databases are different, then you need to follow this step. If not, then proceed with Step 11

Since, we have restored the standby controlfile of primary database on the standby database (Step 7) and mounted the standby database, the standby database controlfile would now have the locations of the datafiles recorded as available in the Primary database. So, we need to make the standby controlfile understand that the datafiles location of the standby database are different from that of the Primary database.

Using RMAN:

You need to catalog the datafile location of the standby database to its controlfile as shown below. Connect the standby database through RMAN and catalog the location of its datafiles and later switch them.

$rman nocatalog target /

rman> catalog start with '/ora/standby_db/'; /* This will give the user a list of files and ask if they should all be catalog. The user should review and say YES if all the datafiles are properly listed.*/

searching for all files that match the pattern /ora/standby_db

List of Files Unknown to the Database
=====================================

File Name: /ora/standby_db/system.dbf
File Name: /ora/standby_db/sysaux.dbf
File Name: /ora/standby_db/undo.dbf
File Name: /ora/standby_db/users.dbf

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files
=======================

File Name: /ora/standby_db/system.dbf
File Name: /ora/standby_db/sysaux.dbf
File Name: /ora/standby_db/undo.dbf
File Name: /ora/standby_db/users.dbf

RMAN> switch database to copy;

datafile 1 switched to datafile copy “/ora/standby_db/system.dbf
datafile 2 switched to datafile copy “/ora/standby_db/sysaux.dbf
datafile 3 switched to datafile copy “/ora/standby_db/undo.dbf
datafile 4 switched to datafile copy “/ora/standby_db/users.dbf

11. After you switch the database to copy, datafile whose location is still as of primary and renaming effort also failed because the datafile is not at all present in the standby you need to do the following steps.

Copy the newly add datafile on primary to the standby database.

Connect primary database through rman and use the copy command to create a copy of the newly added datafile to temporary location.

SQL> select tablespace_name, file_id, file_name from dba_data_files where file_name like '%newfile%'; (or from the RMAN prompt by mentioning "report schema")

TABLESPACE_NAME  FILE_ID  FILE_NAME
---------------  -------  ----------------------------
ADD_NEW                5  /ora/primary_db/addnew.dbf

$rman nocatalog target /

rman> copy datafile 5 to '/ora/backup/addnew.dbf';

Use scp or other means, transfer the datafile to the standby server (for example to /ora/standby_db/ location: scp -p /ora/backup/addnew.dbf oracle@standby_server_ip_or_name:/ora/standby_db/).

Connect the standby database through RMAN and catalog the datafile that you copied in the above step

rman> catalog /ora/standby_db/addnew.dbf;

rman> switch datafile to copy;

11. And once everything is verified again put it back into managed recovery mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;