Roll Forward Physical Standby Database using RMAN incremental backup
There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons
1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.
2. It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.
In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the methods to roll forward the physical standby database to have it in sync with the primary database.
Oracle Database version : 11.2.0.1.0 My Oracle Database is using ASM.
Primary database : sspm Standby database : sssb
Primary Host : dev Standby Host : uat
The maximum archivelog sequence generated on the Primary Database is 1005.
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN sspm PRIMARY SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1005
On the standby database, the maximum archivelog sequence that is applied is sequence 865.
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED sssb PHYSICAL STANDBY SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 865
The standby database is lagging behind the primary database by around 140 archives (1005 – 865).
When I investigated the alert log file of the Primary database to find out the reason for the logs not getting applied on the standby database, I got to see the below error message.
Sun Mar 25 15:40:23 2012 Errors in file /u01/app/oracle/diag/rdbms/sspm/sspm/trace/sspm_arc2_18816.trc: ORA-00308: cannot open archived log '+FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785' ORA-17503: ksfdopn:2 Failed to open file +FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785 ORA-15012: ASM file '+FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785' does not exist
So the problem was here. The archivelog sequence 866 was missing and was unavailable at the FRA site. There were few more archives missing on the FRA and nor did I had the backup to restore them on the standby database. My option was to go with Roll Forwarding the Standby Database using Incremental Backups. Below are the steps on how to roll forward the physical standby database.
Step 1: Take a note of the Current SCN of the Physical Standby Database.
Standby Database:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 991247
Note down the CURRENT_SCN value of the standby database (991247) to proceed further.
Step 2 : Cancel the Managed Recovery Process on the Standby database.
Standby Database:
SQL>alter database recover managed standby database cancel;
Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (991247)
Connect to the primary database and take the incremental SCN backup.
Primary Database:
[oracle@dev ~]$ rman target sys/oracle@sspm Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:44:45 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: SSPM (DBID=1624493265) RMAN> backup incremental from scn 991247 database format '/u02/bkp/stnd_backp_%U.bak'; Starting backup at 25-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=39 device type=DISK backup will be obsolete on date 01-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA_NEW/sspm/datafile/system.256.778803539 input datafile file number=00002 name=+DATA_NEW/sspm/datafile/sysaux.257.778803541 input datafile file number=00003 name=+DATA_NEW/sspm/datafile/undotbs1.258.778803541 input datafile file number=00004 name=+DATA_NEW/sspm/datafile/users.259.778803543 channel ORA_DISK_1: starting piece 1 at 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 using channel ORA_DISK_1 backup will be obsolete on date 01-APR-12 archived logs will not be kept or backed up 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 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/bkp/stnd_backp_11n6p3p4_1_1.bak tag=TAG20120325T154639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-MAR-12
Step 4: Take the standby controlfile backup of the Primary database controlfile.
Connect to the Primary database and create the standby controlfile backup.
Primary Database :
RMAN> backup current controlfile for standby format '/u02/stnd_%U.ctl'; Starting backup at 25-MAR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/stnd_12n6p3qt_1_1.ctl tag=TAG20120325T154845 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-MAR-12
Step 5: Transfer the backups from the Primary Server to the Standby Server.
Primary Database :
[oracle@dev bkp]$ pwd /u02/bkp [oracle@dev bkp]$ ls -lrt total 13576 -rw-r----- 1 oracle oinstall 540672 Mar 25 15:47 stnd_backp_10n6p3nl_1_1.bak -rw-r----- 1 oracle oinstall 13336576 Mar 25 15:47 stnd_backp_11n6p3p4_1_1.bak [oracle@dev bkp]$ scp stnd* uat:/u02/bkp oracle@uat's password: stnd_backp_10n6p3nl_1_1.bak 100% 528KB 528.0KB/s 00:00 stnd_backp_11n6p3p4_1_1.bak 100% 13MB 6.4MB/s 00:02 [oracle@dev bkp]$ cd /u02 [oracle@dev u02]$ ls -lrt stnd* -rw-r----- 1 oracle oinstall 13336576 Mar 25 15:48 stnd_12n6p3qt_1_1.ctl [oracle@dev u02]$ scp stnd* uat:/u02 oracle@uat's password: stnd_12n6p3qt_1_1.ctl 100% 13MB 12.7MB/s 00:01 [oracle@dev u02]$
Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.
I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.
Standby Database:
[oracle@uat ~]$ rman target sys/mydbpwd@sssb Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:51:02 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: SSPM (DBID=1624493265, not open) RMAN> catalog start with '/u02/bkp'; Starting implicit crosscheck backup at 25-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK Crosschecked 15 objects Finished implicit crosscheck backup at 25-MAR-12 Starting implicit crosscheck copy at 25-MAR-12 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 25-MAR-12 searching for all files in the recovery area cataloging files... File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_200.453.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_201.454.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_202.455.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_203.456.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_137.457.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_204.458.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_205.459.778846883 . . . . File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_856.947.778861691 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_858.949.778861709 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_857.950.778861719 searching for all files that match the pattern /u02/bkp List of Files Unknown to the Database ===================================== File Name: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak File Name: /u02/bkp/stnd_backp_11n6p3p4_1_1.bak Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak File Name: /u02/bkp/stnd_backp_11n6p3p4_1_1.bak
Step 7: Recover the standby database with the cataloged incremental backup pieces.
RMAN> recover database noredo; Starting recover at 25-MAR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=32 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: +DATA/sssb/datafile/system.274.778865099 destination for restore of datafile 00002: +DATA/sssb/datafile/sysaux.275.778865193 destination for restore of datafile 00003: +DATA/sssb/datafile/undotbs1.276.778865259 destination for restore of datafile 00004: +DATA/sssb/datafile/users.277.778865273 channel ORA_DISK_1: reading from backup piece /u02/bkp/stnd_backp_10n6p3nl_1_1.bak channel ORA_DISK_1: piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 25-MAR-12
Step 8 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile backup that we had taken from the primary database.
Standby Database:
RMAN> shutdown immediate database dismounted Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 659730432 bytes Fixed Size 2216264 bytes Variable Size 398462648 bytes Database Buffers 255852544 bytes Redo Buffers 3198976 bytes RMAN> restore standby controlfile from '/u02/stnd_12n6p3qt_1_1.ctl'; Starting restore at 25-MAR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+DATA/sssb/controlfile/current.273.778864875 Finished restore at 25-MAR-12
Step 9: Shutdown the standby database and mount the standby database, so that the standby database would be mounted with the new controlfile that was restored in the previous step.
Standby Database:
RMAN> <strong>shutdown immediate Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 659730432 bytes Fixed Size 2216264 bytes Variable Size 398462648 bytes Database Buffers 255852544 bytes Redo Buffers 3198976 bytes
Step 10: If the datafile location of the primary and standby databases are different, then you need to follow this step. If not, then proceed with Step 11.
The datafiles of my primary database are residing on the Diskgroup +DATA_NEW on the primary server and the datafiles on the standby database are residing on the Diskgroup +DATA on the standby server, the datafiles location are different.
Since, I have restored the standby controlfile backuppiece of my primary database on the standby database (Step 7) and mounted the standby database, the standby database controlfile would now have the locations of the datafiles recorded as available in the Primary database. So, we need to make the standby controlfile understand that the datafiles location of the standby database are different from that of the Primary database. For this, you need to catalog the datafile location of the standby database to its controlfile as shown below.
Connect the standby database through RMAN and catalog the location of its datafiles and later switch them.
Standby Database:
RMAN> catalog start with '+DATA/SSSB/DATAFILE'; searching for all files that match the pattern +DATA/SSSB/DATAFILE List of Files Unknown to the Database ===================================== File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099 File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193 File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259 File Name: +data/SSSB/DATAFILE/USERS.277.778865273 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099 File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193 File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259 File Name: +data/SSSB/DATAFILE/USERS.277.778865273 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/sssb/datafile/system.274.778865099" datafile 2 switched to datafile copy "+DATA/sssb/datafile/sysaux.275.778865193" datafile 3 switched to datafile copy "+DATA/sssb/datafile/undotbs1.276.778865259" datafile 4 switched to datafile copy "+DATA/sssb/datafile/users.277.778865273" RMAN>
Step 11: If the datafile locations of the primary and the standby databases are same, then there is no necessity to perform the catalogging operation as done in the previous step.
On the standby database, start the Managed Recovery Process.
Standby Database:
SQL> alter database recover managed standby database disconnectfrom session; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 1010 RFS IDLE 0 MRP0 WAIT_FOR_LOG 0 9 rows selected.
Step 12: On the Primary database, check the Maximum Archivelog Sequence generated.
Primary Database:
SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1009
Step 13: Check the maximum archivelog sequence that is applied on the Physical standby database.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1009
So, here we can see from Steps 12 and 13 that the maximum archivelog sequence generated on the Primary database is sequence# 1009 and that applied on the Physical Standby Database is also 1009 which means that the Standby database is in sync with the Primary Database. You can check it out by generating an archive sequence on the Primary database and check if its shipped and applied on the standby database.
Primary Database:
SQL> alter system switch logfile; System altered. SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1010
Standby Database:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1010
Now standby database is in sync with the Primary Database.
Here you go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 
Step 10 is where the use of db_file_name_convert and log_file_name_convert comes. Right ?
Yes. But here since it was with ASM, I had set the parameter db_create_file_dest to a diskgroup and this parameter would come up as the first preference when set along with the db_file_name_convert.
Thanks. I would mention a note on the db_file_name_convert parameter in this step.
excellent work dude. I would like to subscribe to your posts. It will be helpful if you provide an option for that…
thanks for sharing….
Regards
Thanks Mohammed.
You can subscribe by registering at the “Follow” option available at the bottom right corner of the web page.
Hi. Excellent post. However i am wondering how to handle a situation where the incremental backup contains a datafile that’s created after the current_scn of the standby database. Am getting an RMAN-06571 ‘no recoverable copy’ error
Hi Stephen,
In that case, once you switch the database to copy, you need to do the following steps.
Copy the newly add datafile on primary to the standby database.
1. Connect primary database to rman and use the copy command to create a copy of the newly added datafile to temporary location.
copy datafile to ;
2. Transfer the file placed above in temporary location to the standby server. You can use scp or other means.
3. Connect the standby database through RMAN and catalog the datafile that you copied in the above step
catalog datafilecopy ;
switch datafile to copy;
The file# can be obtained from v$datafile or from the RMAN prompt by mentioning “report schema”
Hi Shivananda ,
I had the similar situation where the incremental backup contained a copy of the datafile created after the current SCN of the standby database.In my case rman recover automatically created the the datafile on the standby site.Also I have the standby_file_management set to Auto.
Excellent Shivanand… Keep posting some more scenarios on stand by DB…. it really help us to enhance our knowledge…
Thanks Chethan.. Would be posting much more in the coming days..
Just wanted to say thank you very much, you filled in some details I didn’t find clearly in the Oracle docs and I have a successful physical standby on +ASM resync this weekend with your notes being very helpful.
Thank you Kevin for your kind words..
Very useful and nice explanation Shivanand….Waiting for more scenarios……….Thanks…
Thank you Vasvi. Would be posting much useful scenarios in the coming days.
Hi Shivananda,
Thanks a lot it’s a good posting and waiting for more ….
Thanks Satish.
Thanks Shivananda, it helped me a lot. Please if you could post other standby scenarios too. I believe it’s the only blog where people can find such most awaited stuff. Thanks again!!!!
Thank you Alok !! Would definitely come up with much more postings.
Hi Shiva,
Its a really nice and helpful topic. I have just recovered my standby from this scenario. Thanks a lot.
Our Standby went out of sync and found out that we are lagging by 200+ archive logs which we do not have on Primary database server anymore. Thanks to the detailed steps it did the magic to bring our DG in sync.
Just wondering how RMAN is creating a backup from a old SCN, how does primary database keep track of the changes?
Hi Shiva,
I have one scenario Like .. If disk is full on standby server without checking i’m adding one datafile from primary database , how to do that at this time and what will happen on standby side ,
Please help me in this situation
Baskar
Hello Baskar,
In this case, the MRP on the standby database terminates and the standby would not be in recovery and goes out of sync.
You can view the relevant errors in the Standby alert log file. The moment you add disk space to your standby and start the recovery, the standby catches itself with primary. Just make sure that at the parameter STANDBY_FILE_MANAGEMENT was set to AUTO on the standby database. You can take a look at this https://shivanandarao.wordpress.com/2012/04/02/managed-recovery-process-mrp-terminates-on-standby-database-upon-adding-datafile-on-the-primary-database/ very similar to your issue but not same.
Thanks for providing much information Shiva …:)
Hi Siva,
Running a DG on Oracle 11gR2. My datafiles/log files are in different mount points in Primary and Standby. While configuring I used the db_file_name_convert and log_file_name_convert.
It was in 100% sync, till recently due to a network outage there is a huge gap. Now following your steps plan to do a incremental recovery, In step 10, I understand we need to Catalog the file locations, but is it must to do a “switch datafile to copy”. I am using a non-asm file system.
What is the purpose of this command..Please advise.
Thanks
Sid
Hi Sid,
You may have to do the “switch database copy” because the files on primary are located at a different path than that of the standby, and you have now restored the “standby controlfile” of the primary database to the standby database. So, it would have the path of files of the primary database and not of the standby.
Once you do a catalog start with, the control file now assumes that these files belong to the standby database, but you need to rename the files as well at the database level. To do this, you need to perform the “switch database to copy”
Regards,
Shivananda
Thanks Shiivananda for such a beautiful article
..
Thank you Lokanah !
Hi Shiva,
I am stuck in step 10. After restoring the controlfile successfully, trying to catalog the datafiles but it doesn’t show the *.dbf files in the listing.; My datafiles, control files all are in same location. This is a small test env.
[oracle@DB-DR u02]$ ls -lrth
total 1.5G
drwxrwxr-x 2 oracle oinstall 16K Sep 2 02:24 lost+found
-rw-r—– 1 oracle oinstall 5.0M Oct 11 14:17 temp01.dbf
-rw-r—– 1 oracle oinstall 9.6M Dec 3 00:06 switch_standby.ctl
-rw-r–r– 1 oracle oinstall 1.9K Dec 3 00:13 pfile021212.txt
-rw-r—– 1 oracle oinstall 51M Dec 6 15:51 redo06.log
-rw-r—– 1 oracle oinstall 51M Dec 6 15:56 redo07.log
-rw-r—– 1 oracle oinstall 5.1M Dec 11 23:18 users01.dbf
-rw-r—– 1 oracle oinstall 51M Dec 11 23:18 undotbs01.dbf
-rw-r—– 1 oracle oinstall 711M Dec 11 23:18 system01.dbf
-rw-r—– 1 oracle oinstall 591M Dec 11 23:18 sysaux01.dbf
-rw-r—– 1 oracle oinstall 9.6M Dec 12 00:05 control01.ctl
RMAN> catalog start with ‘/u02/’;
searching for all files that match the pattern /u02/
List of Files Unknown to the Database
=====================================
File Name: /u02/pfile021212.txt
File Name: /u02/switch_standby.ctl
Do you really want to catalog the above files (enter YES or NO)?
— Its not showing the *.dbf files here..How to go forward..? Pls advise.
Thanks
Sid..
Hi Shiva,
@Update after the last post; I managed to catalog with “catalog datafilecopy”.
and bring up the standby in sync with primary..
Old scn difference;
SQL> select scn_to_timestamp(2067644) from dual; (Primary)
SCN_TO_TIMESTAMP(2067644)
—————————————————————————
11-DEC-12 11.02.26.000000000 PM
SQL> select scn_to_timestamp(2047185) from dual; (Standby)
SCN_TO_TIMESTAMP(2047185)
—————————————————————————
06-DEC-12 03.52.42.000000000
After recovery;
SQL> select scn_to_timestamp(2074050) from dual; (Standby)
SCN_TO_TIMESTAMP(2074050)
—————————————————————————
12-DEC-12 12.25.26.000000000 AM
SQL> select scn_to_timestamp(2074181) from dual; (Primary)
SCN_TO_TIMESTAMP(2074181)
—————————————————————————
12-DEC-12 12.27.23.000000000 AM
I don’t understand why the SCN has such difference than primary ?
-Sid
Hi Sid,
You can skip this step because you said that the file locations are same and also from the outcome what you have showed, it is clear that the Control file is aware of the *.dbf file and the redo logs. The only files that the controlfile is unaware is the .txt and the .ctl file.
Regards,
Shivananda
Hi Shiva,
Thanks much for the article. I dont have ASM storage here. I have Filesystem storage only. How can i proceed in this case?
Hi Siva,
No matter if you are using file system. You need to follow the same steps as mentioned.
Hi Shiva,
As I said my case the the primary and standby has different mount points so different locations for data and redo log files. Don’t we need the following steps;
1. remove all online logs/standby logs in standby directories
2. Check that log_file_name_convert is defined properly
3. Standby – Clear all standby redo logs
Then start the MRP
Hello Sid,
You do not have to remove or clear the online/standby redo logs. Just follow the steps as mentioned in the document and you would be able to recover the standby database. If the datafiles and logfiles are located on different location on the standby database than the primary database, then make sure that the db_file_name_convert and log_file_name_convert is correctly set on the standby database.
Steps to be followed:
1. get the current_SCN from the standby database, take the RMAN backup of the primary database from the SCN of the standby. Also take the controlfile backup (standby format) of the primary database.
2. Cancel the recovery (MRP) on the standby database Copy the backups to the standby server, catalog the backuppieces so that the standby controlfile would get to know about these backuppieces.
3. . Connect the standby database to RMAN and recover it using the above cataloged backup pieces.
4. shut down the standby database, rename the old current controlfiles and restore the new controlfile from the cotnrolfile backup taken above. (restore standby controlfile from ”;)
5. Mount the standby database, try to catalog the controlfile with the location of the datafile on the standby database. If the outcome shows as there are no files to be cataloged, then no issues. If it shows as one of the datafile needs to be cataloged, then catalog it and finally do the “switch database to copy”.
6. If there are no datafiles to be cataloged, then there is no need to do “switch database to copy”. Start the MRP process on the standby database.
HI Shava,
Thanks for your post . Please post more scenarios about standby.
Excellent !
I just needed the steps but boy this was nice.
Steven
Thanks Steven !
Very Nicely explain….
Thanks..
Very good article! Good job on explaining the ‘switch datafile to copy’ step.
Thanks David.
Hi Shivananada
Thanks for the detailed steps but what have not understand is Step 4 i.e.,”Take the standby controlfile backup of the Primary database controlfile.” and restore this control file at the step 8
, why do we need this step of restoring Primary database control file to standby? why I’m asking this is when we recovered standby from Incremental backup pieces Standby database control file would’nt be aware of all the information about restore/recover and the latest status of DB?
Hi Shiva,
M suffering with same scenerio (standby database lags far behind from the primary database) , As I checked tow datafiles are missing on Standby database. By these steps , will it create automatically or I have to create it manualy on standby database.
Regards:
K. Singh
Hello Singh,
The datafiles added on primary after a gap has been detected, they would not be automatically created on standby after recovery using incremental SCN backup. You may have to take a backup of the newly added datafile from primary along with the incremental SCN and standby controlfile backup and restore the datafile on standby, recover the standby. May be this would help you out “Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1)”
Regards,
Shivananda
Hi Shiva,
Thanks for reply…..!!!
Please have a look of error message which is following ……. (during rolling forward to standby database.
login as: oracle
Using keyboard-interactive authentication.
Password:
Last login: Sun Dec 8 13:22:06 2013 from 172.17.103.101
Oracle Corporation SunOS 5.10 Generic Patch January 2005
You have new mail.
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>. ./.profile.ibadb
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>sqlplus
SQL*Plus: Release 10.2.0.5.0 – Production on Sun Dec 8 22:57:28 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ———-
IBADB MOUNTED
SQL>
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>
cersaidb-zone1:10g:/home/oracle>rman target /
Recovery Manager: Release 10.2.0.5.0 – Production on Sun Dec 8 23:03:09 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: IBADB (DBID=1623206932, not open)
RMAN>
RMAN>
RMAN> CATALOG START WITH ‘/tmp’;
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp
no files found to be unknown to the database
RMAN> CATALOG START WITH ‘/tmp/ForStandby_04oqv3ud_1_1’;
searching for all files that match the pattern /tmp/ForStandby_04oqv3ud_1_1
List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_04oqv3ud_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: /tmp/ForStandby_04oqv3ud_1_1
RMAN>
RMAN>
RMAN> CATALOG START WITH ‘/tmp/ForStandby_05oqv4ao_1_1’;
searching for all files that match the pattern /tmp/ForStandby_05oqv4ao_1_1
List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_05oqv4ao_1_1
Do you really want to catalog the above files (enter YES or NO)? y
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/ForStandby_05oqv4ao_1_1
RMAN>
RMAN>
RMAN> recover database noredo;
Starting recover at 08-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1071 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG1/data/ibadb_system_001.dbf
destination for restore of datafile 00002: +DG1/data/ibadb_undotbs_001.dbf
destination for restore of datafile 00003: +DG1/data/ibadb_sysaux_001.dbf
destination for restore of datafile 00004: +DG1/data/ibatbs_01.dbf
destination for restore of datafile 00005: +DG1/data/dc_data_s_01_01.dbf
destination for restore of datafile 00006: +DG1/data/dc_data_m_01_01.dbf
destination for restore of datafile 00007: +DG1/data/dc_data_l_06_01.dbf
destination for restore of datafile 00008: +DG1/data/dc_data_l_05_01.dbf
destination for restore of datafile 00009: +DG1/data/dc_data_l_04_01.dbf
destination for restore of datafile 00010: +DG1/data/dc_data_l_03_01.dbf
destination for restore of datafile 00011: +DG1/data/dc_data_l_01_01.dbf
destination for restore of datafile 00012: +DG2/idx/dc_indx_l_05_01.dbf
destination for restore of datafile 00013: +DG2/idx/dc_indx_l_04_01.dbf
destination for restore of datafile 00014: +DG2/idx/dc_indx_l_03_01.dbf
destination for restore of datafile 00015: +DG2/idx/dc_indx_l_02_01.dbf
destination for restore of datafile 00016: +DG2/idx/dc_indx_l_0_01.dbf
destination for restore of datafile 00017: +DG2/idx/dc_indx_s_01_01.dbf
destination for restore of datafile 00018: +DG2/idx/dc_indx_m_01_01.dbf
destination for restore of datafile 00019: +DG2/idx/dc_indx_l_06_01.dbf
destination for restore of datafile 00020: +DG1/data/dc_data_l_02_01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_04oqv3ud_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/08/2013 23:09:03
ORA-19870: error reading backup piece /tmp/ForStandby_04oqv3ud_1_1
ORA-19646: cannot change size of datafile +DG1/data/ibatbs_01.dbf from 1280000 to 1920000
ORA-17505: ksfdrsz:1 Failed to resize file to size 1920000 blocks
ORA-15041: diskgroup space exhausted
RMAN>
RMAN>
Please check the diskgroup space. Looks like you are running short of space on the diskgroup of standby server.
Regards,
Shivananda
Thanks a lot. I assumed it is space issue on standby server.
For monitoring purpose I would like to configure OEM ( on UAT server first ). On UAT server I have two running database with same ORACLE_HOME. and one ASM instance.
Database- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
Operating System- SunOS 5.10
Please provide steps for configure OEM on UAT server. revert me for any assistance.
Thanks in advance-
Regards:
K. Singh
Regards:
K. Singh
Hi Shivananda,
Please let me know if
backup incremental from scn 991247 database format ‘/u02/bkp/stnd_backp_%U.bak’;
But SCN of Physical standby increased more as i forgot to stop recovery on Standby
Now backup is from old scn and physcial standby now on different SCN.
Can I use this backup for recovery of syandby
Hi Bhavesh,
When standby database is out of sync, it wouldn’t be updating its SCN and thereby the SCN would be far behind from that of primary. I do not understand, how in your case its increased.
Regards,
Shivananda
Hi,
Step 1.
Standby Database:
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1111
Step 2.
From primary.
backup incremental from scn 1111database format ‘/u02/bkp/stnd_backp_%U.bak’;
When this statement executed.I forgot to stop recovery of standby daatabase
So
From standby
SQL> select current_scn from v$database;
2222
..
Now i need to apply incremental backup of step 2 to DR server.
Is it possible or i have to take new backup base on new current_scn ?.
Hi,
When your standby database is not applying the logs due to log sequence gap, it does not make a difference if you stop or do not stop MRP on standby database.
Once a gap is detected and the RFS process is not able to fetch the logs for the standby and MRP showing as log gap, the standby DB would not be updating the SCN
A standby’s SCN can never be forward from that of primary even if the standby’s health is good.
Regards,
Shivananda
Hi Shivananda,
Amazing post with detailed explanation of each and every step with a practical output. Much appreciated. I had the same situation on my production DB today and was able to resolve after following your steps described in this post. 🙂
keep up the good work buddy!
Thanks Sunil
Regards,
Shivananda
Great article buddy and continue with excellent job!!
Thanks for the wonderful documentation .
Helped me just now. Managed to restore. My problem was not renaming files but that three datafiles were in different locations on primary and standby. So I had to move them as controlfile would now show primary location.