Connectivity

Change log:

Clients sometimes experience issues with connectivity where the database runs out of available session slots. This can be caused by idle connections being closed by the Firewall after a given time (normally around 15 minutes) or other network related issues where the connection is lost without the database being aware of this.

CDRator has the following 3 suggestions that may prevent the problem from occurring.

Suggestion 1

The following line

• SQLNET.EXPIRE_TIME = 10

may be missing from $ORACLE_HOME/network/admin/sqlnet.ora

The line causes the database to send a reverse "ping" to the client every 10 minutes, and thereby keeping any ports open in the Firewall.

The value 10 is the recommended value, but can be reduced to 5 without adverse effects. Restart the listener after the change using:

SH> lsnrctl reload

Suggestion 2

The number of processes in the Oracle database is not high enough.  The number can be checked by this SQL statement:

SQL> select name, value from v$parameter where name like 'process%';

The default value is 100 and the recommended value is 800. If your current value is above 800 it is OK. If the current value is below 800 you can change it using this SQL statement:

SQL> alter system set processes = 800 scope=spfile;

After changing this parameter you have to make an instance restart for the change to take effect.

Suggestion 3

Sessions that have lost their client never gets killed, because they never send anything to the database.

A small procedure is available that runs once per hour and kills off any sessions that have been idle for longer than two hours, thereby preventing orphan sessions.

This procedure must be run by a user who has "alter system"-privileges, so you may have to create a separate user for this purpose.

Please contact the DBA at CDRator!