Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter