Setting UNDO_RENTENTION
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Setting UNDO_RENTENTION

  1. #1
    Join Date
    Mar 2007
    Posts
    21

    Setting UNDO_RENTENTION

    Hi,

    I'm setting UNDO_RETENTION=3600. it's mean I retain undo data for 1 hour for read consistency. After 1 hour, if my UNDO tablespace is not full then that undo data would release out of undo segement or keep until UNDO tablespace full?

    Thanks in advance,
    Sinh

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    In your setup Oracle would start releasing undo data after 3600 seconds, it will not wait until undo tablespace gets full.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2007
    Posts
    21
    Thanks for your replying.

    If so, My UNDO tablespace often nearly full then it mean my UNDO tablespace is not large enough? But I think my UNDO tablespace is very large (30GB) and I haven't gotten error : "ORA-1555 snapshot too old". Why my UNDO tablespace often nearly full (~98%)?

    Thanks in advance,
    Sinh

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    There are two main reasons for getting ORA-01555...

    1- UNDO retention too low
    2- Transaction is shooting its own feet

    Check alert log and take note how long it takes for transactions to fail with ORA-01555, if longer than 3600 seconds then -in your setup your undo_retention is too low.

    I would set undo_retention in a way I could avoid at least 95% of the ORA-01555 occurrences.

    In regards to UNDO tablespace sizing... I can't tell you. This is dependent of your application, size of your transactions and undo_retention.
    Who knows? may be you have a small database running huge-monster sized transactions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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