Snapshot too old on 9i (9.1.2)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Snapshot too old on 9i (9.1.2)

  1. #1
    Join Date
    Aug 2001
    Posts
    267

    Snapshot too old on 9i (9.1.2)

    We are getting snapshot too old on 9i . We got Undo retention = 3 hours and having undo tablespace 11 GB. Users never used more than 1GB . But we are getting Snapshot too old error. Our system tablespace size is 2gb . We got lot of objects owned by sys(Oracle finanacials 11i). Any solution??
    Raghu

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Snapshot too old on 9i (9.1.2)

    Originally posted by raghud
    We are getting snapshot too old on 9i . We got Undo retention = 3 hours and having undo tablespace 11 GB. Users never used more than 1GB . But we are getting Snapshot too old error. Our system tablespace size is 2gb . We got lot of objects owned by sys(Oracle finanacials 11i). Any solution??
    The error ORA-01555 is reported when the flashback data is not available anymore in the UNDO Tablespace. Just reduce a bit the UNDO_RETENTION. 3h is really a lot.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'd be inclined to think you may be getting the error due to a long running query/report on your DB. Therefore I'd have receommended either a). A larger retention time or b). Run the query/report out of the main business hours.

    Julian, could you explain why decreasing the retention period would reduce the occurance because it has me a little confused, as Oracle and my own experience tell me to increase. But maybe I'm the one confused

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    frequent commits in your proccesses?
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    I went from 30 MI to 3 hours on Undo retention because off this problem. But too may frequent commits also a problem because Oracle is trying to update the buffered rows which are parallelly selected by other processes. I already recommanded users to commit frequently.
    Raghu

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by raghud
    I already recommanded users to commit frequently.
    That may be the wrong advice. Frequent commits are a cause of, not a solution to, this problem.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Is it a batch job type process that is failing? You shouldn't commit in a cursor.
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Aug 2001
    Posts
    267
    The app is using lot of cursors. Might be they are commiting lot of times.
    Raghu

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That would be the first place to look, i think. If the error really is due to committing inside a cursor then there is no amount of UNDO tweaking that will help.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by grjohnson
    I'd be inclined to think you may be getting the error due to a long running query/report on your DB. Therefore I'd have receommended either a). A larger retention time or b). Run the query/report out of the main business hours.

    Julian, could you explain why decreasing the retention period would reduce the occurance because it has me a little confused, as Oracle and my own experience tell me to increase. But maybe I'm the one confused

    Cheers,
    The bigger the retention period, the more commited information needs to be kept in the undo segments. Thus the chance for Oracle to overwrite such commited transactions grows. If such info is overwritten, then ORA-01555 will be raised. That's OK as this is just a warning.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

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