Automatically Managing Undo
Automatically Managing Undo
This tutorial describes how Oracle Database 10g automates
the management of undo.
Approximately 15 minutes.
This tutorial covers the following topics:
 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.
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
Before starting this tutorial, you should:
Back to Topic List
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
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 SQL*Plus 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
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 SQL*Plus 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
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.
|
