Solved

*** URGENT *** ora-01555 rollback segment too small

Posted on 2001-06-19
16
318 Views
Last Modified: 2008-02-01
Background:
.VB client, Weblogic5.1 app server, and Oracle 8.1.6 back end.

Problem:
.Whenever I get a user base of circa 50 users I get the error :- java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number string with name "string" too small.

Solutions already tried:
.Increassed size of rollback segment
.Made rollback segment space unlimited
.Deleted and replace rollback segments
.Monitored the rollback segment useage when error is produced and useage is only at about 35%

Has anyone any idea what else could be causing this, ITS DRIVING ME ROUND THE BEND :)
0
Comment
Question by:Rizla1972
  • 9
  • 6
16 Comments
 

Author Comment

by:Rizla1972
ID: 6206455
I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
0
 

Author Comment

by:Rizla1972
ID: 6206493
I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
0
 

Author Comment

by:Rizla1972
ID: 6206545
I have also tried preventing the rollback segment size from shrinking, which allowed the users to run for a little longer before the error was produced.
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6209158
Hello Rizla

From you error. I just went to the Oracle Errors page and got the info.

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: Use larger rollback segments.


Okay coming back to your probs.
How many user are allowed to query at a time. Please make sure ur processes of oracle is more and number of user connecting should not exceed and make sure when write is required used writable or open with readonly mode. Since if u open up the records in write. Use transaction mode properly .

I fear ur transaction mode has some probs.

Hey if am i wrong in understand ur probs. Please let me know what the exact probs and what u r looking for

Cheers

Shyam
0
 

Author Comment

by:Rizla1972
ID: 6213543
shyamkumarreddy,

Thanks for your reply but I feel it's not a process problem.  Transaction isolation levels are set in the app server - we use read-consistent which is why we need rollback segments to last a while.  We do access the rows in read only mode unless we try to update them.

Anyone any other suggestions
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6213595
Good Rizla

How clients trying to connect the application at a time.
And

I have a doubt. How many connection does a user open a connection during the application.

Please let me know that.


Shyam
0
 

Author Comment

by:Rizla1972
ID: 6213776
Shyam,

We have one client ( the app server).  It creates 4 connections initially but can make more.  It should never have more than 15  active connections since that's how many threads it has.

Neil
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6213867
Rizla

Good. When do u get this error once the client has reached more then 8 connection. Or on the begin of application itself.


Shyam
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Rizla1972
ID: 6213878
Shyam,

The Oracle client is the app server which itself also has a client (activex web app).

The error does not occur when I start the app server, it happens when about 50 web app clients connect to the app server, I can get about 40 to rn no problem at all but somewhere between 40 and 50 users (generated using mercury Loadrunner) is where this error is reported back from oracle to the app server and consequently back to the activex web client.

Hope this helps, much appreciate your time

Rizla
0
 
LVL 3

Accepted Solution

by:
shyamkumarreddy earned 50 total points
ID: 6213899
Hi Rizla
I fear it is due to the connection rizla
If u know just check this or ask ur dba to watch out the number of connection established from the start of the application to the error of the application.

Just check the number of connection opening to the dba thru ur drivers.

If the number start rising and doesn't decrease then the connection opened by the client is not closed or hanged up then ur database will be locked by the tables queried by the user.

This is the way ur put ur self trouble with rollback segment.

Do u understand what i say.
I hope this will help u to know more abt the probs


Shyam

0
 

Author Comment

by:Rizla1972
ID: 6217549
Shyam,

I have monitired the connections and they seem to be being closed down again, I left a run running overnight with a smaller number of users to prevent the error appearing (40), when I came back in this morning about half of the users had fell over (different errors, mostly due to web architecture), but with the remaining users still running I could see that the amount of connections had dropped from 43 to 19, therefore, I can conclude that from what I can see the connections are not being left open.

Any other ideas?

Rizla
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6217681
Hi Rizzla

If not connection. Is any locks causing u the trouble.
Can u find out any of locks has been made to the tables during the writing and not released.

This is the only way i have. Nothing more to add
Just check up whether anything of ur tables have been locked during writing.


Shyam
0
 

Author Comment

by:Rizla1972
ID: 6217833
mmmmm, no its definately not that, this is really getting on my nerves now, I MUST get to the bottom of this, its completely strange how I can be getting rollback segment too small when it is only about 1/3 utilized at the time the error is produced?  Looks like you are running out of solutions too, oh well thanks for your time, if you can think of anything else that may cause it then plz post another reply, in the meantime I am off to curl up in a corner to cry for the rest of the day :)

Rizla
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6217850
Hey Rizla
I will try to find out from the DBA
Okay. If i get a result i will post it:)
You can solve it.
YOu can do it okay

Take Care Bye.
Cheers
Shyam
0
 

Author Comment

by:Rizla1972
ID: 6218318
Ok, thanks again for your time, still no solution from me so I guess I should go thru into server room and kick the living daylight out of our Oracle box and see if that helps, if nothing else it will relieve my stress :o)

Rizla
0
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 8950807


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


--  points to shyamkumarreddy


Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now