Failover to Snapshot Standby

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;
About these ads

, , ,

  1. #1 by yuri on January 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. #2 by Uwe Hesse on January 16, 2012 - 10:39

    Yuri, thank you for sharing that information :-)

  3. #3 by Kyle Kim on February 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. #4 by Uwe Hesse on February 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.

  1. After Test-Failover, make NEW Primary Standby again « The Oracle Instructor
  2. After Test-Failover, make NEW Primary Standby again | Oracle Administrators Blog - by Aman Sood

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

Follow

Get every new post delivered to your Inbox.

Join 2,892 other followers

%d bloggers like this: