-
Add datafile to UNDOTBS
We have a production process that fills up UNDOTBS very frequently and that causes process to be aborted.
In emergency, I'd like to add more datafile for UNDO tablespace, would it drop/block/reject user transactions?
In stead of adding new data file, would it be better to just change existing data file to grow unlimited.
Thanks
Claire
-
adding datafiles doesnt cause blocks or waits
-
Originally Posted by claire
We have a production process that fills up UNDOTBS very frequently...
If you UNDO space and related parameters are properly set up may be is time to look at the application and fix fix your offending production process.
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.
-
undo tbs
hi,
check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.
thanks
oraclej
-
Originally Posted by oraclej
check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.
1- Performance would not "increase", that's false.
2- Performance is not the issue poster is facing.
3- Lowering undo_retention would increase ORA-01555 likelihood.
Are you trying to misslead poster on purpose?
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.
-
Originally Posted by oraclej
hi,
check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.
thanks
oraclej
what a mongy statement - please stay clear of databases
-
Please pardon my ignorance, I heard that UNDO should overwrite if it hit the space ceiling.
Is this false information?
Only Archive log should overwrite isnt it?
-
Originally Posted by claire
Please pardon my ignorance, I heard that UNDO should overwrite if it hit the space ceiling.
Providing undo segments expired... yes.
Originally Posted by claire
Is this false information?
No, it's not.
Originally Posted by claire
Only Archive log should overwrite isnt it?
Nope.
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.
-
My experience with ora-01555
I have a large (3 TB) data warehouse, with a motley rabble of end users unleashing horrible queries at it all day. We are on v. 10.2.0.3.
Auto undo management is one of the best things Oracle ever rolled out. I admit to being skeptical at first, but I tried it (in 9i), and have never regretted that decision.
HOWEVER:
Considering the apocalyptic queries dispatched by our users, I have set the undo retention to 24000; that's right, 24000, as in 6.666666 hours. The undo tablespace itself has one 30G file.
We very rarely experience an ora-01555 error. When we get one, it is usually traced to a really bad query that would never complete, even if we had a 30 terabyte undo tablespace and unlimited retention. So Oracle puts the offending query out of its misery; a fitting end to a wasted life.
Oracle onlines and offlines the undo segments as needed, and does a good job of managing the undo space.
Here is a link to a website that pasted an Oracle Bulletin on undo management into a word document. You may find it useful.
http://www.globleat.com/articles/ora...0and%20sol.doc
-
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
|