12.10.2006

Migrating to Automatic Undo Management

Although Oracle strongly recommends to use UNDO Management instead of Manual rollback segments, at my previousorgaization, we were shifted from auto undo management to manual rollback segments.I had discussed the reason behind this shift in my old article titled as 'Auto Undo Management performance degradation'.
http://jaffardba.blogspot.com/2006_04_01_jaffardba_archive.html

The problem was with Oracle 9i (9205) version. Now I heard that the database already upgraded to 10gR2.

I am really inspired by the following features of 10gR2 about Auto Undo Management. Therefore,I will recommend my ex-boss to revert back to UNDO MANAGEMENT option.

I can convieance him with the following valid reasons:

Reason One: - Fast Ramp-Up in 10gR2.

In Oracle10gR2 , when the database startsup, undo segments which status were online during the shutdown will be online again. This will avoid the overhead of bringing undo segments online on a heavy OLTP database.

Reason Two: - Migration easy step.

To get an idea how long the UNDO talespace should be, can be easily done by using DBMS_UNDO_ADV.RBU_MIGRATION package provided in 10gr2.

DECLARE
utbsiz_in_MB NUMBER;
BEGIN utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
end;/


http://www.oracle.com/technology/oramag/oracle/05-jul/o45tuning.htmlhttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm

Reason Three:
Undo guarantee and Automatic Tuning of Undo Retention.


Happy Reading,
Jaffar

4 comments:

Bayaraa said...

Hi
I am new for Oracle DB and currently I installed Oracle 10g for .NET development.
My purpose is identify/display the TOTAL SIZE which I can use for ROLLBACK.
I mean, I want to rollback/commit 10000xxx new data in my program.
Currently, I have not error or problems.
But I need to know how many K/M/G bytes data Oracle can rollback, one time.

From your blog and other Oracle site, I understand I can do it following command:

---------
SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
begin
utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line(TO_CHAR(utbsize_in_MB));
end;
/

30
PL/SQL procedure successfully completed.
----------

It means, UNDO table size of my Oracle DB server is 30M also auto mode, right?
If I want to use more than 30M size, what happen ?
I need to configure other things?

Thank you for advice.

Bayaraa said...

Hi
I am new for Oracle DB and currently I installed Oracle 10g for .NET development.
My purpose is identify/display the TOTAL SIZE which I can use for ROLLBACK.
I mean, I want to rollback/commit 10000xxx new data in my program.
Currently, I have not error or problems.
But I need to know how many K/M/G bytes data Oracle can rollback, one time.

From your blog and other Oracle site, I understand I can do it following command:

---------
SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
begin
utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line(TO_CHAR(utbsize_in_MB));
end;
/

30
PL/SQL procedure successfully completed.
----------

It means, UNDO table size of my Oracle DB server is 30M also auto mode, right?
If I want to use more than 30M size, what happen ?
I need to configure other things?

Thank you for advice.

BAYARAA said...

Hi

I read your blog from internet, thank you for nice information of Oracle.

I am new for Oracle DB and currently I installed Oracle 10g for .NET development.
My purpose is identify/display the TOTAL SIZE which I can use for ROLLBACK.
I mean, I want to rollback/commit 10000xxx data in my program.
Currently, I have not error or problems.
But I need to know how many K/M/G bytes data Oracle can rollback.

From your blog and other Oracle site, I understand I can do it following command:

---------
SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
begin
utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line(TO_CHAR(utbsize_in_MB));
end;
/


30 (my result after this command)
PL/SQL procedure successfully completed.
----------

It means, UNDO table size of my Oracle DB server is 30M also auto mode, right?
If I want to use more than 30M size, what happen ?
I need to configure other things?
Automatic UNDO management, it means Oracle manage this size when we need more than 30M size?
I want to know/display exact total size that Oracle can increase automatically for rollback. ?

Thank you for advice.

BAYARAA said...

I am very sorry, i did commit 3 times a same message. Do not approval all, sorry