Implementing Automatic Undo Management


Configuration of your database and its instance to use automatic undo management requires the addition of two parameters in the initialization file: UNDO_MANAGMENT and UNDO_TABLESPACE.

  • UNDO_MANAGEMENT tells the system whether it should use the automatic (determined by setting this parameter to AUTO) method of undo management or the manual (determined by setting this parameter to MANUAL) method. UNDO_MANAGEMENT cannot be reset dynamically after the database starts. AUTO starts the database in the automatic undo method and requires that you have at least one undo tablespace created in the database in which it can manage its undo segments. If you choose to run your instance in MANUAL mode, which is still the default mode, you can create and manage the undo segments whenever needed by the database just as in previous versions of Oracle.

  • UNDO_TABLESPACE specifies to Oracle which undo tablespace is to be used for the management of undo segments in the database. This parameter can be set in the initialization file or can be set, and reset, dynamically using the ALTER SYSTEM command as follows:

     ALTER SYSTEM SET UNDO_TABLESPACE = mydb1_undo1; 

In addition, you have to create at least one undo tablespace in the database. If you have only created one undo tablespace in the database and your UNDO_MANAGEMENT parameter is set to AUTO, the UNDO_TABLESPACE parameter is optionally set and will default to the single UNDO_TABLESPACE that is configured.

You will recall that, when we created the database earlier, we created it with an undo tablespace mydb1_undo1 in the following manner:

 CREATE DATABASE mydb1 CONTROLFILE REUSE LOGFILE GROUP 1 ('/mydatabases/mydb1/log01a.log', '/myotherdatabase/mydb1/log01b.log') SIZE 50M, GROUP 2 ('/mydatabases/mydb1/log02a.log', '/myotherdatabase/mydb1/log02b.log') SIZE 50M, GROUP 3 ('/mydatabases/mydb1/log03a.log', '/myotherdatabase/mydb1/log03b.log') SIZE 50M, GROUP 4 ('/mydatabases/mydb1/log04a.log', '/myotherdatabase/mydb1/log04b.log') SIZE 50M DATAFILE '/mydatabase/mydb1/mydb1_system.dbf' 250M UNDO TABLESPACE mydb1_undo1 DATAFILE '/mydatabase/mydb1/mydb1_undo1.dbf' 50M DEFAULT TEMPORARY TABLESPACE mydb1temp tempfile '/mydatabases/mydb1/mydb1temp1.dbf' size 75M EXTENT MANAGEMENT LOCAL; 

During database creation, if you have set UNDO_MANAGEMENT to AUTO and have omitted the creation of the undo tablespace, Oracle creates an undo tablespace for you called SYS_UNDOTBS with the default data file located in the $ORACLE_HOME/dbs directory. The data file will be named dbu1<SID>.dbf and will have an operating system dependent size and AUTOEXTEND set to ON.


You can also create the undo tablespace after database creation with the following CREATE UNDO TABLESPACE command:

 CREATE UNDO TABLESPACE mydb1_undo1 DATAFILE '/mydatabase/mydb1/mydb1_undo1.dbf' 50M; 

Notice that the CREATE TABLESPACE command for the undo tablespace requires the UNDO keyword. This tells Oracle that this tablespace needs to be created specially for the expressed purpose of maintaining undo information.


Altering the Undo Tablespace

Altering an undo tablespace is the same as altering any other tablespace, you simply make the alterations in the ALTER TABLESPACE command, including adding data files, resizing data files and in effect the tablespace, onlining and offlining the data file, beginning and ending backups, and anything else that you can do with a tablespace.

Switching Undo Tablespaces

You can freely switch from using one undo tablespace to using another. Although only one undo tablespace can be active in a database at any one time, you can make different ones active whenever you see the need do so. Because more than one undo tablespace can exist in a database at any one time, with only one active, this is much easier to do on-the-fly. You can simply switch between undo tablespaces with the ALTER SYSTEM command that follows:

 ALTER SYSTEM SET UNDO_TABLESPACE=mydb1_undo2; 

Although this command will take effect immediately, it is important to note that the "old" undo tablespace may remain active for a period, because active transactions won't be switched to the new tablespace. As new sessions connect and new transactions start, those transactions will be started in the new tablespace.

Dropping an Undo Tablespace

Simply, the DROP TABLESPACE command drops an undo tablespace as elegantly as it drops any other tablespace. An undo tablespace can be dropped only if no active transactions are using the tablespace for maintenance of its undo segments. If the undo tablespace that you need to drop is the active undo tablespace, you first need to make another undo tablespace the active undo tablespace and then drop the tablespace after all active transactions that were using it have completed. The following query can help you to determine when all transactions have stopped using this undo tablespace:

 SELECT a.name,b.status FROM v$rollname a, v$rollstat b WHERE a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'MYDB1_UNDO1') AND a.usn = b.usn; 

Whenever this query returns with no rows found, you can safely stop the undo tablespace in question.

Oracle may still have references to an old undo tablespace even after it has been switched to a new one for the purpose of read consistency. Queries that require information from the switched or dropped undo tablespace after it is no longer available will receive the error: ORA-1555 snapshot too old.


Automatic Undo Management Parameters

Several other parameters for the initialization files control different aspects of automatic undo retention.

UNDO_SUPPRESS_ERRORS

UNDO_SUPPRESS_ERRORS enables users to suppress errors that they may encounter while executing manual undo management mode operations while in automatic undo management mode. An example of an operation that would cause errors to be generated is ALTER ROLLBACK SEGMENT ONLINE (or OFFLINE). These commands are already often embedded in programs, and the switch from manual to automatic undo management mode would ordinarily cause these commands to fail and the program from where they are called to likely fail as well. Setting this parameter enables the use of the automatic undo segment management before all the programs and scripts have been converted completely to remove references to these manual mode commands.

If this parameter is unset, the program would cause the following ORA-30019 error:

 ORA-30019: Illegal rollback segment operation in the automatic undo mode. 

UNDO_RETENTION

The UNDO_RETENTION parameter determines how long to retain undo data to provide for consistent reads in the database. The retention of undo data longer allows for longer queries to run when active updates are occurring in the database. It also means that larger files need to be allocated for the undo tablespace. The UNDO_RETENTION parameter is defined in seconds and can be set either in the initialization file or modified dynamically with the ALTER SYSTEM command. To dynamically set the undo retention period to 20 minutes, you would execute the following ALTER SYSTEM command:

 ALTER SYSTEM SET UNDO_RETENTION=1200; 

Even if you have the UNDO_RETENTION parameter set, if the undo tablespace is sized too small, undo data will not be retained for the specified period of time. Oracle uses a space allocation algorithm within the undo tablespace to allocate space that has information that has not yet passed the time allotted by UNDO RETENTION but that has no active transactions before it allows a transaction to fail. But this does not preclude the failure if not enough space is available. This may be particularly true in an extremely active database.

Sizing the Undo Tablespace

To appropriately determine the size for an undo tablespace, you need three pieces of information:

  • You need to determine how long you need to maintain undo information in your instance. This is UNDO_RETENTION (UR) in seconds.

  • You need the number of undo data blocks generated per second (UPS) at a peak time in your database. This information can be gotten by querying the V$UNDOSTAT view to determine how many blocks are used in given 10-minute intervals and dividing:

     SELECT end_time, begin_time, undo FROM v$undostat; 

    If you determine that you want to maintain undo information for 30 minutes, find the three highest consecutive time periods, or just the highest three time periods, and that will give you the blocks used in 30 minutes. Add up these blocks and divide by 1,500 to get the blocks per second.

    You can also use the following command to determine the number of undo blocks generated per second by running the following query:

     SELECT (SUM(undoblks)/SUM((end_time  begin_time) * 86400)) FROM v$undostat; 

    Because whenever you subtract date data types, even if they only span 10 minutes, you are returned the value in days. You need to multiple by the number of seconds in a day to get the number of seconds elapsed in that time period.

  • You will need to determine the db_block_size (DBS).

When you have all three pieces of information, you can mathematically determine how much undo space to allocate for the database by the following formula:

 Undospace = [UR * UPS * DBS] + DBS * 24 

Or, to generate that information directly from the database, you could run the following query:

 SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size'); 

If you are returned the value 20971520 (as the number of bytes), you will need to divide this number by 1,048,576, the number of bytes in a megabyte, to get the number of megabytes of undo space you need to allocate.

Although you can quantitatively determine how much undo space you will need for the given database under the current load, it would be wise to add a 10% or 15% fudge factor for growth and additional user requirements.


Now that you have allocated the undo space to your database, it still needs to be managed judiciously.

To make the best use of the undo space allocated to a database, you can make decisions concerning space and its allocation. This next section addresses these decisions.

Undo Quota

Long and improperly written transactions can consume more than necessary resources in their executions. Using resource plans, users can be grouped together, and limits can be placed on the amount of resources (undo and others) that can be used by that group. The amount of undo data that can be generated by a given group can be limited by the use of UNDO_POOL and setting its value to some number. By default, this value is unlimited.

Whenever a group, or anyone in that group, exceeds its limits, an error is generated, and no new transactions can be performed until the current transaction either completes or aborts.

Users receive the following error message if their group exceeds its quota:

 ORA-30027: Undo quota violation, failed to get <number> bytes. 

Advice on this error explains to the user that the amount of undo space assigned to the group of the given session has been exceeded. They are instructed to either ask the DBA to increase the quota or wait until other transactions commit before proceeding.

Now that we have created the undo tablespaces and determined how to limit allocation of segments to a given individual or to a group, we now need to determine where to find information in the data dictionary on undo segments.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net