ORA-01594: attempt to wrap into rollback segment (7) extent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-01594: attempt to wrap into rollback segment (7) extent

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Angry

    Hi,
    I noticed an error in the alert.log file and was wonderingh why and what should i do to prevent this in future

    Alert.Log Entry
    -----------------
    Mon Oct 22 10:32:46 2001
    Thread 1 advanced to log sequence 22072
    Current log# 2 seq# 22072 mem# 0: C:\ORANT\DATABASE\LOGSID92.ORA
    Current log# 2 seq# 22072 mem# 1: C:\ORANT\DATABASE\LOGSID92A.ORA
    Mon Oct 22 10:36:43 2001

    Errors in file C:\ORANT\rdbms80\trace\sid9SMON.TRC:
    ORA-01595: error freeing extent (1) of rollback segment (7))
    ORA-01594: attempt to wrap into rollback segment (7) extent (1) which is being freed


    Mon Oct 22 10:49:40 2001
    Thread 1 advanced to log sequence 22073
    Current log# 1 seq# 22073 mem# 0: C:\ORANT\DATABASE\LOGSID91.ORA
    Current log# 1 seq# 22073 mem# 1: C:\ORANT\DATABASE\LOGSID91A.ORA
    Mon Oct 22 10:52:28 2001

    sid9SMON.TRC Entry
    -----------------------

    *** SESSION ID:(5.1) 2001.10.22.10.36.42.828
    *** 2001.10.22.10.36.42.828
    SMON: following errors trapped and ignored:
    ORA-01595: error freeing extent (1) of rollback segment (7))
    ORA-01594: attempt to wrap into rollback segment (7) extent (1) which is being freed

    Please Suggest

    Ronnie

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    To avoid those error.

    1. Create larger rollback segments.
    2. Set minimum extents to 20 and maxextents to 500 for each rollback segment.
    3. Create more number of rollback segments.

  3. #3
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by tamilselvan
    To avoid those error.

    1. Create larger rollback segments.
    2. Set minimum extents to 20 and maxextents to 500 for each rollback segment.
    3. Create more number of rollback segments.

    I have 8 Rollback segments RB0 .. RB7.

    Following is the Extents Storage Parameter for each one of them. (I am using DBA Studio to look at them)

    Initial Size = 256 KB
    Next Size = 256 KB
    Optimal Size = 512 KB
    Minimum Number = 2 (not editable)
    Maximum Number = 2147483645


    The RBS Tablespace has the following Extent Storage paramater

    Initial Size = 1024 KB
    Next Size = 1024 KB
    Minimum Number = 2 (editable)
    Maximum Number = 2147483645


    How do I change the Minimum extent number for the rollback segments and are these settings ok.

    Please Suggest

    Ronnie

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi,

    Those rollback segments are tiny.

    Oracle recommends initial and next extent sizes to be of equal value, I would recommend you set it to about 2M to start with.
    Minextents should be set to 20 to avoid dynamic segment extension.
    optimal should be greater than minextents * next
    The amount of rollback segments created should be decided using the formula.

    transactions/transactions_per_rollback_segment.

    initial and minextents cannot be altered for any segment once created. You will have to drop and recreate your rollback segments.

    Firstly ensure that there are no active transactions in the rollback segment you wish to drop by querying v$transaction. This can be joined to v$session to establish which session owns that transaction.

    Once you are sure the rollback segment has no active transactions issue :

    alter rolback segment rbs_name offline;
    drop rollback segment rbs_name;

    now create using.

    create (public) rollback segment rbs_name
    tablespace rbs
    storage (initial next minextents optimal ... ..)

    query v$rollstat, v$waitstat, v$sysstat to query how your rollback segments are performing.

    Suresh




    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by tamilselvan
    To avoid those error.

    1. Create larger rollback segments.
    2. Set minimum extents to 20 and maxextents to 500 for each rollback segment.
    3. Create more number of rollback segments.
    Not 3! 1 is also a bit questionable. Let me explain why. Every 12th hour SMON tries to shrink the rollback segments. The rollback information related to the shrink is logged into the current rollback extent. This segment gets full at one stage. Then Oracle makes an attempt to wrap into the next extent. But it is the one being shrunk. Thus Ronnie gets the errors. Your 2nd advise should help.


  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    ______________________________________________
    originally posted by ronnie:


    Following is the Extents Storage Parameter for each one of them. (I am using DBA Studio to look at them)

    Initial Size = 256 KB
    Next Size = 256 KB
    Optimal Size = 512 KB
    Minimum Number = 2 (not editable)
    Maximum Number = 2147483645


    The RBS Tablespace has the following Extent Storage paramater

    Initial Size = 1024 KB
    Next Size = 1024 KB
    Minimum Number = 2 (editable)
    Maximum Number = 2147483645
    ____________________________________________________________

    Your RBS Tablespace storage parameter will only take effect
    to your rollback segments storage definition if you did not explicitly define the storage parameter of your rbs.
    Let's say:
    create public rollback segment rbs1 tablespace RBS;
    rbs1 will have automatic storage def of:
    initial size 1024 kb
    next size 1024 kb
    mininum extents 2
    maximum extents 2147483645

    Though it could perform much better, but I believe it's still not enough. So you have to make it much bigger, follow what tamilselvan says about number minimum and maximum extents and if you have a lot of concurrent users that makes a lot of DMLs to your system which leads to exhaust your rbs then you have to add more numbers of rbs.


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