Issue
On an Active Data Guard configuration, we noticed periods where archive log generation on the primary spiked well above normal, and the standby’s apply lag grew along with it. The spikes tracked closely with batch and reporting jobs that make heavy use of Global Temporary Tables (GTTs), and once the extra redo reached the standby, it took longer than expected to apply.
Cause of the Issue
The lag traced back to four separate, compounding causes:
- GTT undo being written to the UNDO tablespace by default, generating redo for data that was never meant to be protected
- Redo apply on the standby running serially instead of in parallel
- Insufficient DB writer processes, causing free buffer waits during redo apply
- Full block checking on the standby adding CPU overhead to every redo apply operation
Business Impact
Left unaddressed, excess archive log generation and growing apply lag translate into real operational risk:
- Increased storage consumption on the archive log destination, on both primary and standby
- Higher RPO (Recovery Point Objective) during failover, since a lagging standby has less recently applied data
- Delayed reporting from the standby, as Active Data Guard read-only queries reflect a more stale copy of the data
- Disaster Recovery synchronization issues, including longer switchover/failover validation and readiness windows
Each fix below carries its own benefit and trade-off, summarized here and expanded on per step:
| Recommendation | Benefit | Risk |
| Enable TEMP_UNDO_ENABLED | Reduces redo/archive log generation from GTT DML | TEMP tablespace usage increases; undersized TEMP can cause query failures |
| Enable Parallel Redo Apply | Applies redo in parallel, improves apply speed | Reporting queries on the standby may slow down |
| Increase DB Writer Processes | Reduces/eliminates free buffer waits, improves write throughput | Increased CPU consumption; requires an instance restart |
| Disable/Lower Block Checking | Significantly improves redo apply speed | Reduced corruption detection; not recommended as a permanent setting |
Prerequisites
- Verify Data Guard health and confirm there is no existing apply or transport gap before making any change
SELECT NAME, VALUE, UNIT
FROM V$DATAGUARD_STATS
WHERE NAME IN (‘transport lag’,’apply lag’,’apply finish time’);
- Confirm the Oracle Database version on both primary and standby, since default values and behavior for these parameters can differ across releases
SELECT banner FROM v$version;
- Take an SPFILE backup before changing any static parameter
RMAN> BACKUP SPFILE;
-or
SQL> CREATE PFILE=’/tmp/spfile_backup_<date>.ora’ FROM SPFILE;
Capture Existing Configuration
Before modifying any parameter, capture and document its current value on every instance. This baseline is what each rollback step below restores, and it also supports change-audit requirements:
SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN (‘temp_undo_enabled’,’db_writer_processes’,
‘recovery_parallelism’,’db_block_checking’)
ORDER BY inst_id, name;
How Do We Solve
Step 1 : Confirm the current TEMP_UNDO_ENABLED setting on the primary database:
SQL> show parameter temp_undo_enabled
Step 2 : Check that the TEMP tablespace has enough headroom. Once TEMP_UNDO_ENABLED is on, GTT undo moves from UNDO into TEMP, so TEMP needs to comfortably absorb both existing query workspace usage and the incoming undo:
SELECT
tf.tablespace_name,
COUNT(tf.file_id) AS no_of_tempfiles,
ROUND(SUM(tf.bytes)/1024/1024/1024,2) AS total_gb,
ROUND(SUM(h.bytes_used)/1024/1024/1024,2) AS used_gb,
ROUND(SUM(h.bytes_free)/1024/1024/1024,2) AS free_gb
FROM dba_temp_files tf
JOIN v$temp_space_header h
ON tf.file_id = h.file_id
GROUP BY tf.tablespace_name;
Step 3 : Enable TEMP_UNDO_ENABLED. This is a dynamic parameter and takes effect for new transactions immediately, with no restart required. It redirects undo for temporary table operations to TEMP while still preserving read consistency, cutting the redo and therefore archive log volume generated by GTT DML:
SQL> ALTER SYSTEM SET temp_undo_enabled=TRUE SCOPE=BOTH;
Risk: TEMP tablespace usage increases. If TEMP is undersized, queries can fail with an ORA-1652 unable to extend TEMP segment error.
Rollback: ALTER SYSTEM SET temp_undo_enabled=FALSE SCOPE=BOTH; takes effect immediately for new transactions, no restart required.
Step 4 : Increase db_writer_processes so dirty buffers on the standby are flushed fast enough to keep up with redo apply and reduce free buffer waits:
SQL> ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE SID=’*’;
Risk: Increased CPU consumption from additional DBWR processes. This is a static parameter it is written to the SPFILE but only takes effect after the instance is restarted, so plan for a maintenance window.
Rollback: ALTER SYSTEM SET db_writer_processes=<baseline_value> SCOPE=SPFILE SID=’*’; then restart the instance to apply it.
Step 5 : Enable parallel redo apply so the standby uses multiple recovery slaves instead of applying redo serially:
SQL> ALTER SYSTEM SET recovery_parallelism=8 SCOPE=BOTH SID=’*’;
This parameter is dynamic, but it only takes effect the next time managed recovery starts. If the Managed Recovery Process (MRP) is already running, restart it after setting the parameter:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Risk: Parallel apply competes for CPU and I/O with other activity on the standby. Reporting or read-only queries running against the standby can slow down during heavy apply.
Rollback: ALTER SYSTEM SET recovery_parallelism=<baseline_value> SCOPE=BOTH SID=’*’; then cancel and restart managed recovery for the change to take effect.
Step 6 : If redo apply is still CPU-bound after the changes above, block checking can be considered as a further, narrowly-scoped option. Full block checking validates every block change during redo apply, which adds meaningful CPU overhead:
SQL> ALTER SYSTEM SET db_block_checking=FALSE SCOPE=BOTH SID=’*’;
Risk: Removes a layer of corruption detection. A corruption that would otherwise have been caught during redo apply can go undetected until it surfaces later as a query error or an inconsistent read.
Rollback: ALTER SYSTEM SET db_block_checking=<baseline_value> SCOPE=BOTH SID=’*’; takes effect immediately.
Important Do not disable block checking permanently.
Oracle recommends keeping DB_BLOCK_CHECKING enabled unless advised by Oracle Support or during controlled, time-boxed troubleshooting. Treat any change to this parameter as temporary: apply it only for the duration needed to confirm its impact on apply performance, and restore the baseline value as soon as that window closes.
For quick reference, here is when each change actually takes effect:
| Change | Effect |
| temp_undo_enabled | Takes effect immediately (dynamic) |
| recovery_parallelism | Effective after MRP restart |
| db_writer_processes | Requires standby instance restart |
| db_block_checking | Takes effect immediately (dynamic) |
Validation After Implementation
After each change, confirm the intended effect before moving to the next one:
- Monitor archive log generation rate
SELECT TRUNC(completion_time) AS log_date, COUNT(*) AS logs_generated
FROM v$archived_log
GROUP BY TRUNC(completion_time)
ORDER BY log_date DESC;
- Check standby apply lag
SELECT name, value FROM v$dataguard_stats WHERE name = ‘apply lag’;
- Review AWR reports on both primary and standby to confirm redo generation and apply throughput trends before and after the change
- Validate Data Guard synchronization status
DGMGRL> show configuration;
DGMGRL> show database verbose <standby_db_unique_name>;
Best Practice
Perform these parameter changes in a non-production environment first, validate the performance improvement and confirm there is no negative impact on applications or reporting, and only implement in production after successful testing and CAB/change approval. Schedule static-parameter changes for a maintenance window, apply one change at a time so its effect can be isolated, and keep the captured baseline values on hand so any change can be rolled back quickly if performance degrades.
Conclusion
Reducing archive log generation and standby apply lag took a combination of fixes rather than a single change. TEMP_UNDO_ENABLED addressed the root cause by keeping GTT undo out of the UNDO tablespace entirely. Parallel redo apply and additional DB writer processes gave the standby enough throughput to keep pace once redo arrived, and block checking was used only as a temporary, closely monitored lever rather than a permanent setting. Applied together with prerequisites checked, a baseline captured, and rollback commands ready archive log volume and apply lag both came down, and we continue to monitor TEMP usage, apply lag, and redo apply CPU to catch any regression early.
