Overall Scenario: We have an Oracle9i database on Unix Solaris 5.9 with three instances processing millions of records. One instance is used for storing Arcsight IDS data, one is used for web application and the other is used for executing six replication (slave) pulls. We often receive the timeout message for one or more jobs. The timeout cause and action suggest that we increase the listener connect_timeout_listener parameter. Setting this parameter to 0 (wait indefinitely) had no effect. Setting it to 60 seconds resulted in exceeded sessions limit. The cause and action for the sessions message recommended increasing sessions. I verified the current session settings (170) for 150 processes using the 1.1*processes+5 formula. It appears I need to increase processes in order to increase sessions.
Question 1: We are using dblink from our server to connect to a remote site's database during the replication pull. Where should I set the listener connect_timeout_listener parameter? Should it be set on the remote database or ours, the site initiating the pull?
Question 2: We have a queue size setting of 1024. This is also the size set in Unix. Does it have any bearing on Question 1? The Unix (netstat -s) tcptimeout indicates approximately 891 timeouts, but no tcpdrops.
Question 3: Our refresh is set to automatically do a refresh after a timeout. Periodically the refresh job will hang and we manually have to execute it. Is this linked to the lack of available database processes or hung Unix processes?
Question 4: When connecting remotely from database to database, which server is posting messages in the ALERT log?
Question 2: I'm not experienced in the use of this parameter, but I'd guess that it could be a factor in timeouts if the number of concurrent connection requests exceeded its setting.
Question 3: Diagnosing why a process appears to hang may require tracing and other diagnostic techniques in the running system.
Question 4: The alert log for a given database should contain information only on events in that database. Without knowing which messages you're referring to, though, it's difficult to answer definitively.
Dig Deeper on Oracle error messages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.