Skip to main content
Delphix

Resolving ORA-01555 Error: Snapshot Too Old (KBA1827)

 

 

The page describes how to best mask an Oracle database table and how to troubleshoot ORA-01555: Snapshot too old.  

At a Glance

 

Note

Note:

This document addresses two reasons for ORA-01555. It is important to know which one it is. Also, note that an earlier (large) DML transaction could have been performed on the database, even before the dSource was created or as part of a large transaction to create the table. Important: Make sure to not run any other DML transactions while masking the data.

 

 
ORA Error: ORA-01555: Snapshot Too Old
Root Cause: The root cause of ORA-01555 is that UNDO is not present to satisfy a read-consistent request. This can be because of either one of two reasons:
  1. "Undo Overwritten". 
  2. "Undo Transaction Slot Overwritten".

Related to masking, this can happen as a result of one of the following cases: 

  1. An earlier large Data manipulation language (DML) transaction caused the issue:
    This is more likely and is due to an earlier large transaction and Delayed Block Cleanout. 
  2. Current DML  transactions caused the issue:
    This is unlikely if the masking job is configured according to the Best Practices below.  
    No other DML transactions should be executed while running the masking job.
Verify using AWR: Past experience has shown the best starting point is Solution 1. This can also be verified using the AWR (Automatic Workload Repository) report from a time period that covers the error (1 hr time period recommended).

If the following AWR report statistic has a value that is greater than 0 then Delayed Block Cleanout was encountered. 
  • "cleanouts only - consistent read gets"
Solution 1:
Delayed
Block Cleanout
If the table is configured according to Best Practice, and a large DML transaction had been performed on the database earlier then these steps are needed.

Delayed Block Cleanout is the process where Oracle completes a block cleanup after the transaction commits.

Some notes about the SQL below:
  • The SQL will force a Full Table Scan on the table which will read the blocks into the SGA and clean out ITL entries in modified data blocks. This will ensure Delayed Block Cleanout happens before the masking job. 
  • All tables with the ORA-01555 and with earlier large transactions need to be 'touched'. The column in the WHERE can be any column in the table that is not indexed (because we want a full table scan on the table).
  • These two statements can be added in the Job Pre-Script (the first line is only needed once for multiple tables) or can be executed before the dSource is created. 
  • It is best to add all masked tables to the script. 
-- STEP 1
ALTER session SET events '10949 trace name context forever, level 1';

-- STEP 2 - All masked tables
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_1_with_ORA-01555]);
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_2_with_ORA-01555]);
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_3_with_ORA-01555]);
...
Solution 2:
Manage UNDO
Tablespace
If solution 1 fails then the UNDO Tablespace size is too small or the Retention too short.

The action here is to increase the size of the UNDO Tablespace, create a new and bigger UNDO Tablespace, or change the UNDO Retention.
 

If the masked database is a vDB, then there are two options to change the UNDO Tablespce:

  • Increase UNDO space in the source database and create the VDB from a SnapShot taken after the changes.
  • Create the VDB using a vdb config template that specifies the proper UNDO sizes.

See note below to check UNDO tablespace size.
For more details, contact your DBA for assistance. 
Best Practices: There are some Best Practices for an Oracle masking job. These points will also reduce the chance of encountering ORA-01555. 
  1. Drop Indexes on all Masked Columns - Select Drop Indexes in the Job configuration (or for PK, these should be dropped manually). 
  2. No ORDER BY in the Rule Set of the masked table. 
  3. Ensure that there is no external transaction running which will change the data on the masked table. This includes Triggers (which should be disabled in this case). 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Date Release
Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1
Feb 13, 2023 8.0.0.0
Jan 12, 2023 7.0.0.0
                                                                                         Releases Prior to 2023
Major Release Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.11.0, 6.0.12.0, 6.0.13.0, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

Issue

ORA-01555 is a complex error. Note that this is not a complete overview of ORA-01555, there can also be other factors that are not detailed in this document that can cause the error. 

Two scenarios explained: 

  1. An earlier DML transaction caused the issue:
    This happens in any job and is due to an earlier large DML and Delayed Block Cleanout. 
  2. Current DML transactions caused the issue:
    This is unlikely in a masking job configured according to the Best Practice. No other DML transactions should be executed while running the masking job.

Delayed Block Cleanout 

For a masking job, this is the likely cause. The ORA-01555 may occur because of Delayed Block Cleanout… which is caused by an earlier large transaction (for example UPDATE, INSERT, and DELETE) on the masked table(s). 

This article goes through both these scenarios and what to do to resolve these issues. 

Not enough UNDO 

This is unlikely the cause when using masking. The root cause here is that data is being changed at the same time it’s being read, and there is insufficient UNDO to satisfy read-consistent requests.

However, on an optimal masking job, this should not happen, as the SELECT statement is fetching 'virgin' unmasked blocks.

ORA-01555 Verification using an AWR report

The issue can be verified by checking the AWR report and the value related to the key 'cleanouts only - consistent read gets'.

If this value is greater than 0 then Delayed Block Cleanout was encountered. 

Best Practice and 'Optimal' Masking Job 

The diagram below shows an 'Optimal' Masking Job in progress. Each rectangle represents an Oracle data block.

The fetch in the SELECT will read the data. On an optimal masking job, this will be using a Full Table Scan (FTS) and block after block is read (the order of the block). After masking transformation, the rows are updated and Oracle stores the original values in the UNDO Segment. 

This means we can mask as many rows as we want - the Masked Committed values (pictured green) will never be touched after the commit and the SELECT will always fetch 'virgin' unmasked data (white below). 

On an Optimal Masking Job: 

  • No 'WHERE' or 'ORDER BY' which will otherwise change the row order - this will ensure Full Table Scan (FTS).
  • No indexes on masked columns - having indexes will make changes in additional Oracle data blocks used for indexes. 
  • Ensure no external transaction is running which will change the data on the masked table.
  • Ensure Delayed Block Cleanout has been performed on all tables.
  • Note: All Oracle jobs will use ROWID by default.

Oracle Moving v5 (fast input).gif
In the example, there are 18 grey blocks which are around 18,000 rows. These are loaded into the masking engine and buffered between the masking steps. The 'x' and 'y' is just added to indicate motion ('x' unmasked and 'y' is masked data). The Transaction has Updated around 8,000 rows out of 10,000 (default Commit Size) but not yet committed (these records are highlighted in orange above).

Masking Execution with ORA-01555 

What happens when the Oracle database throws ORA-01555? 

There are a few key points here: 

  • SELECT
    • It is the SELECT that causes ORA-01555.
    • As discussed, this happens due to one of these two reasons:
      1. Current DML transactions caused the issue:
        • This is unlikely in a masking job configured according to the Best Practice.
      2. An earlier DML transaction caused the issue:
        • This happens in any job and is due to an earlier large DML and Delayed Block Cleanout

 

Masking ORA-01555 - Error v2.png

Solution

The solution is to always run masking jobs:

  • in accordance with the Best Practice; and 
  • ensure sufficient UNDO tablespace (retention and size); and
  • Performed Delayed Block Cleanout on all tables prior to running the masking job.


The Following Block Cleanout SQL Script will force a Delayed Block Cleanout.

  • The script can be executed before on the tables (recommended before dSource ingestion) or be executed as a Pre-Script in the masking job. 
  • All tables might need to be added in the script. 
ALTER session SET events '10949 trace name context forever, level 1';
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_with_ORA-01555]);
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_with_ORA-01555]);
SELECT count(*) FROM (SELECT ROWID FROM [TABLE_with_ORA-01555]);
...

 

Note

Note:

Testing has shown no or little impact on the performance when running the Block Cleanout SQL Script. In the tests performed using the Block Cleanout SQL Script, the performance was actually better by around 10% and that included the execution of the script.

 

SQL to check UNDO Tablespace Size

To check the size of the UNDO tablespace, please use this query:

  • Best is to have Autoextend,
  • Make sure Max has not been reached. 
SELECT 
  tablespace_name,
  file_id,
  bytes/1024/1024/1024 Size_GB,
  maxbytes/1024/1024/1024 Max_GB, 
  autoextensible
FROM dba_data_files 
WHERE tablespace_name IN
  (SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO');

Related Articles