000035690 - How to overcome ORA-01555: Snapshot too old errors in the RSA Identity Governance & Lifecycle Oracle 7.0.x database

Document created by RSA Customer Support Employee on Nov 3, 2017Last modified by RSA Customer Support Employee on Sep 7, 2018
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000035690
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
IssueAn 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 doesn't 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:

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, please view these notes on the Oracle Support portal.

  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.  FOr more information please refer to the following content:


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

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.



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



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


  1. Add the required space to the Undo Tablespace, as per Oracle Support Note 1951696.1 - IF: How to Resize the Undo Tablespace.  The steps from section 2. Add Space to the Undo Tablespace from Note 1951696.1 have been reproduced here, for your convenience.

    1. 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;


For example,



ALTER DATABASE DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' RESIZE 1500M;


  1. Add a new datafile:


ALTER TABLESPACE <UNDO tbs name> ADD DATAFILE '<COMPLETE_PATH_OF_UNDO_DBF_FILE>' SIZE 300M;



For 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.  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 name

  

  
Meaning

  

  
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

  


  • When the columns UNXPSTEALCNT through EXPBLKREUCNT hold 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 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;
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.



  1. Determine the Undo Tablespace name.


SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';


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


    Attachments

      Outcomes