How to Create Snapshot Standby in DataGuard


SNAPSHOT STANDBY:-

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode,
all changes made whilst in read-write mode are lost. This is achieved using flashback database,
but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

Create a physical standby from my post How to create Physical Standby and then follow the below steps.

CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE

  • Make sure the standby instance is in MOUNT mode.
select open_mode, database_role from v$database;
  • Make sure managed recovery is disabled.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  • Convert the standby to a snapshot standby.
SELECT flashback_on FROM v$database;
select name, open_mode, database_role from v$database;

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;

SELECT flashback_on FROM v$database;
select name, open_mode, database_role from v$database;

 

CONVERT SNAPSHOT STANDBY DATABASE TO PHYSICAL STANDBY DATABASE

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
select name, open_mode, database_role from v$database;

2 thoughts on “How to Create Snapshot Standby in DataGuard

  1. Hi Nitish,

    I have doubt here, the standby database should be in FBR to convert to snapshot standby.

    if not, we can’t convert?

    How ever i have converted to snapshot standby as your article..

    This is only my question…

    Thanks & Regards
    Veera

    Liked by 1 person

    • HI Balla,

      I understand your concern, and you are right internally Oracle uses flashback to convert physical to snapshot BUT you don’t have to explicitly enable flashback on snapshot standby. So in the interim phase Oracle utilizes flashback code but since full functionality is not required there is no need to enable it.

      Just look at an alert log snippet during this conversion and you’ll exactly see what I am trying to say.

      Let me know if you have any more queries.

      Regards
      Nitish

      Like

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