Introduction
Convert Physical Standby to Snapshot Standby using DGMGRL is one of the most powerful features available in Oracle Data Guard 19c.
Originally introduced in 11g and enhanced in 19c, this capability allows DBAs to temporarily open a standby database in read-write mode for patch testing, validation, or development — all while maintaining Data Guard synchronization.
This feature is incredibly useful when you need to perform DML, DDL, or schema updates on a standby system for testing without impacting the production (primary) database. Once you switch it back to physical standby, Oracle automatically discards any temporary changes and resumes redo apply — powered by the Flashback Database feature.
In this article, you’ll learn step-by-step how to convert a physical standby into a snapshot standby using Oracle’s DGMGRL utility, verify the configuration, and revert it safely back to physical standby mode.
Before converting a standby, you must have Data Guard configured. If you need help setting up a physical standby first, see our guide on Oracle Data Guard Physical Standby Setup
What Is a Snapshot Standby Database?
A Snapshot Standby Database is a fully updatable standby created from an existing physical standby database.
In Oracle 19c, this feature combines Data Guard and Flashback Database technology to provide a flexible testing environment without compromising data protection.
Here’s how it works:
- The snapshot standby opens in READ WRITE mode.
- It continues to receive redo logs from the primary database but does not apply them.
- When converted back to Physical Standby, Oracle flashes back the database to the restore point and resumes redo apply.
This ensures the standby remains synchronized with the primary while offering temporary write access.
Why Use a Snapshot Standby in Oracle 19c?
Common scenarios for snapshot standby include:
- Application testing on near-real production data.
- Database patch testing and validation.
- Testing upgrades or schema changes without affecting primary.
- Development or reporting environments that require write operations.
Snapshot standby databases are faster to deploy than full clones and can be safely reverted to physical standby at any time.
Environment Details
For this demonstration:
- Oracle Database Version: 19c
- Primary Database:
prod - Standby Database:
stby - Data Guard Configuration:
prod_stby - Protection Mode: MaxPerformance
Step 1 – View the Current Data Guard Configuration
Let’s begin by checking the existing configuration in DGMGRL (Data Guard Manager CLI).
DGMGRL> SHOW CONFIGURATION;
Configuration – prod_stby
Protection Mode: MaxPerformance
Members:
prod – Primary database
stby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 20 seconds ago)
Observation:
At this point, prime is the primary database and stby is the physical standby.
Step 2 – Convert the Physical Standby to Snapshot Standby
Use the following DGMGRL command to perform the conversion:
DGMGRL> CONVERT DATABASE 'stby' TO SNAPSHOT STANDBY;
Output:
Converting database "stb" to a Snapshot Standby database, please wait…
Database "stby" convertyed successfully
Result:
The standby database stbyby is now running in Snapshot Standby mode and can be opened for read-write operations.
Step 3 – Verify the New Configuration
Confirm that the role change was successful:
DGMGRL> SHOW CONFIGURATION;
Configuration – prod_stby
Protection Mode: MaxPerformance
Members:
prod – Primary database
stby – Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 25 seconds ago)
Observation:
The standby is now a Snapshot Standby.
You can connect to it and perform read-write transactions, DDLs, or test scripts without impacting the primary.
Step 4 – Revert the Snapshot Standby Back to Physical Standby
Once testing is complete, it’s time to switch it back to its original role.
DGMGRL> CONVERT DATABASE 'stby' TO PHYSICAL STANDBY;
Output:
Converting database “stby” to a Physical Standby database, please wait…
Operation requires shut down of instance “stby” on database “stby”
Shutting down instance “stby”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance “stand” on database “stby”
Starting instance “stand”…
ORACLE instance started.
Database mounted.
Continuing to convert database “stby” …
Database “stby” converted successfully
Result:
Oracle automatically shuts down, mounts, and converts the database back to physical standby mode, then resumes the Data Guard redo apply.
Step 5 – Verify the Final Configuration
Finally, check the configuration again to ensure everything is back to normal.
DGMGRL> SHOW CONFIGURATION;
Configuration – prod_stby
Protection Mode: MaxPerformance
Members:
prod – Primary database
stby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 48 seconds ago)
Observation:
The stby database has successfully reverted to Physical Standby.
Redo apply resumes automatically, and all temporary changes made during snapshot mode are discarded.
What Happens Internally
When you convert a physical standby to a snapshot standby, Oracle uses Flashback Database to create a restore point.
All changes made while in read-write mode are stored separately.
When you revert back to physical standby, Oracle uses that restore point to flashback the database to the state it was in before conversion, then applies the pending redo logs to catch up with the primary.
This ensures that your standby remains fully consistent and protected, even after multiple snapshot conversions.
Common Use Cases
- Pre-production testing: Safely validate application updates using near-live data.
- Patch and upgrade validation: Test database patches without impacting your HA setup.
- Training and demonstrations: Run updates on real data without risking production corruption.
Best Practices
- Ensure Flashback Database is enabled on the standby before converting.
- Avoid keeping the snapshot standby open for too long — the redo backlog can grow significantly.
- Always monitor redo transport status using
V$ARCHIVE_DEST_STATUS. - Use
DGMGRL SHOW CONFIGURATIONandSHOW DATABASEfrequently to verify status. - After reverting, validate synchronization using
V$DATAGUARD_STATS.
Key Benefits of Using DGMGRL for Conversion
- Simplifies the entire process — no manual SQL required.
- Ensures proper state transitions with built-in checks.
- Automatically handles instance shutdown/startup during conversion.
- Provides clean rollback capability via Flashback restore points.
Conclusion
Converting a Physical Standby to a Snapshot Standby using DGMGRL in Oracle 12c is an elegant and safe way to perform testing or updates on a near-production copy of your data.
This approach eliminates the need for separate clone environments while maintaining full data protection through Data Guard’s redo transport and flashback integration.
With just a few commands, you can seamlessly switch between Physical and Snapshot standby modes — empowering DBAs to test confidently and revert safely.
For more detailed information and official Oracle guidance, refer to the Oracle documentation:
👉 Oracle 19c – Managing Physical Standby Databases (Official Guide)
