Begining Rollback-to-Undo conversion research
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Begining Rollback-to-Undo conversion research

  1. #1
    Join Date
    May 2005
    Posts
    129

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,975
    FYI, You should watch out for any set transaction statements in your code.
    this space intentionally left blank

  3. #3
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131

  4. #4
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Quote 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

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    This might help:

    http://www.oracle-base.com/articles/...Management.php

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    May 2005
    Posts
    129
    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..

  7. #7
    Join Date
    May 2005
    Posts
    129
    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.

  8. #8
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Quote 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

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Quote 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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  10. #10
    Join Date
    May 2005
    Posts
    129
    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
  •  



Click Here to Expand Forum to Full Width