Recovering Physical Standby using Incremental backup of Primary
===============================================================
Scenario :- Physical standby went out of sync from the primary as some of the archive logs are missing,
even those archivelog is deleted in the primary as well and no backup is available. To overcome this
we can take the incremental backup from primary and apply to the standby to keep it is in sync.
Primary DB
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2923116
SQL> select max(sequence#) from V$archived_log where applied = 'YES' and dest_id = 2;
MAX(SEQUENCE#)
--------------
91
Standby DB
----------
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2921713
SQL> select process, STATUS, sequence# from V$managed_standby where process like '%MRP%';
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_GAP 92
SQL> SELECT * FROM v$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 92 113
--
As per the above we could see that there is a gap between 93 and 113 sequence which is missing on the standby site,
the current scn in standby site is 2921713, around 20 archive logs are missing also it is not availble in backup
in the primary site. Lets take the incremental backup from the primary site.
RMAN> run
{
allocate channel a1 device type disk format '/oracle/scn_incre_bkp_%U_%T.bkp';
backup incremental from scn 2921713 database;
}
using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=34 device type=DISK
Starting backup at 28-JAN-15
backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:05
backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
including current control file in backup set
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15
released channel: a1
-- Take the current controlfile trace for standby.
SQL> alter database create standby controlfile as '/oracle/for_standby_ctl.ctl';
Database altered.
--
scp the controlfile and the backup piece to the standby location.
[oracle@rhel11gr2rac1 oracle]$ scp *.bkp oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
scn_incre_bkp_13ptrh7n_1_1_20150128.bkp 100% 2552KB 2.5MB/s 00:00
scn_incre_bkp_14ptrhbl_1_1_20150128.bkp 100% 9792KB 9.6MB/s 00:01
[oracle@rhel11gr2rac1 oracle]$ scp for_standby_ctl.ctl oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
for_standby_ctl.ctl 100% 9744KB 9.5MB/s 00:01
[oracle@rhel11gr2rac1 oracle]$
On standby, stop the MRP process , shut down db and start with the controlfile which been copied from the primary site.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------------------------------------------------------------------------
control_files string /u01/app/oracle/oradata/source/control01.ctl, /u01/app/oracle/fast_recovery_area/source/control02.ctl
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
-- Overwrite the old controlfiles with the new one.
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl
[oracle@standalone2 bkp]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 28 03:04:45 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 226492696 bytes
Database Buffers 79691776 bytes
Redo Buffers 4747264 bytes
-- catalog the backup piece
RMAN> catalog start with '/u01/app/oracle/bkp/scn';
Starting implicit crosscheck backup at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 28-JAN-15
Starting implicit crosscheck copy at 28-JAN-15
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 28-JAN-15
searching for all files in the recovery area
cataloging files... files cataloged
searching for all files that match the pattern /u01/app/oracle/bkp/scn
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
-- Start the recover.
RMAN> recover database;
Starting recover at 28-JAN-15
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/source/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/source/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/source/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/source/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
starting media recovery
archived log for thread 1 with sequence 116 is already on disk as file /u01/app/oracle/archive/1_116_868359284.dbf
archived log file name=/u01/app/oracle/archive/1_116_868359284.dbf thread=1 sequence=116
unable to find archived log
archived log thread=1 sequence=117
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 03:07:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 117 and starting SCN of 2928165
-- Now the restore is completed.
-- Lets check the scn now.
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2928164
-- Now start the MRP process, both primary and standby are in sync now.
===============================================================
Scenario :- Physical standby went out of sync from the primary as some of the archive logs are missing,
even those archivelog is deleted in the primary as well and no backup is available. To overcome this
we can take the incremental backup from primary and apply to the standby to keep it is in sync.
Primary DB
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2923116
SQL> select max(sequence#) from V$archived_log where applied = 'YES' and dest_id = 2;
MAX(SEQUENCE#)
--------------
91
Standby DB
----------
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2921713
SQL> select process, STATUS, sequence# from V$managed_standby where process like '%MRP%';
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_GAP 92
SQL> SELECT * FROM v$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 92 113
--
As per the above we could see that there is a gap between 93 and 113 sequence which is missing on the standby site,
the current scn in standby site is 2921713, around 20 archive logs are missing also it is not availble in backup
in the primary site. Lets take the incremental backup from the primary site.
RMAN> run
{
allocate channel a1 device type disk format '/oracle/scn_incre_bkp_%U_%T.bkp';
backup incremental from scn 2921713 database;
}
using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=34 device type=DISK
Starting backup at 28-JAN-15
backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/source/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/source/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/source/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/source/test_tbls_01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/source/users01.dbf
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:02:05
backup will be obsolete on date 04-FEB-15
archived logs will not be kept or backed up
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
including current control file in backup set
channel a1: starting piece 1 at 28-JAN-15
channel a1: finished piece 1 at 28-JAN-15
piece handle=/oracle/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp tag=TAG20150128T102454 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15
released channel: a1
-- Take the current controlfile trace for standby.
SQL> alter database create standby controlfile as '/oracle/for_standby_ctl.ctl';
Database altered.
--
scp the controlfile and the backup piece to the standby location.
[oracle@rhel11gr2rac1 oracle]$ scp *.bkp oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
scn_incre_bkp_13ptrh7n_1_1_20150128.bkp 100% 2552KB 2.5MB/s 00:00
scn_incre_bkp_14ptrhbl_1_1_20150128.bkp 100% 9792KB 9.6MB/s 00:01
[oracle@rhel11gr2rac1 oracle]$ scp for_standby_ctl.ctl oracle@standalone2.manzoor.com:/u01/app/oracle/bkp/
oracle@standalone2.manzoor.com's password:
for_standby_ctl.ctl 100% 9744KB 9.5MB/s 00:01
[oracle@rhel11gr2rac1 oracle]$
On standby, stop the MRP process , shut down db and start with the controlfile which been copied from the primary site.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------------------------------------------------------------------------------
control_files string /u01/app/oracle/oradata/source/control01.ctl, /u01/app/oracle/fast_recovery_area/source/control02.ctl
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
-- Overwrite the old controlfiles with the new one.
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/oradata/source/control01.ctl
[oracle@standalone2 bkp]$ cp for_standby_ctl.ctl /u01/app/oracle/fast_recovery_area/source/control02.ctl
[oracle@standalone2 bkp]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 28 03:04:45 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 313159680 bytes
Fixed Size 2227944 bytes
Variable Size 226492696 bytes
Database Buffers 79691776 bytes
Redo Buffers 4747264 bytes
-- catalog the backup piece
RMAN> catalog start with '/u01/app/oracle/bkp/scn';
Starting implicit crosscheck backup at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 28-JAN-15
Starting implicit crosscheck copy at 28-JAN-15
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 28-JAN-15
searching for all files in the recovery area
cataloging files... files cataloged
searching for all files that match the pattern /u01/app/oracle/bkp/scn
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/bkp/scn_incre_bkp_14ptrhbl_1_1_20150128.bkp
File Name: /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
-- Start the recover.
RMAN> recover database;
Starting recover at 28-JAN-15
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/source/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/source/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/source/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/source/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/source/test_tbls_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/bkp/scn_incre_bkp_13ptrh7n_1_1_20150128.bkp tag=TAG20150128T102454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
starting media recovery
archived log for thread 1 with sequence 116 is already on disk as file /u01/app/oracle/archive/1_116_868359284.dbf
archived log file name=/u01/app/oracle/archive/1_116_868359284.dbf thread=1 sequence=116
unable to find archived log
archived log thread=1 sequence=117
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 03:07:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 117 and starting SCN of 2928165
-- Now the restore is completed.
-- Lets check the scn now.
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
2928164
-- Now start the MRP process, both primary and standby are in sync now.
No comments:
Post a Comment