Opening a DR Database in READ WRITE Mode in Oracle
Complete Step-by-Step Guide with Warnings & DR Flow Diagram
In an Oracle Disaster Recovery (DR) setup using Data Guard, the standby database is designed to protect the business from outages—not to be casually opened in READ WRITE mode.
Opening a DR database in READ WRITE mode is a critical operation that effectively promotes the standby to a new primary. This blog explains when, why, and how to do it correctly, with required checks, warnings, and a clear DR activation flow.
Understanding the DR Standby Database
- Primary database runs in READ WRITE
- DR database runs as PHYSICAL STANDBY
- Redo is shipped and applied continuously
- Standby remains in MOUNT or READ ONLY mode
Note: A physical standby cannot be opened READ WRITE unless it is activated.
When Should DR Be Opened in READ WRITE Mode?
- Actual primary database outage
- Declared Disaster Recovery event
- Approved failover / cutover
- Business-approved DR drill (with rebuild planned)
DR Activation Flow Diagram (Primary → Standby → New Primary)
PRIMARY DATABASE (Production)
|
| Redo Transport
v
PHYSICAL STANDBY (DR)
(MOUNT / READ ONLY)
|
| ALTER DATABASE ACTIVATE STANDBY DATABASE
v
NEW PRIMARY DATABASE
(READ WRITE)
Step 1: Confirm You Are on the DR Database
Verify database role before making any changes:
SELECT name, open_mode, database_role FROM v$database;
Expected output:
DATABASE_ROLE = PHYSICAL STANDBY OPEN_MODE = MOUNTED or READ ONLY
Step 2: Verify DR Is Fully in Sync (BEFORE Activation)
Synchronization checks must be done before activation.
Check redo apply status
SELECT process, status FROM v$managed_standby;
Ensure MRP0 is applying redo.
Check transport and apply lag
SELECT name, value, unit
FROM v$dataguard_stats
WHERE name IN ('transport lag','apply lag');
Ideal result:
transport lag = 0 seconds apply lag = 0 seconds
Step 3: Stop Managed Recovery
Redo apply must be stopped explicitly:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
🚨 Critical Warning: Point-of-No-Return Command
⚠️ EXTREME CAUTION REQUIRED
ALTER DATABASE ACTIVATE STANDBY DATABASE;
This command permanently converts the standby database into a PRIMARY. After execution:
- Data Guard is irreversibly broken
- Redo shipping cannot resume
- Synchronization checks no longer apply
- Restore points cannot revert the role
- Flashback cannot recreate a standby
- A full standby rebuild is mandatory
There is NO rollback for this command. Execute only with business approval and confirmed DR conditions.
Step 4: Activate the Standby Database
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Step 5: Restart the Database
SHUTDOWN IMMEDIATE; STARTUP;
Step 6: Open the Database in READ WRITE Mode
ALTER DATABASE OPEN;
Verify final status:
SELECT name, open_mode, database_role FROM v$database;
Expected:
DATABASE_ROLE = PRIMARY OPEN_MODE = READ WRITE
Important Note: No Sync or Restore Point Checks After Activation
After activation, the database is no longer a standby. There is no primary–standby relationship, so synchronization queries are not meaningful. Restore points cannot restore Data Guard and Flashback cannot revert the role. To restore DR protection, you must rebuild a new standby database.
Post-Activation Validation Checks
SELECT instance_name, status FROM v$instance; SELECT switchover_status FROM v$database; SHOW PARAMETER db_unique_name;
Key Takeaways
- Physical standby cannot open READ WRITE without activation
- ACTIVATE STANDBY DATABASE is a one-way command
- Complete all sync checks before activation
- After DR usage, rebuild standby to restore DR protection
Final Thoughts
Opening a DR database in READ WRITE mode is not just a technical task—it’s a business decision. Treat DR activation as a controlled emergency, not a routine command.
