|
Oracle recommends using the automatic undo method for rollback purposes in a
RAC system. Each instance in the RAC system can only use one undo tablespace
at a time. In other words, instances cannot share undo tablespaces. Each
instance in the cluster, being an independent transaction-processing
environment, maintains its own UNDO area for undo management.
We can use either automatic undo management or rollback segment undo to
manage undo space. If you are planning to follow the automatic rollback
method, set the global parameter undo_management to auto in your
server parameter file, and set the undo_tablespace parameter to
assign the undo tablespace to the instance.
The RAC system allows you to create and use several undo tablespaces. When
the instance is started, it uses the first available undo tablespace. A
second instance will use another undo tablespace. Thus, each instance in a
RAC system will have exclusive access to a particular undo tablespace at a
given time. The undo tablespace cannot be shared among the instances at the
same time. Once an undo tablespace is released by an instance, it can be
assigned to another instance. However, all instances can read blocks from
any or all undo tablespaces for the purpose of constructing read-consistency
images.
You can switch to an idle undo tablespace if the need arises. This feature
is useful if you want to switch to a new undo tablespace located in a
different file system, or a new undo tablespace that is larger in size.
You may occasionally want to switch to a new undo tablespace for the purpose
of executing a large batch process at night. This way, the instance keeps
running without any down time. At the time of the switch, the instance
maintains control of both the old and new undo tablespaces. The old undo
tablespace is marked ‘pending-offline’ until all the transactions using it
are completed.
You can dynamically redirect undo tablespaces by executing the alter system
set undo_tablespace statement. For example, assume you have instances
RAC1 and RAC2 accessing undo tablespaces undotbs01 and undotbs02
respectively. If you have an idle undo tablespace, for example, undotbs03,
you can execute the following statement from either instance to redirect
undo processing to undotbs03.
ALTER SYSTEM SET UNDO_TABLESPACE =
undotbs3;
The undo tablespace is usually defined when the database is created by using
the create DATABASE statement as shown in the following example:
CREATE DATABASE racdb
.
.
UNDO TABLESPACE undotbs_01
DATAFILE '/u01/oracle/rbdb1/undo_01' SIZE 1024M;
The undo tablespace can also be defined after the database is created, as
shown here in this example:
Create UNDO tablespace UNDOTBS_3
DATAFILE ‘/u01/oracle/rbdb1/undo_3’ SIZE 512M ;
If an undo tablespace runs out of space, or if you want to prevent it from
doing so, you can add files to it or resize the existing data files. The
following example adds another data file to the undo tablespace undotbs_3:
ALTER TABLESPACE undotbs_3
ADD DATAFILE ‘/u01/oracle/rbdb1/undo_31‘ SIZE 128M ;
Initialization Parameters for UNDO management
The following initialization parameters are relevant for undo management:
- undo_management - If AUTO, use automatic undo management mode.
If MANUAL, use manual undo management mode.
- undo_tablespace - A dynamic parameter specifying the name of an
undo tablespace to use.
- undo_retention - A dynamic parameter specifying the length of
time to retain undo. Default is 900 seconds.
- undo_suppress_errors – Set TRUE to suppress error messages
generated by manual undo SQL statements when operating in automatic undo
management mode. If FALSE, error messages are issued. This is a dynamic
parameter.
To view the existing parameters, use the command:
SQL> show parameter undo
NAME
TYPE VALUE
-------------------------------- ----------- ----------undo_management
string AUTO
undo_retention
integer 900
undo_suppress_errors
boolean FALSE
undo_tablespace
string UNDOTBS1
 |
If you like Oracle tuning, check out my latest book
"Oracle
Tuning: The Definitive Reference".
It's 980 pages of hard-core tuning insights, tips and
scripts, and you can buy it direct from the publisher for 30%-off. |
|
