Tag Archives: recover standby database when huge gap

Refresh Standby Database using RMAN Incremental SCN Backup

Refresh Standby Database using RMAN Incremental SCN Backup

Table of Contents
_______________________________________________________________________________________

Action Plan

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: (DELL)

Platform          : Linuxx86_64
Server Name       : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version        : Oracle 11.2.0.4
File system	  : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

STANDBY: (DELL_DG)

		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
DB Version        : Oracle 11.2.0.4
File system       : Normal
DB Name           : DELL
DB_UNIQUE_NAME	  : DELL_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/11.2.0.4

Action Plan


1. Verify GAP

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY

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

MAX(SEQUENCE#)
--------------
           425 <-------

SQL>

[oracle@rac1 DELL]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
      Error: ORA-16724: cannot resolve gap for one or more standby databases

    DELL_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

DGMGRL>

On Standby

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

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

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

MAX(SEQUENCE#)
--------------
           367 <----

SQL>


We don't have archive logs from 368 to 425. Now how to recover standby database without rebuild from scratch?


2. Stop Redo Transfer (On Primary)

Manual:

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>

Using DGMGRL:

DGMGRL> show database "DELL"

Database - DELL

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON <-------
  Instance(s):
    DELL

  Database Error(s):
    ORA-16783: cannot resolve gap for database DELL_DG

Database Status:
ERROR

DGMGRL>


DGMGRL> EDIT DATABASE "DELL" SET STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL>
DGMGRL> show database "DELL"

Database - DELL

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF <-------------
  Instance(s):
    DELL

  Database Error(s):
    ORA-16783: cannot resolve gap for database DELL_DG

Database Status:
ERROR

DGMGRL>


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
-----------
    1430085 <------

SQL>


4. Take RMAN Incremental from SCN (On Primary)

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:17:33 2019

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

connected to target database: DELL (DBID=3965088591)

RMAN>

RMAN> run
2> {
3> allocate channel t1 type disk;
4> allocate channel t2 type disk;
5> allocate channel t3 type disk;
6> backup incremental from SCN 1430085 database tag='STANDBY_TODAY_REFRESH' format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s';
7> release channel t1;
8> release channel t2;
9> release channel t3;
10> }

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=52 device type=DISK

allocated channel: t2
channel t2: SID=46 device type=DISK

allocated channel: t3
channel t3: SID=42 device type=DISK

Starting backup at 24-JAN-19
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/DELL/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/DELL/users01.dbf
channel t1: starting piece 1 at 24-JAN-19
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/DELL/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/DELL/undotbs01.dbf
channel t2: starting piece 1 at 24-JAN-19
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 24-JAN-19
channel t3: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0vto3o79_31 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0tto3o79_29 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t1: backup set complete, elapsed time: 00:00:08
channel t2: finished piece 1 at 24-JAN-19
piece handle=/u02/oracle/backup/DELL_BACKUP/database_DELL_0uto3o79_30 tag=STANDBY_TODAY_REFRESH comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
Finished backup at 24-JAN-19

released channel: t1

released channel: t2

released channel: t3

RMAN>


5. Create Standby Control file

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DELL      READ WRITE           PRIMARY <-------

SQL>

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

Database altered.

SQL>


6. Transfer Backup to standby

[oracle@rac1 DELL_BACKUP]$ scp * oracle@rac2:/u02/oracle/backup/DELL_DG
oracle@rac2's password:
database_DELL_0tto3o79_29                        100%  232KB 232.0KB/s   00:00
database_DELL_0uto3o79_30                        100%  736KB 736.0KB/s   00:00
database_DELL_0vto3o79_31                        100%   10MB  10.0MB/s   00:00
std_control.ctl                                  100%   10MB   9.9MB/s   00:00
[oracle@rac1 DELL_BACKUP]$


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/DELL_DG/co
                                                 ntrol01.ctl, /u02/oracle/orada
                                                 ta/fast_recovery_area/DELL_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
--------- -------------------- ----------------
DELL      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@rac2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/DELL_DG/control01.ctl
[oracle@rac2 DELL_DG]$ cp std_control.ctl /u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl


10. Mount Standby Database

[oracle@rac2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:28:58 2019

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]$


12. Cancel MRP

[oracle@rac2 DELL_DG]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 24 04:32:49 2019

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, 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 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

[oracle@rac2 DELL_DG]$


13. Recover standby

[oracle@rac2 DELL_DG]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 24 04:33:32 2019

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-19
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/DELL_DG/system01.dbf
destination for restore of datafile 00004: /u02/oracle/oradata/DELL_DG/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/backup/DELL_DG/database_DELL_0tto3o79_29
channel ORA_DISK_1: piece handle=/u02/oracle/backup/DELL_DG/database_DELL_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/DELL_DG/sysaux01.dbf
destination for restore of datafile 00003: /u02/oracle/oradata/DELL_DG/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/backup/DELL_DG/database_DELL_0uto3o79_30
channel ORA_DISK_1: piece handle=/u02/oracle/backup/DELL_DG/database_DELL_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-19

RMAN> exit


Recovery Manager complete.
[oracle@rac2 DELL_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>

[oracle@rac1 DELL_BACKUP]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> EDIT DATABASE "DELL" SET STATE='TRANSPORT-ON';
Succeeded.
DGMGRL>


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/DELL
Oldest online log sequence     429
Next log sequence to archive   431
Current log sequence           431
SQL> 


17. Verify sync

On Primary

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

MAX(SEQUENCE#)
--------------
           430 <------

SQL>

DGMGRL> show configuration;

Configuration - DELL

  Protection Mode: MaxPerformance
  Databases:
    DELL    - Primary database
    DELL_DG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database "DELL_DG"

Database - DELL_DG

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    DELL_DG

Database Status:
SUCCESS

DGMGRL>

On Standby

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/DELL_DG
Oldest online log sequence     429
Next log sequence to archive   0
Current log sequence           431
SQL> 

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

MAX(SEQUENCE#)
--------------
           430 <------------

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/