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
___________________________________________________________________________________________________
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
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>
[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>
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
[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]$
[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]$
[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]$
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>
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/