Converting Oracle Standby Database

Converting Oracle Standby Database: Physical to Snapshot and Back Using DataGuard Broker

Oracle Data Guard Administration | Snapshot Standby Database Management Oracle Data Guard provides the ability to convert a physical standby database to a snapshot standby database for testing, development, or validation purposes. This guide provides comprehensive procedures for converting between physical and snapshot standby modes using the DataGuard Broker (DGMGRL), ensuring seamless transitions while maintaining data protection capabilities.

Overview: Physical vs Snapshot Standby
Prerequisites and Considerations
Convert Physical Standby to Snapshot Standby
Convert Snapshot Standby Back to Physical
Validation and Verification
Best Practices and Recommendations

Overview: Physical vs Snapshot Standby

Oracle Data Guard supports multiple standby database types, each serving different operational requirements. Understanding the differences between physical standby and snapshot standby databases is essential for effective disaster recovery and database management strategies.

Physical Standby Database

Block-for-block copy of the primary databaseContinuously applies redo data from primary
Read-only access (with Active Data Guard)
Provides disaster recovery protection
Cannot be opened for read-write operations
Maintains synchronization with primary

Snapshot Standby Database

Fully updateable standby database
Receives but does not apply redo data
Open for read-write operations
Ideal for testing and development
Changes are discarded upon conversion back
Maintains redo log reception from primary

Key Concept: Flashback Database Requirement
Snapshot standby conversion relies on Oracle Flashback Database technology. When converting from physical to snapshot standby, a guaranteed restore point is automatically created. This restore point enables the database to discard all changes made during the snapshot period and return to synchronization with the primary database when converted back to physical standby.
Conversion Workflow Overview

Prerequisites and Considerations

Technical Requirements

Requirement	Description	Verification Command
Flashback Database	Must be enabled on the standby database	SELECT flashback_on FROM v$database;
Fast Recovery Area	Configured with adequate space for flashback logs	SHOW PARAMETER db_recovery_file_dest;
DataGuard Broker	Configuration must be enabled and healthy	SHOW CONFIGURATION;
Database Version	Oracle 11g Release 1 or later	SELECT version FROM v$instance;
Standby State	Physical standby must be mounted or applying redo	SELECT database_role FROM v$database;

Pre-Conversion Checklist

1. Verify Flashback Database Status

SQL*Plus - Standby Database 

-- Connect to standby database 
SQL> SELECT flashback_on, current_scn 
      FROM v$database; 

FLASHBACK_ON     CURRENT_SCN 
---------------- ----------- 
YES              1234567890

2. Check Fast Recovery Area Space

SQL*Plus - Standby Database 

SQL> SELECT 
          name, 
          space_limit/1024/1024/1024 AS limit_gb, 
          space_used/1024/1024/1024 AS used_gb, 
          space_reclaimable/1024/1024/1024 AS reclaimable_gb, 
          ROUND((space_used/space_limit)*100,2) AS pct_used 
      FROM v$recovery_file_dest; 

NAME                          LIMIT_GB    USED_GB  RECLAIMABLE_GB  PCT_USED 
----------------------------- ----------- -------- ---------------  
+FRA                          500         125.5    15.2            25.10

3. Verify DataGuard Broker Configuration

DGMGRL - Connect and Verify 

DGMGRL> CONNECT sys@primary_db 
Password: ******** 
Connected to "PRIMARY_DB" 
Connected as SYSDBA. 

DGMGRL> SHOW CONFIGURATION; 

Configuration - DG_CONFIG 

Protection Mode: MaxPerformance 
   Members: 
   PRIMARY_DB   - Primary database 
     STANDBY_DB - Physical standby database 

Fast-Start Failover:  Disabled 

Configuration Status: 
SUCCESS   (status updated 15 seconds ago)

Important Considerations

Ensure sufficient FRA space for flashback logs during the snapshot period
Plan for increased storage as redo logs accumulate without being applied
Document the snapshot duration to manage resource consumption
All changes made to snapshot standby will be permanently lost upon conversion back

Convert Physical Standby to Snapshot Standby

Converting a physical standby database to a snapshot standby enables read-write access for testing, development, or validation scenarios. The DataGuard Broker simplifies this process to a single command.

Conversion Procedure Using DGMGRL

1 Connect to DataGuard Broker

DGMGRL - Connect to Primary or Standby 

-- Start DGMGRL and connect 
$ dgmgrl 

DGMGRL> CONNECT sys@primary_db 
Password: ******** 
Connected to "PRIMARY_DB" 
Connected as SYSDBA.

2. Verify Current Standby Status

DGMGRL - Check Database Status 

DGMGRL> SHOW DATABASE STANDBY_DB; 

Database - STANDBY_DB 

Role:               PHYSICAL STANDBY 
   Intended State:     APPLY-ON 
   Transport Lag:      0 seconds (computed 1 second ago) 
   Apply Lag:          0 seconds (computed 1 second ago) 
   Average Apply Rate: 1.02 MByte/s 
   Real Time Query:    ON 
   Instance(s): 
     STANDBY_DB1 

Database Status: 
SUCCESS

3. Convert to Snapshot Standby

DGMGRL - Execute Conversion 

DGMGRL> CONVERT DATABASE STANDBY_DB TO SNAPSHOT STANDBY; 

Converting database "STANDBY_DB" to a Snapshot Standby database, please wait... 
Database "STANDBY_DB" converted successfully 

4 Verify Snapshot Standby Status 

DGMGRL - Confirm Conversion 

DGMGRL> SHOW DATABASE STANDBY_DB; 

Database - STANDBY_DB 

Role:               SNAPSHOT STANDBY 
   Intended State:     APPLY-OFF 
   Transport Lag:      0 seconds (computed 0 seconds ago) 
   Apply Lag:          (unknown) 
   Average Apply Rate: (unknown) 
   Real Time Query:    OFF 
   Instance(s): 
     STANDBY_DB1 

Database Warning(s): 
     ORA-16714: the value of property ArchiveLagTarget is inconsistent 
                with the requirement 

Database Status: 
WARNING 

Expected Database State After Conversion 

SQL> SELECT database_role, open_mode FROM v$database; 

DATABASE_ROLE    OPEN_MODE 
---------------- -------------------- 
SNAPSHOT STANDBY READ WRITE 

SQL> SELECT name, guarantee_flashback_database 
      FROM v$restore_point 
      WHERE guarantee_flashback_database = 'YES'; 

NAME                           GUA 
------------------------------ --- 
SNAPSHOT_STANDBY_REQUIRED_01   YES

Conversion Complete The standby database is now a snapshot standby and can accept read-write operations. A guaranteed restore point has been automatically created to enable conversion back to physical standby. Redo logs continue to be received from the primary but are not applied.

Convert Snapshot Standby Back to Physical

After completing testing or development activities on the snapshot standby, convert it back to a physical standby to resume redo apply and data protection. This process uses Flashback Database to discard all changes made during the snapshot period. Data Loss Warning All changes made to the snapshot standby database will be permanently discarded during conversion back to physical standby. Ensure all necessary data has been exported or backed up before proceeding. Conversion Procedure Using DGMGRL
1. Connect to DataGuard Broker

DGMGRL - Connect 

DGMGRL> CONNECT sys@primary_db 
Password: ******** 
Connected to "PRIMARY_DB" 
Connected as SYSDBA.

2. Verify Current Snapshot Status

DGMGRL - Check Current State 

DGMGRL> SHOW DATABASE STANDBY_DB; 

Database - STANDBY_DB 

Role:               SNAPSHOT STANDBY 
   Intended State:     APPLY-OFF 
   Transport Lag:      0 seconds (computed 2 seconds ago) 
   Apply Lag:          2 hours 15 minutes (computed 2 seconds ago) 
   Average Apply Rate: (unknown) 
   Real Time Query:    OFF 
   Instance(s): 
     STANDBY_DB1 

Database Status: 
WARNING

Convert to Physical Standby

DGMGRL - Execute Conversion 

DGMGRL> CONVERT DATABASE STANDBY_DB TO PHYSICAL STANDBY; 

Converting database "STANDBY_DB" to a Physical Standby database, please wait... 
Operation requires shut down of instance "STANDBY_DB1" on database "STANDBY_DB" 
Shutting down instance "STANDBY_DB1"... 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
Operation requires start up of instance "STANDBY_DB1" on database "STANDBY_DB" 
Starting instance "STANDBY_DB1"... 
ORACLE instance started. 
Database mounted. 
Continuing to convert database "STANDBY_DB" ... 
Database "STANDBY_DB" converted successfully 

4Verify Physical Standby Status 

DGMGRL - Confirm Conversion 

DGMGRL> SHOW DATABASE STANDBY_DB; 

Database - STANDBY_DB 

Role:               PHYSICAL STANDBY 
   Intended State:     APPLY-ON 
   Transport Lag:      0 seconds (computed 1 second ago) 
   Apply Lag:          3 minutes 22 seconds (computed 1 second ago) 
   Average Apply Rate: 2.15 MByte/s 
   Real Time Query:    ON 
   Instance(s): 
     STANDBY_DB1 

Database Status: 
SUCCESS 

5Monitor Redo Apply Progress 

SQL*Plus - Monitor Apply Progress 

SQL> SELECT 
          SEQUENCE#, 
          FIRST_TIME, 
          NEXT_TIME, 
          APPLIED 
      FROM v$archived_log 
      WHERE applied = 'NO' 
      ORDER BY SEQUENCE#; 

SEQUENCE# FIRST_TIME          NEXT_TIME           APPLIED 
--------- ------------------- ------------------- ------- 
      1245 11-JAN-26 10:15:00  11-JAN-26 10:30:00  NO 
      1246 11-JAN-26 10:30:00  11-JAN-26 10:45:00  NO 
      1247 11-JAN-26 10:45:00  11-JAN-26 11:00:00  NO 

-- Check apply lag reduction over time 
SQL> SELECT 
          name, 
          value, 
          datum_time 
      FROM v$dataguard_stats 
      WHERE name = 'apply lag'; 

NAME       VALUE               DATUM_TIME 
---------- ------------------- ------------------- 
apply lag  +00 00:01:15        11-JAN-26 11:02:30

Conversion Complete The database has been successfully converted back to physical standby. Redo apply has resumed and is processing the accumulated archive logs. Monitor the apply lag until synchronization is achieved.
Validation and Verification

Post-Conversion Validation Commands

DGMGRL - Comprehensive Status Check 

DGMGRL> SHOW CONFIGURATION; 

Configuration - DG_CONFIG 

Protection Mode: MaxPerformance 
   Members: 
   PRIMARY_DB   - Primary database 
     STANDBY_DB - Physical standby database 

Fast-Start Failover:  Disabled 

Configuration Status: 
SUCCESS   (status updated 5 seconds ago) 

DGMGRL> SHOW DATABASE STANDBY_DB; 

Database - STANDBY_DB 

Role:               PHYSICAL STANDBY 
   Intended State:     APPLY-ON 
   Transport Lag:      0 seconds (computed 1 second ago) 
   Apply Lag:          0 seconds (computed 1 second ago) 
   Average Apply Rate: 1.85 MByte/s 
   Real Time Query:    ON 
   Instance(s): 
     STANDBY_DB1 

Database Status: 
SUCCESS

Database Verification Queries

SQL*Plus - Verify Database State 

-- Verify database role and mode 
SQL> SELECT 
          database_role, 
          open_mode, 
          protection_mode, 
          protection_level, 
          switchover_status 
      FROM v$database; 

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS 
---------------- -------------------- -------------------- -------------------- ----------------- 
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED 

-- Verify MRP (Managed Recovery Process) is running 
SQL> SELECT process, status, thread#, sequence# 
      FROM v$managed_standby 
      WHERE process LIKE 'MRP%'; 

PROCESS   STATUS       THREAD#  SEQUENCE# 
--------- ------------ -------- --------- 
MRP0      APPLYING_LOG        1      1250 

-- Verify restore points are cleaned up 
SQL> SELECT name, guarantee_flashback_database, time 
      FROM v$restore_point; 

no rows selected

Validation Checklist

Validation Item	Expected Result	Status
Database Role	PHYSICAL STANDBY	Verify with v$database
Redo Apply Status	APPLY-ON / MRP Running	Verify with v$managed_standby
Transport Lag	0 seconds (or minimal)	Verify with SHOW DATABASE
Apply Lag	Decreasing to 0 seconds	Verify with v$dataguard_stats
Configuration Status	SUCCESS	Verify with SHOW CONFIGURATION
Restore Points	Cleaned up (no snapshot restore points)	Verify with v$restore_point

Best Practices and Recommendations

Operational Guidelines

Plan Snapshot Duration: Minimize the time in snapshot mode to reduce FRA space consumption and redo log accumulation
Monitor FRA Usage: Continuously monitor Fast Recovery Area utilization during snapshot period
Document Changes: Keep detailed records of all modifications made during snapshot mode for reference
Test Conversion: Perform regular conversion tests to validate procedures and timing
Coordinate with Teams: Notify relevant teams before and after conversions to manage expectations

Performance Considerations Redo Log Accumulation While in snapshot standby mode, redo logs continue to be received and archived but not applied. Plan for increased storage requirements and longer synchronization times when converting back to physical standby. The longer the snapshot period, the more time required to apply accumulated redo logs.

Common Use Cases

Use Case	Description	Typical Duration
Application Testing	Test application upgrades or patches with production data	Hours to days
Report Generation	Run resource-intensive reports without impacting production	Hours
Data Validation	Validate data integrity or run diagnostic queries	Minutes to hours
Training Environment	Provide realistic training environment with production data	Days
Disaster Recovery Testing	Validate DR procedures and application failover	Hours

Troubleshooting Common Issues
Issue: Conversion Fails Due to Insufficient FRA Space
Symptom :ORA-38706: Cannot turn on FLASHBACK DATABASE logging
Resolution :Increase db_recovery_file_dest_size or free up space in FRA by removing obsolete backups and archive logs.
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 600G SCOPE=BOTH;
Issue: MRP Not Starting After Conversion
Symptom :Apply lag not decreasing, MRP process not visible
Resolution :Manually start redo apply if automatic startup fails.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Summary
Converting between physical and snapshot standby modes using DataGuard Broker provides a straightforward method for enabling testing and development activities while maintaining disaster recovery capabilities. The single-command approach simplifies operations, and Flashback Database technology ensures clean transitions. Always monitor FRA usage, plan conversion windows appropriately, and validate synchronization status after returning to physical standby mode.