Snapshot Standby DB


Prior to Oracle Database 11g, the DBA could opt for one of two mutually-exclusive choices for a physical standby databases operating mode:

The database could operate in Managed Recovery mode, in which redo data would be applied as soon as it was received from the corresponding primary database. The physical standby would thus be available immediately for transition to the primary role during either a switchover or a failover operation.
Alternatively, the database could be opened in READ ONLY mode so that applications could execute queries against it. This offered the ability to leverage the physical standby database platform for the intensive read operations that data warehousing, DSS, and OLAP applications typically perform, thus offloading that workload from the production database.


While it was certainly possible to maintain two physical standby databases – one in managed recovery mode, and another in READ ONLY mode – to satisfy both requirements, it also potentially increased licensing costs and the complexity of the Data Guard environment.

Thankfully, Oracle 11g now offers the capability to satisfy both modes within one physical standby database with Real Time Query, part of the separately licensee Active Data Guard option.

This is one of the greatest features added in Oracle 11g.
A snapshot standby database can allow you to easily test your application / code on a copy of your REAL production environment. Once you are done with your tests, you can easily go back to your Standby DB and apply all the remaining logs that you already received from PROD.

A snapshot standby database is a fully updateable standby database where we can basically convert the physical standby database into a read-write real time clone of the production database and we can then temporarily use this environment for carrying out any kind of development testing, QA type work or to test the impact of a proposed production change on an application.

Basically, the Snapshot Standby database in turn uses the Flashback Database technology to create a guaranteed restore point to which the database can be later flashed back to.

So we need to keep in mind that whatever limitations are present in the Flashback database technology, the same will also be inherent in the Snapshot database feature.


Physical to Standby with SQL Plus
Standby to Physical with SQL Plus
Physical to Standby with DGMGRL

Standby to Physical with DGMGRL


Convert a Physical Standby to Snapshot Standby with SQL*Plus


1- Confirm that you have Flashback feature enabled
If that is not enabled, perform the following steps:

1- Review and set (if needed) the db_recovery parameters on both database(standby & primary) and then activate flashback
On Primary
SQL> show parameters db_recovery;
NAME                          TYPE        VALUE
----------------------------  ----------  -------------------------
db_recovery_file_dest         string
db_recovery_file_dest_size    big integer 0


SQL> alter system set db_recovery_file_dest_size=50g scope=both;
SQL> alter system set db_recovery_file_dest=’+ASM_PROD_FRA’ scope=both;
SQL> alter system set db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate

On Standby
SQL> show parameters db_recovery;
NAME                          TYPE        VALUE
----------------------------  ----------  -------------------------
db_recovery_file_dest         string
db_recovery_file_dest_size    big integer 0


SQL> alter system set db_recovery_file_dest_size=50g scope=both;
SQL> Alter system set db_recovery_file_dest=’+ASM_STDBY_FRA’ scope=both;
SQL> alter system set db_flashback_retention_target=60 scope=both;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> select name,database_role,flashback_on from v$database;

On Primary
SQL> startup mount
SQL> alter database flashback on;
SQL> alter database open;
SQL> select name,database_role,flashback_on from v$database;


2- Convert the Standby in Snapshot Standby
After the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode
SQL> shutdown immediate
SQL> startup mount
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

After that you are ready to perform any DDL and DML operations on it.
SQL> shutdown immediate;
SQL> startup;

As you can see, this process is very simple, you just type a few commands, re-start your DB and you are ready to test this amazing feature!!!


Convert Snapshot Standby back to Physical Standby with SQL Plus
The Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.
Note:
A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.

Connect to the Stanbdy DB, shut it down and mount it:
SQL> shutdown immediate;
SQL> startup mount;

Convert the DB.
SQL>  ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Shutdown and mount it again:
SQL> shutdown immediate;
SQL> startup mount

Finally, enable recovery mode:
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;



Convert Physical Standby to Snapshot Standby with DGMGRL
As you can see, this process is very simple, you just type a few commands, re-start your DB and you are ready to test this amazing feature!!!
Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.
Note that the Flashback Database feature is required to create a snapshot standby database.
If Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database.
The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required.

1- Connect to DG Broker
$ dgmgrl
DGMGRL> connect sys
DGMGRL> show configuration

2- Convert the DB.
After the conversion takes place, internally a guaranteed restore point has been created and the database has been opened in read-write mode
DGMGRL> convert database 'STDBY' to snapshot standby;

3- Verify that the database was successfully converted by issuing the SHOW CONFIGURATION command:
DGMGRL> show configuration
Configuration - prod_dg
  Protection Mode: MaxPerformance
Databases:
      PROD  - Primary database
      STDBY – Snapshot standby database
Fast-Start Failover: DISABLED

Configuration Status: SUCCESS



Convert Snapshot Standby back to Physical Standby with DGMGRL
The Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.

Note:
A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.

1- Connect to DG Broker.
You should be on the primary server to complete this step.
$ dgmgrl
DGMGRL > connect sys

2- Convert the DB back to physical
DGMGRL > convert database 'stdby' to physical standby;

3- Verify that the database was successfully converted by issuing the SHOW CONFIGURATION command:
DGMGRL> show configuration
Configuration - prod_dg  
  Protection Mode: MaxPerformance  
Databases:
    PROD  - Primary database
    STDBY – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status: SUCCESS


NOTE:
You may get the following message during the conversion process:

“Converting database "stdby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "STDBY" on database "stdby"
Shutting down instance "STDBY"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "STDBY" on database "stdby"
Starting instance "STDBY"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        start up and mount instance "STDBY" of database "stdby“

If so, then connect to the STDBY and startup and mount the instance:
startup mount




a