TNS Resolution

The listener will sit patiently waiting for connections, but each Oracle client needs a way to identify what database is where. This is done using TNS. TNS configuration is kept in the tnsnames.ora file, which is basically an Oracle equivelent to a hostfile.

This file can be put in a variety of places. Typically it should go in $ORACLE_HOME/network/admin if you have a full client installed, but each OS has a variety of diffrent places it looks when the client starts up. The best way to find out where it's looking is to use truss or strace while connecting to a dummy database and seeing which files it opens (ie: "strace sqlplus user/passwsddatabase").

Here's an example of a tnsnames.ora:

testdb, syslogdb =
          (description =
               (address_list =
                 (address =
                   (protocol = tcp)
                   (host = 10.10.0.130)
                   (port = 1521)
                 )
              )
              (connect_data =
                (sid = test)
              )
          )

Ok, it looks alittle strange, I admit. It's just a lot of nested statements, and actually reminds me alot of Solaris Zones configuration.

Whats important here? First line is the name of the database, followed by all the aliases you want. It's important to note that you can use any name you want! When you use a client to connect to the database it'll look for the name of the database you supply in the tnsnames.ora, and when it finds that name it'll use the information associated with it. Because the SID is specified in the description the name is completely arbitrary. Name the database "mysqlsbetter" if you want.

Inside the description is the important stuff, namely the "host" which I recommend as being the IP address unless you've got a good reason not too. The port will almost always be the default Oracle Listener port 1521. In the connect data section of the description we supply the all important SID.

You can have as many databases listed in the tnsnames.ora as you like, they all follow the above format. But please note that the file is read completely each time it's accessed so if you make a syntax error on line 15, line 16 and beyond won't get proccessed. If something just won't work, maybe it's a database description earlier in the file.

For complete details on syntax, please check out the Oracle Database Net Services Reference Guide:

http://download-west.oracle.com/docs/cd/B12037_01/network.101/b10776/toc.htm