Home » RDBMS Server » Server Administration » ORA-01555: snapshot too old
ORA-01555: snapshot too old [message #208779] Tue, 12 December 2006 04:19 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
its a huge database ,1850GB .
there are many transactions and dialy jobs.today a job failed iwth error (ORA-01555: snapshot too old) .
following are my database settings ...

SQL> show parameter undo_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL> show parameter undo_retention;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900

any recommendation on what to do ?
Re: ORA-01555: snapshot too old [message #208788 is a reply to message #208779] Tue, 12 December 2006 04:44 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923
Re: ORA-01555: snapshot too old [message #208797 is a reply to message #208779] Tue, 12 December 2006 04:53 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
that is a detaild explanatin,sure that will be helpful sometime.
but in my case, UNDO management is MANUAL .

can anyone tell me what i should do here.its an emergency issue Sad
Re: ORA-01555: snapshot too old [message #208801 is a reply to message #208797] Tue, 12 December 2006 04:58 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

In that case I suggest to read this

http://www.ixora.com.au/tips/admin/ora-1555.htm
Re: ORA-01555: snapshot too old [message #208802 is a reply to message #208797] Tue, 12 December 2006 04:58 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
gkrishn wrote on Tue, 12 December 2006 11:53
that is a detaild explanatin,sure that will be helpful sometime.
but in my case, UNDO management is MANUAL .

can anyone tell me what i should do here.its an emergency issue Sad


After 10 sec of google searching maybe this will help

Specifying the Retention Period for Undo Information

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. But for consistent read purposes, long running queries might require old undo information for undoing changes and producing older images of data blocks. The initialization parameter, UNDO_RETENTION, provides a means of explicitly specifying the amount of undo information to retain. With a proper setting, long running queries can complete without risk of receiving the "snapshot too old" error.
Setting the UNDO_RETENTION Initialization Parameter

Retention is specified in units of seconds, for example 500 seconds. It is persistent and can survive system crashes. That is, undo generated before an instance crash, is retained until its retention time has expired even across restarting the instance. When the instance is recovered, undo information will be retained based on the current setting of the UNDO_RETENTION initialization parameter.

The UNDO_RETENTION parameter can be set initially in the initialization parameter file that is used by the STARTUP process:

UNDO_RETENTION = 10

The UNDO_RETENTION parameter value can be changed dynamically at any time using the ALTER SYSTEM command:

ALTER SYSTEM SET UNDO_RETENTION = 5;

The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space for the active transactions. If an active transaction requires undo space and the undo tablespace does not have available space, the system starts reusing unexpired undo space. Such action can potentially cause some queries to fail with the "snapshot too old" error.

If the UNDO_RETENTION initialization parameter is not specified, the default value is 900 seconds.


Source : http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/undo.htm

[Updated on: Tue, 12 December 2006 04:59]

Report message to a moderator

Re: ORA-01555: snapshot too old [message #208804 is a reply to message #208802] Tue, 12 December 2006 05:01 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi bwetkstr .
mine is MANUAL not AUTO undomangement
Re: ORA-01555: snapshot too old [message #208806 is a reply to message #208804] Tue, 12 December 2006 05:02 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have you read the link i purposed ?
Re: ORA-01555: snapshot too old [message #208810 is a reply to message #208779] Tue, 12 December 2006 05:10 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi Tahpush..
that link also doesnt say anything about increasing rollback seg size.i issued following query and got ....

SQL> select rb.segment_name, rs.optsize, rs.hwmsize
from v$rollstat rs, dba_rollback_segs rb
where rs.usn = rb.segment_id
2 3 4 ;

SEGMENT_NAME OPTSIZE HWMSIZE
------------------------------ ---------- ----------
SYSTEM 729088
PRS_50 104857600 157278208
PRS_51 104857600 104849408
PRS_52 104857600 262135808
PRS_53 104857600 157278208
.
.
PRS_99 104857600 157278208

where and how to increase rollback segsize ?
Re: ORA-01555: snapshot too old [message #208817 is a reply to message #208779] Tue, 12 December 2006 05:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

The link I provide have alot of useful script for handling the type
of problem you have. And might change your point of view .....
Also this you might find intresting

http://asktom.oracle.com/pls/ask/f?p=4950:8:14082813098979153327::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:275215756923

But when I comes to alter a rollback-seg
Well there is plenty of examples on net

do a search on google and you might find tons of example on how to do this

[Updated on: Tue, 12 December 2006 05:25]

Report message to a moderator

Re: ORA-01555: snapshot too old [message #208823 is a reply to message #208817] Tue, 12 December 2006 05:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
that is a detaild explanatin,sure that will be helpful sometime.
can anyone tell me what i should do here.its an emergency issue

All emergency issues should be handled by Oracle support.
THis is just forum.We try to help people to help themselves.
That is it.

[Updated on: Tue, 12 December 2006 06:16]

Report message to a moderator

icon14.gif  Re: ORA-01555: snapshot too old [message #208848 is a reply to message #208779] Tue, 12 December 2006 06:20 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Mahesh dont get angry man Smile
Re: ORA-01555: snapshot too old [message #208853 is a reply to message #208848] Tue, 12 December 2006 06:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Mahesh dont get angry man
Nope.
I am too old for that.
Above all, what do i care?
Re: ORA-01555: snapshot too old [message #208856 is a reply to message #208779] Tue, 12 December 2006 06:29 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
ok tht fine.
i have decided to increase datafile size to another 1GB.i hope this can give additional space for rollback segments.
i am a sqlserver dba,hav only 2 months of exp in oracle.so learning continues... thanks buddy
Re: ORA-01555: snapshot too old [message #208880 is a reply to message #208856] Tue, 12 December 2006 07:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>so learning continues.
Hope it does.
I just felt it was unfair.
Someone was trying to help here by investing their time and effort.
But OP's (too frequently nowadays) comeback demanding more with a response like
>>that is a detaild explanatin,sure that will be helpful sometime.
Which might indicate that the OP is not throwing any effort at all.
As said before,
we are here to help others. We can give pointers.
But we cannot teach everything/readout loud every line in manual and eventually doing their work.
Again, no offense intended. Smile
Re: ORA-01555: snapshot too old [message #208923 is a reply to message #208880] Tue, 12 December 2006 10:18 Go to previous message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
Mod-Edit:
I have to edit this. Orafaq is obscenity free zone.
Mahesh Rajendran

[Updated on: Tue, 12 December 2006 11:12] by Moderator

Report message to a moderator

Previous Topic: Creation Date of Table
Next Topic: Regarding Data file size
Goto Forum:
  


Current Time: Wed Dec 07 12:41:59 CST 2016

Total time taken to generate the page: 0.21679 seconds