Oracle Database 10g Debugging Connection problems (contd)

This post is a continuation of the previous post in this topic .We have already mentioned about  how to enable trace and log options on the client.The below information is just for your recap if you have seen the prevoius post and also for those who are familiar about Net services in oracle and want to debug their connection issues

Note:-In this post we have discussed only about Windows Platform

The last option to find out connection issues is to enable log and trace files as below

I)The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN

The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are

sqlnet.ora Parameter

LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.

LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.

TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc

TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing

user (equivalent to 4) traces to identify user-induced error conditions

admin (equivalent to 6) traces to identify installation-specific problems

support (equivalent to 16) provides trace information for troubleshooting information

In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options

Sample sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_DIRECTORY_CLIENT=D:\test
TRACE_FILE_CLIENT=trace_file_test

Go to II) If you want to just debug the trace file without much information required or else check the below examples of connection issues which were debugged

A)First and foremost you have enabled the log and trace files and the trace file is not generating in the path mentioned in TRACE_DIRECTORY_CLIENT in sqlnet.ora or in the default path then check the below

1)If there are multiple Oracle homes make sure that the oracle client which you are using to connect is the default Oracle_Home

2) Suppose you have two oracle homes one D:\orant\bin and D:\Oracle10g\bin.
Suppose you want to debug the application using oracle client D:\Oracle10g\bin
then put this entry as the first entry in Environment Variable "PATH".

MyComputer-->Properties-->Advanced-->Environment variable

for example if PATH= D:\orant\bin;D:\Oracle10g\bin;<OTHER PATHS>

change it to

PATH= D:\Oracle10g\bin;D:\orant\bin;<OTHER PATHS>

3) If still Trace file is not generating then mention the backgorund and details in the comment section.

B)You are getting the error "ORA-12154: TNS:could not resolve the connect identifier specified" one possible error may be that your TNS Entry may be wrong if you are using local naming (TNSNAMES.ORA file)

Hence the trace file may contain this

Success:-

nnftrne: Using tnsnames.ora address (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.170.8.77)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FLEXCUBE.WORLD)
)
) for name CTEST.WORLD


Failure:-

nnftrne: Using tnsnames.ora address (DESCRIPTION = for name CTEST.WORLD


with out having the complete tns entry.

For a better understanding of this error ORA-12154 check the link ORA-12154:TNS:could not resolve the connect identifier specified

C)If you are getting the error "ORA-12545: Connect failed because target host or object does not exist"
the one possible reason may be the Host ip address has some special character in the TNS Entry if you are using local naming(TNSNAMES.ORA)
Hence the trace file may contain this

Failure:- 

snlinGetAddrInfo: Name resolution failed for 10.170.8.77,

for more information about this error check ORA-12545: Connect failed because target host or object does not exist

D)if you are getting the error ORA-12541: TNS:no listener

One possible reason might be the host name is mentioned wrongly.

Then the trace file will contain

Success:-

nttcni: trying to connect to socket 412.
snlinGetNameInfo: entry
snlinGetNameInfo: Using numeric form of host's address 10.170.8.77
snlinGetNameInfo: exit
nttcni: connected on ipaddr 10.170.8.77

Failure:-
nttcni: trying to connect to socket 420.
ntt2err: entry
ntt2err: soc 420 error - operation=1, ntresnt[0]=511, ntresnt[1]=61, ntresnt[2]=0
ntt2err: exit 

for more informnation on this error check ORA-12541: TNS:no listener

II)These are simple examples of what might contain in the trace file for Connection errors from Client machines

 For debugging all kinds of connection errors from client

1) Take the trace file from the client machine in which the Connection is successfull which have same configuration of oracle client and database as the client machine which has connection errors,if the oracle client is 10g or higher then remove the timestamp information  

2) Take the trace file from problematic Client machine ,if the oracle client is 10g or higher then remove the timestamp information 

3)Compare the two trace files to identify the connection problem and  google it for additional information .Also note the trace file for successfull connection might contain additional data which you can ignore.


We hope this information was useful

Oracle Database 10g Debugging Connection problems

Even though this post is about solving Oracle Database 10g connection problems most of the things discussed here are same or similar for other versions of Oracle databases.
In order to diagnose Oracle database connection problems we need to know about Oracle Network services(Net 8).Oracle Net Services(Net 8) is a service which provides methods for understanding and resolving network problems.It uses logs and trace files to diagnose the problem.

Commonly there are two types of Diagnostics we can do to identify Connection Problems

Server Diagnostics:-To identify if there is connection issue in the database server you need the assistance of DBA or system administrator.Also two things you have to know is
1) Whether other systems are able to connect to the database server ,if it is yes then there is no issue with the server.
2)Whether any server,database or listener configuration has been changed recently ,if it is then there is a possibility of issue at the server side.

If there is a server issue Administrator has to do two types of test

1)Verify the database is running
2) Perform a loopback test
The Server Diagnostics is not our concern since it will be taken care by Administrator

Client Diagnostics:-To diagnose Client connection problems you have to know two things

If you have multiple Oracle homes on your client machine or you are establishing a connection from third Party Application(Visual Studio) to oracle,mention your connection problem in the Comments section,i would address them as much as possible

If the same Oracle Client installation has been done on your network and only your Client is not able to connect to the database server jump to point 5 otherwise see the below

1)Check that you have installed the same protocol support as was installed on the database server.This can be verified with the System Administrator.The Common protocol in unix system are
IPC
BEQ
TCP/IP
SSL
RAW

2)Check base connectivity for underlying network transport.For example for the TCP/IP Protocol check the PING Utility(which is very commonly used).Use the PING utility to test the network connection between client and Database server

3)Verify with the Local PC Support that all Net8 Services software for the client has been installed

4) Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files exist in the correct locations and then check Localized Configuration File Support for further details

5)If other client computers are  connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working computer onto the non-working client

6)Perform further  test by verifying Testing Network Connectivity from the Client. While verifying this the TNSPING is not a reliable utility to check connection descriptor but anyhow for testing purpose suppose this is the Tnsnames entry for the database

ORA11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA11)
)
)
you have to go to Command Prompt and type

d:>TNSPING ORA11
to verify the connection service.On success it will give

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =ORA11)))
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)

Also trcroute is not available in Oracle database 10g Windows platform.

7)IF the connection still fails your last option is to enable log and trace files

The Common file where we can enable logging options in client is sqlnet.ora located in <ORACLE_HOME>/NETWORK/ADMIN

The logging parameters and Tracing parameters( TRACE_LEVEL_CLIENT being the most important)for client are

sqlnet.ora Parameter
LOG_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is the current working directory.

LOG_FILE_CLIENT :-Sets the name of the log file for the client. By default the log name is sqlnet.log.

 TRACE_DIRECTORY_CLIENT :-Establishes the destination directory for the client log file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows.

TRACE_FILE_CLIENT :-Sets the name of the log file for the client. Sets the name of the trace file for the client. By default the trace file name is sqlnet.trc

TRACE_LEVEL_CLIENT :- This will decide the level of information provided values are
off (equivalent to 0) provides no tracing

user (equivalent to 4) traces to identify user-induced error conditions

admin (equivalent to 6) traces to identify installation-specific problems

support (equivalent to 16) provides trace information for troubleshooting information

In our case we need to troubleshoot hence set the level to SUPPORT unless problem requires the other options

Sample sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
LOG_DIRECTORY_CLIENT=D:\test
TRACE_LEVEL_CLIENT = SUPPORT
So on a concluding note kindlylet us know the background of the problem and copy the contents of the log and trace files in Comments section if you are not able to figure out the Connection Problem yourself.