Ora-01555
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Ora-01555

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    Ora-01555

    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.
    Last edited by ams-jamali; 02-15-2007 at 05:36 AM. Reason: increase additional information

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  3. #3
    Join Date
    Jan 2007
    Posts
    14
    Set your undo_retention equal to or greater than the length of the longest running query:

    select max(maxquerylen) from v$undostat;

  4. #4
    Join Date
    Jan 2007
    Posts
    231
    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

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    thanx nuushona,
    I will try your idea and see the problem comes again.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Undo retention seems to be large enough but... try it

  7. #7
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44
    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.

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by 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.
    A, C are wrong solutions 1,2,3 are wrong

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width