Skip to Main Content
  • Questions

  • Data guard - READ WRITE to READ ONLY

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nagaraju.

Asked: April 11, 2017 - 5:58 am UTC

Last updated: April 12, 2017 - 5:12 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Yesterday i made my physical standby database to primary (manually) and opened the database in READ WRITE mode. Because my primary database server crashed due to h/w issue and user requested me to open the database in READ WRITE mode and i did.

Now i want to change the role back to PHYSICAL STANDBY as my actual primary database is up and running now.

Can i do it? or do i need to rebuild entire data guard set up?

version : Oracle 11g

Below are the steps i executed on standby to make phy_standby to primary.

SQL> alter database recover managed standby database cancel;

SQL> alter database activate physical standby database;

Thank you..

and Connor said...

*If* you have flashback enabled on the database, then you can "rewind" the standby back to the point just before you activated it, using:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN [before_standby_became_primary_scn];
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

If you did *not* have flashback enabled, then you would need to restore a backup from before that point in time, or re-initiate the standby from scratch, whatever is most efficient for you.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nagaraju P, April 11, 2017 - 8:56 am UTC

Thanks for your reply.

Was there any way to get the SCN number at time of standby became primary.
Sorry i forgot to make a note of scn on standby

Thank you..
Connor McDonald
April 12, 2017 - 1:40 am UTC

You can also flashback to a time, which you could get from the alert log, eg

FLASHBACK DATABASE TO TIMESTAMP timestamp '2017-01-01 12:34:45';

Nagaraju P, April 12, 2017 - 2:27 am UTC

Hi,

Sorry i forgot to mention you that our DB is not FLASHBACK Enabled. So even after execute your query 

FLASHBACK DATABASE TO TIMESTAMP timestamp '2017-01-01 12:34:45';


Will it really work as no flashback logs?

Thank you..


Connor McDonald
April 12, 2017 - 5:12 am UTC

Nope.

No flashback logs = no flashback.

You'll need to restore from backup, or recreate the standby

Nagaraju P, April 12, 2017 - 4:06 pm UTC

Thanks you very much. I'm going with incremental backup from the SCN from the point when i opened the database READ WRITE.

Thank you..
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.