09 - [Oracle 19C] Oracle Connecting

• Connecting to oracle database

• Back in the day, the only way to connect to a database was usually to be on a host .

• Now oracle support TCP/IP and Secure TCP/IP connections from clients.

• To make connection with oracle we need Oracle net services

• Oracle net services installed with the database.

• Oracle Net Listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server

• Oracle uses 2 main files (listener.ora, tnsnames.ora) for network configuration.

 

 

What is the meaning of Oracle Net Listener?

 

• Oracle listener as a gateway to the actual Oracle database

• It is a separate component

• Multiple Oracle instances running locally on the same server can use the same Oracle Listener as a gateway for all connecting clients.

• When a client wants to establish a connection to a specific Oracle instance or database, the Oracle Client will use a combination of the host name where the instance and listener are running as well as the uniquely identifying service name for the desired instance

 

 

Listener.ora is a SQL*Net configuration file used to configure Oracle Database Listeners (required to

accept remote connection requests).

This file normally resides in the ORACLE HOME/network/admin directory

 

LISTENER =
	(DESCRIPTION_LIST =
	  (DESCRIPTION =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1521))
	    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	)
)

 

 

 

• TCP/IP is the standard communication protocol used for client/server communication over a network.

• The IPC protocol support can be used only when the client program and Oracle Database are installed on the same system

 

 

 

• Understanding Database Services

An Oracle database is represented to clients as a service ( it is just an alias for the db)

A database can have one or more services associated with it.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener

Note1: The listener registration process (LREG) registers information about the database instance

and dispatcher processes with the Oracle Net Listener.

Note2: In releases before Oracle Database 12c, PMON performed the listener registration

 

 

 

• Understanding tnsnames.ora

Oracle client will need to know the following:

 The hostname where is the listener is running

 Port number

 Listener protocol

 Name of the service that the listener is handling

 

Instead of having to specify all of these parameters every single time a client needs to connect to an Oracle database, we use client-side configuration file called the tnsnames.ora.

 

# Connect username/password@orcldb

ORCLDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
  )
)

 

 

 

 

 

• How client connections work