LogiUpSkill

Menu

+91 996 011 2296

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.

Table of Contents
  • 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.
Converting Oracle Standby Database