Datafile autoextent into alert.log

[pss]

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

Thanks,
Sergey

[davey23uk]

nope, not logged anywhere

[shajivps]

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.

[davey23uk]

ill repeat, datafile extensions are not recorded anywhere.

[gandolf989]

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.

[shajivps]

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.

[pss]

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

[PAVB]

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.

[shajivps]

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.

[pss]

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.