ORA-01555 Error - Why we are receiving ?

Undo data is stored in the undo tablespace, the tablespace nominated by the UNDO_TABLESPACE instance parameter. This tablespace should be sized according to the rate at which undo data is being generated and the length of the queries running in the database.

But even if the undo tablespace is inadequately sized and therefore comes under space pressure, automatic undo retention tuning will tend to minimize problems.

Undo data generated by a transaction must always be kept until the transaction commits. This is an absolute; under no circumstances will Oracle ever overwrite undo data that might be needed to rollback a transaction. This data is known as “active” undo data.

Once a transaction has committed, its undo data is no longer active, but it could still be needed to support long running queires that began before the transaction. Data that may be needed for this purpose is known as “unexpired” undo data.

 ”Expired” undo is data that is no longer needed either to rollback a transaction or to be read by queries.. Active undo will never be overwritten, and ideally expired undo can be safely overwritten at any time.

Unexpired undo can be overwritten, but at the risk of causing queries to fail with the “ORA-01555 : Snapshot too old” error.

The point at which data transitions from “unexpired” to “expired” is controlled by the instance parameter UNDO_RETENTION.

With release 9i of the Oracle database,Oracle would overwrite any expired undo. This meant that if the UNDO_RETENTION parameter were not set appropriately or (not set at all, in which case it defaults to 9oo seconds), there would be great danger of ORA-1555 errors.

Oracle 10g relaeas of the database effectively ignores the UNDO_RETENTION parameter. It will always overwrite the oldest bit of undo data.This means that is a sense there is no longer any difference between expired and unexpired undo and that the UNDO_RETENTION instance parameter is redundant, because undo retention is automatically tuned for the longest possible query.

To monitor the automatic undo retention tuning ,query V$UNDOSTAT this query will show,

SQL> SELECT BEGIN_TIME,END_TIME,TUNED_UNDORETENTION,ACTIVEBLKS,UNEXPIREDBLKS,EXP IREDBLKS FROM V$UNDOSTAT;

Iin fivteen minutes intervals, how old(in seconds) the oldest block of inactive undo data was. provided that no query started earlier than that, you will never receive a snapshot too old error. The larger the undo tablespace is and the less the transaction workload is, the further back the TUNED_UNDORETENTION will be.

Optimize Oracle UNDO Parameters 
--------------------------------------------------------------------------------
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying. 


Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on  long running queries. 


This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. 
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO. 


However it is worth to tune the following important parameters 


The size of the UNDO tablespace 
The UNDO_RETENTION parameter 
Calculate UNDO_RETENTION  for given UNDO Tabespace 


You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the 


database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The 


following query will help you to optimize the UNDO_RETENTION parameter: 


 OPTIMAL UNDO RETENTION = ACTUAL undo size / db_block_size * undo_block_per_sec.

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time! 




Actual Undo Size 


SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#; 


 UNDO_SIZE
----------
  209715200 




Undo Blocks per Second 


SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat; 


UNDO_BLOCK_PER_SEC
------------------
        3.12166667 


DB Block Size 


SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size'; 




DB_BLOCK_SIZE [Byte]
--------------------
                4096 


Optimal Undo Retention 


209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]