Datafile autoextent into alert.log
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Datafile autoextent into alert.log

  1. #1
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Datafile autoextent into alert.log

    Is there a way to force datafile autoextent information into alert.log file? Any events I can set for this?

    Thanks,
    Sergey

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    nope, not logged anywhere

  3. #3
    Join Date
    Dec 2008
    Location
    Easton,MA
    Posts
    13
    Sergey,

    You can write your own code to figure out and write to alert.log. But, what is the idea behind this ? Even if it is possible to do this, it is going to generate so much messages.

    Regards,

    Shaji.
    Shaji
    DBA(oracle,sqlserver,hyperion essbase,peoplesoft).

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ill repeat, datafile extensions are not recorded anywhere.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    You could not let any data files extend and then monitor when they are close to filling up, and then you would manually extend the datafile. You can also manually monitor the amount of storage allocated in each tablespace every day.
    this space intentionally left blank

  6. #6
    Join Date
    Dec 2008
    Location
    Easton,MA
    Posts
    13
    Sergey,

    Can you please explain what is the idea behind this monitoring ? Are you trying to get an idea on how objects are growing ?

    Regards,

    Shaji.
    Shaji
    DBA(oracle,sqlserver,hyperion essbase,peoplesoft).

  7. #7
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Here is the reason behind. We have a 4 node RAC DB on RH 4.5 over NFS3. Sometimes when a datafile extends (lets say handled by node 1) then node 2 is trying to write to it we get error ORA-27069 (attempt to do I/O beyond the range of the file). Soon after that (about 5-15 min) we get ORA-600 and CRS restarts that instance.
    I'm trying to collect as much info as possible at trying to figure out any dependencies. We had this problem before and it was RH responsible for the problem. Back in November we installed upgraded kernel with the patch and got two occurrences since than. RH provided us with a set of C/shell code to simulate the problem but it's not reproducible since the upgrade.
    Strangely, both times it happened in the same application. Many others extend datafiles on the daily basis (much more than that app) but they are not affected. The app is reloading historical data and it is not running all the time.
    This is the background in few words... well a little more than few.

    Thanks,
    Sergey

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    It looks like your database is experiencing a big deal of datafile extentions, you don't want that to happen -best case scenario it adds a lot of overhead.

    Be sure your tablespaces are correctely sized to take care of the load.

    Monitor space growth and add space as needed -in advance.
    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
    Dec 2008
    Location
    Easton,MA
    Posts
    13
    Sergey,

    Your best bet is to open an SR and get oracle to provide some event that can be set that will dump an entry into the alert log. I am not familiar with any way cos I havent had this requirement before. You will need oracle's help to get to the bottom of the issue. If you cant figure that out, then managing the space yourself instead of autoextend will be be the workaroud. Please update this forum if you are able to figure out the issue.

    Thank you,

    Shaji.
    Shaji
    DBA(oracle,sqlserver,hyperion essbase,peoplesoft).

  10. #10
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    2 PAVB:
    As I mentioned only historical reload app is affected. It deletes old data and reloads it back populating new fields in the process. This is one off process and gets activated only when needed. This is more of a question of coordination as ETL team is running the party.

    2 shajivps:
    Already done 3 weeks ago. They were concentrating on ORA-600 error instead of the root cause 90% of the time. Not really surprising me for a while.

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