Oracle Network Toubleshooting Guide

I hate troubleshooting Oracle Network Problems the most!!! I would MUCH rather see an ORA-600 error. Because I’ve come across these errors and I know how painful they can be, I’ve set up this little troubleshooting guide that will hopefully be of service to you.

Good luck, friend.

First of All: Troubleshooting Network problems will require a bit of patience and a clear head.

If you are angry or stressed, go unwind for a minute. Take a quick lap around your office. Go get a drink from the water cooler. Spend a few minutes outside if the weather is amicable. Try deep breathing exercises. Find some relaxing spa music to listen to. Seriously, I promise, it’ll help you think better.

Are you in a hurry to get this fixed?

HERE IS *THE FASTEST* way to do Oracle Network Troubleshooting:

THINKING CLEARLY IS THE FASTEST WAY TO GET THIS RESOLVED.

YOU CANNOT THINK CLEARLY IF YOUR STRESS HAS A HOLD ON YOU, OR IF YOU’RE PANICKED.

Get up from your chair.

Right now.

And go do whatever you have to to get a clear head.

Take a jog. Get a drink from the water cooler. Step outside for fresh air. Take deep breaths, and focus on your breathing.

Got a clear head?

(Don’t even bother reading ahead until you’re relaxed. Seriously.)

Solving this is going to require some patience.

You need to read this article thoroughly so you can understand what we’re doing.

I’m here to help. Read carefully.

Verifying Network Connectivity

First of all, you want to make sure you can ping the database host from your client machine.

On your client machine, open up a command prompt and type:

ping <database host name>

Where you would replace <database host name> with what you’re using as the host name in your connection–whether that’s a domain name, an IP address, or a network alias.

If that doesn’t connect, then you’ll want to investigate that further.

• Does your network allow traffic between the two hosts?
• Is there a firewall (either hardware, or software) in place that’s preventing a connection?
• Take into account–are you able to reach other network sites? Or is the problem an unplugged someplace?
• Double check that you’re connecting to the correct host or IP address. Did you spell everything correctly?

See if you can PuTTY into to the database host

If you’re able to ping the database host, you’ll want to see if you can login to the database host using something like PuTTY. At this point, you’ll want to verify that you’ve in fact reached the host you think you have.

If you can’t PuTTY into the database host, there’s a chance that some ports are being blocked, while others may not be. Check with your network guys.

If you’re able to PuTTY into the machine, verify that you can open a connection with the database from your PuTTY session.

You’ll want to make sure that you can actually start a connection to the database in your PuTTY connection.

If you’re not able to start a connection from the database on the database server through PuTTY, you’ll want to look into what problems the database is giving you.

• Check the error message you’re getting, see if that gives you any clues.
• Make sure the listener is up and running, and connected to your database:

lsnrctl status 

• Check your listener log for details, see what information your listener log yields. You can find your listener log by running the following command:

lsnrctl status | grep "Listener Log File"

• Check your database alert log as well, and see if it provides any clues about why you can’t connect to the database. Find your alert log here:

$DIAGNOSTIC_DEST/diag/rdbms/<DB_NAME>/<ORACLE_SID>/alert/log.xml

If your database has been setup according to OFA standards (Optimal Flexible Architecture) then DIAGNOSTIC_DEST usually points to /u01/app/oracle

If you’re able to connect to your database and select from a table, then this is promising!!

Now, see if you can’t work backwards: Try pinging your personal machine from the database host in your PuTTY connection.

If there are any difficulties in this step, then you’ll want to bring in the networking guys. Show them that you’re able to connect from your local host to the database, but not from the database back to your local host. I would suspect some kind of a firewall issue at play if this happened.

If you’re able to connect back to your personal machine though, congratulations! You’ve verified your network connectivity.

Figure out if you have a STATICALLY or DYNAMICALLY registered listener.

On your database host, run the following command:

lsnrctl status

Statically Registered Listeners

If the database you’re connecting to has an “UNKNOWN” status, then that probably means your database is statically registered to the listener (as opposed to dynamically registered). You can verify that by logging into the database and running something like this:

show parameter listener

If you get all empty parameters, like this:

SQL> show parameter listener

NAME                            TYPE             VALUE
------------------------------  ---------------  --------------------
listener_networks               string
local_listener                  string 
remote_listener                 string

…then you have a statically-registered database. That means you’ll want to check your listener parameter file: This file defines your database-to-listener connection.

You can find the location of your listener parameter file by typing the following:

lsnrctl status | grep "Listener Parameter File"

Inside this file, carefully double-check your settings. Make sure your host, port, and protocol are what you expect them to be.

Dynamically Registered Listeners

If, when you run lsnrctl status you see a status of READY or BLOCKED or READY/SECONDARY then your listener is dynamically registered. PMON periodically performs this task in the background. Another indication that you have a dynamically registered database is this: You’ll see a value in your local_listener or remote_listener parameter files (as opposed to an empty string), like this:

local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=example.com)(PORT=1534))'
remote_listener='abc:1534'
listener_networks='((NAME=xyz)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=example.com)(PORT=1534))))(REMOTE_LISTENER=qrs:1534))'

Ensure that these values are correct.

If you don’t have a correct configuration, you can change them using commands such as the following:

ALTER SYSTEM SET LOCAL_LISTENER=["]listener_address["][,...];
ALTER SYSTEM SET REMOTE_LISTENER=["]listener_address["][,...];

Etc.

If the problem that you’re facing is that the database hasn’t yet registered with the listener, you can manually cause this to happen for a dynamically registered connection using the following command:

alter system register;

The Oracle documentation provides additional information on dynamically registered databases here.

TNSPing

From your client machine, use the tnsping command with an ezconnect string to attempt to establish a connection with the database.

The format is as follows:

tnsping //<host_name>:<port_number>//<instance_name>

Or!

tnsping //<host_name>:<port_number>/<service_name>

Notice the subtle difference in single vs double slashes for instance name vs. service name.

Additional Oracle Network Troubleshooting Ideas

If the error you’re getting is ORA-12514 TNS:listener does not currently know of a service requested in connect descriptor, or ORA-12505, TNS:listener does not know of SID given in connect descriptor, then that means that you’re able to connect to the listener just fine, but the specific service or SID that you specified in your connect string. Ensure that your local computer’s connection settings align with the database’s connection settings.

Other tools that might be of interest:

• tracert if you’re dealing with a scenario where there are several hops between you and the database. This can help identify if the problem is somewhere between your local client machine and the database
• You may consider flushing the dns cache in case the IP addresses of your host have changed (in windows the command to do this is ipconfig /flushdns)
• Something that may work in very rare instances (for example, if you connected your computer to a new switch or router recently) is flushing the arp cache on your local machine (command on windows is arp -d -a)

I hope this brief Oracle Network Troubleshooting guide has helped you! If you find a solution to a problem that wasn’t addressed here, feel free to leave a comment indicating your problem along with the fix below.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets