Re: Rollback Segments

Carlos,

This issue almost cost me a job last yea (a $300/hr consultant came in and told them what I had been saying and made it all better)r, so perhaps I can help some. I'll try to intersperse my answers after your questions....

John
John Wilson, CCP, LLC

calberto_at_my-dejanews.com wrote:

> Hi all!
>
> I'm having problems with rollback segments in Oracle. We, in our company, have
> an long-running application (about 3 hours) which after 2 hours give us the
> following message :
>
> ORA 01555 - Snapshot too old .....
>
> The server currently has 16 rollback segments, which one with the following
> parameters :
>
> INITIAL - 50K
> NEXT - 50K
> OPTIMAL - DEFAULT (I suppose Oracle's default is NULL)
> MINEXTENTS - 2
> MAXEXTENTS - 121
>
> Is the solution increase the NEXT parameter?

NO. The error occurs when a job which has been holding open a rollback segment during its updates finishes and the space is released. Sometimes as the result of

the Optimal value and sometimes because other jobs need the space, the original rollback segment data is overwritten (remember, the job that put it there is done with it). This is a case of scheduling of the jobs....

>
>
> Beyond this, I have some doubts :
>
> 1) I have manually put all these segments online, but when the machine
> rebooted (for other reasons), Oracle have put some of them offline. Why?

The rollack segments must be listed in the init.ora file. Generally only the first 10 (or so) are listed by default

>
>
> 2) Since my OPTIMAL parameter is the default, when Oracle will release the
> extents of the segments?

Never trust defaults. I suspect nothing is happening. Look at the dynamic table V$ROLLSTAT (crosss it with V$ROLLNAME if desired) to see what your system has been

doing since it was last started.

>
>
> 3) All these rollback segments are in one tablespace which has 2 datafiles of
> 20 MB each, but there is only 2 MB free. Is this related with the problem?

This might be -- no room to expand....not much recovery going on.

Also, try to keep you numbers a multiple of the Oracle block size (typically 8192--8k);e.g., INITIAL 48K NEXT 48K OPTIMAL 96K.

>
>
> We have Oracle 7.3 running on NT 4.0.
>
> Thank in advanced,
>
> Carlos
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Mon Aug 17 1998 - 13:17:40 CDT