|
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.
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;
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;
Altering the Undo TablespaceAltering 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 TablespacesYou 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 TablespaceSimply, 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.
Automatic Undo Management ParametersSeveral other parameters for the initialization files control different aspects of automatic undo retention. UNDO_SUPPRESS_ERRORSUNDO_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_RETENTIONThe 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 TablespaceTo appropriately determine the size for an undo tablespace, you need three pieces of information:
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.
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 QuotaLong 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. |
|