|Applies To||RSA Product Set: RSA Identity Governance & Lifecycle|
|Issue||An ORA-01555 error can occur on the Oracle database used by the RSA Identity Governance & Lifecycle product when it is trying to perform a query but does not find the read consistent data it is looking for. This is one of the prominent errors caused by Oracle's read consistency model.|
For more information, please review Oracle Support Note 40689.1 - ORA-01555 "Snapshot too old" - Detailed Explanation.
Login to the Oracle Support portal to access this note and others referred to in this article.
The following statements are taken from Note 40689.1:
There are two fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image. These are:
The following solutions are from the Oracle Support Note 269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions (login to the Oracle Support portal to access this note).
Once the long-running query has been identified, the solution will be to determine why it is long-running. In some cases, it may be a known issue with the RSA Identity Governance and Lifecycle setup for the Oracle database, or it may be that large Collections need to be re-scheduled. If necessary, please log a case so that an RSA Support engineer can assist.
Please note that all the SQL statements in this section need to be run as SYSDBA, not as the AVUSER account. This is because the tables being accessed and the operations being performed need SYSDBA access. If you are unsure, please consult your Oracle DBA or engage an RSA Support Engineer.
1. The UNDO tablespace is too small
A method for determining the "number of bytes needed to handle a peak undo activity" is detailed in Oracle Support Note 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management (login to the Oracle Support portal to access this note). However, for your convenience, here is the SQL.
For this SQL to return valid results, it needs to be run during peak workload; that is, a time similar to when the ORA-01555 error occurred.
The Undo Tablespace would need to be at least the calculated number of bytes. However, allow 10-20% when re-sizing or adding data files to the Undo Tablespace.
2. Tune the value of the UNDO_RETENTION parameter
The following is from Oracle Support Note 269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions. It is reproduced here for your convenience.
The V$UNDOSTAT view holds undo statistics for 10-minute intervals. This view represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance. This view contains the following columns:
To easily determine if the conditions from Note 269814.1 have been met, please use the SQL statements below.
If this query returns a non-zero value (count), then it is likely that the UNDO_RETENTION needs to be changed to the maximum value of column v$undostat.maxquerylen (see above).
If this query returns a non-zero value (count), then space may need to be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).
If this query returns a non-zero value (count), then space should be added to the Undo Tablespace, see Resolution Section 1 (The UNDO tablespace is too small).
3. Enable retention guarantee for the Undo tablespace
There are several Oracle Support notes that explain why this is necessary. See:
The explanation is "In the event of any undo space constraints, the system will prioritize DML operations over undo retention. In such situations, the low threshold may not be achieved and tuned_undoretention can go below undo_retention.".
So, if you see V$UNDOSTAT.TUNED_UNDORETENTION being less than the UNDO_RETENTION, then setting RETENTION GUARANTEE is recommended by Oracle.
This solution means that the Undo data will never be overwritten, where according to the algorithm the Undo Tablespace will instead be extended.
4. Calculate the size of the UNDO tablespace
The Oracle advice here is to "Use the formula presented in Document 262066.1 to calculate the size of the UNDO tablespace," however, this formula has already been presented in section 1. The UNDO tablespace is too small.