Oracle Data Guard: Features, Configuration & Best Practices
Oracle Data Guard is a powerful feature for database high availability, disaster recovery, and data protection. This guide provides a detailed walkthrough of its features, types of standby databases, processes involved, configuration steps, and key commands. By the end of this post, you’ll have a deep understanding of Oracle Data Guard and how to manage it efficiently.
Main Uses of Standby Databases
| Use Case | Description |
|---|---|
| High Availability | Ensures continuous availability of the database. |
| Data Protection | Safeguards data through redundancy. |
| Disaster Recovery | Provides recovery options during catastrophic events. |
| Backup Management | Facilitates backups from standby databases. |
| Reporting | Offloads reporting tasks to standby databases. |
Types of Standby Databases
| Type | Description |
|---|---|
| Physical Standby | – Exact block-for-block copy of the primary database. – Maintained using REDO-APPLY. |
| Logical Standby | – Converts data from the primary into SQL statements. – Maintained using SQL-APPLY (LSP process applies redo). |
| Snapshot Standby | – A fully updatable standby database converted from a physical standby. |
| Cascaded Standby | – Receives redo logs from another standby to reduce primary system load. |
| Active Data Guard | – Physical standby in read-only mode while applying redo. – Enables reporting and RMAN backups on standby, offloading production load. |
| Transient Logical Standby | – Supports rolling database upgrades by temporarily converting a physical standby to a logical standby. |
Redo Transport Services
| Type | Description |
|---|---|
| Synchronous | – Guarantees Zero Data Loss. – Primary commits changes only after LNS confirms redo write on standby. |
| Asynchronous | – Does not guarantee Zero Data Loss. – LGWR does not wait for LNS confirmation. |
Apply Services
| Type | Description |
|---|---|
| Real-time Apply | – Used in physical standby databases. – Applies changes as they are received without waiting for archiving. |
| SQL Apply | – Converts redo data into SQL statements and applies them on a logical standby database. |
Role Transitions
| Type | Description |
|---|---|
| Switchover | – Switches roles between primary and standby. – No data loss occurs. |
| Failover | – Promotes standby to primary in case of primary failure. – Risk of data loss if in MAXIMUM performance mode. |
Affirm and Noaffirm
| Type | Description |
|---|---|
| Affirm | – Ensures redo is written to the standby’s disks. |
| Noaffirm | – Ensures redo is received by one of the standby databases. |
Protection Modes
| Mode | Transport Method | Description |
|---|---|---|
| Maximum Protection | SYNC / LGWR | – Guarantees Zero Data Loss. – Shuts down primary if redo cannot be written to standby. |
| Maximum Availability | SYNC / LGWR | – Operates like MAXIMUM PROTECTION but allows primary to continue if standby synchronization fails temporarily. |
| Maximum Performance | ASYNC / LGWR / ARCH | – Focuses on performance, with minimal data loss in normal operation. |
Physical Standby Database Related Processes
| Database | Process | Description |
|---|---|---|
| Primary Database | LGWR | Log Writer flushes log buffers from the SGA to Online Redo Log files. |
| LNS | LogWriter Network Service reads redo flushed by LGWR and sends it over the network to the standby. | |
| ARCH | Archives Online Redo Log (ORL) files to archive log files. | |
| Standby Database | RFS | Remote File Server receives redo from the primary, writes it to Standby Redo Log (SRL) files. |
| ARCH | Archives SRL files into archived log files, similar to primary database. | |
| MRP | Managed Recovery Process coordinates media recovery management. |
Data Guard Broker Configuration
| Step | Description |
|---|---|
| Purpose | Provides unified administration for Data Guard, including switchover and failover. |
| Configuration Files | Maintains information in flat files, with redundant copies stored on each database in the Data Guard configuration. |
| Parameters | Controlled by: – DG_BROKER_CONFIG_FILE_1– DG_BROKER_CONFIG_FILE_2. |
Configuration Steps
| Step | Commands/Actions |
|---|---|
| 1. Configure Parameters | Primary: – ALTER SYSTEM SET dg_broker_config_file_1='path';– ALTER SYSTEM SET dg_broker_config_file_2='path';– ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;– ALTER SYSTEM SET local_listener='';Standby: Repeat the same steps on the standby database. |
| 2. Verify TNS | Ensure connectivity between the primary (tns_prod) and standby (tns_stby). |
| 3. Configure DGMGRL | Commands: – dgmgrl– CONNECT sys/password@tns_prod– CONNECT sys/password@tns_stby– SHOW CONFIGURATION |
| 4. Create Configuration | Primary:CREATE CONFIGURATION 'PROD' AS PRIMARY DATABASE IS 'PROD_DB' CONNECT IDENTIFIER IS 'tns_prod';Ensure LOG_ARCHIVE_DEST_2 is empty. |
| 5. Add Standby | ADD DATABASE 'DR' AS CONNECT IDENTIFIER IS 'DR'; |
| 6. Enable Configuration | ENABLE CONFIGURATION; |
Switchover and Switchback Commands Using DGMGRL
| Action | Command |
|---|---|
| Perform Switchover to Standby | SWITCHOVER TO DR; |
| Convert Standby to Snapshot Standby | CONVERT DATABASE 'DR' TO SNAPSHOT STANDBY; |
| Convert Back to Physical Standby | CONVERT DATABASE 'DR' TO PHYSICAL STANDBY; |
Useful Commands to Show Data Guard Configuration
| Command | Description |
|---|---|
SHOW CONFIGURATION; | Displays the overall Data Guard configuration. |
SHOW CONFIGURATION VERBOSE; | Provides detailed information about the configuration. |
SHOW DATABASE 'PRIM'; | Displays details of the primary database. |
SHOW DATABASE VERBOSE 'PRIM'; | Provides detailed information about the primary database. |
VALIDATE DATABASE 'PRIM'; | Validates the primary database setup. |
VALIDATE DATABASE VERBOSE 'PRIM'; | Provides detailed validation output for the primary database. |
SHOW DATABASE 'PRIM' SENDQENTRIES; | Shows the send queue entries for the primary database. |
SHOW DATABASE 'DR' RECVQENTRIES; | Shows the receive queue entries for the standby database. |
VALIDATE NETWORK CONFIGURATION FOR ALL; | Validates the network configuration for all databases |
Edit Database Properties
| Action | Command |
|---|---|
| Disable Apply Mode | EDIT DATABASE 'DR' SET STATE='APPLY-OFF'; |
| Disable Transport Mode | EDIT DATABASE 'DR' SET STATE='TRANSPORT-OFF'; |
| Enable Apply Mode | EDIT DATABASE 'DR' SET STATE='APPLY-ON'; |
| Enable Transport Mode | EDIT DATABASE 'DR' SET STATE='TRANSPORT-ON'; |
Change Protection Mode
| Action | Command |
|---|---|
| Change Log Transport to Synchronous | EDIT DATABASE 'chennai' SET PROPERTY 'LogXptMode'='SYNC'; |
EDIT DATABASE 'delhi' SET PROPERTY 'LogXptMode'='SYNC'; | |
| Set Protection Mode to Max Availability | EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY; |
Enable Fast-Start Failover
| Step | Command |
|---|---|
| Ensure Protection Mode | Set to MAX AVAILABILITY. |
| Enable Fast-Start Failover | ENABLE FAST_START FAILOVER; |
| Show Fast-Start Failover Status | SHOW FAST_START FAILOVER; |
| Start Observer | nohup dgmgrl sys/oracle@orcl "start observer file='/home/oracle/fsfo.dat'" -logfile $HOME/observer.log & |