ORA-03114 Not Connected to Oracle

Problem Description:
----------------------------------------------------------------------------
Error : ORA-03114 Not Connected to Oracle
Server :
Oracle 8.1.6, Window 2000 Advanced Server
Domain Controller: Window 2000 Advanced Server
Client:
Oracle Net 8 (8.1.6)
Window XP SP2
Case:
Common causes for the ORA-03114 error include:
* Webserver or listener process is not running
* Missing entry to the tnsnames.ora
* Network issues
* Problems with connection pooling (shared servers, MTS)

Problem Explanation:
----------------------------------------------------------------------------
Problem Explanation:
====================
A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection.
There are a variety of common network connectivity error messages, and most DBA's have seen TNS error messages these at sometime in their careers. Here is just a small sample of possible TNS network connectivity-related errors:
· TNS-12545: Connect failed because target host or object does not exist
· ORA-12154: TNS:could not resolve service name
ORA-12157: TNS:internal network communication error

Solution Description:
====================
1)In the simplest of terms, the Oracle*Net architecture is like peeling on onion, with one transport layers nested inside the inner payer. The lowest level of Oracle transport is TCP/IP (or rarely other protocols), where the packets are shipped between the servers.
To troubleshoot & diagnose Oracle connectivity problems, I like to start at the simplest, outermost level, and follow this checklist:
1. Check with "ping" using the IP address ("ping 198.11.34.43"
2. Check with "ping" using DNS name (e.g. "tnsping uranus")
3. Check with "tnsping" using TNS service name
4. Sign-on to SQL*Plus with a remote user ("connect fred/flintstome@remote_link"
5. Within SQL*Plus, try a select from table@remote_db_link
2) Or you can check more
1. Verify there is no firewall, router setting which terminates connections which are active for longer than x minutes.

2. Verify there is no firewall, router setting which terminates connections which are idle for longer than x minutes.

3. Use latest version of the Connect wire protocol driver for ODBC for Oracle (some issues were reported with early versions of 4.0 and 4.1 with relationship to Oracle sending a probe packet).

4. Verify Oracle configuration. Let the database send a packet every x minutes, so that the firewall, router,... does not close the connection due to being 'idle'.


Sample Case
======================
1.> The Oracle documentation lists the following details for the sqlnet.ora parameter: SQLNET.EXPIRE_TIME

Purpose:
Use parameter SQLNET.EXPIRE_TIME to specify the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:
• It is not allowed on bequeathed connections.
• Though very small, a probe packet generates additional traffic that may downgrade network performance.
• Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.

Default:
0
Minimum Value:
0
Recommended Value:
10
Example:
SQLNET.EXPIRE_TIME=10

You may want to perform the following test:

* Stop your Oracle database instance
* edit the $ORACLE_HOME/network/admin/sqlnet.ora file and add SQLNET.EXPIRE_TIME=3
* restart the Oracle database instance
* Do a full connect with the Oracle WP driver via ODBCTest as SYSTEM/MANAGER
* Execute the following stored procedure: {call dbms_lock.sleep(3000)} (sleeps for 50 minutes). Adapt the value to your needs.

2.> Also check the Oracle setting 'connection idle time limit.

* This can be set in the Oracle enterprise manager console.
* Select the database you are using and click down to the "security\profile\\idle time".
* Setting this to something lower than 60 mins should get the user disconnected after XX mins. of inactivity.

TrungNEMO 12:24 AM Tags : 3.Oracle Help

Subscribe by Email

Follow Updates Articles from This Blog via Email