Page: 1 2

Oracle Orphaned Sessions:

Most of the times we encounter a scenario where a connection is made by a client application a transaction is started that holds locks. If at that time a power failure occur at the machine where our client application is running or the network connectivity is lost then hung sessions will be left by the clients. These sessions are called as orphaned sessions.

Oracle Process Monitor:

Oracle process monitor (PMON) performs process recovery when a user process fails or it is killed. It cleans up the cache and frees resources used by that process. However PMON can not clean Oracle orphaned sessions or the resources used by them as Oracle orphaned sessions are not visible to PMON.

Issues with Oracle Orphaned Sessions:

Oracle Orphaned sessions use server resources. There may be open cursors, temporary tables, or locks associated with them. Due to Oracle orphaned sessions, the locks may block other connections and at extreme it may result in a major stack of locks. As a result we may face the severe scenario where it appears that Oracle has stopped working.

Troubleshooting Oracle Orphaned Sessions:

A number of options are available to get rid of Oracle orphaned sessions. Let us discuss some ways to troubleshoot them.

Enabling Dead Client Detection:

One option is that you enable dead client detection (DCD) in the SQLNET.ORA file. Remember it must be enabled on the database server and not on the client application machines.

SQLNET.EXPIRE_TIME parameter:

You can enable Dead Client Detection by setting the parameter sqlnet.expire_time in the SQLNET.ORA file. This parameter represents the time specified in minutes. The sqlnet.expire_time parameter determines how often a probe should be sent verifying that client/server connections are active. If you set it to value greater than 0 then it means that due to any abnormal client termination the connections are not left open indefinitely. However if a terminated connection is found or a connection that is no longer in use then it will return an error and your server process will exit. If you want that your connections are not left open indefinitely or up to the time set by OS-specific parameters then you should set a value that is greater than 0.

Although this approach will help you get rid of orphaned session but a number of pitfalls are associated with it. Firstly you can use this approach on bequeathed connections. Secondly, although the probe packet is quite small but still it generates additional traffic that may affect your network performance. Also depending on the OS in use your server may need to perform additional processing so that it could distinguish the connection probing event from other events. Continued...

Page: 1 2