Data guard GAP Detection and Resolution
=============================================
How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database
Resyncing the standby data using RMAN incremental backup taken from the primary database.
Manually Resolving a Gap using incremental backup
On your physical standby database:
SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 32 64
SQL>
Get the current scn of the standby database.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1501331
SQL>
On Primary database
******************************************
Connect to the primary database as the RMAN target and create an incremental backup from the current SCN (for a standby lagging far behind the primary):
----------------------------------------------------------------------------------------------
backup incremental from scn 1501331 database FORMAT '/u01/app/inc_backup/inc_bkp_%U';
[oracle@pri inc_backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 11 09:12:12 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: HR (DBID=3602606687)
RMAN> backup incremental from scn 1501331 database FORMAT '/u01/app/inc_backup/inc_bkp_%U';
Starting backup at 11-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/hr/test01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/hr/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/hr/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/hr/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/hr/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/hr/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-SEP-17
channel ORA_DISK_1: finished piece 1 at 11-SEP-17
piece handle=/u01/app/inc_backup/inc_bkp_16se66jg_1_1 tag=TAG20170911T091216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
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 11-SEP-17
channel ORA_DISK_1: finished piece 1 at 11-SEP-17
piece handle=/u01/app/inc_backup/inc_bkp_17se66o2_1_1 tag=TAG20170911T091216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-SEP-17
RMAN>
Create a standby controlfile
------------------------------------------
alter database create standby controlfile as '/u01/app/inc_backup/CONTROL01.CTL';
[oracle@pri inc_backup]$ sqlp
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 11 09:16:44 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
HR READ WRITE
SQL>
SQL> alter database create standby controlfile as '/u01/app/inc_backup/CONTROL01.CTL';
Database altered.
[oracle@pri inc_backup]$ ls -lrt
total 92504
-rw-r----- 1 oracle oinstall 74719232 Sep 11 09:14 inc_bkp_16se66jg_1_1
-rw-r----- 1 oracle oinstall 10027008 Sep 11 09:14 inc_bkp_17se66o2_1_1
-rw-r----- 1 oracle oinstall 9977856 Sep 11 09:17 CONTROL01.CTL
[oracle@pri inc_backup]$
Transfer all the backup pieces,control file backup created on the primary to the standby.
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$ scp * oracle@stby:/u01/app/inc_backup/
oracle@stby's password:
CONTROL01.CTL 100% 9744KB 9.5MB/s 00:00
inc_bkp_16se66jg_1_1 100% 71MB 35.6MB/s 00:02
inc_bkp_17se66o2_1_1 100% 9792KB 9.6MB/s 00:01
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$
[oracle@pri inc_backup]$
Now on Standby database:
******************************************
verify backup which transfered from pramiry
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ ls -lrt
total 92504
-rw-r-----. 1 oracle oinstall 9977856 Sep 7 02:56 CONTROL01.CTL
-rw-r-----. 1 oracle oinstall 74719232 Sep 7 02:56 inc_bkp_16se66jg_1_1
-rw-r-----. 1 oracle oinstall 10027008 Sep 7 02:56 inc_bkp_17se66o2_1_1
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$
Recreate the standby controlfile on the standby
--------------------------------------------------
shut database
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ sqlp
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 7 02:58:38 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut abort
ORACLE instance shut down.
SQL>
open database into nomount mode
----------------------------------------
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ sqlp
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 7 02:59:02 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2252824 bytes
Variable Size 788533224 bytes
Database Buffers 452984832 bytes
Redo Buffers 8892416 bytes
SQL>
connect to the rman prompt and connect to the target database
[oracle@stby inc_backup]$
[oracle@stby inc_backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 7 03:00:20 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: HR (not mounted)
RMAN>
restore the controlfile using the below command.
--------------------------------------------------------
restore controlfile from '/u01/app/inc_backup/CONTROL01.CTL';
RMAN> restore controlfile from '/u01/app/inc_backup/CONTROL01.CTL';
Starting restore at 07-SEP-17
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/hrsty/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/hrsty/control02.ctl
Finished restore at 07-SEP-17
RMAN>
RMAN>
Mount the standby database.
---------------------------------------
SQL>
SQL> alter database mount;
Database altered.
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/hrsty/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/hrsty/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/hrsty/redo01.log NO
4 STANDBY /u01/app/oracle/oradata/hrsty/stby_redo01.log NO
5 STANDBY /u01/app/oracle/oradata/hrsty/stby_redo02.log NO
6 STANDBY /u01/app/oracle/oradata/hrsty/stby_redo03.log NO
7 STANDBY /u01/app/oracle/oradata/hrsty/stby_redo04.log NO
7 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/hrsty/system01.dbf
/u01/app/oracle/oradata/hrsty/sysaux01.dbf
/u01/app/oracle/oradata/hrsty/undotbs01.dbf
/u01/app/oracle/oradata/hrsty/users01.dbf
/u01/app/oracle/oradata/hrsty/example01.dbf
/u01/app/oracle/oradata/hrsty/test01.dbf
6 rows selected.
SQL>
catalog backup pieces if the location on the standby is different from the primary.
-----------------------------------------------------------------------------------------------
catalog start with '/u01/app/inc_backup/';
[oracle@stby inc_backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 7 03:09:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: HR (DBID=3602606687, not open)
RMAN> catalog start with '/u01/app/inc_backup/';
Starting implicit crosscheck backup at 07-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 07-SEP-17
Starting implicit crosscheck copy at 07-SEP-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 07-SEP-17
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_07/o1_mf_1_87_dv0rb5m9_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_81_dtp4o6qo_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_69_dtp3t104_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_71_dtp3t1mg_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_75_dtp3t2p7_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_72_dtp3t1pq_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_79_dtp3x58l_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_68_dtp3t0wt_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_66_dtp3szgr_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_77_dtp3t3h5_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_70_dtp3t11d_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_74_dtp3t2gw_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_80_dtp4h979_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_67_dtp3szf6_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_73_dtp3t1sc_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_76_dtp3t2tt_.arc
File Name: /u01/app/oracle/fast_recovery_area/HRSTY/archivelog/2017_09_03/o1_mf_1_78_dtp3wbcl_.arc
searching for all files that match the pattern /u01/app/inc_backup/
List of Files Unknown to the Database
=====================================
File Name: /u01/app/inc_backup/inc_bkp_17se66o2_1_1
File Name: /u01/app/inc_backup/CONTROL01.CTL
File Name: /u01/app/inc_backup/inc_bkp_16se66jg_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: /u01/app/inc_backup/inc_bkp_17se66o2_1_1
File Name: /u01/app/inc_backup/CONTROL01.CTL
File Name: /u01/app/inc_backup/inc_bkp_16se66jg_1_1
RMAN>
Apply incremental backup to the standby.
------------------------------------------------------
recover database noredo;
RMAN> recover database noredo;
Starting recover at 07-SEP-17
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/hrsty/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/hrsty/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/hrsty/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/hrsty/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/hrsty/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/hrsty/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/inc_backup/inc_bkp_16se66jg_1_1
channel ORA_DISK_1: piece handle=/u01/app/inc_backup/inc_bkp_16se66jg_1_1 tag=TAG20170911T091216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished recover at 07-SEP-17
RMAN>
Start the media recovery process in standby database
--------------------------------------------------------------------
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
alter database recover managed standby database disconnect from session;
SQL> select name,open_mode from gv$database
2 /
NAME OPEN_MODE
--------- --------------------
HR MOUNTED
SQL>
SQL> alter database open ;
Database altered.
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
HR READ ONLY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL> select name,open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
HR READ ONLY WITH APPLY
SQL>
on primary
------------------
[oracle@pri inc_backup]$ sqlp
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 11 09:40:23 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 89
Next log sequence to archive 91
Current log sequence 91
SQL>
on standby
--------------------
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 89
Next log sequence to archive 0
Current log sequence 91
SQL>
SQL>
SQL> select sequence#,archived,applied from v$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
66 YES YES
67 YES YES
68 YES YES
69 YES YES
70 YES YES
71 YES YES
72 YES YES
73 YES YES
74 YES YES
75 YES YES
76 YES YES
77 YES YES
78 YES YES
79 YES YES
80 YES YES
81 YES YES
87 YES YES
88 YES YES
89 YES YES
90 YES IN-MEMORY
SQL> SELECT * FROM V$ARCHIVE_GAP;
no rows selected
SQL>
*********************************************************************
Now both primary and standby database in sync mode
*********************************************************************
==================================================================
Use the RMAN CATALOG command to register the backup sets in the RMAN repository at the duplicate. With an RMAN client connected to the standby database and the recovery catalog (if you use one at the standby), mount the standby and run the following command:
RMAN> CATALOG START WITH '/backup_location/';
The backups are now available for use in recovery of the standby.
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. With an RMAN client connected to the standby database, run the following command:
RMAN> RECOVER DATABASE NOREDO;
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.
