Thursday, September 29, 2011

Troubleshooting Database Network Connectivity

The user comes to you and tells you that he can not connect to the database. Now, you can think of zillion of things in a flash but here is what I'd do to start with to diagnose oracle database connectivity

Option 1

Use the 'ping' utility to make sure if the remote box is accessible
[oracle@testlab ~]$ ping ovm-test-lab

PING ovm-test-lab.ho.bilal.com (192.168.32.51) 56(84) bytes of data.
64 bytes from ovm-test-lab.ho.bilal.com (192.168.32.51): icmp_seq=1 ttl=64 time=2.17 ms
64 bytes from ovm-test-lab.ho.bilal.com (192.168.32.51): icmp_seq=2 ttl=64 time=0.168 ms
64 bytes from ovm-test-lab.ho.bilal.com (192.168.32.51): icmp_seq=3 ttl=64 time=0.171 ms
64 bytes from ovm-test-lab.ho.bilal.com (192.168.32.51): icmp_seq=4 ttl=64 time=0.180 ms


--- ovm-test-lab.ho.bilal.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3001ms
rtt min/avg/max/mdev = 0.168/0.672/2.172/0.866 ms

If ping doesn’t work, contact your system/ network administrator to make sure you have server-to-server connectivity. 

Option 2

Use telnet to see if you can connect to the remote server and port (that the listener is listening on)
[oracle@testlab ~]$ telnet ovm-test-lab 1521


Trying 192.168.32.51...
Connected to ovm-warehouse-lab.ho.bilal.com (192.168.32.51).
Escape character is '^]'.

If the above command fails, contact your SA or network administrator for further assistance

Option 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
$ tnsping bilal_db

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 29-SEP-2011 12:30:59
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ovm-test-lab)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =bilal_db)))
OK (0 msec)
[oracle@testlab ~]$
If tnsping can’t contact the remote database, verify that both remote listener and database are  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


No comments:

Post a Comment