Standby Databases

-

Standby Databases:
Till Oracle 10g, we have two types of standby databases namely physical standby database and logical standby database.
From Oracle 11g, we got two more types of standby databases, called ADG (Active Data Guard) and snapshot standby database.

Will the details of each Standby Databases below:
1.) Physical Standby Database:

2.) Logical Standby Database:

3.) Oracle Active Data Guard:

4.) Snapshot Standby Database:
Oracle provides a unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions. Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active data guard implemented, but a snapshot standby database can be used to perform both READ and WRITE activities. The use of snapshot standby database is in the situation where if we want to clone a production database for testing something, we can convert existing physical standby database to snapshot standby database which is as close as to the production database w.r.t data, do required testing on the snapshot standby database and convert it back to physical standby database. Snapshot standby database is updatable and it provides the replicated version of the primary database, which can be used for development, testing purposes. Once when the updateable transactions are completed for testing purposes on the snapshot standby database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means that the transactions that were made when the standby database was opened in READ WRITE mode will be flushed out. Again, we can convert to snapshot version do the testing and resynchronize with primary. This cycle can be repeated any number of times. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.
A snapshot standby database receives and archives redo data from a primary database, but are not applied. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database. So, data from the primary database is always protected. It gives data protection and disaster recovery exactly like the physical standby database.
Introduced in Oracle 11gR1, a snapshot standby database is a physical standby that is temporarily disconnected from Data Guard configuration and able to be used as a fully updatable stand-alone database. As this is in READ WRITE mode, any kind of testing or changes can be done, including features like using Database Replay.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled. 

Converting Snapshot Standby Database into Physical Standby Database:
Oracle uses restore point and Flashback Database feature to accomplish this conversion of snapshot standby database to physical standby database. Restore point and Flashback Database concepts were first introduced in Oracle 10g. When we convert the physical standby database to snapshot standby database a guaranteed restore point is created. This guaranteed restore point is used to flashback a snapshot standby database to its original state. The name of this guaranteed restore points begins with ‘SNAPSHOT_STANDBY_REQUIRED_’.

When the database is converted back, it is flashed back to the guaranteed restore point, put back into the full Data Guard configuration, and the redo is applied eventually catching the standby database back up to the primary. Once this is completed, the guaranteed restore point is removed automatically. Using the guaranteed restore point the snapshot standby database will be flashed back to its original state and subsequently the restore point will be dropped. After the conversion, the redo will be applied which were received by the snapshot standby database.

When we convert a physical standby database to snapshot standby database, then 
  • Redo gap detection and resolution will work as before.
  • A snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
  • In case of role transition of one of the standby database (like failover or switchover), the snapshot standby database continues to receive the redo from the new primary.
  • Even if the primary database has a new incarnation (because of Flashback database or Open reset logs), the snapshot database continues to receive the redo from the primary.
 There are three restrictions where we cannot convert to a snapshot.
  • If it is the only standby of a Maximum Protection configuration.
  • If it is the target of a current switchover operation.
  • If it is the designated failover database for Fast Start Failover (FSFO).
If this is your only standby database, if you have to failover to it, the failover is going to take longer - because the standby has to be flashed back, and caught up, before the transition can complete, be aware that worst case scenario. It’s always recommended considering a Data Guard configuration that consists of two physical standby databases (at least), one is always kept physical standby database, and the other can be used/tested as a snapshot standby.

Comments

Post a Comment

Popular posts from this blog

Flashback Database to Restore Point in Primary-Standby Environment

-
Flashback Database to Restore Point in Primary-Standby Environment: When we have a Data Guard environment (Primary RAC and Standby Standalone Databases) and we want Flashback database to restore point. We need to follow below steps. a) Always flashback the primary database first. Then follow below step for Standby. b) On the primary, connect using RMAN and list the incarnations. c) On the standby, cancel the recovery and flashback the standby to restore point created earlier. d) On the standby, connect using RMAN and list the incarnations. e) If there is a mismatch in incarnation between primary and standby then need to reset the incarnation of standby and start recovery (Details not available in this DOC). 1)    Disable Log Transport Services to the Standby Database for the Destination serving the Standby Database on all Primary Instances: $ dgmgrl sys/<Password>@<PRIM/STDBY> DGMGRL>  show database 'PRIM '; edit database...
Read more

Guarantee Restore Point Primary-Standby Environment

-
Guarantee Restore Point Primary-Standby Environment: When we have a Data Guard environment (Primary RAC and Standby Standalone Databases) and we want to make use of restore points. We need to follow below steps. Always create a restore point on the standby database first then in Primary. The main reason for this is because the SCN of the standby must be less than the SCN of the primary in case you need to flashback your database. If we didn’t follow the step 1 then we need to rebuild the standby. 1.) Stop Managed Recovery on the Standby Database using SQL*PLUS or Data Guard Broker. $ dgmgrl sys/<Password>@<PRIM/STDBY> DGMGRL>  show database ' STDBY '; edit database 'STDBY' set state = 'APPLY-OFF'; show database ' STDBY ';   OR STDBY.SYS.SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby order by inst_id; alter database recover managed standby database ca...
Read more

Steps on converting a Snapshot Standby database to Physical Standby database

-
Steps on converting a Snapshot Standby database to Physical Standby database: 1) Check for database role, open mode & Restore point create by this conversion. STDBY.SYS.SQL> set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000 set trimspool on time on timi on feedback on verify off echo off heading on; col name for a65 col scn for 9999999999999999999999 col time for a40 col guarantee_flashback_database for a30 col db_unique_name for a15 select a.name db_name, a.db_unique_name, b.instance_name, b.instance_role, a.database_role, a.open_mode, a.protection_level, a.fs_failover_status, a.dataguard_broker, a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b; select name, scn, time, guarantee_flashback_database, storage_size, database_incarnation# from gv$restore_point; 2) If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversi...
Read more