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 :)


Related Posts:
- How to connect SQLPlus without tnsnames.ora
- ORA-12154: TNS:could not resolve the connect identifier specified
- ORA-12545: Connect failed because target host or object does not exist
- ORA-12560: TNS:protocol adapter error
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
- ORA-03135: connection lost contact
- ORA-01017: invalid username/password; logon denied

16 comments:

  1. Excellent Nimish. Thanks for explaining, good step by step explaining of the connection process.

    ReplyDelete
    Replies
    1. Nice to know you liked it, hoping it was informative for you :)

      Delete
  2. 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"

    ReplyDelete
  3. can you explain point 5 please

    ReplyDelete
  4. Superb..!! Simple and very informative... Thanks a lot Nimish..

    ReplyDelete
  5. Very Good.
    This issues commonly encounters to many developers while connecting Database server
    I think this is very useful to many people.
    ThanX Nimish.

    ReplyDelete
  6. Very good and Informative.
    Thank you.

    ReplyDelete
  7. What about environment variable TNS_ADMIN ?
    Actually we can connect to Oracle Database Server using connect string which is not refer to tnsnames.ora.

    ReplyDelete
    Replies
    1. TNS_ADMIN is an environment variable that points to the directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located.

      yes you can connect using connection string or ezconnect, but they will ultimately use HOST, PORT and SERVICE.

      Delete
  8. @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.

    ReplyDelete
  9. @Nimish. We good article. Please can you clarify if a single machine can run more than one Database Instance.

    ReplyDelete
    Replies
    1. Yes a single machine can have more than one Database Instance

      Delete