In a recent Data Guard course that I tought, the question came up whether it is possible to failover to a Snapshot Standby if the Primary is lost. My answer was that this is of course possible but will take a longer time, because first the Snapshot Standby need to be converted back to Physical Standby and then all the collected Redo Protocol on the Standby site needs to be applied.
However, when we tried to do it, the Broker refused to do the convert and the failover, complaining about the Primary not being reachable. We needed to do the convert without the Broker then. Today, when I wanted to reproduce the issue, it worked like a charm, though:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
This is the same version we had in the classroom. Now the configuration:
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
I will now shutdown the Primary like there is an emergency on the Primary Site:
[oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 14 09:42:47 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown abort ORACLE instance shut down.
Now with the Broker, a convert fails:
[oracle@uhesse ~]$ dgmgrl sys/oracle@physt DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> convert database physt to physical standby; Converting database "physt" to a Physical Standby database, please wait... Error: ORA-01034: ORACLE not available Error: ORA-16625: cannot reach database "prima" Failed. Failed to convert database "physt"
That was until this point the same as in the class. But now my failover succeeds, while it did not in the class:
DGMGRL> failover to physt; Converting database "physt" to a Physical Standby database, please wait... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Continuing to convert database "physt" ... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Database "physt" converted successfully Performing failover NOW, please wait... Failover succeeded, new primary is "physt"
All is fine now. It worked as it was supposed to. But it was a little scary, though, that it wasn’t possible to do the very same in the class – not sure what was different there. My advice based upon this experience would be to test the failover to a Snapshot Standby that has no connection to the Primary. Also, consider to configure two Standby Databases and use only one as Snapshot Standby. And notice the manual convert in case:
SQL> alter database convert to physical standby;
Pickleball spielen 
#1 von yuri am Januar 14, 2012 - 14:18
Hi,
if you are using OEM, you will get below :
Warning
This operation will convert the last physical standby database to a snapshot standby database. Although a snapshot standby database provides data protection, failover requires additional time compared to a physical standby database. If this is a concern, consider creating an additional physical standby database prior to performing conversion.
#2 von Uwe Hesse am Januar 16, 2012 - 10:39
Yuri, thank you for sharing that information 🙂
#3 von Kyle Kim am Februar 28, 2012 - 20:48
Hello,
I wonder if you make any changes on snapshot database and try to failover, it will still work.
In other word, if on snapshot some dmls or ddls were made, and you try to failover to it, would it still work or would you get the same error you got from your class?
Kyle
#4 von Uwe Hesse am Februar 29, 2012 - 12:06
Kyle,
thanks for the question: We expect that changes are done on the Snapshot Standby. During the convert command, these are flashed back again, returning as the Physical Standby as it was before we turned it into a Snapshot Standby. For this procedure, there is no connection between the Primary and the Standby needed – although the Data Guard Broker got irritated in some cases during that class because of the missing connection. As a workaround, we can do the convert manually.
#5 von Joshua Wangalwa am Dezember 8, 2014 - 09:18
Hello,
How can one change a database from snapshot to Primary role?
Regards
Joshua
#6 von Uwe Hesse am Dezember 12, 2014 - 16:13
Joshua, you failover to it. Just DGMGRL> failover to herethenameofthesnapshotstandby;
#7 von wajoshua am Dezember 15, 2014 - 17:44
Hello,
Thank you very much for the solution, it worked for me.
Joshua
#8 von Uwe Hesse am Dezember 20, 2014 - 20:37
Joshua, thank you for taking the time to leave the positive feedback 🙂