Oracle Database Networking3 min read

Networking in Oracle Databases helps to establish communication between Oracle Database Server and Database Clients. This can include end users, applications, databases etc.

Let’s check out some of the key components and management of these.

Oracle Net Services

It is the foundation of Oracle Database networking which enables communication between client and database. It includes

• Oracle Net Listener: It is a server side process which listens to the client requests.
• Oracle Net Client: It is Client side utility which sends requests and connects to the database.
• TNS (Transparent Network Substrate): This is the protocol which is used for communication.

Listener

This is the critical component which have the first contact to the incoming connections coming from outside which are then routed to respective database instance.

• Configuration File: listener.ora
• Key Parameters:
  • Host: IP or hostname where it resides
  • Port: Where it is running. By default it is 1521
  • SID or Service Name: Identification of database to which connection will be routed.

TNS Names

It is a file (tnsnames.ora) which contains the details of network service names and connection details which can be used by Oracle Net Client.

Entry can look like below:

DB_ALIAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mydbservice)
    )
  )

SQL*Net

It is also a protocol which facilitate communication between DB and Client. It is configured via sqlnet.ora and also sets priority for connection testing via configuration.

Example of configuration can be:

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

Connection Methods/Clients

Oracle databases can be connected via multiple methods or ways. Some commonly used are below:

• Easy Connect: It is simplified syntax which does not require any configurations.
• TNS Alias: Defined in tnsnames.ora
• LDAP Directory: Uses LDAP directory for centralized management of connections

Example are listed below:

--Easy Connect
     sqlplus user/password@//hostname:port/service_name

--TNS Alias
    sqlplus user/password@DB_Alias

--LDAP Entry
--In this case entry will be defined in ldap server whose details would be present in ldap.ora
   sqlplus user/password@ldap_DB_alias

Service Management

These represents database instances and plays critical role in client connections. Key operations include:

Creating Service

exec DBMS_SERVICE.CREATE_SERVICE(
 service_name => 'my_service',
 network_name => 'my_service_network');

Starting Service

 exec DBMS_SERVICE.START_SERVICE('my_service');

Stopping Service

 exec DBMS_SERVICE.STOP_SERVICE('my_service');

Deleting Service

exec DBMS_SERVICE.DELETE_SERVICE('my_service');

Viewing Service

SELECT name FROM v$active_services;

Utilities for Service and Listener Management

There are many utilities which can be utilized for services and listener management. Below are the common commands used.

Listener Control Utility (lsnrctl):

--Start the listener: 
	lsnrctl start
--Stop the listener: 
	lsnrctl stop
--Check the status of the listener: 
	lsnrctl status
--Reload the listener configuration: 
	lsnrctl reload

Service Control Commands in SQL*Plus:

--Enable or disable services dynamically using the ALTER SYSTEM command: 
		ALTER SYSTEM REGISTER;

Network Configuration Assistant (netca):

It is a graphical tool to configure listeners, naming methods, and directory usage.

--To launch: 
    netca

Database Configuration Assistant (dbca):

It can be used for creating and managing Database services during the phase of DB creation.

--To launch
    dbca