Resolving Archive GAP
Recover standby database using
incremental backup
Today, I going to show how to resolve Archive
GAP during network failure in our environment.
Here I have manually simulate the scenario.
BRIEF IMPLEMENTATION SUMMARY
- · Missing archive redo logs on standby site due to Network failure
- · Create standby controlfile on primary
- · Create incremental backup on primary from SCN (Standby SCN)
- · SCP to standby location
- · Here we have different file system for datafile on both server then we rename datafile before recovery
- · Start Managed recovery on standby site.
mp.sql => select PROCESS,STATUS, THREAD#,SEQUENCE#,
BLOCK#, BLOCKS from v$managed_standby ;
open.sql=> select name, instance_name, open_mode, database_role,
flashback_on , current_scn from v$database,v$instance
assl.sql=> alter system switch logfile ;
SQL> @mp
PROCESS
STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
--------- ------------ ---------- ----------
---------- ----------
ARCH
CLOSING 1 24 1 106
ARCH
CLOSING 1 25 1 41
ARCH
CONNECTED 0
0 0 0
ARCH
CLOSING 1 23 1 23
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 1 26 5 1
MRP0
APPLYING_LOG 1 26 5
204800
SQL> @open
NAME INSTANCE_NAME OPEN_MODE DATABASE_ROLE FBK_ON
CURRENT_SCN
----- ------------- --------------------
---------------- ------- ----
PROD STBY
READ ONLY WITH APPLY PHYSICAL
STANDBY NO 1115459
Connect with broker check and simulate the scenario
DGMGRL>
SHOW DATABASE PROD ;
Database - prod
Role: PRIMARY
Intended
State: TRANSPORT-ON
Instance(s):
PROD
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL>
SHOW DATABASE STBY
Database - stby
Role: PHYSICAL STANDBY
Intended
State: APPLY-ON
Transport
Lag: 0 seconds
Apply
Lag: 0 seconds
Real Time
Query: ON
Instance(s):
STBY
Database Status:
SUCCESS
Manually Simulate scenario for network failure
between primary and standby
DGMGRL>
edit database prod set state='Transport-off' ;
Succeeded.
DGMGRL>
show database prod ;
Database - prod
Role: PRIMARY
Intended
State: TRANSPORT-OFF
Instance(s):
PROD
Database Status:
SUCCESS
Checking Primary database status
SQL> @open
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
--------- ---------------- ------------
---------------- ------------------ -----------
PROD
PROD READ WRITE PRIMARY YES 1115277
SQL> @assl
System altered.
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
--------- ---------------- --------------------
---------------- ------------ ----
PROD
PROD READ WRITE PRIMARY YES
1115532
SQL> select
max(sequence#) from v$archived_log ;
MAX(SEQUENCE#)
--------------
31
Deleting/Moving failed archivelog on primary
vm224:PROD>
mv o1_mf_1_27_c009bt20_.arc /Shared_NAS/BACKUP/
vm224:PROD>
mv o1_mf_1_28_c009bwqj_.arc /Shared_NAS/BACKUP/
vm224:PROD>
mv o1_mf_1_30_c009cb5r_.arc /Shared_NAS/BACKUP/
SQL> @assl
System altered.
SQL> /
System altered.
SQL>
SQL>
SQL>
SQL> @open
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
--------- ---------------- --------------------
---------------- ------------------ -----------
PROD
PROD READ WRITE PRIMARY YES 1120665
Re-enable transport on standby site with broker
DGMGRL>
edit database PROD SET STATE='Transport-on' ;
Succeeded.
DGMGRL> show database prod
Database - prod
Role: PRIMARY
Intended
State: TRANSPORT-ON
Instance(s):
PROD
Database
Error(s):
ORA-16783:
cannot resolve gap for database stby
Database Status:
ERROR
DGMGRL> show database stby
Database - stby
Role: PHYSICAL STANDBY
Intended
State: APPLY-ON
Transport
Lag: 34 minutes 9 seconds
Apply
Lag: 34 minutes 9 seconds
Real Time
Query: ON
Instance(s):
STBY
Database Status:
SUCCESS
Now Creating standby Controlfile
SQL> alter
database create standby controlfile as '/Shared_NAS/BACKUP/controlPROD.ctl' ;
Database altered.
Moving to Standby and Checking Current SCN
select
current_scn from v$database ;
CURRENT_SCN
-----------
1117351
Now Taking incremental Backup from Primary Database
vm224:PROD>
rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on
Mon Sep 21 21:50:16 2015
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
connected to target database: PROD (DBID=302858279)
RMAN> run
2> {
3>
configure device type disc parallelism 4
;
4> backup
incremental from scn 1117351 as compressed backupset database
tag='INCR_PROD_STBY' format '/Shared_NAS/BACKUP/PROD_INCR_%U' ;
5> }
using target database control file instead of
recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'DISC' PARALLELISM 4 BACKUP
TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully
stored
Starting backup at 21-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
backup will be obsolete on date 28-SEP-15
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full
datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup
set
input datafile file number=00001
name=/u01/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002
name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00005
name=/u01/app/oracle/oradata/PROD/example01.dbf
input datafile file number=00003
name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00004
name=/u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-SEP-15
channel ORA_DISK_1: finished piece 1 at 21-SEP-15
piece handle=/Shared_NAS/BACKUP/PROD_INCR_07qho7u6_1_1
tag=INCR_PROD_STBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:25
using channel ORA_DISK_1
backup will be obsolete on date 28-SEP-15
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed 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 21-SEP-15
channel ORA_DISK_1: finished piece 1 at 21-SEP-15
piece handle=/Shared_NAS/BACKUP/PROD_INCR_08qho7uv_1_1
tag=INCR_PROD_STBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:01
Finished backup at 21-SEP-15
Since Primary and Standby datafile resides on
different file system then we need to generate the rename datafile script as I
have created standby controlfile from primary database where datafile mount point
are different.
alter database rename file '/u01/app/oracle/oradata/PROD/users01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_users_06qhntk4_.dbf' ;
alter database rename file
'/u01/app/oracle/oradata/PROD/undotbs01.dbf'
to '/u01/app/ORADATA/STBY/datafile/o1_mf_undotbs1_05qhntjt_.dbf' ;
alter database rename file '/u01/app/oracle/oradata/PROD/sysaux01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_sysaux_03qhntij_.dbf' ;
alter database rename file
'/u01/app/oracle/oradata/PROD/system01.dbf'
to '/u01/app/ORADATA/STBY/datafile/o1_mf_system_02qhnthq_.dbf' ;
alter database rename file
'/u01/app/oracle/oradata/PROD/example01.dbf'
to '/u01/app/ORADATA/STBY/datafile/o1_mf_example_04qhntje_.dbf' ;
ON STANDBY DATABSE
SQL>
RECOVER MANAGED STANDBY DATABASE CANCEL ;
SQL> SHUT
IMMEDIATE ;
Restoring newly create controlfile from backup
location to standby controlfile path
vm225:STBY>
mv o1_mf_c001bldt_.ctl o1_mf_c001bldt_.ctl_old
vm225:STBY>
cp /Shared_NAS/BACKUP/controlPROD.ctl
/u01/app/ORADATA/STBY/controlfile/o1_mf_c001bldt_.ctl
Catalog Incremental backup
RMAN>
catalog start with '/Shared_NAS/BACKUP' ;
using target database control file instead of
recovery catalog
searching for all files that match the pattern /Shared_NAS/BACKUP
List of Files Unknown to the Database
=====================================
File Name: /Shared_NAS/BACKUP/PROD_INCR_08qho7uv_1_1
File Name: /Shared_NAS/BACKUP/PROD_INCR_07qho7u6_1_1
File Name: /Shared_NAS/BACKUP/controlPROD.ctl
Do you really want to catalog the above files (enter
YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /Shared_NAS/BACKUP/PROD_INCR_08qho7uv_1_1
File Name: /Shared_NAS/BACKUP/PROD_INCR_07qho7u6_1_1
File Name: /Shared_NAS/BACKUP/controlPROD.ctl
Since our primary and standby database both are
different location recover will not be possible until we rename the datafile in
newly created controlfile.
vm225:STBY> rman target /
RMAN>
recover database noredo ;
Starting recover at 21-SEP-15
using target database control file instead
of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device
type=DISK
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE
STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-03002: failure of recover command at
09/21/2015 22:04:47
RMAN-06094: datafile 1 must be restored
Recovery Manager complete.
Let's change the datafile name in controlfile
SQL> alter
database rename file '/u01/app/oracle/oradata/PROD/undotbs01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_undotbs1_05qhntjt_.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data
files
ORA-01275: Operation RENAME is not allowed
if standby file management is
automatic.
Since parameter standby_file_management is set to
auto. So it can't be possible to change
until STANDBY_FILE_MANAGEMENT is MANUAL.
Let's change the parameter value to MANUAL.
SQL> show
parameter standby_file_management
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
standby_file_management string AUTO
SQL> alter
system set standby_file_management=manual
;
System altered.
Renaming Datafile on standby site
SQL> alter
database rename file '/u01/app/oracle/oradata/PROD/users01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_users_06qhntk4_.dbf' ;
alter database
rename file '/u01/app/oracle/oradata/PROD/undotbs01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_undotbs1_05qhntjt_.dbf' ;
alter database
rename file '/u01/app/oracle/oradata/PROD/sysaux01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_sysaux_03qhntij_.dbf' ;
alter database
rename file '/u01/app/oracle/oradata/PROD/system01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_system_02qhnthq_.dbf' ;
alter database
rename file '/u01/app/oracle/oradata/PROD/example01.dbf' to
'/u01/app/ORADATA/STBY/datafile/o1_mf_example_04qhntje_.dbf' ;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
On Standby Site
vm225:STBY>
rman target /
RMAN> recover
database noredo ;
Starting recover at 21-SEP-15
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 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/ORADATA/STBY/datafile/o1_mf_system_02qhnthq_.dbf
destination for restore of datafile 00002:
/u01/app/ORADATA/STBY/datafile/o1_mf_sysaux_03qhntij_.dbf
destination for restore of datafile 00003:
/u01/app/ORADATA/STBY/datafile/o1_mf_undotbs1_05qhntjt_.dbf
destination for restore of datafile 00004:
/u01/app/ORADATA/STBY/datafile/o1_mf_users_06qhntk4_.dbf
destination for restore of datafile 00005:
/u01/app/ORADATA/STBY/datafile/o1_mf_example_04qhntje_.dbf
channel ORA_DISK_1: reading from backup piece /Shared_NAS/BACKUP/PROD_INCR_07qho7u6_1_1
channel ORA_DISK_1: piece handle=/Shared_NAS/BACKUP/PROD_INCR_07qho7u6_1_1
tag=INCR_PROD_STBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time:
00:00:01
Finished recover at 21-SEP-15
Checking and putting in managed recovery mode on standby
database
vm225:STBY> sqlplus / as sysdba
SQL> @mp
PROCESS
STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
--------- ------------ ---------- ----------
---------- ----------
ARCH
CONNECTED 0 0
0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 1 37 10 1
MRP0
APPLYING_LOG 1 36
74047 80376
8 rows selected.
Reset the standby_file_management value on standby
site.
SQL> show
parameter standby
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> alter system set
standby_file_management=AUTO ;
System altered.
Checking standby database status
SQL> @open
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON CURRENT_SCN
--------- ---------------- --------------------
---------------- ------------------ -----------
PROD
STBY READ ONLY WITH
APPLY PHYSICAL STANDBY NO
1134390
DGMGRL>
show configuration
Configuration - dg_con1
Protection
Mode: MaxPerformance
Databases:
prod -
Primary database
stby -
Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
ON PRIMARY
DEST_NAME ERROR STATUS
APPLIED_SCN VER
------------------------------
------------------------------ --------- ----------- ---
LOG_ARCHIVE_DEST_1 VALID 0 NO
LOG_ARCHIVE_DEST_2 VALID 1193313 NO
LOG_ARCHIVE_DEST_3 INACTIVE 0 NO
LOG_ARCHIVE_DEST_4 INACTIVE 0 NO
Hope this will help
No comments:
Post a Comment