ORA-01555 on Physical Standby Databases Opened in Read Only Mode

Deep Dive into Snapshot Too Old Errors in Active Data Guard Environments

Modern Oracle environments frequently use standby databases not only for disaster recovery but also for reporting and analytics. With Active Data Guard, organizations can open a physical standby database in read-only mode while redo apply continues in real time. This architecture is powerful, but it introduces a problem many DBAs do not initially expect:

ORA-01555: snapshot too old

Most administrators associate ORA-01555 with highly transactional primary databases. However, this error can also occur on standby databases even though users are not directly modifying data there.

This article explains why ORA-01555 happens on standby databases, how Oracle internally manages read consistency during redo apply, and the most effective methods to eliminate the issue in production systems.

Understanding ORA-01555

ORA-01555 occurs when Oracle cannot reconstruct an older version of a data block required for query consistency.

Oracle guarantees that a query sees data exactly as it existed when the query started. This mechanism is called read consistency.

To achieve this consistency, Oracle uses undo records. When blocks change, Oracle stores older versions of the data inside undo segments so queries can continue reading a consistent image.

The error appears when:

A query runs for a long period
Oracle needs older undo information
The required undo has already been overwritten

At that point, Oracle can no longer recreate the earlier block image, and the query fails with ORA-01555.

Why ORA-01555 Happens on a Read-Only Standby

One of the biggest misconceptions is that standby databases do not generate undo because users cannot perform DML operations.

That assumption is incorrect.

In an Active Data Guard configuration:

Redo is continuously shipped from the primary database
Managed Recovery Process (MRP) applies those changes on standby
Data blocks on standby are constantly changing internally
Oracle still requires undo information to maintain read consistency for active queries

Although the standby is open in read-only mode for users, redo apply behaves like continuous internal update activity.

As a result, reporting queries running on standby may need older versions of blocks while redo apply continues modifying them.

If the necessary undo information disappears before the query completes, Oracle raises:

ORA-01555: snapshot too old

Internal Concept Behind the Error

To fully understand the issue, consider the following example.

A reporting query begins at:

10:00 AM

At the same time:

Redo from primary continues arriving
MRP continuously applies changes
Blocks are updated internally on standby
Undo records are generated and reused

Suppose the query still requires an old version of a block at:

10:45 AM

But the undo information needed to reconstruct that block has already been overwritten due to ongoing redo apply activity.

Oracle can no longer provide a consistent image to the query, which leads to:

ORA-01555

This is why even a “read-only” standby database can experience snapshot too old errors.

Common Reasons for ORA-01555 on Standby

1. Long-Running Reporting Queries

This is the most common cause.

Large reporting jobs, BI dashboards, or ETL extraction queries may run for several hours while redo apply continuously changes underlying blocks.

The longer the query runs, the higher the chance that required undo information gets overwritten.

2. Heavy Redo Generation on Primary

Standby databases directly inherit workload pressure from the primary database.

Operations such as:

Bulk updates
Data purges
Massive ETL jobs
Index rebuilds
Batch processing

generate huge amounts of redo.

When redo apply becomes extremely active, undo segments on standby recycle more aggressively.

This significantly increases ORA-01555 probability.

3. Small Undo Tablespace

Even if undo retention is configured correctly, a small undo tablespace forces Oracle to reuse undo extents earlier than desired.

Many standby databases are configured with minimal storage because administrators assume they only serve DR purposes. Once reporting workloads begin using standby, that sizing becomes insufficient.

4. Low Undo Retention

Undo retention determines how long Oracle attempts to preserve undo data.

If retention is shorter than the runtime of reporting queries, undo may disappear before queries finish.

5. Simultaneous Reporting and Recovery Peaks

ORA-01555 commonly appears during periods where:

Reporting activity is high
Redo apply is also extremely active

For example:

Nightly reporting overlapping with ETL windows
Financial reporting during month-end loads
BI extractions during bulk imports

Diagnosing ORA-01555 on Standby

The first step is confirming that the standby database is actively applying redo while serving queries.

Check Database Role and Open Mode

SELECT database_role,
       open_mode
FROM v$database;

Typical output:

PHYSICAL STANDBY
READ ONLY WITH APPLY

This confirms Active Data Guard is enabled.

Identifying Long Running Queries

Queries with long execution times are primary candidates.

SELECT sid,
       serial#,
       sql_id,
       username,
       event,
       last_call_et
FROM v$session
WHERE status='ACTIVE'
ORDER BY last_call_et DESC;

Focus on sessions with very large LAST_CALL_ET values.

Analyzing Undo Statistics

Oracle provides undo history in V$UNDOSTAT.

SELECT begin_time,
       tuned_undoretention,
       maxquerylen,
       undoblks,
       txncount
FROM v$undostat
ORDER BY begin_time;

Important values:

Column	Meaning
TUNED_UNDORETENTION	-- Actual effective undo retention
MAXQUERYLEN	-- Longest running query duration

If:

MAXQUERYLEN > TUNED_UNDORETENTION

then ORA-01555 risk is extremely high.

Checking Undo Tablespace Size

SELECT tablespace_name,
       SUM(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

Small undo tablespaces are often the root cause in standby reporting systems.

Reviewing Alert Logs

Alert logs help correlate ORA-01555 with workload spikes.

Search for:

ORA-01555

Then compare timestamps with:

ETL execution
Batch jobs
Redo spikes
Reporting schedules

Best Solutions for ORA-01555 on Standby

Increase Undo Retention

One of the most effective fixes is increasing undo retention.

Check current value:

SHOW PARAMETER undo_retention;

Increase retention:

ALTER SYSTEM SET undo_retention=14400;

This preserves undo for four hours.

However, retention alone is not sufficient unless adequate undo space exists.

Expand Undo Tablespace

A small undo tablespace causes Oracle to overwrite undo regardless of retention settings.

Adding additional space is often the real fix.

Example:

ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/u01/oradata/UNDOTBS02.dbf'
SIZE 20G AUTOEXTEND ON;

Also consider enabling autoextend:

ALTER DATABASE DATAFILE
'/u01/oradata/UNDOTBS01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

Optimize Reporting Queries

Long-running queries increase ORA-01555 exposure.

Performance tuning can dramatically reduce failures.

Key improvements include:

Using indexes efficiently
Eliminating unnecessary full table scans
Partition pruning
Reducing sorting overhead
Improving join conditions
Avoiding Cartesian joins

Reducing query runtime reduces undo dependency duration.

Reduce Redo Apply During Reporting Windows

Some organizations temporarily pause redo apply during critical reporting periods.

Stop apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Restart apply afterward:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

This approach reduces internal block churn while reports execute.

However, it also increases standby lag and should be used carefully.

Separate Reporting from Disaster Recovery

Large enterprise environments often deploy:

One standby for disaster recovery
Another standby dedicated for reporting

This isolates reporting workloads from recovery activity and significantly improves stability.

Monitor Undo Proactively

Many ORA-01555 incidents can be avoided through monitoring.

Recommended query:

SELECT tuned_undoretention,
       maxquerylen
FROM v$undostat;

If maximum query length consistently approaches undo retention values, action should be taken before failures occur.

Real Production Scenario

A financial institution deployed Oracle 19c Active Data Guard for reporting.

The standby database was configured with:

8 GB undo tablespace
900-second undo retention

Nightly ETL jobs on primary generated massive redo volumes between 1 AM and 3 AM.

At the same time, reporting teams executed large BI reports that ran for nearly two hours.

During peak windows, reports failed repeatedly with:

ORA-01555: snapshot too old

Investigation revealed that redo apply activity recycled undo much faster than reporting queries could complete.

The DBA team implemented the following changes:

Increased undo retention to four hours
Expanded undo tablespace from 8 GB to 64 GB
Tuned reporting SQL
Rescheduled ETL overlap

After these adjustments:

ORA-01555 incidents disappeared
Reporting stabilized
Standby lag remained acceptable

Key Takeaways

ORA-01555 on standby databases is not unusual in Active Data Guard environments.

Even though users cannot modify data directly on standby, redo apply continuously changes blocks internally, and Oracle still relies on undo for consistent reads.

The most common causes include:

Long-running queries
Heavy redo apply
Insufficient undo retention
Small undo tablespaces

The most effective solutions are:

Increase undo retention
Expand undo tablespace
Tune reporting SQL
Reduce overlap between heavy redo generation and reporting workloads

Understanding how Oracle maintains read consistency during redo apply is essential for designing stable and scalable standby reporting environments.

When properly sized and monitored, Active Data Guard can support large reporting workloads without ORA-01555 interruptions.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

ORA-01555 on Physical Standby Databases Opened in Read Only Mode