Add datafile to UNDOTBS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Add datafile to UNDOTBS

  1. #1
    Join Date
    May 2004
    Location
    NJ
    Posts
    15

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    adding datafiles doesnt cause blocks or waits

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

  4. #4
    Join Date
    Apr 2008
    Posts
    1

    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote 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

  7. #7
    Join Date
    May 2004
    Location
    NJ
    Posts
    15
    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?

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

    Quote Originally Posted by claire
    Is this false information?
    No, it's not.

    Quote 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.

  9. #9
    Join Date
    Apr 2008
    Location
    Pennsylvania
    Posts
    15

    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

  10. #10
    Join Date
    May 2004
    Location
    NJ
    Posts
    15
    Hat off to all!

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