Monthly Archives: November 2013

Using RMAN Incremental Backups to Refresh Standby Database

In this post , I will demonstrate synchronization of a standby DB from an incremental RMAN backup. It might be needed in following scenarios:

1-The standby database is considerably lagging behind the primary . The copying and applying of archive logs from primary will be time consuming as it will apply both the COMMITED and the NON COMMITED transactions then will ROLLBACK the non committed transactions. Incremental backup will recover the standby database much faster than applying the archives as it will apply only the COMMITED transactions on the standby database .

2-Some archivelogs on the primary which have not been applied to the standby have been lost. In such cases, you can create an incremental backup of the primary database containing changes since the standby database was last refreshed. This incremental backup can be applied to the standby database to synchronize it with a primary database.

Overview:

  • Stop redo transport on primary
  • Switch logs on primary – results in some archived logs on primary which have not been sent to standby
  • Rename newly generated archived logs on primary to simulate their loss
  • Restart redo transport – gives error as gap cannot be resolved due to missing logs
  • Create a control file for standby database on primary
  • Take incremental backup on primary starting from the SCN# of standby database
  • Copy the incremental backup to the standby host and catalog it with RMAN
  • Mount the standby database with newly created standby control file
  • Cancel managed recovery of standby database and apply incremental backup to the standby database
  • Start managed recovery of standby database

Implementation:

– Check current log sequence on primary

PRI>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

- check that all the archived logs prior to the current log have been sent to standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
41

– stop redo transport from primary (dg01)

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Status:
SUCCESS

DGMGRL> edit database dg01 set state='Transport-off';
Succeeded.

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-OFF
Instance(s):
dg01

Database Status:
SUCCESS

– switch log on primary

PRI>alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     44
Next log sequence to archive   45
Current log sequence           46

– check that logs after sequence# 42 are not being transported to sby

SBY> select max(sequence#) from v$archived_log
MAX(SEQUENCE#)
--------------
42

– Find out names of archived logs generated on primary which have not been transported to standby

PRI>set line 500
col name for a40
select sequence#, name from v$archived_log where sequence# > 42;

SEQUENCE# NAME
---------- ----------------------------------------
43 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_43_9780s1
ch_.arc

44 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_44_9780tl
t0_.arc

45 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_45_9780tl
vo_.arc

– To simulate loss of archived logs on primary, rename them

PRI>ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_43_9780s1ch_.arc  /home/oracle/arch_43.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_44_9780tlt0_.arc /home/oracle/arch_44.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_45_9780tlvo_.arc /home/oracle/arch_45.arc

– Restart redo transport

DGMGRL>  edit database dg01 set state='Transport-on';

– check that gap in redo logs on standby cannot be resolved as some logs are missing on primary

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Error(s):
 ORA-16783: cannot resolve gap for database dg02

Database Status:
ERROR

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
Error: ORA-16724: cannot resolve gap for one or more standby databases

dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– check current scn# of standby

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
998647

– Create a standby control file:

PRI>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby_control.ctl';

– copy the standby control file to standby host

PRI> !scp /home/oracle/standby_control.ctl node2:/home/oracle/standby_control.ctl

– Take incremental backup on primary starting from scn# of standby database
RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.
Note:
• RMAN does not consider the incremental backup as part of a backup strategy at the source database. The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database.

RMAN> Backup incremental from SCN 987005 database tag='FOR_STANDBY' format '/home/oracle/%d_%t_%s_%p';

Starting backup at 02-NOV-13

channel ORA_DISK_1: starting full datafile backup set
...
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

– Move the backup pieces to the standby host

PRI> !scp /home/oracle/DG01_830395300_22_1 node2:/home/oracle/DG01_830395300_22_1

– Catalog the Incremental Backup Files at the Standby Database

RMAN>  catalog backuppiece  '/home/oracle/DG01_830395300_22_1';

list backup tag FOR_STANDBY;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Incr    9.38M      DISK        00:00:00     02-NOV-13
BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: FOR_STANDBY
Piece Name: /home/oracle/DG01_830395300_22_1
Keep: NOLOGS             Until: 09-NOV-13

List of Datafiles in backup set 9

– Find out names of current control files

SQL>  col value for a50
col name for a15
select name, value   from v$parameter where upper(name)= 'CONTROL_FILES';

NAME            VALUE
--------------- --------------------------------------------------
control_files   /u01/app/oracle/oradata/dg02/control01.ctl, /u01/a
pp/oracle/flash_recovery_area/dg02/control02.ctl

- Shutdown the standby database and rename the original control file of the standby database:

SBY> Shu immediate;
!mv /u01/app/oracle/oradata/dg02/control01.ctl /u01/app/oracle/oradata/dg02/control01.bak
!mv /u01/app/oracle/flash_recovery_area/dg02/control02.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.bak

– Restore the standby control file we just copied from the primary

SBY>    ! cp /home/oracle/standby_control.ctl /u01/app/oracle/oradata/dg02/control01.ctl
! cp /home/oracle/standby_control.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.ctl

– Startup the Standby database with the new controlfile:

SBY> startup mount;

– Apply the Incremental Backup to the Standby Database
Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database.

SBY> recover managed standby database cancel;
RMAN> RECOVER DATABASE from tag for_standby NOREDO;

Starting recover at 02-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/dg02/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/dg02/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/dg02/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/dg02/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/dg02/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/DG01_830395300_22_1
channel ORA_DISK_1: piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 02-NOV-13

Now your standby database is refreshed from the incremental backup.
You can start the Managed Recovery process on the standby DB:

SBY>recover managed standby database disconnect;
Media recovery complete.

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.

— check that SCN# of the standby database has advanced.

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
1005729

DGMGRL> edit database dg01 set state='transport-on';
DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:
This technique cannot be used to update a duplicate database.

References :

http://dba-tips.blogspot.in/2011/10/refresh-standby-database-from-rman.html

http://www.stanford.edu/dept/itss/docs/oracle/10gR2/backup.102/b14191/rcmdupdb008.htm

———————————————————————————————–

Related Links:

Home
11g Dataguard Index