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

 

Introduction

Recently, I had to work on a requirement where the standby database needed to be opened in read-write mode for testing and validation, without impacting the production primary database. In such cases, Oracle Data Guard provides a very useful feature called Snapshot Standby Database.

A Snapshot Standby Database is basically a physical standby database that can temporarily be opened in READ WRITE mode. This allows the DBA, application team, or testing team to perform activities such as application testing, patch validation, schema changes, or data validation on a standby copy of production.

In a normal Physical Standby Database, redo is continuously received from the primary database and applied on the standby to keep it synchronized. However, once the standby is converted into a Snapshot Standby, redo from the primary database continues to be received and archived, but it is not applied until the database is converted back to physical standby.

The best part is that when the snapshot standby is converted back to physical standby, all changes performed during snapshot mode are discarded automatically, and redo apply resumes from the point where it was paused.

 

What is Snapshot Standby?

A Snapshot Standby Database is converted from an existing physical standby database. Once converted, it becomes available for read-write operations. During this time, the database is no longer applying redo, but it continues to receive and archive redo from the primary database.

Oracle uses Flashback Database and a Guaranteed Restore Point internally to make this possible. When we convert it back to physical standby, Oracle flashes the database back to the restore point and removes all temporary changes made during the snapshot standby period.

This is very helpful in real production-like scenarios where we need to test something on near-production data, but we do not want to disturb the primary production system.

 

Common Use Cases Snapshot standby is useful for:

• Application testing on production-like data
• Patch validation
• Upgrade testing
• Schema change testing
• Functional testing by users
• Performance or query testing
• Troubleshooting production-like issues in an isolated environment

However, it should not be treated as a permanent testing environment because redo apply remains paused while the standby is in snapshot mode.

 

Important Points Before Starting

Before converting physical standby to snapshot standby, make sure the following points are checked:

• Data Guard configuration must already be working properly.
• Standby database should be synchronized with primary.
• Fast Recovery Area should be configured.
• FRA should have sufficient space.
• Flashback Database must be enabled.
• Snapshot standby should be used for a controlled and limited time.
• Redo will be received but not applied during snapshot mode.
• All changes done in snapshot standby mode will be lost after converting back to physical standby.

 

Pre-Requisites

Before starting the activity, I verified that:

• The standby database was already configured as a physical standby.
• Redo apply was running.
• The standby database was open in READ ONLY WITH APPLY mode.
• FRA was configured.
• Sufficient space was available in the FRA.
• The activity window was agreed with the application/testing team.

 

 

Step 1: Verify Standby Database Role and Open Mode

First, I connected to the standby database and verified its current open mode.

SELECT open_mode FROM v$database;

Expected output:

READ ONLY WITH APPLY

Then I verified the database role.

SELECT database_role FROM v$database;

Expected output:

PHYSICAL STANDBY

At this stage, the database must show as a physical standby. If the database role or open mode is different, we should not proceed until the Data Guard status is reviewed.

 

Step 2: Stop Redo Apply / MRP Process

Before converting the standby database into snapshot standby, I stopped the managed recovery process.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

After cancelling MRP, I shut down the standby database and started it in mount mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

This is required because the conversion to snapshot standby is performed while the database is mounted.

 

Step 3: Verify FRA and Enable Flashback Database

Flashback Database is mandatory for snapshot standby because Oracle uses a guaranteed restore point to revert the database back to its original physical standby state.

First, I checked the FRA configuration.

SHOW PARAMETER db_recovery_file_dest;

Then I enabled Flashback Database.

ALTER DATABASE FLASHBACK ON;

After enabling flashback, I verified the database role and instance status.

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

Expected output:

PHYSICAL STANDBY
MOUNTED

At this point, the standby database was mounted and ready for conversion.

 

Step 4: Convert Physical Standby to Snapshot Standby

Now I converted the physical standby database into snapshot standby.

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Once the conversion completed successfully, I opened the database.

ALTER DATABASE OPEN;

After opening, the standby database became available in read-write mode for testing.

 

Step 5: Verify Snapshot Standby Status

To confirm the conversion, I checked the database role.

SELECT database_role FROM v$database;

Expected output:

SNAPSHOT STANDBY

This confirmed that the physical standby had been successfully converted to snapshot standby.

 

Step 6: Check Guaranteed Restore Point

When a physical standby is converted to snapshot standby, Oracle automatically creates a guaranteed restore point. This restore point is used later when converting the snapshot standby back to physical standby.

I verified the restore point using the following query:

SELECT name, guarantee_flashback_database FROM v$restore_point;

Sample output:

SNAPSHOT_STANDBY_REQUIRED_2026_04_30 YES

This confirmed that the required guaranteed restore point was created successfully.

 

Step 7: Perform Read-Write Testing

Once the database was in snapshot standby mode, I performed a small read-write test to validate that the database was open for DML operations.

I created a test table.

CREATE TABLE test_data (id NUMBER(5));

Then I inserted sample data.

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

After that, I verified the record count.

SELECT COUNT(*) FROM test_data;

Expected output:

10000

This confirmed that the snapshot standby database was working in read-write mode.

 

Step 8: Shutdown Snapshot Standby After Testing

Once the testing activity was completed, I shut down the database and started it again in mount mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

This step is required before converting the snapshot standby back to physical standby.

 

Step 9: Convert Snapshot Standby Back to Physical Standby

Now I converted the snapshot standby database back to physical standby.

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

After conversion, I verified the database role and open mode.

SELECT database_role, open_mode FROM v$database;

Expected output:

PHYSICAL STANDBY
MOUNTED

Then I started the database.

STARTUP;

After startup, I checked the open mode.

SELECT open_mode FROM v$database;

Expected output:

READ ONLY

Finally, I enabled managed recovery again.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

At this point, the standby database was converted back to physical standby, and redo apply was resumed.

 

Step 10: Validate That Snapshot Changes Were Removed

To confirm that the test changes were discarded, I checked the test table that was created during snapshot standby mode.

SELECT * FROM test_data;

Expected error:

ORA-00942: table or view does not exist

This confirmed that the changes performed during snapshot standby mode were successfully removed after conversion back to physical standby.

 

Validate Archive Synchronization Between Primary and Standby

After converting the database back to physical standby, I also validated archive synchronization from both primary and standby sides.

Check Last Sequence Generated on Primary

select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log ar, v$database db
where ar.resetlogs_change# = db.resetlogs_change#
group by thread#
order by 1;

Check Last Sequence Applied on Standby

select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log ar, v$database db
where ar.resetlogs_change# = db.resetlogs_change#
and ar.applied in (‘YES’,’IN-MEMORY’)
group by thread#
order by 1;