Troubleshooting of Oracle networking issues can be a bit more complicated than a general database issue because it involves the client configuration, network, and server configuration. On the client side we have determine the method used for connection and that all the parameter used to resolve the server side connection are correct. Ensuring the network connection from the client machine to the server is also critical and will normally include network administrative support. Finally, on the server side, the listener process must be properly configured to accept client connection requests and pass them to the appropriate server process. The entire process of determining the solution to an Oracle networking issue can be very convoluted. It helps to eliminate first what is not the problem. This article outlines steps to address networking issues in a systematic process. It addresses the three areas of network issues separately and if all work should make for a fluid network connection between oracle client and oracle database server. It is assumed that the network connection has worked in the past and all appropriate files have been configured.
Server side network troubleshooting:
- Logon to your oracle database server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Thu Jan 5 07:25:33 EST 2015 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$
- Verify that the listener process is running with the command lsnrctl status.
[oracle@linux2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:46:48
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@linux2 ~]$
- The listener process is not running start the process with lsnrctl start.
[oracle@linux2 ~]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:47:01
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.102)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 05-JAN-2015 10:47:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.102)(PORT=1521)))
Services Summary…
Service “cdb1” has 1 instance(s).
Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@linux2 ~]$
- Once listener has been started attempt to use the listener with tnsping utility.
[oracle@linux2 ~]$ tnsping orcl
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@linux2 ~]$
- This shows that the listener is accepting connection, but there may be issues in the database such as dispatcher or server processes that are beyond this instruction set.
Network troubleshooting between client and server:
- From the client machine open a command prompt, ensure that you can successfully ping the client machine.
C:\Users\MYWINDOWS>ping 192.168.0.102
Pinging 192.168.0.102 with 32 bytes of data:
Reply from 192.168.0.102: bytes=32 time<1ms TTL=64
Reply from 192.168.0.102: bytes=32 time<1ms TTL=64
Reply from 192.168.0.102: bytes=32 time<1ms TTL=64
Ping statistics for 192.168.0.102:
Packets: Sent = 3, Received = 3, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
Control-C
^C
C:\Users\MYWINDOWS>
- Success full connection to server machine. If this connection fails, contact your network administrator.
Client side:
Client side network troubleshooting:
- Logon to your oracle client machine with normal user account.
- Open a command prompt and attempt a tnsping to your alias machine
C:\Users\MYWINDOWS> tnsping orcl
TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 05-JAN-2015 10:48:42
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
C:\Users\MYWINDOWS>
- If this fails verify that the values for POTOCAL, HOST, PORT, SERVICE_NAME are correct for the server and database service you are seeking to connect to.
NOTE: You can trace oracle net communications at the server level and client level this gives very detail logs of the communications occurring and very helpful in troubleshooting network connectivity issues.
Larry Catt
OCP