000035690 - How to overcome ORA-01555 errors in the RSA Identity Governance and Lifecycle Oracle database

Document created by RSA Customer Support Employee on Nov 3, 2017•Last modified by RSA Customer Support Employee on Nov 23, 2017
Version 2Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000035690
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
Issue

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.
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).


However, for your information, the following statements are taken from Note 40689.1.


ORA-01555 Explanation


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 rollback 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.
Tasks

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).


  1. The UNDO tablespace is too small.
  2. Tune the value of the UNDO_RETENTION parameter.
  3. Enable retention guarantee for the Undo tablespace.
  4. 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, you will need to login to the Oracle Support portal to access these notes.
  1. Oracle Support Note 1950577.1 - IF: ORA-1555 Reported with Query Duration = 0 , or a Few Seconds
  2. Oracle Support Note 846079.1 : LOBs and ORA-01555 troubleshooting and Note 452341.1 : ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption 
However, the problem is most likely due to a long-running query, so the Oracle AWR report needs to be generated and examined to identify the long-running query.

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.
Resolution


ATTENTION:  Please note that all the SQL statements in this section need to be run as SYSDBA, not 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.

NOTE:  For this SQL to return valid results, it needs to be run during peak workload i.e. a time similar to when the ORA-01555 occurred.



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'));

An example of the output is as follows.



     Bytes
----------
 269519503

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.



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');

An example of the output is as follows (NOTE: These results do not show a problem).



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

Then, add the required space to the Undo Tablespace, as per Oracle Support Note 1951696.1 - IF: How to Resize the Undo Tablespace (login to the Oracle Support portal to access this note).
The steps from section 2. Add Space to the Undo Tablespace from Note 1951696.1 have been reproduced here, for your convenience.


a) To resize the existing undo datafile:



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;
Example:
alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' resize 1500M;

b) Step to add a new datafile:



alter tablespace <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' size 300M;
Example:
alter tablespace UNDOTBS1 ADD DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS02.DBF' size 300M;

 

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 (login to the Oracle Support portal to access this note).  It is reproduced here for your convenience.
 
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:
 
Column nameMeaning
BEGIN_TIMEThe beginning time for this interval check
END_TIMEThe ending time for this interval check
UNDOTSNThe undo tablespace number
UNDOBLKSThe total number undo blocks consumed during the time interval
TXNCOUNTThe total number of transactions during the interval
MAXQUERYLENThe maximum duration of a query within the interval
MAXCONCURRENCYThe highest number of transactions during the interval
UNXPSTEALCNTThe number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNTThe number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNTThe number of unexpired undo blocks reused by transactions
EXPSTEALCNTThe number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNTThe number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNTThe number of expired undo blocks reused within the same undo segments
SSOLDERRCNTThe number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNTThe number of Out-of-Space errors


When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure. If the 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 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.


Oracle Support Note 1100313.1 - Tuned_UndoRetention Can be Less Than Undo_Retention in Init.ora
Oracle Support Note 1579779.1 - Automatic Tuning of Undo Retention Common Issues


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;
MIN(TUNED_UNDORETENTION)
------------------------
511

This solution means that the Undo data will never be over-written, 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, above.

    Attachments

      Outcomes