Snapshot Standby Database – Complete Step-by-Step Guide to Convert Physical Standby to Snapshot Standby

Introduction

A Snapshot Standby Database is a special type of Oracle Data Guard standby that allows the standby database to be opened in read-write mode for testing, development, or validation purposes.

In a normal Physical Standby Database, redo data from the primary database is continuously applied to keep it synchronized. However, in a Snapshot Standby, redo data is received and archived but not applied.

This allows DBAs and application teams to:

Perform read-write operations
Run application testing
Conduct development or patch validation
Test upgrades or schema changes

Once testing is complete, the snapshot standby can be reverted back to a physical standby, and all changes made during snapshot mode are discarded, restoring synchronization with the primary database.

High-Level Architecture Flow


+-------------------+
|  Primary Database |
|  (READ WRITE)     |
+-------------------+
          |
          | Redo Transport (LGWR / ARCH)
          v
+-------------------+
| Physical Standby  |
| (READ ONLY APPLY) |
+-------------------+
          |
          | Convert to Snapshot Standby
          v
+-------------------+
| Snapshot Standby  |
| (READ WRITE)      |
| - Redo Archived   |
| - Redo NOT Applied|
| - Flashback ON    |
+-------------------+
          |
          | Flashback to Guaranteed Restore Point
          v
+-------------------+
| Physical Standby  |
| (READ ONLY APPLY) |
+-------------------+

Key Characteristics of Snapshot Standby

Converted from a physical standby
Opened in READ WRITE mode
Redo is received but not applied
Requires Flashback Database
Automatically creates a guaranteed restore point
All changes are rolled back when converted back to physical standby

Important Notes

Fast Recovery Area (FRA) must be configured
Flashback Database must be enabled
Snapshot standby is not suitable for production workloads
Redo apply resumes automatically after conversion back

Architecture Overview


Primary Database
      |
      | (Redo Transport)
      v
Snapshot Standby (Redo archived, not applied)
      |
      | (Flashback to restore point)
      v
Physical Standby (Redo apply resumes)

Pre-Requisites

Before starting:

Data Guard configuration must already exist
Standby database must be in READ ONLY WITH APPLY
FRA configured with sufficient space

Step-by-Step Conversion Process

Step 1: Verify Standby Database Role and Mode

Ensure the database is a physical standby and redo apply is active.


SELECT open_mode FROM v$database;

Expected output:


READ ONLY WITH APPLY


SELECT database_role FROM v$database;

Expected output:


PHYSICAL STANDBY

Step 2: Stop Redo Apply (MRP)

Cancel the managed recovery process.


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shutdown and mount the database:


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Step 3: Enable Flashback Database

Flashback is mandatory for snapshot standby.

Verify FRA:


SHOW PARAMETER db_recovery_file_dest;

Enable flashback:


ALTER DATABASE FLASHBACK ON;

Verify status:


SELECT database_role FROM v$database;
SELECT status FROM v$instance;

Expected:


PHYSICAL STANDBY
MOUNTED

Step 4: Convert Physical Standby to Snapshot Standby


ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Mount and open the database:


ALTER DATABASE OPEN;

Step 5: Verify Snapshot Standby Status


SELECT database_role FROM v$database;

Expected output:


SNAPSHOT STANDBY

Step 6: Check Guaranteed Restore Point

Oracle automatically creates a restore point.


SELECT name, guarantee_flashback_database
FROM v$restore_point;

Sample output:


SNAPSHOT_STANDBY_REQUIRED_2025_11_25    YES

Step 7: Perform Read-Write Operations (Testing Phase)

Create a test table and insert data.


CREATE TABLE test_users (id NUMBER(5));

Insert sample data:


BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test_users VALUES (i);
  END LOOP;
END;
/
COMMIT;

Verify data:


SELECT COUNT(*) FROM test_users;

Step 8: Shutdown After Testing


SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Step 9: Convert Snapshot Standby Back to Physical Standby


ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Verify:


SELECT database_role, open_mode FROM v$database;

Expected:


PHYSICAL STANDBY
MOUNTED

Open database:


STARTUP;

Check mode:


SELECT open_mode FROM v$database;

Expected:


READ ONLY

Enable MRP:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step 10: Validate Data Reversion

Check for test objects:


SELECT * FROM test_users;

Expected error:


ORA-00942: table or view does not exist

This confirms that all changes made in snapshot standby mode were discarded.

Validate Archive Sync on Primary and Standby

PRIMARY SEQUENCE

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

STANDBY SEQUENCE

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

Summary

Feature	Snapshot Standby
Read-Write Access	Yes
Redo Apply	No
Redo Archive	Yes
Flashback Required	Yes
Testing Allowed	Yes
Changes Persist After Revert	No

Best Practices

Ensure adequate FRA space
Limit snapshot duration
Monitor archived redo volume
Revert to physical standby promptly
Avoid heavy workload during snapshot mode

Conclusion

A Snapshot Standby Database provides a powerful and safe way to perform read-write testing on a standby database without impacting production.
By leveraging Flashback Database and guaranteed restore points, Oracle ensures that the standby can always be restored to its original synchronized state.

This makes snapshot standby an excellent choice for testing, validation, and controlled development activities in enterprise environments.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Snapshot Standby Database – Complete Step-by-Step Guide to Convert Physical Standby to Snapshot Standby