| ORA-01555: snapshot too old [message #208779] |
Tue, 12 December 2006 04:19  |
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 #208802 is a reply to message #208797] |
Tue, 12 December 2006 04:58  |
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 
|
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 #208810 is a reply to message #208779] |
Tue, 12 December 2006 05:10 |
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 #208823 is a reply to message #208817] |
Tue, 12 December 2006 05:41 |
 |
Mahesh Rajendran
Messages: 10707
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 |
|
|
|
|
|
|
|
| Re: ORA-01555: snapshot too old [message #208856 is a reply to message #208779] |
Tue, 12 December 2006 06:29 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
