Advertisements
Skip to content
March 26, 2012 / Shivananda Rao P

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, 2013, 2014. 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.

Advertisements

55 Comments

Leave a Comment
  1. Mahesh Menon / Mar 28 2012 8:06 am

    Step 10 is where the use of db_file_name_convert and log_file_name_convert comes. Right ?

  2. shivanandarao / Mar 29 2012 6:46 am

    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.

  3. Mohammed Mubeen Ahmed / May 16 2012 9:02 pm

    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

  4. Shivananda Rao P / May 16 2012 9:50 pm

    Thanks Mohammed.

    You can subscribe by registering at the “Follow” option available at the bottom right corner of the web page.

  5. Stephen / Jul 19 2012 9:53 pm

    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

  6. Shivananda Rao P / Jul 21 2012 5:37 am

    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”

  7. Shashank Pandey / Sep 7 2012 9:12 pm

    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.

  8. Chetan / Sep 15 2012 1:23 pm

    Excellent Shivanand… Keep posting some more scenarios on stand by DB…. it really help us to enhance our knowledge…

  9. Shivananda Rao P / Sep 15 2012 1:59 pm

    Thanks Chethan.. Would be posting much more in the coming days..

  10. Kevin Little / Sep 16 2012 9:48 pm

    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.

  11. Shivananda Rao P / Sep 16 2012 11:30 pm

    Thank you Kevin for your kind words..

  12. vasavi / Sep 18 2012 8:07 pm

    Very useful and nice explanation Shivanand….Waiting for more scenarios……….Thanks…

  13. Shivananda Rao P / Sep 19 2012 3:39 am

    Thank you Vasvi. Would be posting much useful scenarios in the coming days.

  14. Satish / Sep 20 2012 12:42 am

    Hi Shivananda,
    Thanks a lot it’s a good posting and waiting for more ….

  15. Shivananda Rao P / Sep 20 2012 5:03 am

    Thanks Satish.

  16. Alok / Sep 25 2012 6:57 pm

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

  17. Shivananda Rao P / Sep 25 2012 7:43 pm

    Thank you Alok !! Would definitely come up with much more postings.

  18. Farhan Ali / Oct 4 2012 6:42 am

    Hi Shiva,
    Its a really nice and helpful topic. I have just recovered my standby from this scenario. Thanks a lot.

  19. SAIKRISHNA / Oct 15 2012 8:27 pm

    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?

  20. Baskar / Nov 7 2012 5:05 am

    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

  21. Shivananda Rao P / Nov 7 2012 7:31 am

    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.

  22. baskar / Nov 7 2012 11:48 am

    Thanks for providing much information Shiva …:)

  23. Sid / Dec 6 2012 10:10 am

    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

  24. Shivananda Rao P / Dec 6 2012 11:07 am

    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

  25. Lokanah / Dec 10 2012 8:10 am

    Thanks Shiivananda for such a beautiful article
    ..

  26. Shivananda Rao P / Dec 10 2012 10:21 am

    Thank you Lokanah !

  27. sidhant1231 / Dec 11 2012 4:09 pm

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

  28. sidhant1231 / Dec 11 2012 4:38 pm

    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

  29. Shivananda Rao P / Dec 11 2012 6:34 pm

    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

  30. Siva / Dec 12 2012 9:12 am

    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?

  31. Shivananda Rao P / Dec 12 2012 9:38 am

    Hi Siva,

    No matter if you are using file system. You need to follow the same steps as mentioned.

  32. sidhant1231 / Dec 14 2012 3:05 pm

    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

  33. Shivananda Rao P / Dec 15 2012 12:14 pm

    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.

  34. Jit / Feb 7 2013 7:25 pm

    HI Shava,
    Thanks for your post . Please post more scenarios about standby.

  35. Steven / Feb 8 2013 7:07 pm

    Excellent !

    I just needed the steps but boy this was nice.

    Steven

  36. Shivananda Rao P / Feb 8 2013 7:50 pm

    Thanks Steven !

  37. Jamsher Khan / Apr 1 2013 3:16 am

    Very Nicely explain….

  38. Shivananda Rao P / Apr 1 2013 8:17 am

    Thanks..

  39. David Cheung (@david95391) / Aug 3 2013 4:50 am

    Very good article! Good job on explaining the ‘switch datafile to copy’ step.

  40. Shivananda Rao P / Aug 12 2013 4:47 am

    Thanks David.

  41. Divakara / Dec 4 2013 3:39 pm

    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?

  42. K. Singh / Dec 8 2013 3:38 pm

    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

  43. Shivananda Rao P / Dec 9 2013 2:31 pm

    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

  44. K.Singh / Dec 10 2013 5:37 am

    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>

  45. Shivananda Rao P / Dec 10 2013 3:13 pm

    Please check the diskgroup space. Looks like you are running short of space on the diskgroup of standby server.

    Regards,
    Shivananda

  46. K.Singh / Dec 12 2013 11:07 am

    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

  47. Bhavesh / Feb 6 2014 4:57 pm

    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

  48. Shivananda Rao P / Feb 9 2014 4:38 am

    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

  49. bhavesh / Feb 10 2014 4:56 am

    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 ?.

  50. Shivananda Rao P / Feb 10 2014 1:26 pm

    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

  51. sunil / May 30 2014 12:10 pm

    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!

  52. Shivananda Rao P / May 30 2014 4:45 pm

    Thanks Sunil

    Regards,
    Shivananda

  53. Thomas / Jun 23 2014 8:47 am

    Great article buddy and continue with excellent job!!

  54. Yogesh / Jul 2 2014 7:35 pm

    Thanks for the wonderful documentation .

  55. Uthra Abi / Oct 4 2017 11:58 pm

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: