|Applies To||RSA Product Set: RSA Identity Governance and Lifecycle|
An ORA-01555 error can occur on the Oracle database used by the RSA Identity Governance and Lifecycle product when it is trying to perform a query but doesn't find the read consistent data it is looking for. This is one of the prominent errors caused due to Oracle's read consistency model.
However, for your information, 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).
RSA Article How to generate Oracle Automatic Workload Repository (AWR) report for RSA Identity Governance and Lifecycle
RSA Video How to Generate an AWR Report
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.
|BEGIN_TIME||The beginning time for this interval check|
|END_TIME||The ending time for this interval check|
|UNDOTSN||The undo tablespace number|
|UNDOBLKS||The total number undo blocks consumed during the time interval|
|TXNCOUNT||The total number of transactions during the interval|
|MAXQUERYLEN||The maximum duration of a query within the interval|
|MAXCONCURRENCY||The highest number of transactions during the interval|
|UNXPSTEALCNT||The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests|
|UNXPBLKRELCNT||The number of unexpired blocks removed from undo segments to be used by other transactions|
|UNXPBLKREUCNT||The number of unexpired undo blocks reused by transactions|
|EXPSTEALCNT||The number of attempts when expired extents were stolen from other undo segments to satisfy a space request|
|EXPBLKRELCNT||The number of expired extents stolen from other undo segments to satisfy a space request|
|EXPBLKREUCNT||The number of expired undo blocks reused within the same undo segments|
|SSOLDERRCNT||The number of ORA-1555 errors that occurred during the interval|
|NOSPACEERRCNT||The number of Out-of-Space errors|
To easily determine if the conditions from Note 269814.1 have been met, please use the SQL statements below.
Query to determine if UNDO_RETENTION is properly set, where 0 means no tuning is needed.
select COUNT(*) as "Tune UNDO_RETENTION" from V$UNDOSTAT where SSOLDERRCNT > 0;
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).
Query to determine if there is Space Pressure, where 0 means no.
select COUNT(*) as "Space Pressure" from V$UNDOSTAT where UNXPSTEALCNT > 0 or UNXPBLKRELCNT > 0 or UNXPBLKREUCNT > 0 or EXPSTEALCNT > 0 or EXPBLKRELCNT > 0 or EXPBLKREUCNT > 0;
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.
Query to determine is there is a serious space problem, where 0 means no.
select COUNT(*) as "Serious Space Problem" from V$UNDOSTAT where NOSPACEERRCNT > 0;
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, where you will need to login to the Oracle Support portal to access these notes.
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.
SQL> show parameter undo_retention
NAME TYPE VALUE
-------------------------- ---------- ------------------------------
undo_retention integer 900
SQL> select min(TUNED_UNDORETENTION) from V$UNDOSTAT;
This solution means that the Undo data will never be over-written, where according to the algorithm the Undo Tablespace will instead be extended.
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';
alter tablespace <tablespace-name> RETENTION GUARANTEE;