Begining Rollback-to-Undo conversion research
One of our clients needs an upgrade to take advantage of 9i's UNDO automation.
I am going to embark on the best approach to this - if anyone knows a great link please post it here. Otherwise I will post what I end up going with.
FYI, You should watch out for any set transaction statements in your code.
this space intentionally left blank
ooooooooo!!! good catch Gandolf!
Originally Posted by gandolf989
Oracle it's not just a database it's a lifestyle!
BTW....You need to get a girlfriend who's last name isn't .jpg
Tim, did you author that article ?
"Hope this helps. Regards Tim..."
I am wondering about how sized rollback segments map over to the size of undo segments.
..off to tahiti..
Finding that most content on the web talks about 10G, this is a 9i client so I need a way to size it based on existing rollback. I could just make it as big as all rollback space.
DBMS_UNDO_ADV.RBU_MIGRATION ( 10 G )
Migrating from Rollback Segments to UNDO Tablespace
To move an existing Oracle9i or Oracle Database 10g system from manual undo management (using rollback segments) to automatic undo management requires a shut down and restart—you must shut-down the database, set the undo_management parameter to AUTO, and then restart the database—assuming you've created the UNDO tablespace first, however. To get some guidance about how large to make that UNDO tablespace, you can use a function from the Undo Advisor PL/SQL package created just for that purpose, as in this example:
SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
3 utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
PL/SQL procedure successfully completed.
In this case, the return value of 187 is the suggested starting size in megabytes for an UNDO tablespace based on current rollback segment utilization in an existing system.
Shouldn't pose a problem if undo_supress_errors=TRUE
Originally Posted by gandolf989
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Yes I did ;-)
Originally Posted by roadwarriorDBA
I would start by making the UNDO tablespace about that same size as your current total redo size. Of course, if you want to retain the data for longer you are going to need a bit more space.
Once you are up and running you can use the undo advisor to check how your undo is performing, and what sort of retention time you can expect under typical load.
Suppress errors? Yes, unless you want to identify code that tries to use a specific RBS.
So I offline the rb segments the drop the RBS tablespace including contents.
SYSTEM SYSTEM 819200
R000 SYSTEM 1064960
RBS01 RBS 11714560
RBS02 RBS 11714560
RBS03 RBS 11714560
RBS04 RBS 11714560
RBS05 RBS 11714560
RBS06 RBS 11714560
RBS07 RBS 11714560
RBS08 RBS 10649600
RBS09 RBS 11714560
RBS10 RBS 11714560
RBS11 RBS 11714560
RBS12 RBS 11714560
RBS13 RBS 11714560
RBS14 RBS 11714560
RBS15 RBS 11714560
RBS16 RBS 11714560
RBS17 RBS 10649600
RBS18 RBS 11714560
RBS19 RBS 11714560
RBS20 RBS 11714560
RBSBIG RBS 15728640
What should be done with the 2 system RBS's ?
So far I have not seen in any Oracle doc a recommended process steps for converting from RB to UNDO.
Click Here to Expand Forum to Full Width