When
we issue any DML statements i.e., insert, update, delete the changed
blocks are stored in buffer cache and Server Processes keeps the before
values for those changed blocks in UNDO segments.
What is the use of
UNDO?
Rollback
transactions:
Whenever we issue a ROLLBACK command it uses the undo segment to
rollback to previous value.
Recover
the database:
During database recovery, undo records are used to
undo any uncommitted changes applied from the redolog to the datafiles
Provide
read consistency:
Undo records provide read consistency by maintaining the
before image of the data for users who are accessing the data at the
same time that another user is changing it.
Analyze
data as of an earlier point in time by using Flashback Query.
Recover from logical corruptions using Flashback features.
Generally, Undo is stored in Undo tablespace in the database.
Another Question
here …
How long can undo data
stored in the database? and Is it Permanent?
Undo data is not permanent, and Oracle provides flexibility of how
long should undo data be stored with the help of undo_retentions parameter. We can set
undo_management parameter to automatic for oracle to manage undo
retention(default), or even set this value manually and it’s value should be
greater than the time taken by the longest running query in your database.
IN 11g Onward Oracle tuned this parameter automatically according to the
size of the undo tablespace
Let's
have a look about undo parameters ..
UNDO_RETENTION:
Committed undo information normally is lost when its undo space is
overwritten by a newer transaction. However, for consistent read purposes,
long-running queries sometimes require old undo information for undoing changes
and producing older images of data blocks. The success of
several Flashback features can also depend upon older undo
information.
The default value for the UNDO_RETENTION parameter is 900. Retention is
specified in units of seconds. This value specifies the amount of time, undo is
kept in the tablespace. The system retains undo for at least the time specified
in this parameter.
You
can set the UNDO_RETENTION in the parameter file:
UNDO_RETENTION
= 1800
You
can change the UNDO_RETENTION value at any time using:
SQL> ALTER
SYSTEM SET UNDO_RETENTION = 2400;
The effect of the UNDO_RETENTION parameter is immediate, but it can only
be honored if the current undo tablespace has enough space. If an active
transaction requires undo space and the undo tablespace does not have available
space, then the system starts reusing unexpired undo space (if retention is not
guaranteed). This action can potentially cause some queries to fail with the
ORA-01555 "snapshot too old" error message.
UNDO_RETENTION
applies to both committed and uncommitted transactions since the introduction
of flashback query feature in Oracle needs this information to create
a read consistent copy of the data in the past.
Retention
Guarantee:
Oracle Database 10g lets you guarantee undo retention.
When you enable this option, the database never overwrites unexpired undo data
i.e. undo data whose age is less than the undo retention period. This option is
disabled by default, which means that the database can overwrite the unexpired
undo data in order to avoid failure of DML operations if there is not enough
free space left in the undo tablespace.
You enable the guarantee option by specifying the RETENTION
GUARANTEE clause for the undo tablespace when it is created by either the
CREATE DATABASE or CREATE UNDO TABLESPACE statement or you can later specify
this clause in an ALTER TABLESPACE statement. You do not guarantee that
unexpired undo is preserved if you specify the RETENTION NOGUARANTEE clause.
In order to guarantee the success of queries even at the
price of compromising the success of DML operations, you can enable retention
guarantee. This option must be used with caution, because it can cause DML
operations to fail if the undo tablespace is not big enough. However, with
proper settings, long-running queries can complete without risk of receiving
the ORA-01555 "snapshot too old" error message, and you can guarantee
a time window in which the execution of Flashback features will succeed.
From 10g, you can use the DBA_TABLESPACES view to determine
the RETENTION setting for the undo tablespace. A column named RETENTION will
contain a value on GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces
other than the undo tablespace).
A
typical use of the guarantee option is when you want to ensure deterministic
and predictable behavior of Flashback Query by guaranteeing the availability of
the required undo data.
Enabling
and disabling undo retention guarantee
SQL>
ALTER TABLESPACE undotbs RETENTION GUARANTEE;
SQL> ALTER TABLESPACE undotbs RETENTION NOGUARANTEE
What
is ORA-01555 Error ?
Cause: Rollback
records needed by a reader for consistent read are overwritten by other
writers.
As we discussed earlier ,Undo also provides us the read
consistency to a user as he can only see the previous values until a
transaction is committed. To explain about ORA-01555 I would
give one example,
User
A started the select cursor on the table at time 10:00 PM and it
takes another 1 hour to fetch the data.
And
User B started updating the same table at the same time and it takes 2 hours to
complete the update.
Now
user A should get the read consistent version of the table between 10:00
PM to 11:00 PM . But the blocks are getting updated by User B after some
time.
User A getting the read consistent from UNDO ,And User B occupied
undo space .
Now
User C connected and trying to update some other table ,while User C required
Undo segments to complete his update.
As discussed earlier If Undo retention is in NOGURANTEE MODE
and the User C doesn't have enough space in Undo(Expired Blocks) to complete
the update then Undo data will be overwritten and
throughs ORA-01555 to the User A
v ORA-01555 relates to insufficient rollback segments
or undo_retentions parameter values that are not large enough. The
modified data by performed commits and rollbacks causes rollback data to be
overwritten when the rollback segments are smaller in size and number of the
changes being performed at the time.
How Can we Avoid
ORA-01555 Error?
To resolve
this issue, either increase the parameter of UNDO_RETENTION if you are
in AUM mode or use larger rollback segments.
The latter solution will allow your rollback data for
completed transactions to be kept longer.
You may also run into this error when cursors are not being in
programs after FETCH and UPDATE statements. Make sure you are closing cursors
when you no longer need them. The error can also appear if a FETCH
statement is run after a COMMIT statement is issued. If this occurs, you will
begin to overwrite earlier records because the number of rollback records
created since the last CLOSE will fill the rollback segments.
In
summary, follow these practices to avoid seeing error ORA-01555 in the
future:
v Do not run discrete queries
and sensitive queries simultaneously unless the data is mutually
exclusive.
v If possible, schedule
queries during off-peak hours to ensure consistent read blocks do not need
to rollback changes.
v Use large optimal
values for rollback segments.
v Use a large database
block size to maximize rollback segment transaction table slots.
v Reduce transaction slot reuse
by performing less commits, especially in PL/SQL queries.
v Avoid committing inside a cursor
loop.
v Do not fetch between commits,
especially if the data queried by the cursor is being changed in the current
session.
v Optimize queries to read
fewer data and take less time to reduce the risk of consistent get rollback
failure.
v Increase the size of your
UNDO tablespace and set the UNDO tablespace in guaranteed mode.
v When exporting tables, export
with CONSISTENT = no parameter.
When we execute an operation that needs to allocate
undo space:
v Allocate an extent in an undo segment
which has no active transaction. Why in other segments? Because Oracle tries to
distribute transactions over all undo segments.
v If no undo segment was
found, then oracle tries to online and off-line undo segment and uses it to
assign the new extent.
v If no undo segment was
possible to online, then Oracle creates a new undo segment and uses it.
v If the free space doesn't
permit creation of the undo segment, then Oracle tried to reuse an expired
extent from the current undo segments.
v If failed, Oracle tries
to reuse an expired extent from another undo segment.
v If failed, Oracle tries to
autoextend a datafile (if autoextensible=yes)
v If failed, Oracle tries to
reuse an unexpired extent from the current undo segment.
v If failed, Oracle tries to
reuse an unexpired extent from another undo segment.
v If failed, then only the
operation will fail.
The
only difference with retention guarantee is enabled is that Unexpired Extents
will not be reused.
In
other cases
if
there are no Expired extents that can be re-used then it's possible to
encounter ORA-30036.If we see mostly Unexpired extents then it can be either a
Undo spacing issue or caused by unreasonably high Undo_retentions.
In
other words, this means the Undo space is not enough for the specified
Undo_retentions or the Tuned_undoretention value.
If
Expired extents are present and if ORA-30036 is encountered , it means that the
EXPIRED extents are not being reused.
These
Expired extents should have been reused and instead we are getting an ORA-30036
error.
This
could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and
11g ).
And
this is the Oracle doc id to size undo tablespace for Automatic undo
management Doc ID 262066.1
Size of Undo
Tablespace:
You can size the undo tablespace appropriately either by using automatic
extension of the undo tablespace or by manually estimating the space.
Oracle Database supports automatic extension of the undo tablespace to
facilitate capacity planning of the undo tablespace in the production environment.
When the system is first running in the production environment, you may be
unsure of the space requirements of the undo tablespace. In this case, you can
enable automatic extension for datafiles of the undo tablespace so that they
automatically increase in size when more space is needed. By combining
automatic extension of the undo tablespace with automatically tuned undo
retention, you can ensure that long-running queries will succeed by
guaranteeing the undo required for such queries.
After
the system has stabilized and you are more familiar with undo space
requirements, Oracle recommends that you set the maximum size of the tablespace
to be slightly (10%) more than the current size of the undo tablespace.
If you have decided on a fixed-size undo tablespace, the Undo Advisor can help
us estimate needed capacity, and you can then calculate the amount of retention
your system will need. You can access the Undo Advisor through Enterprise
Manager or through the DBMS_ADVISOR package.
The Undo Advisor relies for its analysis on data collected in the
Automatic Workload Repository (AWR). An adjustment to the collection interval
and retention period for AWR statistics can affect the precision and
the type of recommendations the advisor produces.
Undo Advisor:
Oracle Database provides an Undo Advisor that provides advice on
and helps automate the establishment of your undo environment. You activate the
Undo Advisor by creating an undo advisor task through the advisor framework.
The following example creates an undo advisor task to evaluate the undo
tablespace. The name of the advisor is 'Undo Advisor'. The analysis is based on
AWR snapshots, which you must specify by setting parameters START_SNAPSHOT and
END_SNAPSHOT.
In
the following example, the START_SNAPSHOT is "1" and END_SNAPSHOT is
"2".
DECLARE
tid
NUMBER;
tname
VARCHAR2(30);
oid
NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo
Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname,'UNDO_TBS',null,
null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,
'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,
'START_SNAPSHOT', 1);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,
'END_SNAPSHOT', 2);
DBMS_ADVISOR.execute_task(tname);
end;
/
Once
you have created the advisor task, you can view the output and recommendations
in the Automatic Database Diagnostic Monitor (ADDM) in Enterprise Manager. This
information is also available in the DBA_ADVISOR_* data dictionary views.
Calculating
space requirements for Undo tablespace
You
can calculate space requirements manually using the following formula:
Undo
Space = UNDO_RETENTION in seconds * undo blocks for each second + overhead
where:
*
Undo Space is the number of undo blocks
*
overhead is the small overhead for metadata and based on extent and file size
(DB_BLOCK_SIZE)
As an
example, if UNDO_RETENTION is set to 2 hours, and the transaction rate (UPS) is
200 undo blocks for each second, with a 4K block size, the required undo space
is computed as follows:
(2 *
3600 * 200 * 4K) = 5.8GBs
Such
computation can be performed by using information in the V$UNDOSTAT view. In
the steady state, you can query the view to obtain the transaction rate. The
overhead figure can also be obtained from the view.
Monitoring Undo
Tablespaces:
Oracle Database also provides proactive help in managing
tablespace disk space use by alerting you when tablespaces run low on available
space.
In
addition to the proactive undo space alerts, Oracle Database also provides
alerts if your system has long-running queries that cause SNAPSHOT TOO OLD
errors. To prevent excessive alerts, the long query alert is issued at most
once every 24 hours. When the alert is generated, you can check the Undo
Advisor Page of Enterprise Manager to get more information about the undo
tablespace.
The
following dynamic performance views are useful for obtaining space information
about the undo tablespace:
Views:
V$UNDOSTAT
Contains
statistics for monitoring and tuning undo space. Use this view to help estimate
the amount of undo space required for the current workload. Oracle uses this
view information to tune undo usage in the system.
V$ROLLSTAT
For
automatic undo management mode, information reflects behavior of the undo
segments in the undo tablespace.
V$TRANSACTION
Contains
undo segment information.
DBA_UNDO_EXTENTS
Shows
the status and size of each extent in the undo tablespace.
WRH$_UNDOSTAT
Contains
statistical snapshots of V$UNDOSTAT information.
WRH$_ROLLSTAT
Contains
statistical snapshots of V$ROLLSTAT information.
To
findout the undo segments in the database.
SQL> select
segment_name, tablespace_name from dba_rollback_segs;
Undo Scripts :
For
Tablespace Space size :
SELECT
d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+
u.exp_space),2)
as
max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+
(1024*1024*u.exp_space)))*100/a.maxbytes),2)
used_pct
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes)
bytes,
sum(greatest(maxbytes,bytes))
maxbytes from sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name, sum(bytes) bytes from sys.dba_free_space group by
tablespace_name) f ,
(select
tablespace_name , sum(blocks)*8/(1024) exp_space from
dba_undo_extents
where status NOT IN ('ACTIVE','UNEXPIRED') group by
tablespace_name) u
WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
f.tablespace_name(+)
AND
d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND
u.tablespace_name = (select UPPER(value)
from
v$parameter where name = 'undo_tablespace');
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free
Space",
round((1-(b.fsz/a.tsz))*100)
"Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select
tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where
tablespace_name like '%&1%' group by tablespace_name) a,
(select
tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where
tablespace_name like '%&1%' group by tablespace_name) b
Where
a.tbl=b.tblsp;
Undo
Usage by Session:
col
name heading 'UNDO|Name' form a12
col
module heading 'Module' form a18 trunc
col
username heading 'User' form a10 wrap
col
sql_hash_value heading 'Hash Value' form 9999999999
col
totsize heading 'Undo Bytes Used' form 999,999,999,999
col
log_io heading 'Redo Log I/O' form 999,999,999
col
phy_io heading 'Physical I/O' form 999,999,999
col
used_ublk heading 'Undo Blks' form 9,999,999
col
spid heading 'Svr|PID' form 999999
col
sid heading 'SID' form 99999
col
serial# heading 'Serial#' form 9999999
selectr.name,p.spid,s.sid,s.serial#,s.username,s.module,s.sql_hash_value,t.used_ublk,used_ublk*8192
totsize,t.log_io,t.phy_io
from
v$transaction t, v$session s,v$process p, sys.v_$rollname r
where
t.ses_ADDR = s.saddr
AND
p.addr = s.paddr
and
t.xidusn = r.usn
and
used_ublk*8192 > 10000000
order
by r.name,totsize desc
/
To
show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace
select
tablespace_name,
status,
count(extent_id)
"Extent Count",
sum(blocks)
"Total Blocks",
sum(blocks)*8/(1024*1024)
total_space
from
dba_undo_extents
group
by tablespace_name, status;
To
check for Active Transactions
set
head on
select
usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576
optsize,shrinks,wraps
from
v$rollstat where xacts>0
order
by rssize;
Undo
retention in hours
col
"Retention" for a30
col
name for a30
col
value for a50
select
name "Retention",value/60/60 "Hours" from v$parameter where
name like '%undo_retention%';
To
show UndoRetention Value
Show
parameter undo_retention;
