1. Verify GAP

2. Stop Redo Transfer (On Primary)

3. Find current SCN from Standby

4. Take RMAN Incremental from SCN (Primary)

5. Create Standby Control file

6. Transfer Backup to standby

7. List Control file location on standby

8. Shutdown Standby

9. Replace the controlfile from backup

10. Mount Standby Database

11. Catalog backuppiece

12. Cancel MRP

13. Recover standby

14. Enable MRP

15. Enable log_archive_dest_state_2 on primary

16. Switch logfile (On Primary)

17. Verify sync

0. Issue Description

1. Resolve Huge GAP between Primary and Standby

2. Resolve GAP when archivelogs Missing/corrupted.

Environment

PRIMARY: (ERPDB)

Platform          : Linuxx86_64
Server Name       : HOST1.DOMAIN.COM, IP: 192.168.1.11
DB Version        : Oracle 19.3.0.0.0
File system	  : Normal
DB Name           : ERPDB
DB_UNIQUE_NAME	  : ERPDB
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/19.3.0.0.0

STANDBY: (ERPDB_DG)

		
Platform          : Linuxx86_64
Server Name       : HOST2.DOMAIN.COM, IP: 192.168.1.12
DB Version        : Oracle 19.3.0.0.0
File system       : Normal
DB Name           : ERPDB
DB_UNIQUE_NAME	  : ERPDB_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/19.3.0.0.0

1. Verify GAP (On Standby)

SQL> select name,open_mode,database_role from v$database;

In RAC

SELECT ARCH.THREAD# Thread, ARCH.SEQUENCE# Last_Sequence_Received,
APPL.SEQUENCE# Last_Sequence_Applied, (ARCH.SEQUENCE#-APPL.SEQUENCE#) Difference
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

2. Stop Redo Transfer (On Primary)

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL>

SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

SQL>

3. Find current SCN from Standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    22530075

SQL>

4. Take RMAN Incremental from SCN (On Primary)

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.3.0.0.0 - Production on Thu Jan 24 04:17:33 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DELL (DBID=3965088591)

RMAN>

RMAN> run
 {
 allocate channel t1 type disk;
 allocate channel t2 type disk;
 allocate channel t3 type disk;
 backup incremental from SCN 22530075 database tag='STANDBY_TODAY_REFRESH' format '/u02/oracle/backup/ERPDB_BACKUP/database_%d_%u_%s';
 release channel t1;
 release channel t2;
 release channel t3;
 }

5. Create Standby Control file

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ERPDB      READ WRITE           PRIMARY

SQL>

SQL> alter database create standby controlfile as '/u02/oracle/backup/ERPDB_BACKUP/std_control.ctl';

Database altered.

SQL>

6. Transfer Backup to standby

[oracle@host1 ERPDB_BACKUP]$ scp * oracle@host2:/u02/oracle/backup/ERPDB_DG

7. List Control file location on standby

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u02/oracle/oradata/ERPDB_DG/co
                                                 ntrol01.ctl, /u02/oracle/orada
                                                 ta/fast_recovery_area/ERPDB_DG/
                                                 control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

8. Shutdown Standby

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ERPDB      MOUNTED              PHYSICAL STANDBY

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

9. Replace the controlfile from backup

[oracle@host2 ERPDB_DG]$ cp std_control.ctl /u02/oracle/oradata/ERPDB_DG/control01.ctl
[oracle@host2 ERPDB_DG]$ cp std_control.ctl /u02/oracle/oradata/fast_recovery_area/ERPDB_DG/control02.ctl

10. Mount Standby Database

[oracle@host2 ERPDB_DG]$ sqlplus / as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Thu Jan 24 04:28:58 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted.
SQL> 

11. Catalog backuppiece (On Standby)

[oracle@rac2 DELL_DG]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:29:48 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DELL (DBID=3965088591, not open)

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29';

Starting implicit crosscheck backup at 24-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 24-JAN-19

Starting implicit crosscheck copy at 24-JAN-19
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 24-JAN-19

searching for all files in the recovery area
cataloging files...
no files cataloged

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29 RECID=16 STAMP=998368237

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30 RECID=17 STAMP=998368262

RMAN> catalog backuppiece '/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31';

cataloged backup piece
backup piece handle=/u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31 RECID=18 STAMP=998368289

RMAN> list backup tag STANDBY_TODAY_REFRESH;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Incr    224.00K    DISK        00:00:00     24-JAN-19
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/system01.dbf
  4       Incr 1431069    24-JAN-19 /u02/oracle/oradata/DELL_DG/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Incr    728.00K    DISK        00:00:00     24-JAN-19
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/sysaux01.dbf
  3       Incr 1431070    24-JAN-19 /u02/oracle/oradata/DELL_DG/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Incr    9.95M      DISK        00:00:00     24-JAN-19
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_DELL_0vto3o79_31
  Control File Included: Ckp SCN: 1431069      Ckp time: 24-JAN-19

RMAN> exit


Recovery Manager complete.
[oracle@rac2 DELL_DG]$

11. Catalog backuppiece (On Standby)

[oracle@host2 DELL_DG]$ rman target /

Recovery Manager: Release 19.3.0.0.0 - Production on Thu Jan 24 04:29:48 2022

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ERPDB (DBID=3965088591, not open)

RMAN> catalog backuppiece '/u02/oracle/backup/ERPDB_DG/database_ERPDB_0tto3o79_29';

Starting implicit crosscheck backup at 24-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 24-JAN-22

Starting implicit crosscheck copy at 24-JAN-22
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 24-JAN-229

searching for all files in the recovery area
cataloging files...
no files cataloged

cataloged backup piece
backup piece handle=/u02/oracle/backup/ERPDB_DG/database_ERPDB_0tto3o79_29 RECID=16 STAMP=998368237

RMAN> catalog backuppiece '/u02/oracle/backup/ERPDB_DG/database_ERPDB_0uto3o79_30';

cataloged backup piece
backup piece handle=/u02/oracle/backup/ERPDB_DG/database_ERPDB_0uto3o79_30 RECID=17 STAMP=998368262

RMAN> catalog backuppiece '/u02/oracle/backup/ERPDB_DG/database_ERPDB_0vto3o79_31';

cataloged backup piece
backup piece handle=/u02/oracle/backup/ERPDB_DG/database_ERPDB_0vto3o79_31 RECID=18 STAMP=998368289

RMAN> list backup tag STANDBY_TODAY_REFRESH;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Incr    224.00K    DISK        00:00:00     24-JAN-22
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_ERPDB_0tto3o79_29
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Incr 1431069    24-JAN-22 /u02/oracle/oradata/ERPDB_DG/system01.dbf
  4       Incr 1431069    24-JAN-22 /u02/oracle/oradata/ERPDB_DG/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17      Incr    728.00K    DISK        00:00:00     24-JAN-22
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/DELL_DG/database_ERPDB_0uto3o79_30
  List of Datafiles in backup set 17
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Incr 1431070    24-JAN-22 /u02/oracle/oradata/ERPDB_DG/sysaux01.dbf
  3       Incr 1431070    24-JAN-22 /u02/oracle/oradata/ERPDB_DG/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Incr    9.95M      DISK        00:00:00     24-JAN-22
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: STANDBY_TODAY_REFRESH
        Piece Name: /u02/oracle/backup/ERPDB_DG/database_ERPDB_0vto3o79_31
  Control File Included: Ckp SCN: 1431069      Ckp time: 24-JAN-22

RMAN> exit


Recovery Manager complete.
[oracle@rac2 DELL_DG]$

12. Cancel MRP

[oracle@host2 DERPDB_DG]$ sqlplus / as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Thu Jan 24 04:32:49 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 19.3.0.0.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>  recover managed standby database cancel;
Media recovery complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 19.3.0.0.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

[oracle@host2 ERPDB_DG]$

13. Recover standby

[oracle@host2 ERPDB_DG]$ rman target /

Recovery Manager: Release 19.3.0.0.0 - Production on Thu Jan 24 04:33:32 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DELL (DBID=3965088591, not open)

RMAN> RECOVER DATABASE FROM TAG STANDBY_TODAY_REFRESH NOREDO;

Starting recover at 24-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 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: /u02/oracle/oradata/ERPDB_DG/system01.dbf
destination for restore of datafile 00004: /u02/oracle/oradata/ERPDB_DG/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/backup/ERPDB_DG/database_DELL_0tto3o79_29
channel ORA_DISK_1: piece handle=/u02/oracle/backup/DELL_DG/database_ERPDB_0tto3o79_29 tag=STANDBY_TODAY_REFRESH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00002: /u02/oracle/oradata/ERPDB_DG/sysaux01.dbf
destination for restore of datafile 00003: /u02/oracle/oradata/ERPDB_DG/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/backup/ERPDB_DG/database_DELL_0uto3o79_30
channel ORA_DISK_1: piece handle=/u02/oracle/backup/DELL_DG/database_ERPDB_0uto3o79_30 tag=STANDBY_TODAY_REFRESH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 24-JAN-22

RMAN> exit


Recovery Manager complete.
[oracle@host2 ERPDB_DG]$ 

14. Enable MRP

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> /
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> 

15. Enable log_archive_dest_state_2 (On primary)

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;

System altered.

SQL>

16. Switch logfile (On Primary)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/ERPDB
Oldest online log sequence     14651
Next log sequence to archive   14652
Current log sequence           14652
SQL> 

17. Verify sync

On Primary

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
           14651

On Standby

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
           14651
SQL>