How to Create and Manage Snapshot Standby Database

Snapshot Standby Databases: Overview

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database to a snapshot standby database. A snapshot standby database receives and archives—but does not apply—redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back to a physical standby database, after discarding all local updates to the snapshot standby database.

You can create a snapshot standby database by using DGMGRL commands or SQL commands. When the standby database is converted to a snapshot standby database, an implicit guaranteed restore point is created and Flashback Database is enabled. After performing operations on the snapshot standby database, you can convert it back to a physical standby database. Flashback Database will be discussed later in the course.

Data Guard implicitly flashes the database back to the guaranteed restore point and automatically applies the primary database redo that was archived by the snapshot standby database since it was created. The guaranteed restore point is dropped after this process is completed.

Snapshot Standby Database: Architecture

After a physical standby database is converted to a snapshot standby database, Redo Apply no longer applies the redo data. The redo data continues to be received using the defined transport method (SYNC or ASYNC), but it is not applied until the snapshot standby database is converted back to a physical standby database.

Converting a Physical Standby Database to a Snapshot Standby Database

To convert a physical standby to a snapshot standby:

1. Stop Redo Apply if it is active.
2. Ensure that the database is mounted, but not open.
3. Ensure that the fast recovery area has been configured Ensure that the fast recovery area has been configured.
4. Issue the following SQL statement to perform the conversion:

   SQL&> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

5. Open the snapshot standby database in read-write mode.

   SQL> ALTER DATABASE OPEN;

The above steps are necessary to convert a physical standby database to a snapshot standby database. Before converting the standby database, Redo Apply must be stopped if it is running. If Redo Apply is running, the following error message will be returned:

SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point
   'SNAPSHOT STANDBY RE _ _ QUIRED 08 _ / / 16 2013 16:25:49'.

ORA-01153: an incompatible media recovery is active

Activating a Snapshot Standby Database: Issues and Cautions

Keep the following in mind when activating a snapshot standby database:

Potential data loss when a log file is corrupted

The snapshot standby database accepts redo log files but does not apply them. If there is a corrupted redo log file at the snapshot standby database, it is not discovered until the database is converted back to a physical standby database and the managed recovery process (MRP) is started. If the primary database is unavailable at that time, there is no way to retrieve that log. Also, the loss or corruption of a flashback log file might prevent conversion back to a physical standby database.

Lengthy conversion of the snapshot standby database to a primary database

In the event of a failure of the primary database the snapshot standby database can be the event of a failure of the primary database, the snapshot standby database can be converted back to a physical standby database. The redo that has been received can then be applied, and the database can be converted to a primary database. If the snapshot standby database lags far behind the primary database, it may take a long time to apply the redo that has been received and convert it to the primary database.

Snapshot Standby Database: Target Restrictions

When you convert the physical standby database to a snapshot standby database, it cannot be the only standby database in the configuration if your configuration is in maximum protection mode. In addition, you cannot make changes to the configuration after converting to a snapshot standby database that would create this situation. Protection modes will be discussed later in the course.

You cannot perform a switchover to a snapshot standby database. A snapshot standby database cannot be configured as a fast-start failover target.

In summary, a snapshot standby database cannot be:

• The only standby database in a maximum protection configuration
• The target of a switchover The target of a switchover
• A fast-start failover target

Viewing Snapshot Standby Database Information

The DATABASE_ROLE column of the V$DATABASE view indicates that the database is a snapshot standby database.

View the database role by querying V$DATABASE:

SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY

Snapshot Standby Space Requirements

Monitor the space requirements for enabling snapshot standby:

SQL> select file_type, number_of_files,
percent_space_used from v$recovery_area_usage;

FILE_TYPE                 NUMBER_OF_FILES  PERCENT_SPACE_USED
------------------         ---------------  ----------------
CONTROL FILE                  0               0
REDO LOG                      0               0
ARCHIVED LOG                 106            41.81
BACKUP PIECE                  1              .17
IMAGE COPY                    0               0
FLASHBACK LOG                 2              .98
FOREIGN ARCHIVED LOG          0               0
AUXILIARY DATAFILE COPY       0               0

Flashback Database does not have to be enabled to issue the command to convert a physical standby database to a snapshot database; however, the flash recovery area must be configured. The command will automatically enable Flashback Database and create a guaranteed restore point. This will result in a flashback logs being created in the flash recovery area. If there is not enough space in the flash recovery area to create the flashback logs, an error will occur and an error message written to the alert log. The longer the period of time that the standby database is in the snapshot mode, the larger the flashback logs will become. It will be necessary to monitor this space usage with the SQL statement presented in the above slide. The above query monitors overall space usage in the flash recovery area. To determine the specific space requirements for the guaranteed restore point that was created, the following query can be used:

SQL> SELECT NAME, STORAGE_SIZE FROM V$RESTORE_POINT;
NAME                                           STORAGE_SIZE
---------------------------------------------  ------------
SNAPSHOT_STANDBY_REQUIRED_08/16/2013 16:41:11  52428800

Converting a Snapshot Standby Database to a Physical Standby Database

To convert a physical standby to a snapshot standby:

1. On an Oracle Real Applications Cluster (Oracle RAC) database, shut down all but one instance.
2. Ensure that the database is mounted but not open Ensure that the database is mounted, but not open.
3. Issue the following SQL statement to perform the conversion:

    SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Use the CONVERT DATABASE command to convert the database back to a physical standby database. The snapshot database must have been opened at least once in read-write mode before it can be converted back to a physical standby; otherwise the following error is returned:

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:ORA-16433: The database or pluggable database must
be opened in read/write mode.

If the snapshot standby is currently open, it must be shut down and started back up in the MOUNT mode to convert back to a physical standby. Otherwise, the following error occurs:

ORA-01126: database must be mounted in this instance and not open in any instance