Ora-01555

[ams-jamali]

Hi,

Iam using oracle9.2 and suse linux.Iam getting repeatedly this error but i have enough space in undo tablespace nearly 800mb and it was already kept in autoextend on.

Wed Feb 14 07:11:53 2007
ORA-01555 caused by SQL statement below (Query Duration=1158 sec, SCN: 0x0000.3544a4a3):
Wed Feb 14 07:11:53 2007
SELECT activ_n
FROM STR_A
WHERE is_del = 'Y' AND delete_date = '4-jan-1876'
Wed Feb 14 07:11:53 2007
Errors in file /mnt/db/oracle/admin/raid2/bdump/raid20_j00_2653.trc:
ORA-12012: error on auto execute of job 1
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12$" too small
ORA-06512: at line 1

in pfile it was like this,
undo_management=AUTO
undo_retention=10800

What necessary steps should be taken i don't know whether i should increase retention period or not. pls help.

Thanks in advance.

[Bore]

What you mean "In pfile it looks like this"
Please use
sqlplus / as sysdba
show parameter undo

to find out what the actual settings of the parameters are

[nuushona]

Set your undo_retention equal to or greater than the length of the longest running query:

select max(maxquerylen) from v$undostat;

[ams-jamali]

Hi,
Sorry for giving u the value without specifying the db using pfile.
I have entered show parameter
it shows the same result as

NAME TYPE VALUE
------------------------------------ ----------- ----
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS

[ams-jamali]

thanx nuushona,
I will try your idea and see the problem comes again.

[Bore]

Undo retention seems to be large enough but... try it

[liakat]

ORA-1555 SNAPSHORT TO OLD ERROR POSSIBLE CUASE ARE..
A.UNDO SEGMENT IS SMALL
B.UNDO RETANTION IS LOW VALUE
C.USER NOT COMMIT THERE TRANSACTION IMMEDIATLY.
SO SOLUTION IS ...
1.INCREASE THE SIZE OF UNDO TABLESPACE
2.USE SPECIFIC ROLLBACK SEGMENT FOR LARGE TRANSACTION BASICALY BATCH PROCESS.
3.MAKE UNDO_SUPPRESS_ERROR=TRUE

BEST OF LUCK
LIAKAT HOSSAIN
MCP,OCAAD9I,OCPDBA8I,9I,10G,OCA10gAS.

[davey23uk]

ORA-1555 SNAPSHORT TO OLD ERROR POSSIBLE CUASE ARE..
A.UNDO SEGMENT IS SMALL
B.UNDO RETANTION IS LOW VALUE
C.USER NOT COMMIT THERE TRANSACTION IMMEDIATLY.
SO SOLUTION IS ...
1.INCREASE THE SIZE OF UNDO TABLESPACE
2.USE SPECIFIC ROLLBACK SEGMENT FOR LARGE TRANSACTION BASICALY BATCH PROCESS.
3.MAKE UNDO_SUPPRESS_ERROR=TRUE

BEST OF LUCK
LIAKAT HOSSAIN
MCP,OCAAD9I,OCPDBA8I,9I,10G,OCA10gAS.

A, C are wrong solutions 1,2,3 are wrong

[Bore]

Ok, I have not done that for years but I started debuging around and here is what I found
The error message states:
Query Duration=1158 sec
With undo retention set to undo_retention=10800 that seems quite more than necessary.
So... either the undo tablespace is really small in comparisment to the transaction load (800M per 10800 sec - that's 3 hours - seems realistic to go over 800M for 3 hours)
or for any reason the unexpired undo extents in that specific segment gets reused (what can happen in 9.2)
I read the Metalink Note:269814.1 - really good one, which gives an idea how the algorithm works and mentions bugs 5475085 and 2900863 which seems to be more or less relevant for your case.
Based on that I feel (feel is the word, I cannot explain why) that it worths to start lowering UNDO_RETENTION until we do not see the result of
select UNXPSTEALCNT from v$undostat
comes close to 0.
So, try lowering UNDO_RETENTION say to 5000 and let's see if it will help

Cheers
Boris

[tamilselvan]

The real solution is to reduce run time, tune the SQL.

There are many more reasons to get 1555 error. Some of them are fetch across commit by other transactions or same transactions, rollback segments too small, rbs extents over written by frequently etc.

Tamil