SQLNET: How does Oracle Client connect with Oracle Server

Last night one of my friend called me after his Interview, and asked me how Oracle client connects to Oracle Server, and what are the general issues we can face in that. Believe me he has around 6 years of experiance in Oracle Development.

I am trying to explain here (in little detail), what I told him last night.

Lets first try to understand what is Oracle Server and what is Oracle Client:

Oracle Database Server:
Oracle Database Server is usually a physical machine running one or more Oracle Database Instance along with its Database, which also facilitate by providing a connection mechanism to database using listener.

Oracle Client:
Oracle Client is usually a process or a program on a machine, which tries to connect to Oracle Database Server. Oracle Client can exist on any computer usually connected to Oracle Database Server machine by a network. It may be on Same Oracle Database Server also.

Now lets understand what happens when an Oracle Client tries to connect to Oracle Database Server in happy scenario i.e. every thing is up and running and properly configured. Lets try to understand it by what happens when we try to connect Oracle Server by sqlplus as

sqlplus scott/tiger@orcl

Steps / Process Involved
1) Operating System locates "sqlplus" and pass command line parameter "scott/tiger@orcl"
2) sqlplus now asks TNS to connect to "orcl" using user "scott" and password "tiger"
3) TNS now looks into "tnsnames.ora" file to find out the meaning of "orcl". Default location of the file is "$ORACLE_HOME/network/admin" and most probably has entry for "orcl" like

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydomain.co.in)
    )
  )

4) Now it will try to connect Oracle Database Server Machine "ngarg.mydomain.co.in" on port 1521.
5) On Oracle Database Server there will be a listener running on 1521 port, listening to "orcl" service.
6) The listener will assign a server process to client.

General Issues we can face in connecting Oracle Client to a Oracle Database Server:
1) ORA-12154: TNS:could not resolve the connect identifier specified: When there is an issue in TNSNAMES.ORA like connect identifier entry for "ORCL" is not existing or proper in TNSNAMES.ORA.
2) ORA-12545: Connect failed because target host or object does not exist: "ngarg.mydomain.co.in" can not be reached over the network.
3) ORA-12541: TNS:no listener: There is no listener running on "ngarg.mydomain.co.in"
4) ORA-12560: TNS:protocol adapter error: There is no listener running on "ngarg.mydomain.co.in" at port 1521, or listener not properly configured.
5) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor: Listener is running but is not configured for the service "orcl.mydomain.co.in"

Oh yes, I did not remember these ora-codes last night while discussing this with my friend on phone, and just told him in general language. Hope you have enjoyed reading this and it was informative :)

17 comments:

Anupam MazumdarApril 21, 2014 at 10:19 AM
Excellent Nimish. Thanks for explaining, good step by step explaining of the connection process.
AnonymousApril 24, 2014 at 9:32 PM
Very well put. Thanks for the explanation, which I found very useful.

If I may, I noticed a minor discrepancy between your example tnsnames.ora file block vs your notes.

The tnsnames.ora file references the server "ngarg.mydomain.co.in" while the notes reference "ngarg.mydomain.com"
UnknownApril 24, 2014 at 11:50 PM
can you explain point 5 please
UnknownApril 25, 2014 at 12:38 AM
Superb..!! Simple and very informative... Thanks a lot Nimish..
AmolApril 25, 2014 at 12:29 PM
Very Good.
This issues commonly encounters to many developers while connecting Database server
I think this is very useful to many people.
ThanX Nimish.
AmolApril 25, 2014 at 12:30 PM
Very good and Informative.
Thank you.
AnonymousApril 25, 2014 at 12:55 PM
What about environment variable TNS_ADMIN ?
Actually we can connect to Oracle Database Server using connect string which is not refer to tnsnames.ora.
UnknownApril 25, 2014 at 7:21 PM
Very well explained..
Gaja Krishna VaidyanathaApril 26, 2014 at 5:39 AM
@Nimish - nice writeup. Just a little bit more detail to add for step #6. As you are already aware, Oracle supports both dedicated and shared server connections. Assuming that the client can communicate to the listener at 1521 with no issues, the following is true:

In the case of a dedicated server connection, the listener does a "spawn and bequeath". This means that the server process is spawned and given the TCP socket address where the client is communicating from. The server process then connects to client's TCP socket address and starts independently communicating with the client process (the listener is now out of the picture). If the connection type is shared, then the listener does a "redirect" to an already running shared server process.

The big implication from the above is that the listener is not involved in anything beyond the initial connection request. Which is why when you shutdown the listener although new connections to the database will fail, existing connections will persist and work just fine.
AnonymousApril 30, 2014 at 8:59 AM
@Nimish. We good article. Please can you clarify if a single machine can run more than one Database Instance.

SreeharshaJune 6, 2016 at 10:13 AM
Good explanation Bro..

Mahesh NaiduJune 6, 2016 at 11:32 PM
Nice One
snapstories9May 9, 2021 at 7:40 PM
where can i find EZZCONNECT IN LINUX