MacLochlainns Weblog

I tried to answer a question in the forum a couple weeks ago about connections. It contained all the trite stuff about check this, then that, et cetera because I couldn’t remember why I’d seen an TNS-03505 error. It returns an illustrative message “Failed to resolve name” error. Along the way, I tripped into ORA-12560 and one I’d never seen before an ORA-12518. If you want the dirt on these read on …

TNS-03505
You’ll typically encounter this error when you’re working on a laptop. It gets triggered when you run the tnsping utility. When you’ve qualified the hostname and hostname.domain name in the hosts file and in the tnsnames.ora file, it’ll throw the TNS-03505 error because you’re on another network. You may also encounter it when there’s a change or discrepancy between the machine hostname and DNS server results. Lastly, you may encounter it when you’ve lost the lease on an IP address and now have a new lease with a different IP address.

These errors effectively block successful tnsping calls. You get around it by shutting down the listener, modifying the IP address in the tnsnames.ora file if you’re not using a hostname, setting the %TNS_ADMIN% environment variable in a command session when you have multiple Oracle homes, and restarting the listener.

TNS-12533
This is an doubly annoying error because while it can occur for a meaningful reasons like fat fingering a key word in the ADDRESS descriptor, it can be a stray tab character. That’s why the error text is: TNS:illegal ADDRESS parameters

In vi or vim, you can see hidden characters by typing the : (colon), and enabling list. List shows you hidden characters, like the $ for line returns, et cetera. You run it like this:

:set list

If you see a stray tab character, ^I, remove it and the error should go away. You can disable list by setting nolist.

ORA-01017
This is a principally new error with Oracle 11gR2, and involves the Oracle Call Interface (OCI). You’ll see the errors when you try to change the password of a connect user, and Chris Jones at Oracle explains it well.

ORA-12170
This is generally an easy fix. It typically means that the listener can’t be found. The most frequent cause of the error is a generic installation of the Oracle database or client on a DHCP configured laptop. If there’s no hostname to IP address map defined in the /etc/hosts (Linux or Unix) or the C:\WINDOWS\system32\drivers\etc\hosts file, the installation uses the current IP number. You typically see this error after you’ve fixed your Oracle listener that wouldn’t start by replacing the IP address with a hostname, which is done in the listener.ora file. Unfortunately, you also need to change the same thing in the tnsnames.ora file.

When you connect your laptop to another network, a TNS-12170 exception is thrown. You can fix it by replacing the (HOST = nnn.nnn.nnn.nnn) with the machine name, like (HOST = some_hostname).

ORA-12500
This one generally has two causes. First, when the service was created by somebody other than the Administrator, which can be a user with Administrator role but not in the Administrator group. Second, when the machine doesn’t have enough physical or virtual memory to spawn a new dedicated process.

ORA-12514
This one states that Oracle Listener can’t find the connect descriptor. You get an error like the following:

ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor

The ORA-12514 typically means that the Oracle Listener is running but that Oracle on a Linux or Unix machine, or the Oracle Service on a Windows machine is not started.

ORA-12518
This one is cute. I only hit it because my touchpad is too sensitive on the Dell laptop. It occurs if you pause the Windows listener service. You fix it by restarting the service. It is an enigma within a conundrum (Churchill on the old Soviet Union) why anybody would create a service like this with a pause option. Wouldn’t it be cool if somebody at Oracle were listening now (that is reading this blog) and they got the service fixed.

ORA-12541
This typically means one of two things, which depends on the text of the error. It generally returns either a "no listener" or "could not resolve the connect identifier specified" message text.

The former means the listener isn’t running or can’t be found by the Oracle Instant Client software. If the listener isn’t running, you can start it with the lsnrctl utility, or in Windows by starting the service.

# lsnrctl start

When the listener is running but isn’t seen by an Oracle Instant Client installation or another database through a database link, you probably have a firewall rule in place blocking the port that is listening for incoming traffic. Check your server firewall rules.

The latter error means sqlplus can’t find the tnsnames.ora file. Oracle checks for the tnsnames.ora file in the present working directory, then the location of the $TNS_ADMIN directory (or Windows %TNS_ADMIN%), and finally the location of the $ORACLE_HOME/network/admin directory. If you’re getting this error in a PHP configuration, like a CGI mode, put a copy of the tnsnames.ora file in the same directory as the php-cgi.exe file. Alternatively, you can define the following in your httpd.conf file.

SetEnv TNS_ADMIN "tns_admin_directory"

ORA-12560
This is the sqlplus utility cousin of the TNS-03505. All the rules that apply to it apply to this.