-
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.
-
-
Originally Posted by gandolf989
FYI, You should watch out for any set transaction statements in your code.
ooooooooo!!! good catch Gandolf!
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..."
thanks
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;
2 begin
3 utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
4 dbms_output.put_line(TO_CHAR(utbsize_in_MB));
5 end;
6 /
187
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.
-
Originally Posted by gandolf989
FYI, You should watch out for any set transaction statements in your code.
Shouldn't pose a problem if undo_supress_errors=TRUE
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.
Oracle DBA
-
Originally Posted by roadwarriorDBA
Tim, did you author that article ?
"Hope this helps. Regards Tim..."
thanks
I am wondering about how sized rollback segments map over to the size of undo segments.
..off to tahiti..
Yes I did ;-)
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.
Cheers
Tim...
-
PHP Code:
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
So I offline the rb segments the drop the RBS tablespace including contents.
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|