Oracle Data Guard – Converting Physical Standby Database to Snapshot Standby Database Step-by-Step Implementation Guide

 


1. Introduction

Oracle Data Guard is widely used in enterprise environments to provide high availability, disaster recovery, and data protection for critical databases. In a typical Data Guard configuration, a Physical Standby database continuously applies redo logs received from the primary database to maintain an exact replica of the production system.

However, organizations often require a temporary environment for testing, patch validation, application testing, or reporting workloads without impacting the production system.

Oracle provides a feature called Snapshot Standby Database, which allows administrators to temporarily convert a physical standby database into a fully read-write database. This allows testing activities to be performed safely while the primary database continues normal operations.

Once testing is complete, the snapshot standby database can be reverted back to a physical standby database, and the standby will automatically synchronize with the primary database using redo logs that were received during the snapshot period.

This document demonstrates the complete implementation of converting a Physical Standby database to Snapshot Standby and then converting it back to Physical Standby in an Oracle 19c Data Guard environment.

2. Architecture Overview

The architecture used in this implementation consists of a Primary Database and a Physical Standby Database configured using Oracle Data Guard.

Primary Database

The primary database handles production workloads and generates redo logs.

Physical Standby Database

The standby database receives redo logs from the primary database and applies them using Managed Recovery Process (MRP).

Snapshot Standby Database

When converted to snapshot standby:

  • The standby database becomes read-write

  • Redo logs from the primary database continue to be received

  • Redo logs are not applied until the database is converted back to physical standby

Key components involved in the architecture include:

  • Primary Database

  • Physical Standby Database

  • Snapshot Standby Database

  • Oracle Data Guard Redo Transport

  • Flashback Database

  • Guaranteed Restore Point

  • Managed Recovery Process (MRP)

ComponentDetails
Database Version                       Oracle Database 19c Enterprise Edition (19.0.0.0)
Primary Database Role                       PRIMARY
Standby Database Role                                      PHYSICAL STANDBY
Operating System                       Windows 11 Pro
Replication Technology                       Oracle Data Guard
Conversion Target                       Snapshot Standby Database



3. Overview of Snapshot Standby Implementation

The Snapshot Standby feature allows a physical standby database to be temporarily converted into a fully updatable database.

This feature is extremely useful for:

  • Application testing

  • Patch validation

  • Development testing

  • Reporting workloads

  • Data validation exercises

The implementation process involves the following major steps:

1. Synchronization Check

Verify that the primary and standby databases are fully synchronized.

2. Stop Managed Recovery

Stop the MRP process on the standby database before conversion.

3. Enable Flashback Database

Flashback must be enabled because Oracle uses guaranteed restore points to revert the database back to the physical standby state.

4. Convert Physical Standby to Snapshot Standby

Use the command:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

This command automatically creates a guaranteed restore point.

5. Open the Snapshot Standby Database

The database can now be opened in read-write mode, allowing testing activities.

6. Perform Testing

During this phase:

  • Tablespaces can be created

  • Data can be modified

  • Application testing can be performed

All changes remain temporary.

7. Convert Snapshot Standby Back to Physical Standby

Once testing is completed, the database can be converted back using:

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Oracle automatically uses Flashback Database to revert all temporary changes and synchronize with the primary database.


4. Implementations

Step1: Ensure your primary and standby database are in sync.

Primary:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE  DATABASE_ROLE PROTECTION_MODE     CONTROL
---- -------------- ---------- ------------- ------------------- -------
PROD PROD           READ WRITE PRIMARY       MAXIMUM PERFORMANCE CURRENT

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           370
Standby:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL
---- -------------- --------- ---------------- ------------------- -------
PROD PRODSTBY       READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
           370


Step 2: You can convert physical standby database into snapshot standby while your physical standby is in either mounted mode or in read only mode. 

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2147482432 bytes
Fixed Size                  9030464 bytes
Variable Size             452984832 bytes
Database Buffers         1677721600 bytes
Redo Buffers                7745536 bytes
Database mounted.

OR

SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL
---- -------------- --------- ---------------- ------------------- -------
PROD PRODSTBY       READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY




Step 3: Set below parameters to generate flashback logs. Set db_recovery_file_dest_size first and then db_recovery_file_dest parameters. 

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

SQL> alter system set db_recovery_file_dest='D:\KHAN\Setups\Standby\FLB';

System altered.

SQL> show parameter db_recovery

NAME                         TYPE        VALUE
---------------------------- ----------- ------------------------------
db_recovery_file_dest        string      D:\KHAN\Setups\Standby\FLB
db_recovery_file_dest_size   big integer 2G




Step 4: Now enable the flashback in physical standby database. To turn on the flashback, MRP must be down. You can convert the snapshot standby database without enabling flashback as well as the convert command will automatically enable flashback with status "RESTORE POINT ONLY" which means Flashback is on but you can only flashback to guaranteed restore points. 

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select name from v$restore_point;

no rows selected


Step 5: Now its time to convert physical standby database into snapshot standby database.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> col name for a40
SQL> col TIME for a34
SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE,TIME,NAME from v$restore_point;

SCN      GUA STORAGE_SIZE TIME                          NAME
-------- --- ------------ -------------------------------  ---------------------------------------------
10179700 YES 209715200     20-AUG-23 04.14.53.000000000 PM   SNAPSHOT_STANDBY_REQUIRED_08/20/2023 16:14:53   

Physical Standby Alert Log File:
2023-08-20T16:14:53.435650+05:30
alter database convert to snapshot standby
2023-08-20T16:14:53.609248+05:30
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_08/20/2023 16:14:53
.... (PID:13588): Killing 2 processes (PIDS:7348,12632) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 13588
2023-08-20T16:14:54.988602+05:30
.... (PID:13588): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3243]
.... (PID:13588): Begin: SRL archival
.... (PID:13588): End: SRL archival
RESETLOGS after incomplete recovery UNTIL CHANGE 10179701 time 08/20/2023 16:07:58
Resetting resetlogs activation ID 2835307338 (0xa8ff5b4a)
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO01.LOG: Thread 1 Group 1 was previously cleared
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO02.LOG: Thread 1 Group 2 was previously cleared
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO03.LOG: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 10179699
2023-08-20T16:14:55.478836+05:30
Setting recovery target incarnation to 3
2023-08-20T16:14:55.542745+05:30
.... (PID:13588): Redo network throttle feature is disabled at mount time
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
2023-08-20T16:15:24.888621+05:30

Now check the database status and open the database.

SQL> select name,open_mode,database_role,controlfile_type from v$database;

NAME    OPEN_MODE   DATABASE_ROLE    CONTROL
------- ----------- ---------------- -------
PROD    MOUNTED     SNAPSHOT STANDBY CURRENT

Note that once you converted the physical standby database into snapshot and you want to go back to physical standby immediately without opening the database into read-write, you must open the database into read-write mode first, otherwise you will face below error message.

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.


SQL> alter database open;

Database altered.

Physical Standby Alert Log File:
2023-08-20T16:18:29.373019+05:30
alter database open
2023-08-20T16:18:29.404271+05:30
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
Endian type of dictionary set to little
2023-08-20T16:18:29.591755+05:30
Assigning activation ID 2839889992 (0xa9454848)
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\KHAN\SETUPS\STANDBY\DB\DR\REDO01.LOG
Successful open of redo thread 1
2023-08-20T16:18:29.701147+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR failed: start:64382421 end:64382437 diff:16 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 64382421 end: 64382468 diff: 47 ms (0.0 seconds)
[13588] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 64382484 end: 64382562 diff: 78 ms (0.1 seconds)
Undo initialization finished serial:0 start:64382421 end:64382578 diff:157 ms (0.2 seconds)
Dictionary check beginning
Dictionary check complete
Database Characterset is AL32UTF8
2023-08-20T16:18:30.375392+05:30
No Resource Manager plan active
2023-08-20T16:18:30.953780+05:30
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
joxcsys_required_dirobj_exists: directory object exists with required path D:\RUPESH\SETUPS\WINDOWS.X64_193000_DB_HOME\JAVAVM\ADMIN\, pid 3392 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
2023-08-20T16:18:31.834112+05:30
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2023-08-20T16:18:31.942898+05:30
AQPC started with pid=40, OS id=18668 
Starting background process CJQ0
2023-08-20T16:18:32.445918+05:30
CJQ0 started with pid=42, OS id=4744 
2023-08-20T16:18:33.456076+05:30
Completed: alter database open

Check the database status again.

SQL>  select name,open_mode,database_role,controlfile_type from v$database;

NAME OPEN_MODE    DATABASE_ROLE    CONTROL
---- ------------ ---------------- -------
PROD READ WRITE   SNAPSHOT STANDBY CURRENT



Step 6: You can perform read-write testing now on Snapshot Standby database. During the testing, if you want to create new tablespace or add any data file then you can do the same which will be dropped/deleted or discarded before converting back into physical standby database once you execute "alter database convert to physical standby" command.



Step 7: Once all testing is done then refer below steps to convert the snapshot standby database back into physical standby database.

Note that your database must be in mounted mode to convert back into physical standby database otherwise you will face below error message.

NAME DB_UNIQUE_NAME OPEN_MODE  DATABASE_ROLE    PROTECTION_MODE    CONTROL
---- -------------- ---------- ---------------- ------------------- -------
PROD PRODSTBY       READ WRITE SNAPSHOT STANDBY MAXIMUM PERFORMANCE CURRENT

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

Shut down the snapshot standby database first and start it in mount mode.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2147482432 bytes
Fixed Size                  9030464 bytes
Variable Size             452984832 bytes
Database Buffers         1677721600 bytes
Redo Buffers                7745536 bytes
Database mounted.

SQL> alter database convert to physical standby;

Database altered.

Standby Alert Log File:
2023-08-20T16:33:12.298520+05:30
alter database convert to physical standby
2023-08-20T16:33:12.558173+05:30
Flashback Restore Start
2023-08-20T16:33:13.310460+05:30
Deleted file D:\KHAN\SETUPS\STANDBY\DB\DR\USERS02.DBF
Flashback: deleted datafile #2 in tablespace #4 of pdb #0 from control file.
Deleted file D:\KHAN\SETUPS\STANDBY\DB\DR\TEST_FLB01.DBF
Flashback: deleted datafile #5 in tablespace #6 of pdb #0 from control file.
Flashback: dropped tablespace #6: 'TEST_FLB' of pdb #0 from the control file.
2023-08-20T16:33:13.624656+05:30
Flashback Restore Complete
2023-08-20T16:33:14.403434+05:30
Drop guaranteed restore point 
Guaranteed restore point  dropped
2023-08-20T16:33:14.485667+05:30
.... (PID:16464): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8969]
Clearing standby activation ID 2839889992 (0xa9454848)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
.... (PID:16464): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8974]
.... (PID:16464): RT: Role transition work is not done
.... (PID:16464): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2023-08-20T16:33:15.063275+05:30
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
2023-08-20T16:33:54.724299+05:30

You can see in the above logs that newly created tablespace and data files are dropped from snapshot standby database.

Turn the flashback off to delete flashback logs to free up the FRA space. The recover file destination parameters will be reset after converting to physical standby database.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> select name from v$restore_point;

no rows selected

SQL>  alter database flashback off;

Database altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

You can start the MRP process and monitor the gap between Primary and Standby database.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

Standby Alert Log File:
2023-08-20T16:43:03.054049+05:30
 alter database flashback off
Stopping background process RVWR
2023-08-20T16:43:04.079890+05:30
Deleted Oracle managed file D:\KHAN\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LG3V8BOZ_.FLB
Deleted Oracle managed file D:\KHAN\SETUPS\STANDBY\FLB\DR\FLASHBACK\O1_MF_LG3V8CKV_.FLB
Flashback Database Disabled 
Completed:  alter database flashback off
2023-08-20T16:43:11.942341+05:30
ALTER DATABASE RECOVER  managed standby database disconnect from session  
Starting background process MRP0
2023-08-20T16:43:12.005824+05:30
MRP0 started with pid=30, OS id=10128 
2023-08-20T16:43:17.069644+05:30
 Started logmerger process
2023-08-20T16:43:17.101037+05:30
PR00 (PID:14820): Managed Standby Recovery starting Real Time Apply
2023-08-20T16:43:17.447203+05:30
Parallel Media Recovery started with 8 slaves
2023-08-20T16:43:17.479029+05:30
Stopping change tracking
2023-08-20T16:43:17.542208+05:30
TT02 (PID:6992): Waiting for all non-current ORLs to be archived
2023-08-20T16:43:17.557223+05:30
TT02 (PID:6992): All non-current ORLs have been archived
TT02 (PID:6992): Clearing ORL LNO:1 D:\KHAN\SETUPS\STANDBY\DB\DR\REDO01.LOG
Clearing online log 1 of thread 1 sequence number 1
2023-08-20T16:43:17.732264+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_374_1134494132.ARC
2023-08-20T16:43:18.048409+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_375_1134494132.ARC
2023-08-20T16:43:18.095970+05:30
TT02 (PID:6992): Clearing ORL LNO:1 complete
2023-08-20T16:43:18.255486+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_376_1134494132.ARC
2023-08-20T16:43:18.446231+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_377_1134494132.ARC
2023-08-20T16:43:18.684156+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_378_1134494132.ARC
2023-08-20T16:43:18.842589+05:30
PR00 (PID:14820): Media Recovery Log D:\KHAN\SETUPS\STANDBY\ARCH\1_379_1134494132.ARC
PR00 (PID:14820): Media Recovery Waiting for T-1.S-380 (in transit)
2023-08-20T16:43:19.030523+05:30
Recovery of Online Redo Log: Thread 1 Group 4 Seq 380 Reading mem 0
  Mem# 0: D:\RUPESH\SETUPS\STANDBY\SRLS\STANDBY01.LOG
2023-08-20T16:43:19.046167+05:30
Completed: ALTER DATABASE RECOVER  managed standby database disconnect from session  
2023-08-20T16:43:23.205912+05:30
TT02 (PID:6992): Waiting for all non-current ORLs to be archived
2023-08-20T16:43:23.238704+05:30
TT02 (PID:6992): All non-current ORLs have been archived

On Primary:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL
---- -------------- --------- ---------------- ------------------- -------
PROD PROD           MOUNTED   PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           379

On Standby:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE    PROTECTION_MODE     CONTROL
---- -------------- --------- ---------------- ------------------- -------
PROD PRODSTBY       READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
           379


5. Conclusion

Snapshot Standby databases provide a powerful capability within Oracle Data Guard environments by allowing administrators to temporarily convert a standby database into a fully functional read-write database for testing and validation purposes.

This implementation demonstrated the process of converting a Physical Standby database to Snapshot Standby, performing testing operations, and safely converting the database back to Physical Standby mode while maintaining synchronization with the primary database.

By leveraging Snapshot Standby functionality, organizations can perform application testing, patch validation, and development activities without impacting the production environment, making it a valuable feature for enterprise database management.


6. Risks and Mitigation

Risk 1 – Insufficient FRA Space

Snapshot standby requires Flashback logs which consume space in the Fast Recovery Area (FRA).

Mitigation
Ensure adequate FRA space before enabling flashback.


Risk 2 – Long Snapshot Duration

If snapshot standby remains in read-write mode for a long time, redo backlog may accumulate.

Mitigation
Limit snapshot standby usage to controlled testing windows.


Risk 3 – Data Synchronization Delay

Large testing workloads may delay synchronization when converting back to physical standby.

Mitigation
Monitor redo apply progress and archive log transport.


Risk 4 – Flashback Failure

If flashback logs are deleted or corrupted, reverting to physical standby may fail.

Mitigation
Ensure flashback database is enabled and FRA storage is monitored.