Managing Undo Tablespace

To determine the optimal value for the UNDO_RETENTION parameter, you must first calculate the actual amount of undo that the database is generating. Once you know approximately how much undo the database is generating, you can calculate a more precise value for the UNDO_RETENTION parameter.

Use the following formula to calculate the value of the UNDO_RETENTION parameter:

UNDO_RETENTION = UNDO SIZE/(DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC)

You can calculate the actual undo that’s generated in your database by issuing the following query:


select sum(d.bytes) "undo"
from v$datafile d,
v$tablespace t,
dba_tablespaces s
where s.contents = 'UNDO'
and s.status = 'ONLINE'
and t.name = s.tablespace_name
and d.ts# = t.ts#;


undo
-----------------------
36507222016

You can calculate the value of UNDO_BLOCKS_PER_SEC with the following query:

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

 UNDO_BLOCK_PER_SEC
 -----------------------
 17.4183333333333

 *****************************************************************************
 You most likely remember the block size for your database,if not, you can look it up in the SPFILE
or find it by issuing the command show parameter db_block_size. Let’s say the db_block_size is 8 KB (8,192 bytes) for your database. You can then calculate the optimal value for the UNDO_RETENTION parameter using the formula shown earlier, for example, giving a result in seconds:

255853.0256056953 = 36507222016/(17.418 * 8192)

In this case, assigning a value of 1,800 seconds for the undo_retention parameter is appropriate, because it’s a bit more than what is indicated by our formula
for computing the value of this parameter.

Automatic undo management is the default mode for undo management starting with release 11g. If you create a database with the Database Configuration Assistant (DBCA), Oracle automatically creates an auto-extending undo tablespace named UNDOTBS1. If you’re manually creating a database, you specify the undo tablespace in the database creation statement, or you can add the undo tablespace at any point. If a database doesn’t have an explicit undo tablespace, Oracle will store the undo records in the SYSTEM tablespace.


Once you set the UNDO_TABLESPACE initialization parameter, Oracle automatically manages undo retention for you. Optionally, you can set the UNDO_RETENTION parameter to specify how long Oracle retains older undo data before overwriting it with newer undo data.
The formula in the “Solution” section shows how to base the undo retention period on current database activity. Note that we rely on the dynamic view V$UNDOSTAT to calculate the value for the undo retention period. Therefore, it’s essential that you execute your queries after the database has been running for some time, thus ensuring that it has had the chance to process a typical workload.
If you configure the UNDO_RETENTION parameter, the undo tablespace must be large enough to hold the undo generated by the database within the time you specify with the UNDO_RETENTION parameter.When a transaction commits, the database may overwrite its undo data with newer undo data. The undo retention period is the minimum time for which the database will attempt to retain older undo data. Oracle retains the undo data for both read consistency purposes as well as to support Oracle Flashback operations, for the duration you specify with the UNDO_RETENTION parameter. After it saves the undo data for the period you specified for the UNDO_RETENTION parameter, the database marks that undo data as expired and makes the space occupied by that data available to write undo data for new transactions.
By default, the database uses the following criteria to determine how long it needs to retain undo data:

• Length of the longest-running query
• Length of the longest-running transaction
• Longest flashback duration

It’s somewhat difficult to understand how the Oracle database handles the retention of undo data.
Here’s a brief summary of how things work:
• If you don’t configure the undo tablespace with the AUTOEXTEND option, the database simply ignores the value you set for the UNDO_RETENTION parameter. The
database will automatically tune the undo retention period based on database workload and the size of the undo tablespace. So, make sure you set the undo
tablespace to a large value if you’re receiving errors indicating that the database is not retaining undo for a long enough time. Typically, the undo retention in this
case is for a duration significantly longer than the longest-running active query in the database.

• If you want the database to try to honor the settings you specify for the UNDO_RETENTION parameter, make sure that you enable the AUTOEXTEND option for the undo tablespace. This way, Oracle will automatically extend the size of the undo tablespace to make room for undo from new transactions, instead of overwriting the older undo data. However, if you’re receiving ORA-0155 (snapshottoo old) errors, say due to Oracle Flashback operations, it means that the database isn’t able to dynamically tune the undo retention period effectively. In a case such as this, try increasing the value of the UNDO_RETENTION parameter to match the length of the longest Oracle Flashback operation. Alternatively, you can try going to a larger fixed-size undo tablespace (without the AUTOEXTEND option).
The key to figuring out the right size for the undo tablespace or the correct setting for the
UNDO_RETENTION parameter is to understand the nature of the current database workload. In order to understand the workload characteristics, it’s important to examine the V$UNDOSTAT view, because it contains statistics showing how the database is utilizing the undo space, as well as information such as the length of the longest-running queries. You can use this information to calculate the size of the undo space for the current workload your database is processing. Note that each row in the V$UNDOSTAT view shows undo statistics for a ten-minute time interval. The table contains a maximum of 576 rows, each for a ten-minute interval. Thus, you can review undo usage for up to four days in the past.

Here are the key columns you should monitor in the V$UNDOSTAT view for the time period you’re
interested in—ideally, the time period should include the time when your longest-running queries are
executing. You can use these statistics to size both the UNDO_TABLESPACE as well as the

UNDO_RETENTION
initialization parameters.
begin_time: Beginning of the time interval.
end_time: End of the time interval.
undoblks: Number of undo blocks the database consumed in a ten-minute interval; this is what we used in our formula for the estimation of the size of the undo tablespace.
txncount: Number of transactions executed in a ten-minute interval.
maxquerylen: This shows the length of the longest query (in seconds) executed in this instance during a ten-minute interval. You can estimate the size of the UNDO_RETENTION parameter based on the maximum value of the MAXQUERYLEN column.
maxqueryid: Identifier for the longest-running SQL statement in this interval.
nospaceerrcnt: The number of times the database didn’t have enough free
space available in the undo tablespace for new undo data, because the entire
undo tablespace was being used by active transactions; of course, this means
that you need to add space to the undo tablespace.
tuned_undoretention: The time, in seconds, for which the database will retain
the undo data after the database commits the transaction to which the undo
belongs.
The following query based on the V$UNDOSTAT view shows how Oracle automatically tunes undo retention (check the TUNED_UNDORETENTION column) based on the length of the longest-unning query(MAXQUERYLEN column) in the current instance workload.

SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME,'HH24:MI:SS') END_TIME,
MAXQUERYLEN,NOSPACEERRCNT,TUNED_UNDORETENTION
FROM V$UNDOSTAT;
**********************************************************************************

Note that the value of the TUNED_UNDORETENTION column fluctuates continuously, based on the value
of the maximum query length (MAXQUERYLEN) during any interval. You can see that the two columns are
directly related to each other, with Oracle raising or lowering the tuned undo retention based on the
maximum query length during a given interval (of ten minutes). The following query shows the usage of
undo blocks and the transaction count during each ten-minute interval.

SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS'),TO_CHAR(END_TIME,'HH24:MI:SS'),
MAXQUERYLEN,SSOLDERRCNT,NOSPACEERRCNT,UNDOBLKS,TXNCOUNT FROM V$UNDOSTAT
ORDER BY UNDOBLKS;

Oracle provides an easy way to help set the size of the undo tablespace as well as the undo retention period, through the OEM Undo Advisor interface. You can specify the length of time for the advisor’s analysis, for a period going back to a week—the advisor uses the AWR hourly snapshots to perform its analysis. You can specify the undo retention period to support a flashback transaction query. Alternatively, you can let the database determine the desired undo retention based on the longest query in the analysis period.

******************************************************************************

Finding What’s Consuming the Most Undo

SELECT S.SQL_TEXT FROM V$SQL S, V$UNDOSTAT U
WHERE U.MAXQUERYID=S.SQL_ID;

You can join the V$TRANSACTION, V$SQL and the V$SESSION views to find out the most undo used by a  session for a currently executing transaction, as shown here:

SELECT S.SID, S.USERNAME, T.USED_UREC, T.USED_UBLK
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR
ORDER BY T.USED_UBLK DESC;

-------------------------------------------------------

SELECT S.SID, S.USERNAME, T.USED_UREC, T.USED_UBLK, S.SQL_ID, D.SQL_TEXT
FROM V$SESSION S, V$TRANSACTION T, V$SQL D
WHERE S.SADDR = T.SES_ADDR
AND S.SQL_ID=D.SQL_ID
ORDER BY T.USED_UBLK DESC;

You can also issue the following query to find out which session is currently using the most undo in
an instance:

SELECT S.SID, T.NAME, S.VALUE
FROM V$SESSTAT S, V$STATNAME T
WHERE S.STATISTIC# = T.STATISTIC#
AND T.NAME = 'UNDO CHANGE VECTOR SIZE'
ORDER BY S.VALUE DESC;

The query’s output relies on the statistic undo change vector size in the V$STATNAME view, to show the SID for the sessions consuming the most undo right now. The V$TRANSACTION view shows details about active transactions. Here’s another query that joins the V$TRANSACTION, V$SQL and V$SESSION views:

SELECT SQL.SQL_TEXT SQL_TEXT, T.USED_UREC RECORDS, T.USED_UBLK BLOCKS,
(T.USED_UBLK*8192/1024) KBYTES FROM V$TRANSACTION T,
V$SESSION S,
 V$SQL SQL
 WHERE T.ADDR = S.TADDR
 AND S.SQL_ID = SQL.SQL_ID
 AND S.USERNAME ='&USERNAME'

The column USED_UREC shows the number of undo records used, and the USED_UBLK column shows the undo blocks consumed by a transaction.

You can issue the queries described in the “Solution” section to identify the sessions that are responsible for the most undo usage in your database, as well as the users that are responsible for those sessions. You can query the V$UNDOSTAT with the appropriate begin_time and end_time values to get the SQL identifier of the longest-running SQL statement during a time interval. The MAXQUERYID column captures the SQL identifier. You can use this ID to query the V$SQL view in order to find out the actual SQL statement. Similarly, the V$TRANSACTION and the V$SESSION views together help identify the users that are consuming the most undo space. If excessive undo usage is affecting performance, you might want to look at the application to see why the queries are using so much undo.

****************************************************************************************
Resolving an ORA-01555 Error

Problem
You’re receiving the ORA-01555 (snapshot too old) errors during nightly runs of key production batch
jobs. You want to eliminate these errors.

Solution
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving “snapshot too old” errors, it doesn’t guarantee that the database won’t overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren’t running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of “snapshot too old” errors with these approaches, you can’t completely eliminate such errors without specifying the guaranteed undo retention feature. When you configure guaranteed undo retention in a database, no transaction can fail
because of the “snapshot too old” error. Oracle will keep new DML statements from executing when  you set up guaranteed undo retention. Implementing the guaranteed undo feature is simple. Suppose you want to ensure that the database retains undo for at least an hour (3,600 seconds). First set the undo retention threshold with the alter system command shown here, and then set up guaranteed undo
retention by specifying the retention guarantee clause to alter the undo tablespace.

alter system set undo_retention=3600;
System altered.
 alter tablespace undotbs1 retention guarantee;
Tablespace altered.


You can switch off guaranteed undo retention by executing the alter tablespace command with
the retention noguarantee clause.

Occurrence of the Error
The ORA-01555 error (snapshot too old) may occur in various situations. The following is a case where the
error occurs during an export.
EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
EXP-00000: Export terminated unsuccessfully
And you can receive the same error when performing a flashback transaction:
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at li
The “snapshot too old” error occurs when Oracle overwrites undo data that’s needed by another
transaction. The error is a direct result of how Oracle’s read consistency mechanism works. The error
occurs during the execution of a long-running query when Oracle tries to read the “before image” of any changed rows from the undo segments. For example, if a long-running query starts at 1 a.m. and runs until 6 a.m., it’s possible for the database to change the data that’s part of this query during the period in which the query executes. When Oracle tries to read the data as it appeared at 1 a.m., the query may fail if that data is no longer present in the undo segments.
If your database is experiencing a lot of updates, Oracle may not be able to fetch the changed rows, because the before changes recorded in the undo segments may have been overwritten. The transactions that changed the rows will have already committed, and the undo segments don’t have a record of the before change row values because the database overwrote the relevant undo data. Since Oracle fails to return consistent data for the current query, it issues the ORA-01555 error. The query  that’s currently running requires the before image to construct read-consistent data, but the before image isn’t available.
The ORA-01555 error may be the result of one or both of the following: too many updates to the
database or too small an undo tablespace. You can increase the size of the undo tablespace, but that
doesn’t ensure that the error won’t occur again.

==================================================
Dropping Undo Tablespace

We have a tablespace with name UNDOTBS01 we want to drop. For this follow below steps.

1. Create a New Undo Tablespace. e.g UNDOTBS02
2. Change default undo tablespace to newly created tablespace by changing UNDO_TABLESPACE parameter. You can change it while database is open.
ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS02' SCOPE=BOTH
3.Run below command to confirm that no user is using old tablespace
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
          SELECT segment_name
          FROM dba_segments 
          WHERE tablespace_name = 'UNDOTBS01'
         ); 
4. Take old tablespace UNDOTBS01 offline.
5. Drop old tablespace UNDOTBS01