# Snapshot Standby - Oracle 11g

nother great new Data Guard feature in Oracle Database11g is the snapshot standby database. Now in Oracle Database 11g, you can open a physical standby database in full read/write mode, allow modifications to the database, and, after a specified period, revert to a physical standby database. Simply stated, a snapshot standby database is an updatable standby database. To create a snapshot standby database, you simply convert a physical standby database using the following syntax:

alter database convert to snapshot standby;

While the standby database is in snapshot standby mode, you continue to receive archivelogs, but the archivelogs cannot be applied. Archivelogs from the primary database will be automatically applied when the snapshot standby database is converted to a physical standby database. If your protection mode is Maximum Protection, you cannot convert a physical standby database to a snapshot standby database if it is the only standby database participating in Maximum Protection mode.

Note :For RAC databases, you will need to shut down all the RAC instances except for the one you are

planning to convert to a snapshot standby database. Once you convert the physical standby database on the single RAC instance, you can restart the other RAC instances.

With the snapshot standby database, companies can leverage their disaster recovery hardware for alternative solutions such as QA test validations and performance benchmarks, allowing the reallocation of money spent on purchasing Oracle licenses for QA database environments to go to additional Oracle licenses to support a Data Guard environment. By leveraging a production-sized database to support QA activities such as hotfix testing, load testing, functional testing, or even stress testing, hardware allocated for disaster recovery does not sit idle. The snapshot standby database facilitates maximum return on investment for both hardware and Oracle database licensing.

Convert to Snapshot Standby

Converting a physical standby database to a snapshot standby database is a, well, snap. To create a snapshot standby, the MRP process must be shut down, and you cannot be in readonly or real-time mode. The following example will result in an error since the database was still in real-time query mode (from the previous example):

SQL> alter database convert to snapshot standby;

LTER DATABASE CONVERT TO SNAPSHOT STANDBY
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_04/30/2007
16:30:23'.
ORA-01153: an incompatible media recovery is active

You must be in mount mode to convert the database to snapshot standby. Once you issue the command to convert to snapshot standby, Oracle dismounts your database. If you attempt to mount the database, you will receive an error, as shown here:

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

Behind the scenes, Oracle replaces the standby control file with the primary controlfile. Even though you are dismounted, you have to shut down the database and restart the database. In the following steps, we will shut down the database and open the database:

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down. SQL> startup
ORACLE instance started. Total System Global Area 1071333376 bytes
Fixed Size 1302468 bytes
Variable Size 478150716 bytes
Database Buffers 587202560 bytes
Redo Buffers 4677632 bytes
Database mounted.
Database opened.

Next, we will query the open_mode column in the V$DATABASE view to confirm that the database is truly in read/write mode: SQL> select open_mode from v$database;

OPEN_MODE
--------------------

Additionally, we will query the database_role column in V$DATABASE to verify that the database is in snapshot standby mode: SQL> select primary_db_unique_name, db_unique_name, database_role from v$database;

PRIMARY_DB_UNIQUE DB_UNIQUE_NAME DATABASE_ROLE
----------------- ------------------------- ----------------------
DBA11G DBA11GDR SNAPSHOT STANDBY

Note :Oracle Database 11g introduces a new column in V$DATABASE called PRIMARY_DB_UNIQUE_NAME. Now, you are able to see the primary database unique name and the standby database unique name in a single view. Starting from Oracle Database11g, databases with the same db_unique_name parameter will not be able to participate in a Data Guard configuration. Now you can perform DDL and DML on the snapshot standby database. In this example, we will create a table called docs_dr and insert rows: SQL> create table docs_dr (doc_id number, name varchar2(4000), lob_content blob); Table created. SQL> insert into docs_dr (doc_id, name) values (1, 'Oracle Maximum Availability Architecture'); 1 row created. SQL> commit; Commit complete. Since you do not apply archivelogs while the database is in standby mode, over time the snapshot standby database deviates from the primary database. The duration a snapshot standby database can continue in this mode is dependent on the company’s recovery point objectives (RPOs) and recovery time objectives (RTOs). The snapshot standby database can be ideal for short testing scenarios such as a load test to confirm that a schema or an application patch or a major upgrade will cause performance issues. The good news is that even though the database continues to be in snapshot standby mode, you can continue to ship archivelogs from the primary database to the disaster recovery site. The DBA should know how many archivelogs can be applied per hour, and based on the RPO/RTO, they will know how long they can continue in the snapshot standby mode without compromising the business’s RPO/RTO. There are two factors to consider while the physical database is in the snapshot standby database mode: • The number of archivelogs generated from the primary database source • The amount of data updated while the standby database is in updatable mode Businesses can reap the benefits from a temporary snapshot of the primary database and justify the return on investment from the amount of money spent on disaster recovery licenses. Behind the scenes, Oracle creates an implicit guaranteed restore point on the physical standby database prior to converting the snapshot standby database. GAP detection and resolution continue as usual while the database is in snapshot standby mode. Once the database is ready to be flashed back to the point prior to opening the database in updatable mode, a guaranteed restore point is used to flash back a snapshot standby to the point in time prior to converting the physical standby database to the snapshot standby database. By default, the guaranteed restore point is named with the SNAPSHOT_STANDBY_REQUIRED_ prefix appended by the system date. You can query the V$RESTORE_ POINT view to determine the name of the restore point, as revealed here:

SQL> select name, storage_size from v$restore_point; NAME STORAGE_SIZE ------------------------------------ ------------------------ SNAPSHOT_STANDBY_REQUIRED_05/01/2007 8192000 You can accomplish the equivalent of the snapshot standby database in Oracle Database 10g Release 2 Data Guard using the guaranteed restore points. The only caveat is that the DBA has to perform all the steps required to convert the physical standby database to the snapshot standby database manually. In addition, you must perform the steps required to convert the snapshot standby database to the physical standby database manually. These steps are significantly more complicated in Oracle Database 10g Release 2 than in Oracle Database 11g. Many DBAs are not aware that the snapshot standby database equivalent capability is available in Oracle Database 10g Release 2. For complete details, please refer to the case study on Oracle’s Maximum Availability Architecture web site: The syntax to convert the snapshot physical database to the standby physical database is as follows: alter database convert to physical standby; Convert Back to Physical Standby Similarly, the database must also be in a mounted state to convert to physical standby mode. If you are not in a mounted state, you will receive this error if you attempt to convert back to a physical standby: SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any Instance Once you mount the database, you will be able to convert back to a physical standby, as shown here: SQL> alter database convert to physical standby; Database altered. Now you need to shut down the database again, mount or open the database, and restart managed recovery. The conversion process back to physical standby will discard all the changes that were made while the database was in snapshot standby mode. As stated earlier, Oracle utilizes an implicit guaranteed restore point created earlier during the conversion from the physical standby to the snapshot standby. Oracle issues a flashback database to the specific guaranteed restore point. Before the snapshot standby is converted to the physical standby, the guaranteed restore point is dropped. As the Data Guard database is brought back into the physical standby mode, redo apply will apply all the redo data received while the database was in the snapshot standby mode. Once the conversion back to physical standby is complete, the docs_dr table created in the earlier example disappears. In the following several queries, you will notice that the database is opened in read-only mode and the docs_dr table no longer exists: SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> select open_mode from v$database;
OPEN_MODE
--------------------