Business-critical applications depend on predictable database response times and Data Guard protection with minimal data loss exposure. Sudden redo-generation spikes can create log buffer space pressure that affects customer-facing sessions on the primary database, or increase transport lag to asynchronous standby databases, affecting recovery objectives.

Oracle AI Database 26ai Release Update 23.26.3 introduces Auto Redo Prioritization to respond automatically to these conditions. When log buffer space pressure occurs, it helps relieve it. When asynchronous transport lag exceeds a configured target, it helps reduce it and keep the standby closer to the primary.

To do this, Oracle AI Database 26ai applies redo prioritization by reducing redo generation from batch-behaving sessions while the condition persists. OLTP sessions are not directly affected by prioritization, and their performance can eventually improve as a side effect of reduced batch redo generation.

Why Auto Redo Prioritization matters

Existing Resource Manager capabilities can shape workload behavior through administrator-defined classifications and policies, but they cannot automatically adapt to changing redo-generation conditions. In many environments, separating workloads in advance is impractical or impossible because enterprise applications can share services and database users for online transaction processing, batch work, data ingestion, and maintenance operations.

Auto Redo Prioritization is designed for these mixed and changing workloads. Rather than requiring administrators to identify and classify every source of redo generation beforehand, it responds to current redo conditions and applies prioritization only when it is needed.

Protect OLTP responsiveness under log buffer space pressure

One form of Auto Redo Prioritization addresses redo log buffer space pressure.

If sessions start waiting on the log buffer space wait event, OLTP application response time can be affected. With log buffer space redo prioritization enabled, Oracle AI Database continuously monitors redo log buffer utilization. When free space falls below an internal threshold, it applies redo prioritization to help make redo resources more available to OLTP workloads. When non-OLTP redo generation contributes to log buffer space pressure, this can allow online transactions to make progress more consistently during periods of elevated redo demand. Potential benefits include higher OLTP throughput and more predictable response times.

Enable this capability with the dynamic LOG_REDO_PRIORITIZATION initialization parameter:

ALTER SYSTEM SET LOG_REDO_PRIORITIZATION = TRUE;

The parameter defaults to FALSE, does not require an instance restart, and can be set independently on Oracle RAC instances.

Auto Redo Prioritization is not a replacement for normal redo tuning. Redo log sizing, storage performance, log buffer configuration, and workload design remain important. Instead, it provides an additional protection mechanism when a mixed workload encounters a temporary log buffer contention condition.

Help keep asynchronous standbys closer to the primary

Auto Redo Prioritization also addresses a closely related Data Guard concern: transport lag for an asynchronous physical standby database.

Asynchronous redo transport is often the right choice when applications require primary database performance without commit latency being dependent on a remote standby. However, a sudden increase in redo generation can outpace redo transport throughput for a period of time. As transport lag grows, the standby becomes less current and potential data-loss exposure increases.

With transport lag-based redo prioritization, you define a target transport lag for an asynchronous physical standby destination. When the transport lag exceeds that target, redo transport notifies the resource manager, which applies the redo prioritization.

Configure the target with the REDO_PRIORITIZATION_WHEN_TRANSPORT_LAGS_BY_SEC attribute of LOG_ARCHIVE_DEST_n:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 =
  'SERVICE=boston ASYNC NOAFFIRM
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
   DB_UNIQUE_NAME=boston
   REDO_PRIORITIZATION_WHEN_TRANSPORT_LAGS_BY_SEC=20';

A nonzero value enables the capability for an ASYNC physical standby destination; a value of 0, the default, disables it.

On Oracle Data Guard Broker configurations, use the new member property AdditionalLadAttributes to add the log archive destination attribute:

EDIT DATABASE newyork SET PROPERTY
   AdditionalLadAttributes='REDO_PRIORITIZATION_WHEN_TRANSPORT_LAGS_BY_SEC=20';

This helps keep the asynchronous standby closer to the primary when the redo spike is caused by non-OLTP workloads. Auto Redo Prioritization does not affect OLTP-behaving sessions—only batch-behaving sessions. Therefore, redo spikes generated by OLTP sessions can still increase the transport lag.

In Fast-Start Failover (FSFO) environments that use transport lag for FastStartFailoverLagLimit, setting an Auto Redo Prioritization transport-lag target below the FSFO lag limit gives the primary database an opportunity to reduce redo generation before the target reaches the FSFO limit. When transport lag regularly moves above and below that limit, this can help the target FSFO standby remain viable more consistently.

Key takeaway

Available beginning with Oracle AI Database 26ai Release Update 23.26.3, Auto Redo Prioritization provides two independent safeguards:

  • It helps protect OLTP responsiveness when redo log buffer pressure occurs, by enabling LOG_REDO_PRIORITIZATION.
  • It helps reduce asynchronous Data Guard transport lag, by configuring REDO_PRIORITIZATION_WHEN_TRANSPORT_LAGS_BY_SEC for an ASYNC physical standby destination.