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.
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 rollback information itself is overwritten so that Oracle is unable to roll back the (committed) transaction entries to attain a sufficiently old enough version of the block.
The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.
Enable retention guarantee for the Undo tablespace.
Calculate the size of the UNDO tablespace.
There are also solutions for ORA-01555 errors in specific circumstances, where there are Oracle Support notes available. Given that the steps are quite detailed, please view these notes on the Oracle Support portal.
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.
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.
Run the following command and note the output below:
SELECT (UR * (UPS * DBS)) AS "Bytes" FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
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.
Next, show the sizes and Autoextend setting for the current Data Files used by the Undo Tablespace, along with the output. Note that the results below do not show a problem.
COL AUTOEXTENSIBLE FORMAT A14
SELECT FILE_NAME, BYTES/1024/1024 AS "BYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
FILE_NAME BYTES (MB) AUTOEXTENSIBLE
-------------------------------------------------------------------------------- ---------- --------------
/u01/app/oracle/oradata/AVDB/undotbs01.dbf 440 YES
/u01/app/oracle/oradata/AVDB/undotbs02.dbf 128 YES
/u01/app/oracle/oradata/AVDB/undotbs03.dbf 128 YES
col T_NAME for a23
col FILE_NAME for a65
SELECT tablespace_name T_NAME,file_name, bytes/1024/1024 MB FROM dba_data_files WHERE tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') ORDER BY file_name;
ALTER DATABASE DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' resize <SIZE>M;
ALTER DATABASE DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' RESIZE 1500M;
Add a new datafile:
ALTER TABLESPACE <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' SIZE 300M;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS02.DBF' SIZE 300M;
This is important for systems running long queries. The parameter's value should at least be equal to the length of the longest-running query on a given database instance. This can be determined by querying V$UNDOSTAT view once the database has been running for a while:
SQL> SELECT MAX(maxquerylen) FROM v$undostat;
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:
The beginning time for this interval check
The ending time for this interval check
The undo tablespace number
The total number undo blocks consumed during the time interval
The total number of transactions during the interval
The maximum duration of a query within the interval
The highest number of transactions during the interval
The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
The number of unexpired blocks removed from undo segments to be used by other transactions
The number of unexpired undo blocks reused by transactions
The number of attempts when expired extents were stolen from other undo segments to satisfy a space request
The number of expired extents stolen from other undo segments to satisfy a space request
The number of expired undo blocks reused within the same undo segments
The number of ORA-1555 errors that occurred during the interval
The number of Out-of-Space errors
When the columns UNXPSTEALCNT through EXPBLKREUCNT holds non-zero values, it is an indication of space pressure.
If column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.
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 that 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. 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. For example:
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 overwritten, where according to the algorithm the Undo Tablespace will instead be extended.
Determine the Undo Tablespace name.
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';
Using the tablespace name returned by the above query, enable Retention Guarantee on the Undo Tablespace.
ALTER TABLESPACE <tablespace-name> RETENTION GUARANTEE;
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.