Ladysign

4 checks for diagnosing remote database connection problems

Difficulty:

There are 4 main checks for diagnosing remote database connection problems:

• tnsnames.ora
• Oracle class path
• ping
• tnsping

tnsnames.ora

First check if the tnsnames.ora file has the correct database information/credentials.
The tnsnames.ora file can be found in: \ORACLE\ora92\network\ADMIN
These credentials should look like:

MYDATABSENAME =
	(DESCRIPTION=
		(ADDRESS_LIST=
			(ADDRESS=(PROTOCOL=TCP)(HOST=ladysign)(PORT=<port 1111>))
		)
		(CONNECT_DATA=
			(SERVICE_NAME=myServiceName)
		)
	)

Oracle Class path

Make sure oracle is in your class path.
Press Windows Key + Break key; Advanced Tab > Environment Variables
Make sure there is a ORACLE_HOME system variable. For example:
ORACLE_HOME = C:\\ORACLE\ora92

Ping

The ping utility is used to test the connectivity to a remote machine. ping will indicate whether a remote server is accessible and responding. If the ping command indicates that a machine cannot be accessed, the other connectivity tests will also fail.
U can use the ping command via the (DOS) command line; ping . For example:

C:\>ping ladysign
Pinging ladysign [198.64.245.67] with 32 bytes of data:

Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254

TNSPing

When the connectivity to the host is confirmed with the ping command, the next connection to check is the listener.
You can do this with the tnsping utility, which determines wheter or not an Oracle service can be succesfully reached.
However, tnssping will only report if the listener process is up and provides no indication of the state of the database:
For example:

C:\>tnsping MYDATABASENAME

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 06-APR-2009 11:02:32

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\ORACLE\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=ladysign)(PORT=1111))) (CONNECT_DATA= (SERVICE_NAME=myDatabaseService)))
OK (80 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)