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

[ronnie]

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

[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.

[ronnie]

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

[Sureshy]

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

[julian]

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.

[reydp]

______________________________________________
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.