OraFAQ Forum: Server Administration » rollback segment too small ora-01555

Home » RDBMS Server » Server Administration » rollback segment too small ora-01555 (Oracle 11g R2, Windows Server 2003 sp2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

rollback segment too small ora-01555 [message #539965]

Thu, 19 January 2012 10:07

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi All,

One of the users received the error
ora-01555: rollback segment too small

I have read about the error and saw that it is caused by transactions made upon same data, filling to maximum one of the UNDOTBS rollback segments.

It happens only once in a while, each time on a different Rollback Segment.

I've read that i should do a few things to enlarge those segments, such as bring a 1. segment offline, 2. drop it and then 3. create it with a bigger size & possibly a bigger extent setting

However,
i've also read that those actions aren't relevant if you have the parameter UNDO_MANAGEMENT set on AUTO, which is actually the case.

is that why when i execute
ALTER ROLLBACK SEGMENT RB_SEG_NAME_11$ OFFLINE;

and then

1. How do i solve my issue with the
the Rollback segment being too small to contain the snapshot with the requested SCN?

2. what does the parameter UNDO_MANAGEMENT mean?
should i change it, in order to adjust my Rollback Segments attributes, to prevent my error of re-occurring?

Sorry for the silly questions,

Great Thanks to those trying to help,

Andrey

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539974 is a reply to message #539965]

Thu, 19 January 2012 10:20

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

A. With UNDO tablespace do not touch any of the undo segments ("new" name of rollback segment). ALWAYS check the version of the Oracle when you read an article and try to apply to your database. If there is no version number then leave it.

B. The most frequent reason for ORA-01555 is commit inside loop. Check your code.

1. You do not solve this issue at database side, you have to first investigate your code
2. Read Database Concepts, at least the first chapter.
In addition, all parameters are described in Database Reference but you must first read the previous link to understand what is said in the second one.

Regards
Michel

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539982 is a reply to message #539974]

Thu, 19 January 2012 10:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

To add to what Michel has posted, the session reporting the ORA-01555 error is the victim; not the culprit.
Some session is doing many DML & many COMMIT.
The code needs to be modified so only a single COMMIT is done & then no more ORA-01555 will occur.

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539987 is a reply to message #539974]

Thu, 19 January 2012 10:34

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi Michael,

as i said, i have an Oracle version of 11g.
you have posted link to documentation of 10g.

Other then that, there was so much information there, you could'ave sent me straight to the bible, and look for the answer there.

What i asked was specifically about the issue of rollback segments in 11G, and if you are unable to help - please don't try to insult with these kind of replies.

I know i ask silly questions, since i am a beginner,
but it doesn't mean you have to post silly answers.

can anyone please assist regarding the specified issue?

Regards,

Andrey

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539989 is a reply to message #539987]

Thu, 19 January 2012 10:39

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

as i said, i have an Oracle version of 11g.
you have posted link to documentation of 10g.

1/ It does not matter for this
2/ You can find the documentation for your version by yourself at http://tahiti.oracle.com

Quote:

What i asked was specifically about the issue of rollback segments in 11G, and if you are unable to help - please don't try to insult with these kind of replies.

I know i ask silly questions, since i am a beginner,
but it doesn't mean you have to post silly answers.

I do not see where you read I insulted you in any way and I do not think this is a silly question (just this is a FAQ).

Quote:

can anyone please assist regarding the specified issue?

I told you where you have to FIRST search for.
99% of the cases the error comes from the code NOT from the database.

Regards
Michel

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539990 is a reply to message #539987]

Thu, 19 January 2012 10:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If the code is corrected, nothing needs to be done at the DB level.

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539993 is a reply to message #539990]

Thu, 19 January 2012 10:49

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi Michel Cadot,
First of all, i want to apologize if i went too far with my reply.
Secondly, I have read SOME Oracle Documentation and didn't find an answer there(or understand it),
sending me to it seems to be just avoiding the question asked.

Still, sorry and thanks for your reply.

BlackSwan,
Thank you for your kind reply.

I understand that reducing the number of COMMIT actions will solve my problem,
however, it does not happen over customer sites, only our local environment.

If we change the code here, we have to change it everywhere, meaning, in places where it WORKS,
so it's a problem, in addition to the fact that it occurs only on our local environment.

What method do you suggest to check amount of commits per some time on an environment\schema?
i do not have the Statistics & Diagnostic pack license for running an AWR report.

Thanks again,

Andrey

Report message to a moderator

Re: rollback segment too small ora-01555 [message #539994 is a reply to message #539993]

Thu, 19 January 2012 10:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If we change the code here, we have to change it everywhere, meaning, in places where it WORKS,
Above is NOT cast in stone. Folks can decide to do otherwise.

Since you should know which procedure reported the ORA-01555 error,
knowledgeable application developer (KAD) can inspect the code to see which tables are involved;
especially table involved with LONG running SELECT.
Then KAD can find where in application code where "heavy" DML likely occurs along with COMMIT.
It should take only 2 - 4 hours to find & fix for KAD who really knows the application.
In some cases the culprit & victim is one in the same.

Report message to a moderator

Re: rollback segment too small ora-01555 [message #540334 is a reply to message #539994]

Sun, 22 January 2012 04:49

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

I'll check on that.

Thank you very much for your help.

Andrey

Report message to a moderator

Re: rollback segment too small ora-01555 [message #540390 is a reply to message #540334]

Mon, 23 January 2012 04:30

abhi_sri
Messages: 20
Registered: September 2010
Location: India

Junior Member

Increase the value of undo_retention parameter in initialization file.

Report message to a moderator

Re: rollback segment too small ora-01555 [message #540391 is a reply to message #540390]

Mon, 23 January 2012 04:40

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It may or may not be useful depending on the origin of the error.
This origin must be determined FIRST in order to "prevent my error of re-occurring" (OP¨request).

Regards
Michel

Report message to a moderator

Previous Topic:	How to find when the datafile was resized?
Next Topic:	compatible issue 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 17:26:32 CDT 2024