Notes on Troubleshooting Oracle
When troubleshooting anything, use the KISS rule - Keep It Simple Stupid - for as long as you can. Check the obvious causes first (Is it plugged in? Is it turned on?) before going off on a wild-goose chase in Metalink or Google. Is tnsnames.ora correct? Are you using an alias contained in tnsnames.ora? Are you using the correct tnsnames.ora file?
Most of the errors I have encountered in my limited experience can be classified as configuration errors. The most common symptom of configuration errors is the infamous TNS-12154 TNS:could not resolve service name. This error is in most cases caused by:
In all cases, you can save yourself time and grief by verifying the simple things first. Oracle's configuration files are text-based and sensitive to misplaced or missing punctuation. Most Oracle errors won't give you much of clue as to what is causing the error. Oracle's error message index is the best place to start your investigation.
Using ping to test network connectivity
The simplest connectivity test is the ping utility. You will find it on Windows and all *nix flavors. On a Windows system, open a command window (Start > Run > cmd > ok) and type:
ping localhost
You should see something like this in response:
C:\WINDOWS\SYSTEM32>ping localhost
Pinging foo.bar.com [127.0.0.1] with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128
Ping statistics for 127.0.0.1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
NOTE: You may have to change to the /windows/system32 (WinXP) or /winnt/system32 (Win2K) to execute ping
To test connectivity between two computers, type the IP address or fully qualified hostname of the machine under test after ping:
ping 192.168.1.101
or:
ping foo.bar.com
If ping fails, you've got some fundamental problems that need to be fixed before going on. Some things to check:
Using tnsping to test database connectivity
The tnsping utility is installed with Oracle databases servers, database clients, and application servers. It is usually found in the \ORACLE_HOME\bin directory.
tnsping will test if Oracle database instances listed in your tnsnames.ora file can be reached. To use tnsping, open a command window and type:
tnsping alias
where alias is the alias of the oracle database instance you want to test. The results for a good test will look like this:
C:\oracle\bin>tnsping foo TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 31-JAN-20 05 13:09:26 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: c:\oracle\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp .world)(PROTOCOL = TCP)(Host = 192.168.1.101)(Port = 1521))) (CONNECT_DATA = (SID = FOO))) OK (120 msec)
Some things to check if tnsping fails:
Using lsnrctl to test the Oracle listener
The Oracle listener is a service that simply listens for incoming connection requests for an oracle instance. To test if the listener is working correctly, type lsnrctl status alias
D:\ORACLE\iSuites\BIN>lsnrctl status foo LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 - Production on 08-FEB-2005 16:01: 14 (c) Copyright 1998 Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=FOO))(ADDRESS=(P ROTOCOL=TCP)(HOST=FOO.BAR.COM)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc tion Start Date 29-DEC-2004 11:38:36 Uptime 41 days 4 hr. 29 min. 13 sec Trace Level off Security OFF SNMP OFF Listener Parameter File D:\oracle\ora92\network\admin\listener.ora Listener Log File D:\oracle\ora92\network\log\listener.log Services Summary... PLSExtProc has 1 service handler(s) foodb has 1 service handler(s) foodb has 2 service handler(s) The command completed successfully D:\ORACLE\iSuites\BIN>
For help, type lsnrctl help
Using netstat
The netstat command displays information about the network connections your computer has open. This command is useful for determing which ports are open on your computer as well as the computer it is connected to. To see all the options available for netstat type netstat -h:
C:\WINDOWS\SYSTEM32>netstat -h
Displays protocol statistics and current TCP/IP network connections.
NETSTAT [-a] [-b] [-e] [-n] [-o] [-p proto] [-r] [-s] [-v] [interval]
-a Displays all connections and listening ports.
-b Displays the executable involved in creating each connection or
listening port. In some cases well-known executables host
multiple independent components, and in these cases the
sequence of components involved in creating the connection
or listening port is displayed. In this case the executable
name is in [] at the bottom, on top is the component it called,
and so forth until TCP/IP was reached. Note that this option
can be time-consuming and will fail unless you have sufficient
permissions.
-e Displays Ethernet statistics. This may be combined with the -s
option.
-n Displays addresses and port numbers in numerical form.
-o Displays the owning process ID associated with each connection.
-p proto Shows connections for the protocol specified by proto; proto
may be any of: TCP, UDP, TCPv6, or UDPv6. If used with the -s
option to display per-protocol statistics, proto may be any of:
IP, IPv6, ICMP, ICMPv6, TCP, TCPv6, UDP, or UDPv6.
-r Displays the routing table.
-s Displays per-protocol statistics. By default, statistics are
shown for IP, IPv6, ICMP, ICMPv6, TCP, TCPv6, UDP, and UDPv6;
the -p option may be used to specify a subset of the default.
-v When used in conjunction with -b, will display sequence of
components involved in creating the connection or listening
port for all executables.
interval Redisplays selected statistics, pausing interval seconds
between each display. Press CTRL+C to stop redisplaying
statistics. If omitted, netstat will print the current
configuration information once.
C:\WINDOWS\SYSTEM32>
To display all connections, type netstat -a:
C:\WINDOWS\SYSTEM32>netstat -a Active Connections Proto Local Address Foreign Address State TCP foo:epmap foo.bar.com:0 LISTENING TCP foo:microsoft-ds foo.bar.com:0 LISTENING TCP foo:2030 foo.bar.com:0 LISTENING TCP foo:1045 foo.bar.com:0 LISTENING TCP foo:3161 foo.bar.com:0 LISTENING TCP foo:3553 localhost:3554 ESTABLISHED TCP foo:3554 localhost:3553 ESTABLISHED TCP foo:http foo.bar.com:0 LISTENING TCP foo:netbios-ssn foo.bar.com:0 LISTENING TCP foo:3189 dog.bar.com:49152 ESTABLISHED TCP foo:3193 tybex.bar.com:2630 ESTABLISHED TCP foo:3348 granite.bar.com:netbios-ssn ESTABLISHED UDP foo:microsoft-ds *:* UDP foo:isakmp *:* UDP foo:1025 *:* UDP foo:1026 *:* UDP foo:1473 *:* UDP foo:1474 *:* UDP foo:1475 *:* UDP foo:1476 *:* UDP foo:2967 *:* UDP foo:3191 *:* UDP foo:3546 *:* UDP foo:3980 *:* UDP foo:3981 *:* UDP foo:4500 *:* UDP foo:ntp *:* UDP foo:1027 *:* UDP foo:1048 *:* UDP foo:1900 *:* UDP foo:3199 *:* UDP foo:3203 *:* UDP foo:3629 *:* UDP foo:ntp *:* UDP foo:netbios-ns *:* UDP foo:netbios-dgm *:* UDP foo:1900 *:* C:\WINDOWS\SYSTEM32>
To see which programs are using which connections, type netstat -b:
C:\WINDOWS\SYSTEM32>netstat -b Active Connections Proto Local Address Foreign Address State PID TCP foo:3553 localhost:3554 ESTABLISHED 3104 [firefox.exe] TCP foo:3554 localhost:3553 ESTABLISHED 3104 [firefox.exe] TCP foo:3189 dog.bar.com:49152 ESTABLISHED 1124 [OUTLOOK.EXE] TCP foo:3193 tybex.bar.com:2630 ESTABLISHED 1124 [OUTLOOK.EXE] TCP foo:3348 granite.bar.com:netbios-ssn ESTABLISHED 4 [System] TCP foo:3688 pro12.abac.com:ftp ESTABLISHED 3208 [HTMLKit.exe] TCP foo:3693 pro12.abac.com:44800 TIME_WAIT 0 C:\WINDOWS\SYSTEM32>
To see only active connections displayed in numerical form type netstat -n:
C:\WINDOWS\SYSTEM32>netstat -n Active Connections Proto Local Address Foreign Address State TCP 127.0.0.1:3553 127.0.0.1:3554 ESTABLISHED TCP 127.0.0.1:3554 127.0.0.1:3553 ESTABLISHED TCP xxx.xx.xxx.184:3189 xxx.xx.xxx.9:49152 ESTABLISHED TCP xxx.xx.xxx.184:3193 xxx.xx.xxx.61:2630 ESTABLISHED TCP xxx.xx.xxx.184:3348 xxx.xx.xxx.240:139 ESTABLISHED TCP xxx.xx.xxx.184:3688 66.226.64.13:21 ESTABLISHED C:\WINDOWS\SYSTEM32>