Snapshot Physical Standby database

Creating a Snapshot Standby database

The Snapshot Standby database concept was introduced from the 11g Version, which allows the use of a physical standby database in the read-write mode for a short period of time. You can convert a physical standby to a snapshot standby by using either traditional SQL *Plus or using the Data Guard broker and grid control at any time. Even if you convert it to a snapshot standby, it will still receive data continuously from the production database archive, so that in the next conversion from a snapshot to a physical standby it will be used for recovery. In case you have performed recovery at any point in time, the new incarnation will be started. Even though a new incarnation has started, the snapshot standby database will still continue accepting redo from the primary database. If your standby database is clustered and has more than one node, then shut down all the auxiliary RAC instances of the standby prior to performing a snapshot. Note that you should not put a standby database in the snapshot mode for a long time; it results in huge archive logs between the production database and the standby database and in case of critical databases it can have a serious impact.

Converting to a Snapshot Standby database

Perform the following steps to convert a physical standby database to a Snapshot Standby database:

To convert a physical standby database to a snapshot standby database, flashback should be enabled and the database should be brought to the mount status after cancelling recovery as follows: Snapshot Standby database To convert a physical standby database to a snapshot standby database, flashback should be enabled and the database should be brought to the mount status after cancelling recovery as follows:

SQL> select open_mode,database_role,flashback_on from v$database;

OPEN_MODE     DATABASE_ROLE             FLASHBACK_ON

——————–       —————-                     ——————

mounted      physical                   standby yes

ACTIVE DATA GUARD, SNAPSHOT STANDBY, AND ADVANCED TECHNIQUES

Now process the following command to convert the physical standby database to a snapshot standby database:

SQL> Alter database convert to snapshot standby;

Database altered.

Internally, a Snapshot Standby database creates a restore point so that we can convert the snapshot standby database to a physical standby database at any time, and the standby database will be converted as a primary with a new incarnation as follows:

SQL> select open_mode,database_role,resetlogs_change#,prior_resetlogs_change# from v$database;

 

OPEN_MODE DATABASE_ROLE RESETLOGS_CHANGE# PRIOR_RESETLOGS_CHANGE#

———-      —————-                   —————-                 ——————–

mounted   snapshot standby        3966240                 745084

After successful conversion, you can now validate the snapshot standby database as follows:

SQL> select name,restore_point_time from v$restore_point;

 

NAME RESTORE_POINT_TIME

—————————————— —————- —

snapshot_standby_required_15/04/2015 20:10:27 08.10.27.000000000 PM

Even though the old standby database is converted to a snapshot standby database, the archives will be received from the primary database whenever log switch occurs, and note that the database will be in the MOUNT status after conversion as follows:

You have to explicitly open the database so that it will be ready for read and write purposes.

And perform any DDL and DML operations on it.

SQL> shutdown immediate;

SQL> startup;

As you can see, it’s very simple, you just write a few commands, and re-startup your database and you are ready to perform testing on snapshot standby machine.

Convert a Snapshot Standby database again back to Physical Standby database

However the Redo data is received while the database was a snapshot standby database and it will be automatically applied when Redo Apply is started.
After converted physical standby database into snapshot standby database you have to must be opened at least once your snapshot standby in read write mode before it can be converted back into a physical standby database.

Now shutdown the snapshot database and again open it into mount state.

SQL> shutdown immediate;

SQL> startup mount;

Convert the DB.

SQL> Alter database convert to physical standby;

Now Shutdown the database and open to the mount stage

SQL> shutdown immediate;

SQL> startup mount;

completely you have to enable MRP(managed recovery process) recovery mode to applied all archived to the last stage and restore the flashback granted restore point to last position when you convert the physical standby into snapshot standby database.

SQL> Alter database recover managed standby database using current logfile disconnect;

For creating Logical standby you can click on LOGICAL STANDBY DATABASE

Or you can go to oracle-base site.

Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.

Creating a Snapshot Physical Standby database step by step

Leave a Comment Cancel Reply