Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV

Previously we covered How to open Manual Physical Standby Database in READ ONLY mode.

Let us discuss one scenario and then you will realize the use of Incremental backup from SCN.

Scenario:

Consider the production database configured with Manual data guard, and unfortunately any archive log file gets corrupted or lost. Here we solely depend on that corrupted archive log to continue database replication, in this case we can fix the problem with RMAN incremental backup from SCN. Input SCN from standby database current state and backup incremental from that SCN from Primary database, and recover standby database. By this method, we no more need that corrupted archive log file.

OR

There is huge archive log sequence gap between primary and standby database, and we need to recover it in one go.

Before taking Incremental backup from SCN, we need to perform some pre-requisites on primary and standby database server, are as follows:

First:

Check listener status, it should be running and listening to databases.

[oracle@PR ~]$ lsnrctl status

Second:

Check database mode and role, On primary site, database should be in READ WRITE mode and role should be PRIMARY.

On standby site, database should be in MOUNTED mode and role should be PHYSICAL STANDBY.

Steps to take incremental backup from SCN:

Step-I

On primary database, take standby controlfile backup:

SQL> alter database create standby controlfile as '/home/oracle/backup/standby_control.ctl';
Database altered.

Step-II

Get current SCN from Standby database:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 957835

Step-III

On primary database, take RMAN incremental backup from SCN, take SCN as an input from Step-II:

[oracle@PR ~]$ rman target /
RMAN> backup device type disk incremental from scn 957835 database format '/home/oracle/backup/Inc_backup_%U';
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
using channel ORA_DISK_1
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_06r5f6g7_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAY-16

Step-IV

Transfer backup to standby database server:

[oracle@DR backup]$ pwd
/home/oracle/backup
[oracle@DR backup]$ ll
total 59728
-rw-r--r--. 1 oracle oinstall 41615360 May 13 04:58 Inc_backup_05r5f6en_1_1
-rw-r--r--. 1 oracle oinstall 9797632 May 13 04:58 Inc_backup_06r5f6g7_1_1
-rw-r--r--. 1 oracle oinstall 9748480 May 13 04:36 standby_control.ctl

Step-V

On Standby database, Restore Incremental backup:

one:

Shutdown standby database and startup in nomount mode:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 448793376 bytes
Database Buffers 301989888 bytes
Redo Buffers 2768896 bytes

Two:

Restore standby controlfile:

[oracle@DR ~]$ rman target /
RMAN> restore controlfile from '/home/oracle/backup/standby_control.ctl';
Starting restore at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/RTS/control01.ctl
output file name=/u01/app/oracle/oradata/RTS/control02.ctl
Finished restore at 13-MAY-16

Three:

Alter database to MOUNT mode:

SQL> alter database mount;
Database altered.

Four:

Catalog RMAN incremental backup files to RMAN repository(In my case, i.e. controlfile):

[oracle@DR ~]$ rman target /
RMAN> catalog start with '/home/oracle/backup/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/backup/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_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: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1

Five:

Recover standby database:

RMAN> recover database;
Starting recover at 13-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
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/RTS/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/RTS/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/RTS/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Inc_backup_05r5f6en_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
unable to find archived log
archived log thread=1 sequence=0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2016 05:13:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 972205

Above recovery successful and expecting next archive log and starting SCN 972205, which is greater than old current SCN of standby database.( Ref: Step-II )
Ensure archive log gap sequence between primary and standby database, it should be zero or one or two, because logfiles might have switched due to heavy load on your production primary database.

Cheers!! Standby database successfully recovered from incremental backup. Now onward you can continue replication with new archive log files.

 

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on failover(Activate) of standby database.

Thank you 🙂

Leave a Reply

%d bloggers like this: