Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

rman restore of backup from standby database

kbrown1333928078Mar 26 2014 — edited Mar 28 2014

I'm working on an exercise to prove recoverability of our production database and believe I'm just missing something.

I have a full backup from my standby database and backups of archivelogs from the primary database.  When I try to restore I am able to recover at any point in time and open the database read only .  When I try to open the database with resetlogs I get an ORA-01666 telling me the controlfile is from a standby database.  I've done the restore from both the standby controlfile autobackup and the primary database controlfile autoback and both with same result.  The database, via v$database, believes it's a standby and I can't figure out how to chage it.

My general process is to restore the control file from backup, mount the control file, catalog the backup folder, restore the database, then recover the db.  It's at this point where the process goes off rail.  Any assistance would be appreciated.

Comments

oscrub Mar 26 2014 — edited on Mar 26 2014

What version of Oracle are you using? On what OS? And what command are you using to restore the controlfile?

If the DATABASE_ROLE is PRIMARY, then you should be able to restore and recover as normal.

I did some quick testing on one of my boxes here. If you restore the controlfile without the 'standby' parameter, you should be able to restore and recover without encountering the error.

On the primary, I backed up the controlfile for standby:

RMAN> backup current controlfile for standby format '/u02/stdb.ctl';

On the standby I restored without using the 'standby' parameter:

RMAN> restore controlfile from '/u02/stdb.ctl';

Starting restore at 27-MAR-14

using target database control file instead of recovery catalog

And then checked the database_role:

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

So if the role is PRIMARY, you should be fine and be able to do a restore and recover as normal.

Doing it again but this time using the 'standby' parameter:

RMAN> restore standby controlfile from '/u02/stdb.ctl';

Starting restore at 27-MAR-14

using target database control file instead of recovery catalog

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

If the role is 'PHYSICAL STANDBY', then you'll encounter the same error that you're having now.

You can also follow this guide which you can follow step-by-step to do the restore and recover process:

https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf

Renu-Oracle Mar 27 2014

Hi,

A physical Standby database can NOT be opened with RESETLOG option. Yoyu SHALL get this error
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database


A Physical Standby database CAN be ACTIVATED and ONLY then opened for READ-WRITE access

Please be aware, that ONCE a Physical Standby database is activated ==> there is NO way back unless you enabled FlashBack


Thanks,

Renu

kbrown1333928078 Mar 27 2014

I'm running AIX and Oracle 10.2.0.4.  I'm restoring the controlfile that was taken from the primary database using restore controlfile from '/...';

However, even after restoring from the primary database controlfile I can check the v$database on the restored database and it still shows it's a standby database.

oscrub Mar 27 2014 — edited on Mar 27 2014

Did you check the link I gave in my previous post?

https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf

Although the title states it is how to open the standby if the primary is lost, the same process and commands listed on pg 4 of the pdf can be applied to your situation.

1. Restore controlfile, either from prod backup or from standby.

2. Restore database files

3. Recover using archivelogs

4. If DATABASE_ROLE is PHYSICAL STANDBY, issue the commands:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

5. Check the status of DATABASE_ROLE, it should now be PRIMARY.

6. Open the database using RESETLOGS.

Renu-Oracle Mar 28 2014

Hi,

You need to check previous post and let us know if you are able to activate the standby.

Thanks

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 25 2014
Added on Mar 26 2014
5 comments
1,715 views