 |
|
Managing the UNDO
Tablespace
Oracle Tips by Burleson
|
There are several initialization parameters
that control the UNDO tablespace feature of Oracle. They are:
UNDO_MANAGEMENT. Can be set to AUTO or
MANUAL. In AUTO, the database manages undo operations; under MANUAL,
the DBA manages undo operations. The default value is MANUAL, and any
UNDO_TABLESPACE specification is ignored. If set to MANUAL, management
of rollback segments is identical to pre-9i rollback segment
management.
UNDO_TABLESPACE. A dynamic parameter
that specifies which tablespace to use as an UNDO tablespace.
UNDO_RETENTION. A dynamic parameter
that specifies the length of time to retain undo information for use
in flashback queries; the default is 900 seconds.
UNDO_SUPPRESS_ERRORS. Either TRUE or
FALSE. Used to suppress error messages if manual undo management SQL
commands are entered in auto undo management mode.
Creation of the UNDO Tablespace
As stated above, the UNDO tablespace can be
created either when the database is created, using the UNDO TABLESPACE
clause of the CREATE DATABASE command, or it an be added later, using
the CREATE UNDO TABLESPACE command and the appropriate initialization
parameters set for it. An example UNDO tablespace creation command is
shown here:
CREATE UNDO
TABLESPACE undo_tbs1 DATAFILE '/oracle3/oradata/galinux1/data/undo_tbs2.dbf'
SIZE 200M AUTOEXTEND ON;
Oracle determines the other attributes of the
undo tablespace automatically; you only need to specify the datafile
clause and size information. The Oracle system will automatically
assign an UNDO tablespace with locally managed, auto-allocated
extents; the maximum size will be set at approximately one and a half
times the specified size. You can specify the autoextend parameters
and the extent management parameters in the CREATE UNDO command, but
options such as LOGGING will cause an error.
Altering an UNDO Tablespace
An UNDO tablespace can only be altered for the
following items:
* Adding a datafile.
* Renaming a datafile.
* Bringing a datafile on- or offline.
* Beginning or ending backup.
* Resizing a datafile.
Dropping an UNDO Tablespace
The UNDO tablespace(s) are dropped using the
DROP TABLESPACE command. The UNDO tablespace to be dropped cannot have
any currently active transactions.
Switching Active UNDO Tablespaces
Before dropping an UNDO tablespace, you will
need to make sure there is a second UNDO tablespace; then you must
switch the database to use the new tablespace. The ALTER SYSTEM
command is used to switch the database from one UNDO tablespace to
another, as shown here:
ALTER SYSTEM
SET UNDO_TABLESPACE = new_undo_tbsp_name;
Of course, if for any reason, the switch
cannot be accomplished, an error is issued and no switch occurs.
Errors that will prevent a switch are:
* Tablespace to be switched to doesn't exist.
* Tablespace to be switched to isn't an UNDO
tablespace.
* Tablespace to be switched to is in use by
another RAC instance.
If there are active transactions in the old
tablespace, the tablespace goes into PENDING OFFLINE status until the
transactions complete. A PENDING OFFLINE tablespace is in a state of
LIMBO until its incomplete transactions are finished. A tablespace in
PENDING OFFLINE cannot be dropped or used. If the UNDO_TABLESPACE
parameter is set to the null string, which in Oracle is represented by
two single quotes with no space between them. (for example, ALTER
SYSTEM SET UNDO_TABLESPACE='') then the existing undo tablespace is
taken offline and no new undo tablespace is assigned. @@@Au: Close
double quote mark correct here? No open quote given. Yes these are two
single quotes with no space, in Oracle this means the null string.@@@
The assignment of the UNDO tablespace to the null string would be used
if you were switching to MANUAL managed undo.
Assigning Usage Quotas for UNDO Tablespaces
A new feature in Oracle is the ability to
establish quotas on UNDO space for specific types of users. This is
accomplished using the DBMS_RESOURCE_MANAGER package. If a resource
group is assigned a quota on an UNDO tablespace, and that quota is
reached, the current transaction that is generating UNDO is halted and
rolled back and no further UNDO transactions will be allowed from that
resource group until the usage of UNDO space drops below its quota. If
no quota is assigned, a resource group is considered by default to be
unlimited. I will cover the use of DBMS_RESOURCE_MANAGER in detail in
Chapter 9, User and Security Administration.
Retention Periods and UNDO Tablespaces
One of the interesting new features in
Oracle dealing with UNDO tablespaces is the ability to perform
flashback queries. Flashback queries allow you to view data that has
been updated or deleted as it was before it was updated or deleted.
Being able to look back in time is accomplished through the use of the
UNDO tablespace retention capability. The DBA can specify how much
data, based on an asset retention period, is maintained for flashback
query. The default is 900 seconds (15 minutes). The retention period
for UNDO tablespaces is set using the UNDO_RETENTION initialization
parameter. The information retained for flashback query is persistent
between database startups. This persistence of data means that even
after a crash and restart the data will still be available. The
retention period restarts its countdown after each startup.
The ALTER SYSTEM command can also be used to
reset the retention period dynamically. The effect of the ALTER SYSTEM
command on retention time is immediate but will only be honored
completely if there is enough room in the UNDO tablespace to
accommodate the data requirements.
Flashback queries are facilitated using the
DBMS_FLASHBACK package, which is covered in Chapter 9. The space
required to facilitate flashback is calculated by the formula:
UndoSpace = UR * UPS + overhead
where:
* UndoSpace is the number of undo blocks
required.
* UR is the value of UNDO_RETENTION in
seconds.
* UPS is the number of UNDO blocks generated
per second.
* Overhead is a small number of blocks used
for metadata..
The information for the above calculation is
contained in the v$undostat view. A sample SELECT command to get this
information about number of transaction UNDO blocks per second since
database startup would be:
SELECT
SUM(undoblks)/((MAX(end_time)-MIN(BEGIN_TIME))*24*3600)
FROM v$undostat;
The problem with this SELECT is that it will
give an abnormally low value for databases with periods of low
activity. It may be better to look at all of the available periods and
get values that correspond to high activity times:
SELECT
undoblks/((end_time-begin_time)*24*3600)) FROM v$undostat;
More useful fields from the V$UNDOSTAT view
are SSOLDERRCNT and NOSPACEERRCNT. If you get values in either of
these columns for your current UNDO tablespace, then you may need
either a smaller UNDO_RETENTION parameter value or larger UNDO
tablespaces.
Other useful UNDO views are:
V$ROLLSTAT. Shows behavior of undo or
rollback segments.
V$TRANSACTION. Contains undo segment
usage information.
DBA_UNDO_EXTENTS. Shows the commit time
for each extent in the UNDO tablespace.
See
Code Depot for Full Scripts
 |
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
