 |
|
Manual Rollback Segment
Maintenance
Oracle Tips by Burleson
|
If you are in pre-9i databases, or just like
to practice all those hard-earned rollback segment management skills
you needed in previous Oracle releases, you can manage the UNDO (read
rollback) segments manually. All you need to do is set the
initialization parameter UNDO_MANAGEMENT to MANUAL (or don't set it at
all) and poof! You are back in pre-9i behavior.
The following initialization parameters
control rollback segment behavior in pre-9i or in 9i with manual UNDO
management:
UNDO_MANAGEMENT. Must be left unset or
set to MANUAL.
ROLLBACK_SEGMENTS. A list of all
private rollback segments to be brought online on instance startup.
TRANSACTIONS. Must be set to the number
of expected transactions if public rollback segments are used; usually
this value is automatically set to 1.1*PROCESSES, so set PROCESSES
instead of manually setting TRANSACTIONS.
TRANSACTIONS_PER_ROLLBACK_SEGMENT. Set
to the maximum number of concurrent transactions that will be allowed
to access a single rollback segment; should usually equal the
MINEXTENTS for a public rollback segment. The calculation:
TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT
MAX_ROLLBACK_SEGMENTS. Sets the maximum
rollback segments that can be brought online for any instance. This
defaults to the above ratio for public rollback segments, 30 for
private rollback segments, and requires a database bounce to increase
or decrease.
The following subsections discuss rollback
segment management for manual mode.
Creating Rollback Segments
Manually managed rollback segments are created
using the CREATE command. The format for this command follows.
CREATE
[PUBLIC] ROLLBACK SEGMENT rollback name
TABLESPACE tablespace name
STORAGE storage clause;
where:
Rollback name is the name for the rollback
segment; this name must be unique.
Tablespace name is the name of the tablespace
where the segment is to be created.
STORAGE clause specifies the required
storage parameters for the rollback segment. It is strongly suggested
that the following guidelines be used for this:
INITIAL = NEXT. For an operating
database, I usually recommend that the dba_rollback_segs and
v$rollstat views be used to determine the settings for INITIAL, NEXT,
and OPTIMAL, setting INITIAL=NEXT and both equal to the multiple of
the average transaction size as reported in the aforementioned views.
MINEXTENTS = 2 (default on CREATE
ROLLBACK). Oracle suggests this be set to 20; however, I believe
it should be set based on your database usage characteristics. I
generally shoot for four transactions per rollback segment. Remember,
only DML transactions and some DDL (that result in tables in the data
dictionary receiving updates) require rollback segments.
MAXEXTENTS. A calculated maximum based
on the size of the rollback segment tablespace, the size of rollback
segments extents, and the number of rollback segments.
OPTIMAL. Reflects the size to
which the system will restore the rollback segment after it has been
extended by a large transaction. Usually, I set it to MINEXTENTS
multiplied by the initial/next values. Another method is to set
OPTIMAL as the average of the maximum transaction size times a
multiple such as 1.25, as indicated in the dba_rollback_segs and
v$rollstat views, and then back-calculate MINEXTENTS.
When a rollback segment is created, it is not
online. To be used, it must be brought online using the ALTER ROLLBACK
SEGMENT name ONLINE; command, or the database must be shut down, the
INIT.ORA parameter ROLLBACK_SEGMENTS modified, and the database
restarted.
Altering a Rollback Segment
The rollback segment can be altered using the
ALTER command. However, this can result in mismatched extent sizes, so
is not recommended. You cannot alter a rollback segment from public to
private or private to public; it must be dropped and re-created for
this type of change. The format of the command follows:
ALTER
ROLLBACK SEGMENT name
STORAGE(storage_clause_changes)
ONLINE
OFFLINE
SHRINK TO n K|M
where:
ONLINE brings the rollback segment
online.
OFFLINE takes the rollbacks segment
offline (after any transactions it has are completed).
STORAGE. This clause cannot
contain new values for INITIAL, MINEXTENTS, or PCTINCREASE (which is
not allowed for rollback segments).
SHRINK returns a rollback segment that
has expanded beyond its OPTIMAL setting to the OPTIMAL size or to the
size specified.
Dropping a Rollback Segment
Periodically, the DBA will have to drop a
rollback segment. This is required when the rollback segment has
overextended due to a large transaction, has too many extents in
general, or a larger size is desired for all rollback segments. This
is accomplished through the DROP command. The format of this command
follows:
DROP
ROLLBACK SEGMENT name
A rollback segment must not be in use or
online, or it cannot be dropped. Once dropped, it must be removed from
the INIT.ORA - ROLLBACK_SEGMENT clause, or the database cannot be
restarted.
Moving Database Files
Periodically, DBAs will need to move database
files, such as the SYSTEM tablespace datafiles or redo logs, from one
location to another. This is accomplished through the following
procedure:
1.
Shut down the database.
2.
Use the operating system to copy the file(s) to their new
location. (On UNIX, don’t move them with the mv command; use cp or
dd.)
3.
Using SVRMGR, or SQLPLUS in Oracle8i, issue the CONNECT
INTERNAL or CONNECT SYS command to connect to the database.
4.
Using SVRMGR, or SQLPLUS in Oracle8i, MOUNT the database, but
don’t OPEN it.
5.
Issue the ALTER DATABASE command to rename the file.
6.
ALTER DATABASE database name
7.
RENAME DATAFILE 'OLD FILE NAME' TO 'NEW FILE NAME';
8.
Shut down and restart the database.
9.
Use SVRMGR or SQLPLUS to look at the view DBA_DATA_FILES to be
sure the file is renamed.
10.
Delete the old file via the appropriate operating system
command. (Be sure the database is started before you delete the file;
if the database is running, it will prevent you from deleting files
that are still active on VMS and NT. In UNIX, use the “fuser” command
against the file to see if it is active.)
Use of the ALTER SYSTEM Command
The ALTER SYSTEM command is used to
dynamically alter the Oracle instance under an Oracle7, Oracle8,
Oracle8i or Oracle database. The ALTER SYSTEM command allows the
following system-level items to be modified:
* Set or change resource limits.
* Create or terminate shared server or
dispatcher processes.
* Switch redo log groups.
* Perform a checkpoint.
* Verify data file access.
* Restrict login to users with RESTRICTED
SESSION privilege (replaces DBA mode).
* Perform distributed recovery in single
process environment.
* Disable distributed recovery.
* Manually archive redo logs or enable or
disable automatic archiving.
* Clear the shared pool in the SGA.
* Terminate a session.
The ALTER SYSTEM command allows the DBA much
greater control over the Oracle environment than was possible in
previous releases.
ALTER SYSTEM Command Format
The ALTER SYSTEM command’s format follows:
ALTER SYSTEM
SWITCH LOGFILE
CHECKPOINT [GLOBAL|LOCAL]
CHECK DATAFILES [GLOBAL|LOCAL]
ENABLE|DISABLE RESTRICTED SESSION
ENABLE|DISABLE DISTRIBUTED RECOVERY
Archive_log_clause
FLUSH SHARED_POOL
KILL SESSION 'N1,N2'
DISCONNECT SESSION 'N1,N2' POST TRANSACTION
SUSPEND|RESUME
QUIESCE RESTRICTED|UNQUIESCE
SHUTDOWN [IMMEDIATE] [dispatcher name]
REGISTER
SET clause
RESET clause
where:
SWITCH LOGFILE. This switches the
active log file groups.
CHECKPOINT. This performs either
a GLOBAL (all open instances on the database) or LOCAL (current
instance) checkpoint.
CHECK DATAFILES. This verifies
access to data files. If GLOBAL is specified, all data files in all
instances accessing the database are verified accessible. If LOCAL is
specified only the current instance’s data files are verified.
ENABLE RESTRICTED SESSION. This
only allows users with RESTRICTED SESSION privilege to log on to the
database.
DISABLE RESTRICTED SESSION. This
allows any user to log on to the instance.
ENABLE DISTRIBUTED RECOVERY. This
enables distributed recovery.
DISABLE DISTRIBUTED RECOVERY.
This disables distributed recovery.
ARCHIVE LOG
clauses:
THREAD n
[SEQUENCE n] [TO 'location']
[CHANGE n] [TO 'location']
[CURRENT] [TO 'location']
[GROUP n] [TO 'location']
[LOGFILE 'filename'] [TO 'location']
[NEXT] [TO 'location']
[ALL] [TO 'location']
[START] [TO 'location']
[STOP]
FLUSH SHARED_POOL -- Flushes SQL and
PL/SQL form shared pool if it is not pinned using the DBMS_SHAREDPOOL
package.
KILL SESSION 'N1,N2' -- Kills the
session pid N1=sid and N2=serial number from V$SESSION
DISCONNECT SESSION 'N1,N2' [IMMEDIATE|POST
TRANSACTION] -- A nicer form of KILL, allows transactions to
complete with the POST TRANSACTION, same as KILL if IMMEDIATE is used.
SUSPEND|RESUME -- used for suspension
of all IO as well as queries, in all instances, allowing you to make
copies of the database without having to handle ongoing transactions.
Database tablespaces must be in hot backup mode. This is useful for
when you don't want the penalties of shutting down, restarting the
database and reloading the SGA as you would for a cold backup but
don't need the database to be available as it would be during a normal
hot backup.
QUIESCE RESTRICTED|UNQUIESCE -- Used to
restrict access to a database so the DBA can use the SYS or SYSTEM
accounts to perform maintenance. No new transactions or connections
are allowed in the QUIESCE RESTRICTED mode. Oracle will wait for
current transactions to complete and resource to be release before
going into QUIESCE.
SHUTDOWN [IMMEDIATE] [dispatcher name]
-- Used to immediately stop a dispatcher process when you are using
MTS for your database. If IMMEDIATE is specified the dispatcher is
stopped now, if IMMEDIATE is not specified it tells the dispatcher to
not accept any more connections and to terminate when its last
transaction completes.
REGISTER -- Causes the instance to
register with the listener processes immediately instead of waiting
for the next PMON discovery cycle. If not used the registration can
take up to 60 seconds or more, which prevents the database from being
accessed by remote users.
SET clause -- used to set various
initialization parameters dynamically. Allows specification of a
comment that is added to the value in the V$PARAMETER file, a new
value to which the parameter is reset, the scope of the value change,
and whether the change is just for the memory or should be made
permanent in the spfile.
RESET clause -- Same as for SET clause
only applies to specific instances in a RAC cluster.
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. |
|
