A few basic notes on Oracle Database Administration.

Thursday, July 10, 2008

Something is VERY busy

I have been suspecting that something is causing excess connects/disconnects from our database. To see the number of connects I went in search of the listener.log. By default it is in $ORACLE_HOME/network/log. There was a listener.ora there , but according to it, there hasn't been ANY connections to our database in months!

This is a solaris box. I read that there is a 2G file limit on Solaris. (Maybe that means a limit on log files. Obviously, my data files are much larger than that.).

I issued the lsnrctl command:

lsnrctl >
set log_file listener2.log

Quickly it resumed recording connections, now into listener2.log. It recorded 2 of them EVERY second!!! OK. my suspicion was right. Now to track down exactly WHAT is connecting and disconnecting so much.


Bradd Piontek said...

I like to turn on auditing of connections. This assumes:
1. audit_trail = DB
2. you can run 'audit connect;'
The dba_audit_session view becomes very handy at this point.

Check the /etc/fstab or is it /etc/vfstab on solaris and see if the admin's enabled large file support on the Oracle software mount. If not, you may want to implement a listener.log 'pruning' job on the server.

girlgeek said...
This comment has been removed by the author.
girlgeek said...

Thanks for the suggestion, Bradd. I am pretty sure that it is Oracle Workflow that is the busy trouble maker. It might be nice to audit for a proof.

As for pruning the log, I am more interested right now in slowing down the connections.

I really appreciate receiving suggestions such as yours. There is SO much to learn! Thank you.