How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database

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.