Automatically Managing Undo

Purpose

This tutorial describes how Oracle Database 10g automates the management of undo.

Time to Complete

Approximately 15 minutes.

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Creating an Undo Tablespace
	Generating Some Activity
	Reviewing Undo Advisor Recommendations
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

To simplify management of rollback segments, the Oracle Database has Automatic Undo Management (AUM) where the database automatically manages allocation and management of undo (rollback) space among various active sessions. In a database instance that uses Automatic Undo Management, all transactions share a single undo tablespace. Any executing transaction can consume free space in this tablespace. Undo space is dynamically transferred from committed transactions to executing transactions in the event of space scarcity in the undo tablespace.

Automatic Undo Management also helps avoid ORA-1555 error ("Snapshot too old error") by automatically adjusting the retention of undo based on current system activity. This maximizes the usage of the available space in the undo tablespace and allows long-running queries to complete without encountering any “Snapshot Too Old” errors provided that your undo tablespace is big enough.

In Oracle Database 10g Release 1, undo retention was tuned to stay just ahead of the longest running query even if the size of the undo tablespace was large enough to provide much longer retention. This meant that you did not have to worry about queries or transactions failing with the “snapshot too old” error message but if you wanted to use Flashback features, you still had to specify the minimum undo retention value by setting the UNDO_RETENTION parameter. In Oracle database 10g Release 2 this feature has been enhanced and the system is tuned to give you the best possible undo retention for a given undo tablespace. This means that now you don't even have to set UNDO_RETENTION parameter for flashback purposes. The only remaining configuration requirement is to size the undo tablespace adequately.

In this tutorial, you intentionally switch to a small undo tablespace, and although you use Automatic Undo Management, you encounter ORA-1555 ("Snapshot too old") because the undo tablespace is too small. You then use the Undo Advisor to compute the right size of the undo tablespace for your workload.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.	Perform the Installing Oracle Database 10g on Windows tutorial.
2.	Download and unzip the undo.zip into your working directory (c:\wkdir).

Back to Topic List

Creating an Undo Tablespace

To create an undo tablespace and use it for undo retention in Enterprise Manager, perform the following steps:

1.	Open a browser and enter the following URL (Replace <hostname> with your own host name or IP address): http://<hostname>:1158/em Enter sys as the username, oracle for the password, connect as SYSDBA, and then click Login.
2.	Click the Administration tab.
3.	Click Tablespaces under Storage.
4.	Click Create.
5.	Enter UT1 as Name and select Undo as Type. Click Add to add a data file.
6.	Enter UT1.DBF as File Name. To specify the File Size, enter 400 and select KB from the drop-down list. Click Continue.
7.	Click OK.
8.	Your undo tablespace UT1 is created successfully.

Back to Topic List

Generating Some Activity

To see how undo management is handled, you must generate some activity. Perform the following steps:

1.	Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK. Then enter the following command, to determine the date and time of your database: @c:\wkdir\datetime The datetime.sql script contains the following commands: connect / as sysdba select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
2.	Enter the following command, to switch Undo tablsepaces: @c:\wkdir\setundo The setundo.sql script contains the following command: alter system set undo_tablespace = ut1 scope=memory;
3.	From the same session, execute the following command. If you get an error, wait for a minute and execute the script again. @c:\wkdir\newtable The newtable.sql script contains the following commands: drop table newtable purge: create table newtable(b int, s varchar2(100)); begin for i in 1..100 loop insert into newtable values(i, rpad('s', 100)); end loop; commit; end; / If the NEWTABLE does not exist, the DROP statement causes an error, which you can ignore.
4.	From the same session, execute the following script: @c:\wkdir\uscript1 The uscript1.sql script contains the following commands: prompt Proceed with the next step declare b number; cursor c1 is select b from newtable; begin open c1; loop fetch c1 into b; dbms_lock.sleep(6); exit when c1%notfound; end loop; close c1; commit; end; / Proceed with the next step.
5.	Open another SQLPlus session (as described in step 1) and execute the following commands: @c:\wkdir\uscript2* The uscript2.sql script contains the following commands: connect / as sysdba prompt Proceed with the next step begin for i in 1..1000 loop update newtable set b=b+1, s=rpad('t', 100); commit; end loop; end; / Proceed with the next step.
6.	Switch back to the first SQL*Plus session. Soon, you get a "Snapshot too old" error. Although you are using Automatic Undo Retention tuning, your undo tablespace is too small to accommodate the update script.

Back to Topic List

Reviewing Undo Advisor Recommendations

To review the recommendations that the undo advisor made, perform the following steps:

1.	Switch back to Enterprise Manager and click Administration.
2.	Click Undo Management.
3.	You can see the potential problems and the recommendations suggested by Undo Advisor. Follow the recommendations and increase the size of your undo tablespace. Click Edit Undo Tablespace. Note: The recommendations vary depending on the activity of your database.
4.	Click Add to add a data file.
5.	Enter UT2.DBF as the alias name and specify the file size (e.g. 30 MB) as per the recommendations. Click Continue.
6.	Click Apply.
7.	Click on Database Instance: orcl locator link.
8.	Click Undo Management under Database Configuration.
9.	Note that there are no problems or recommendations any longer. You may try running uscript1 and uscript2 again to make sure that the size of your tablespace is appropriate.
10.	Return to one of your SQLPlus sessions and execute the following script: @c:\wkdir\cleanup* The cleanup.sql script contains the following commands: alter system set undo_tablespace = undotbs1 scope=memory; drop tablespace ut1 including contents and datafiles; host del C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UT1.DBF host del C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UT2.DBF

Back to Topic List

Summary

In this tutorial, you learned how to:

	Create an undo tablespace and use it for undo retention
	Review undo advisor recommendations

Back to Topic List

Place the cursor on this icon to hide all screenshots.