Logging Options in Oracle for Data Guard
When preparing a primary Oracle Database for use with Oracle Data Guard, enabling the correct logging mode is critical to ensure data integrity and that all changes are shipped to standby databases correctly. The default logging behavior in a standalone database is not suitable for a Data Guard configuration because certain data-load operations can be performed without writing to redo logs, which causes missing data on the standby.
Why Logging Mode Matters
In Oracle, redo logs are the source of truth for Data Guard replication. Without proper logging:
• NOLOGGING operations on the primary may not produce redo records.
• Standbys will miss these changes unless corrected manually.
• This can compromise failover and recovery operations.
To avoid this, Oracle provides specific logging modes that control how changes are recorded.
Available Logging Modes for Data Guard
You must set the logging mode on the primary database to one of the following, depending on your Data Guard strategy:
1. FORCE LOGGING
Ensures all database changes are logged, regardless of whether an operation uses the NOLOGGINGattribute.
• Prevents unlogged operations that could lead to missing redo.
• Ideal for maximum data protection environments.
• Can impact performance for bulk loads since redo is always generated.
ALTER DATABASE FORCE LOGGING;
The database must be at least mounted, and this operation can take time as it waits for all unlogged direct writes to finish.
2. STANDBY NOLOGGING FOR DATA AVAILABILITY
A special logging mode where:
• Loaded data is sent synchronously to all connected standbys.
• Commit waits until standby(s) have applied the data.
• Useful when you want safe performance balancing with Data Guard.
ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
This mode optimizes throughput while still guaranteeing data is shipped to standbys before commit.
3. STANDBY NOLOGGING FOR LOAD PERFORMANCE
This mode prioritizes load performance, allowing:
• Fast load operations with asynchronous delivery to standbys.
• Standbys may temporarily miss some data but will fetch it automatically later during managed recovery.
ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
This is ideal for high-volume data loads where network bandwidth or latency might be a bottleneck, and delayed propagation is acceptable.
Notes and Considerations
• Database State: All the above commands require the database to be at least mounted (it can also be open).
• I/O Delay: Changing logging mode may take significant time as Oracle flushes unlogged direct I/O.
• Active Data Guard: When using STANDBY NOLOGGING modes, standby databases employing multi-instance redo apply can stop with ORA-10892. You may need to restart redo apply to proceed past the NOLOGGING period. • Recovery Behavior: The choice of logging mode directly impacts how consistent your standby will be during failover or switchover operations.
*************************
Scenario :
• Primary DB
• Some tables behave like queues
(high-frequency INSERT / DELETE / UPDATE)
• Result:
• Very high redo rate
• Archive log pressure
• Potential transport / apply lag on standby
Which logging mode is best?
What NOT to use
FORCE LOGGING
Technically safe, operationally painful here.
Why it’s bad for queue-heavy workloads:
• Forces redo for everything
• Amplifies redo generation
• Increases:
• LGWR pressure
• ARCH backlog
• Network traffic
• Standby apply lag
Use only if zero data loss is more important than performance (e.g. financial systems).
Use Carefully
STANDBY NOLOGGING FOR DATA AVAILABILITY
Characteristics:
• Commit waits for standby acknowledgment
• Still generates redo
• Safer than load-performance mode
Why it’s risky for queue tables:
• Queue workloads are commit-intensive
• You’ll add commit latency
• TPS may drop noticeably
Acceptable only if:
• Queues are business-critical
• Commit latency is acceptable
• Network latency is very low
BEST CHOICE for this Case
STANDBY NOLOGGING FOR LOAD PERFORMANCE
This is the recommended model when:
• Tables behave like queues
• Redo rate is extremely high
• Data can be eventually consistent on standby
Why this works best
✔ Primary performance stays high
✔ Commit does not wait for standby
✔ Reduces redo transport pressure
✔ Standby will self-heal missing blocks during recovery
This mode is designed exactly for this pattern.
Important Architecture Insight (very important)
For queue-like tables:
Standby consistency ≠ business correctness
Ask yourself:
• Are queues replayable?
• Are they rebuildable?
• Are they ephemeral?
If yes → you don’t need synchronous protection for them.
Many mature architectures:
• Exclude queue tables from DR correctness guarantees
• Rebuild queues after failover
• Protect core business tables only
Extra Recommendations (Advanced, but powerful)
1️⃣ Isolate redo impact
If possible:
• Separate queue tablespaces
• Separate I/O
• Monitor v$log_histogram
2️⃣ Standby tuning
• Increase PARALLEL_EXECUTION_MESSAGE_SIZE
• Use real-time apply
• Tune APPLY_PARALLELISM
3️⃣ Consider Far Sync
If network latency is an issue:
• Far Sync + async redo
• Keeps primary fast, improves DR reliability
Decision Matrix
Workload Type | Recommended Mode |
Queue / event tables | Load Performance |
Bulk ETL | Load Performance |
Financial / OLTP core | Force Logging |
Mixed workload | Data Availability |
Bottom line:
For queue-style tables with massive archive generation:
STANDBY NOLOGGING FOR LOAD PERFORMANCE is the right model
It’s not a compromise, it’s the correct architectural choice.
Summary:
Logging Mode | Writes All Redo | Standby Sync | Performance Impact |
FORCE LOGGING | Yes | N/A | Moderate |
STANDBY NOLOGGING FOR DATA AVAILABILITY | Conditional | Yes | Moderate–Low |
STANDBY NOLOGGING FOR LOAD PERFORMANCE | Conditional | Async | Low |
Choosing the right logging mode ensures:
✔ Reliable redo shipping to standby
✔ Correct application of changes on standby
✔ Reduced risk of data loss during failover
Alireza Kamrani
