Applying an incremental backup for missing archive log in to a physical standby
(OR)
ROLLING FORWARD A STANDBY DATABASE USING RMAN INCREMENTAL BACKUP
My setup
1) 2 node primary RAC database on 11202
2) 2 node physical standby RAC database on 11202
3) Running on ASM
We found that couple of archive log are delete before apply it in the standby database
Primary DB SCN Number
SQL> select current_scn from gv$database;
CURRENT_SCN
-----------
13395160
13395160
Standby DB SCN Number
SQL> select current_scn from gv$database;
CURRENT_SCN
-----------
9642901
9642901
SQL> select min (checkpoint_change#) from v$datafile_header;
ERROR MESSAGE
In our DG Environment, it has been reported as a error message
wyclorau003[BHU_2]>dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;
Configuration - DG_BHU
Protection Mode: MaxAvailability
Databases:
BHU_B - Primary database
Error: ORA-16810: multiple errors or warnings detected for the database
BHU_A - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL> show database 'BHU_A';
Database - BHU_A
Role: PHYSICAL STANDBY
Transport Lag: 13 days 31 minutes 26 seconds
Apply Lag: 13 days 31 minutes 16 seconds
Real Time Query: OFF
Instance(s):
BHU_1
BHU_2 (apply instance)
Database Status:
SUCCESS
DGMGRL> show database 'BHU_B';
Database - BHU_B
Role: PRIMARY
Instance(s):
BHU_1
BHU_2
Database Error(s):
ORA-16783: cannot resolve gap for database BHU_A
Database Warning(s):
ORA-16629: database reports a different protection level from the protection mode
Database Status:
ERROR
STEP #1 STOP THE RECOVERY PROCESS (APPLY OFF)
$ dgmgrl -----à On standby server
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> edit database 'BHU_A' set state='APPLY-OFF';
Succeeded.
DGMGRL> show database 'BHU_A';
Database - BHU_A
Role: PHYSICAL STANDBY
Transport Lag: 13 days 31 minutes 26 seconds
Apply Lag: 13 days 31 minutes 16 seconds
Real Time Query: OFF
Instance(s):
BHU_1
BHU_2 (apply instance)
Database Status:
SUCCESS
IF NO BROKER
Stop the managed recovery process (MRP)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
STEP#2 PERFORM A INCREMENTAL BACKUP FROM PRIMARY DB
$ rman target / -à primary DB
RMAN> run
2> {
3> allocate channel ch1 type disk;
4> allocate channel ch2 type disk;
5> backup incremental from scn 9642000 database format '/oracle/BHU/standby_%d_%t_%c_%p';
6> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=95 instance=BHU_1 device type=DISK
allocated channel: ch2
channel ch2: SID=120 instance=BHU_1 device type=DISK
Starting backup at 31-OCT-11
backup will be obsolete on date 07-NOV-11
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=+BHU_DATA1/BHU_b/datafile/pindb.257.762433489
input datafile file number=00001 name=+BHU_DATA1/BHU_b/datafile/system.260.762433549
channel ch1: starting piece 1 at 31-OCT-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00002 name=+BHU_DATA1/BHU_b/datafile/sysaux.261.762433557
input datafile file number=00005 name=+BHU_DATA1/BHU_b/datafile/undo_2.259.762433541
input datafile file number=00003 name=+BHU_DATA1/BHU_b/datafile/undo.258.762433535
channel ch2: starting piece 1 at 31-OCT-11
channel ch1: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989757_1_1 tag=TAG20111031T145557 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:16
channel ch2: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989758_1_1 tag=TAG20111031T145557 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:25
backup will be obsolete on date 07-NOV-11
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 31-OCT-11
channel ch1: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989783_1_1 tag=TAG20111031T145557 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-OCT-11
released channel: ch1
released channel: ch2
RMAN> exit
STEP#3 RECOVER STANDBY DB
A) Move the rman backup piece to the standby server
B) Catalog backup piece on the standby server
$ rman target / -à STANDBY SERVER
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Oct 31 15:05:10 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BHU (DBID=19835845, not open)
RMAN> catalog start with '/oracle/BHU/saptrace/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/BHU/bkp
List of Files Unknown to the Database
=====================================
File Name: /oracle/BHU/bkp/standby_BHU_765989757_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989758_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989783_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: /oracle/BHU/bkp/standby_BHU_765989757_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989758_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989783_1_1
C) Recover the standby database using the backup piece which has taken from the PRIMARY DB
-- RUN FROM STANDBY SERVER
RMAN> run
2> {
3> allocate channel ch1 type disk;
4> recover database noredo;
5> }
allocated channel: ch1
channel ch1: SID=2729 instance=BHU_2 device type=DISK
Starting recover at 31-OCT-11
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +BHU_DATA1/BHU_a/datafile/system.265.762888379
destination for restore of datafile 00004: +BHU_DATA1/BHU_a/datafile/pindb.270.762888453
channel ch1: reading from backup piece /oracle/BHU/bkp/standby_BHU_765989757_1_1
channel ch1: piece handle=/oracle/BHU/saptrace/bkp/standby_BHU_765989757_1_1 tag=TAG20111031T145557
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +BHU_DATA1/BHU_a/datafile/sysaux.268.762888411
destination for restore of datafile 00003: +BHU_DATA1/BHU_a/datafile/undo.269.762888431
destination for restore of datafile 00005: +BHU_DATA1/BHU_a/datafile/undo_2.264.762888353
channel ch1: reading from backup piece /oracle/BHU/bkp/standby_BHU_765989758_1_1
channel ch1: piece handle=/oracle/BHU/saptrace/bkp/standby_BHU_765989758_1_1 tag=TAG20111031T145557
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
Finished recover at 31-OCT-11
released channel: ch1
STEP#4 Create standby control from primary DB
SQL> alter database create standby controlfile as '/oracle/BHU/standby.ctl';
Database altered.
STEP#5 Restore the Control file in the STANDBY DB
-- Below steps are in the standby server
A) STOP THE STANDBY DATBASE( Both instances)
$ srvctl stop database -d BHU_a
B) START ONLY ONE INSTANCE OF THE STANDBY DATBASE
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 31 15:10:18 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.4431E+10 bytes
Fixed Size 2240272 bytes
Variable Size 3892314352 bytes
Database Buffers 1.0503E+10 bytes
Redo Buffers 34148352 bytes
C) IDENTIFY THE CONTROL FILE LOCATION
Note: you can remove the existing control file or we can allow the system to over write the control file which is placed in the ASM. I am over writing the controlfiles
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +BHU_DATA1/BHU_a/controlfile/current.258.762889151,
+BHU_ARCH/BHU_a/controlfile/current.332.762889151,
+BHU_RECO/BHU_a/controlfile/current.285.762889151
D) I have the copied standby control file from primary DB
$ ls
standby.ctl
$ pwd
/oracle/BHU/bkp
E) RESTORING CONTROL FILE TO STANDBY DATABASE
RMAN> restore controlfile from '/oracle/BHU/bkp/standby.ctl';
Starting restore at 31-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=660 instance=BHU_2 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+BHU_DATA1/BHU_a/controlfile/current.258.762889151
output file name=+BHU_ARCH/BHU_a/controlfile/current.332.762889151
output file name=+BHU_RECO/BHU_a/controlfile/current.285.762889151
Finished restore at 31-OCT-11
RMAN> exit
RMAN> alter database mount;
Database mount
STEP#6 CHANGING CONTROLFILE CONFIGURATION
A) Controlfile is restored from PRIMARY DB the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
B) Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.
$ rman target / à STANDBY DB
Recovery Manager: Release 11.2.0.2.0 -Production on Mon Oct 31 17:27:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BHU (DBID=19835845, not open)
RMAN> CATALOG START WITH '+BHU_DATA1/BHU_A/DATAFILE/';
Starting implicit crosscheck backup at 31-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 7 objects
Finished implicit crosscheck backup at 31-OCT-11
Starting implicit crosscheck copy at 31-OCT-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-OCT-11
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern +BHU_DATA1/BHU_A/DATAFILE/
List of Files Unknown to the Database
=====================================
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO_2.264.762888353
File Name: +BHU_data1/BHU_A/DATAFILE/SYSTEM.265.762888379
File Name: +BHU_data1/BHU_A/DATAFILE/SYSAUX.268.762888411
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO.269.762888431
File Name: +BHU_data1/BHU_A/DATAFILE/INDB.270.762888453
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO_2.264.762888353
File Name: +BHU_data1/BHU_A/DATAFILE/SYSTEM.265.762888379
File Name: +BHU_data1/BHU_A/DATAFILE/SYSAUX.268.762888411
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO.269.762888431
File Name: +BHU_data1/BHU_A/DATAFILE/INDB.270.762888453
STEP#7 Check for New Datafile added in primary DB
Any datafiles have been added to Primary AFTER SCN 13395160 they will also have to be restored to the standby host and catalog as shown above before doing the switch.
To determine if any files have been added to Primary since the standby current SCN
To determine if any files have been added to Primary since the standby current SCN
-- IN STANBDY DB
SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 13395160;
no rows selected
NOTE: if any files available, we have to copy it from primary to the standby database using RMAN
copy the datafile from the primary database.
run
{
allocate channel a1 type disk;
backup datafile 10 format '/oracle/BHU_A/bkp_ctl_file/datafile_%d_%U_%p_%c';
}
Transfer the rman backup datafile to standby database location then catalog the backup piece in the standby database.
run
{
allocate channel a1 type disk;
Restore datafile 10;
}
copy the datafile from the primary database.
run
{
allocate channel a1 type disk;
backup datafile 10 format '/oracle/BHU_A/bkp_ctl_file/datafile_%d_%U_%p_%c';
}
Transfer the rman backup datafile to standby database location then catalog the backup piece in the standby database.
run
{
allocate channel a1 type disk;
Restore datafile 10;
}
STEP#8 Switch the datafile USING RMAN
This process will change the datafile details of the standby database in the controlfile
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/system.265.762888379"
datafile 2 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/sysaux.268.762888411"
datafile 3 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/undo.269.762888431"
datafile 4 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/pindb.270.762888453"
datafile 5 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/undo_2.264.762888353"
STEP#9 Clear the standby log files
On standby database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 100; SQL> ALTER DATABASE CLEAR LOGFILE GROUP 110; SQL> ALTER DATABASE CLEAR LOGFILE GROUP 200;SQL> ALTER DATABASE CLEAR LOGFILE GROUP 210;....
STEP#10 START THE RECOVEY PROCESS ON STANDBY
$ dgmgrl -----à On standby server
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> edit database 'BHU_A' set state='APPLY-ON';
Succeeded.
IF NO BROKER
On the STANDBY database, start the MRP on apply instance
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
FINAL RESULT
DGMGRL> show configuration
Configuration - DG_BHU
Protection Mode: MaxAvailability
Databases:
BHU_B - Primary database
BHU_A - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database 'BHU_A';
Database - BHU_A
Role: PHYSICAL STANDBY
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
BHU_1
BHU_2 (apply instance)
Database Status:
SUCCESS
DGMGRL>
great article and very helpful keep it up
ReplyDeletewhat if online/standby log files path is different on primary and standby
ReplyDeletehow to set correct path in restored standby controlfile...