This is the first posting on the blog and after a lot of thought process decided to write something on TNSNAMES.ora file.I am sure that most of us see this file almost everyday but never bother to understand what this means.Ok, so lets get started.......
TNS stands for Transparent Network Substrate.Whenever we want to connect to Oracle through any client say sqlplus we refer to the entries in this file by giving the Host String which should match the entry in the TNSNAMES.ora file. So what does this file contain? This file contains the details of the databases that can be connected to. So lets start with an example here:
One of the entry in my machine looks like this
1. DB1111 =
2. (DESCRIPTION =
3. (ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1521))
4. (CONNECT_DATA =
5. (SERVER = DEDICATED)
6. (SERVICE_NAME = db1)
7. )
8. )
Lets go through the entire entry now.Please note that i have marked the line numbers for clarity of explanation here. Lets start with line 3. " PROTOCOL = TCP " refers to the networking protocol .
" HOST = TESTSERVER " refers to the server on which the database is installed.
" PORT = 1521 " refers to the port on the server on which the listener is running.
A listener is a process on the data base which accepts new connections to the database.
Line 5 : " SERVER = DEDICATED " Mode of connection if it is DEDICATED or SHARED. There are two types of connections. Dedicated Server Mode and Shared Server Mode. More about this in the next post.
Line 6 : SERVICE_NAME = db1 Name of the database to which we intend to connect. It is also referred to as SID sometimes. SID refers to Site Identifier.
( Extended information : The service_name concept came available with TNS V3 with Oracle 8i. Sessions using the SID mechanism still use TNS V2 and it's only for backward compatibility . Using services is the way to configure tns connect alias starting from 8i. A service is a way to refer to an instance. Administering by means of services is a helpful topic when administering RAC environments. You can declare certain instances to belong to a service and other to another service name, this way you can better distribuite workloads during peak hours and off time, you can better schedule tasks and avoid bottlenecks by redirecting jobs to specific services and using features such as resource manager.)
Line 1 : This is the HOST STRING that we refer to while connecting.
So when we basically connect say by typing scott/tiger@DB1111 in the sqlplus window, our request gets translated to a request to connect to the the database DB1 which is running on TESTSERVER and the listener is accepting the connections on PORT 1521 on this server.
Can i connect to Oracle without TNSNAMES.ora on my local machine? Yes, of course you can by knowing the HOST, the PORT and the DATABASENAME.
Go to COMMAND PROMPT and type sqlplus "username"/"pwd"@"ENTIRE TNS ENTRY"
In my local machine here it is
C:\Documents and Settings\Smi>sqlplus scott/illiyaz@(description=(address=(proto
col=TCP)(Host=localhost)(PORT=1521))(connect_data=(server=dedicated)(service_nam
e=db1)))
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 9 10:20:16 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
SQL>
With all this information, we are in a position to solve ORA-12505 error ( TNS: listener could not resolve SID given in connection ).
More to come on the types of connections in the next post...Until then happy learning
Illiyaz
