-
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
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|