Troubleshooting Database Network Connectivity Issues

Troubleshooting Database Network Connectivity Issues

Step 1.
Use the OS ping utility to determine whether the remote box is accessible

for example:

$ ping qproddb
qproddb is alive

If ping doesn’t work, work with your system or network administrator to ensure you have server-to-server connectivity in place.

Step 2. 

Use telnet to see if you can connect to the remote server and port (that the listener is listening on)

for example:

$ telnet qproddb 1521
Trying 127.0.0.1...
Connected to qproddb.
Escape character is '^]'.

The prior output indicates that connectivity to a server and port is okay. If the prior command hangs, then contact your SA or network administrator for further assistance.

Step 3. 

Use tnsping to determine whether Oracle Net is working. This utility will verify that an Oracle Net connection can be made to a database via the network.

for example:

$ tnsping qproddb 

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
(HOST = qproddb.us.farm.com)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = QPRODDB)))
OK (500 msec)

If tnsping can’t contact the remote database, verify that the remote listener and database are both up and running. On the remote box, use the lsnrctl status command to verify that the listener is up. Verify that the remote database is available by establishing a local connection as a non-SYS account (SYS can often connect to a troubled database when other schemas will not work).

Step 4. 

Verify that the TNS information is correct. If the remote listener and database are working, then ensure that the mechanism for determining TNS information (like the tnsnames.ora file) contains the correct information.

Sometimes the client machine will have multiple TNS_ADMIN locations and tnsnames.ora files. One way to verify whether a particular tnsnames.ora file is being used is to rename it and see whether you get a different error when attempting to connect to the remote database.

Step 5. 

If you’re still having issues, examine the client sqlnet.log file and the remote server listener.log file.

Sometimes these log files will show additional information that will pinpoint the issue.