Oracle: Synchronize standby

Follow

1. Synchronize using Dbvisit Sync feature

1.1 Introduction

This section will provide you with an overview of the Synchronize Standby Database feature in Multiplatform.

This option is to assist in two particular scenarios:

  • Resolving unrecoverable archive log gaps

    • This can happen when archive logs were lost or deleted by accident before they were transferred and applied to the standby database.  This way we can roll the standby database forward using RMAN incremental backups, instead of recreating it.  

  • Fixing logical corruption on the standby database due to nologging operations that were performed on the primary

    • When nologging operations are performed on a primary database, there is no redo available that can be used to update the standby database.  The effect is logical corruption on the standby database.  

    • We recommend that FORCE LOGGING be enabled for the primary database

The Sync feature, in summary, does the following:

  1. Detect the current standby database SCN number

  2. Compares this with the Primary database and then starts an incremental backup on the primary for all blocks from this SCN number.

  3. The backup process can take a similar time to a full RMAN backup based on the time the standby was out of sync with primary and the changes that happened during the timeframe.

  4. The backup should, however, be small - depending on the difference between the primary and standby

  5. The backup is then transferred to the standby database server

  6. From there a recovery process is started that will use this incremental backup to roll forward the standby database.

  7. During this process, the standby controlfile is recreated

1.2  Synchronize Standby Database

In the section below we will show you how you can run the Sync feature for both options to recover from unrecoverable archive log gap, as well as fix logical corruption due to nologging operations.

It may happen that an archivelog was removed by backups before it was shipped to the standby, or if the standby database is far behind the primary for some reason - it is then possible to make use of the Synchronize Standby Database feature provided by Dbvisit Multiplatform. This method can be used instead of the traditional rebuild of the standby database using the CSD (Create Standby Database) feature.
During this process, Dbvisit Multiplatform will perform an incremental backup on the primary database backing up the required changed blocks from the SCN number where the standby is currently and then ship this backup to the standby and then use RMAN to recover the standby using this backup. The result is that the standby database is "rolled forward" past this missing archived log. The process is easy and described in the steps below.

Choose the configuration for which you need to synchronise the standby database.

( 1 ) The primary and standby details are displayed including the SCN and timestamp the Primary and standby are at and also the time gap between them

( 2 ) By default, the Synchronize Lag option is selected.

( 3 ) We can use the transportable media option for the destination so that the external media can be used to take backup in source and we could resume the sync once the media has been attached in the standby.

( 4 ) Specify the temporary backup location where the incremental backup will be created on the primary database server. It is important that this location must be able to hold at least a full compressed backup of the primary database (RMAN compressed backupset). If you do not have sufficient disk space, the Synchronize Standby process will fail. Note that the backup in most cases will be much smaller than a normal full backup as only the blocks needed to recover the standby database will be backed up. Make sure this location has correct permission and that the Oracle software owner has full read-write permission on this folder. This is the temporary location on the standby database server where the temporary incremental backup will be copied. It is recommended that this location have sufficient space to hold a full compressed backup of the primary database to ensure you do not run into any space-related issues during this process. This location must exist on the standby database server and the Oracle software owner must have full read/write permission on this folder.

( 5 ) Once you have provided the source and destination temporary backup locations, you can click on start to start the process.

The progress of the synchronize standby can be seen from the tasks .

Once the process is completed, the automatic standby update can be enabled and the archivelogs would automatically be shipped from primary and applied on standby.

2. Synchronize standby DB manually

It is possible to use directly RMAN to synchronize standby database with incremental backups without using streamlined Dbvisit "Synchronize" process. Below are the steps needed for manual incremental backup synchronization of your standby database if you can't use the streamlined automated process for any reason.

NOTE: This process should be performed only by an Oracle DBA as it requires Oracle database knowledge. Whenever possible, use and prefer the streamlined synchronize process as described on Oracle: Synchronize standby

2.1 Prerequisites

A. Standby database must be mounted. Verify this by running on standby:

[oracle@czlin0212 oracle]$ sqlplus / as sysdba
 
select open_mode,database_role from v$database;
 
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

If your standby database is not MOUNTED or can't be MOUNTED, it's very likely in no shape for Incremental backup sync and you should recreate it from the scratch: Create DR Database for Oracle

B. Standby database has no datafile errors. On standby run:

[oracle@czlin0212 oracle]$ sqlplus / as sysdba
 
select file#, name, status from v$datafile
where status = 'RECOVER' or status = 'OFFLINE' or status = 'SYSOFF' or ERROR = 'FILE NOT FOUND';

The result must return no rows, otherwise your standby database is likely in no condition for Incremental backup sync (recreate from the scratch).

C. Standby database has all datafiles which primary database has and is on the same incarnation. On primary and standby run:

[oracle@czlin0211 oracle]$ sqlplus / as sysdba
 
select file#,name from v$datafile;
select * from v$database_incarnation;

The results must match. It's possible to have different paths and names on primary and standby (for example when using OMF and ASM to filesystem conversion), but otherwise the datafiles need to be equal. 

D. You need to ensure no archivelog will be deleted on primary from the point in time you start with the process, until the point in time when apply of the incremental backup is finished.

2.2 Performing Incremental backup sync manually

1. Find out the lowest needed recovery SCN on standby database:

[oracle@czlin0212 oracle]$ sqlplus / as sysdba
 
select min(CHECKPOINT_CHANGE#) from v$datafile_header where con_id <> 2;
 
MIN(CHECKPOINT_CHANGE#)
-----------------------
              137273928

The con_id <> 2 condition is to skip PDB$SEED datafiles. 

2. Run incremental backup on your primary database:

Be sure to have backup directory ready (in this case /u02/app/oracle/dbvisit_backup). There's unfortunately no way how to predict the size of the incremental backup - all depends on number of changes done to your primary database since last synchronization with standby database.

[oracle@czlin0211 oracle]$ rman target /
 
run {
allocate channel C_DBVISIT DEVICE TYPE DISK ;
backup  AS COMPRESSED BACKUPSET INCREMENTAL FROM SCN 137273928 database format '/u02/app/oracle/dbvisit_backup/DBV_SYNC_%U.RMAN' tag 'DBV_SYNC';
backup current controlfile for standby format '/u02/app/oracle/dbvisit_backup/standby-ctl';
release channel C_DBVISIT;
}

3. Copy all backup files to standby server:

Copy all files located in /u02/app/oracle/dbvisit_backup to same location on standby server via scp

[oracle@czlin0211 oracle]$ scp /u02/app/oracle/dbvisit_backup/* czlin0212:/u02/app/oracle/dbvisit_backup

4. Restore controlfile on your standby server:

[oracle@czlin0212 oracle]$ rman target /
 
run{
startup nomount force;
restore standby controlfile from '/u02/app/oracle/dbvisit_backup/standby-ctl';
sql 'alter database mount';
}

If you copied backup to standby server into different directory, you will need to catalog it before proceeding:

RMAN> catalog start with '<directory on standby server where you copied backup from primary>';

5. Catalog standby database datafiles on standby:

This step is necessary to perform if using OMF and/or different datafile locations on primary and standby

[oracle@czlin0212 oracle]$ rman target /
 
catalog start with '/u01/app/oracle/oradata/MEGA';

You will need to catalog datafiles for each separate location, for example if you would have datafiles in /u02/app/oracle/oradata/MEGA you would need to catalog datafiles in that location as well.

example output:

searching for all files that match the pattern /u02/app/oracle/oradata/MEGA
 
List of Files Unknown to the Database
=====================================
File Name: /u02/app/oracle/oradata/MEGA/datafile/o1_mf_system_lh14zdly_.dbf
File Name: /u02/app/oracle/oradata/MEGA/datafile/o1_mf_sysaux_lh151jqk_.dbf
File Name: /u02/app/oracle/oradata/MEGA/datafile/o1_mf_undotbs1_lh152g7f_.dbf
File Name: /u02/app/oracle/oradata/MEGA/datafile/o1_mf_users_lh15331k_.dbf
File Name: /u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_system_lh150z8h_.dbf
File Name: /u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_sysaux_lh1525bd_.dbf
File Name: /u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_undotbs1_lh152ptl_.dbf
File Name: /u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_system_lh1539dk_.dbf
File Name: /u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_sysaux_lh153oj4_.dbf
File Name: /u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_undotbs1_lh1549n6_.dbf
File Name: /u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_users_lh154hto_.dbf
 
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

6. Switch database to copy on standby:

[oracle@czlin0212 oracle]$ rman target /
 
switch database to copy;

example output:

datafile 1 switched to datafile copy "/u02/app/oracle/oradata/MEGA/datafile/o1_mf_system_lh14zdly_.dbf"
datafile 2 switched to datafile copy "/u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_system_lh150z8h_.dbf"
datafile 3 switched to datafile copy "/u02/app/oracle/oradata/MEGA/datafile/o1_mf_sysaux_lh151jqk_.dbf"
datafile 4 switched to datafile copy "/u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_sysaux_lh1525bd_.dbf"
datafile 5 switched to datafile copy "/u02/app/oracle/oradata/MEGA/datafile/o1_mf_undotbs1_lh152g7f_.dbf"
datafile 6 switched to datafile copy "/u02/app/oracle/oradata/MEGA/CD4BDC0E5E26A8F9E053D308A8C02EE6/datafile/o1_mf_undotbs1_lh152ptl_.dbf"
datafile 7 switched to datafile copy "/u02/app/oracle/oradata/MEGA/datafile/o1_mf_users_lh15331k_.dbf"
datafile 94 switched to datafile copy "/u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_system_lh1539dk_.dbf"
datafile 95 switched to datafile copy "/u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_sysaux_lh153oj4_.dbf"
datafile 96 switched to datafile copy "/u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_undotbs1_lh1549n6_.dbf"
datafile 97 switched to datafile copy "/u02/app/oracle/oradata/MEGA/EFA6895F1F3C29B0E053D508A8C0A6F8/datafile/o1_mf_users_lh154hto_.dbf"

ALL datafiles need to be switched successfully.

The switch datafile to copy is needed only in case your datafiles are OMF or if you have different path on primary and on standby server. If you need to switch only some datafiles to copy, you can generate individual commands by running following SQL on standby:

SQL> select 'switch datafile '||file#||' to copy;' from v$datafile_header where ERROR is not NULL;

before running next step, following SQL on standby must have ERROR column empty:

SQL> select file#,STATUS,ERROR from v$datafile_header;

7. Apply incremental backup on standby:

[oracle@czlin0212 oracle]$ rman target /
 
recover database from tag 'DBV_SYNC';

8. Synchronize archivelogs which were created in the meantime from your primary server by running following command:

[oracle@czlin0212 oracle]$ cd standbymp/oracle
$ ./dbvctl -d <DDC> -R

9. Apply archivelogs on standby by running on standby:

[oracle@czlin0212 oracle]$ cd standbymp/oracle
$ ./dbvctl -d <DDC>

10. Enable automated standby update as per: Miscellaneous Oracle Functions#1.-Scheduling-archive-log-send-and-apply

Have more questions? Submit a request

Comments