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/[email protected]
Steps / Process Involved
1) Operating System locates “sqlplus” and pass command line parameter “scott/[email protected]”
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 = test.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: “test.mydomain.co.in” can not be reached over the network.
3) ORA-12541: TNS:no listener: There is no listener running on “test.mydomain.co.in”
4) ORA-12560: TNS:protocol adapter error: There is no listener running on “test.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”
