Oracle Net Service

Oracle Net Service refers to the configuration that allows clients to connect to the Oracle Database over a network. The Net Service is essentially a database connection descriptor that provides all the information required to connect to a database, such as hostname, port number, Protocol and the service name or SID (System Identifier).

Way of Network Configuration:

1. Enterprise Manager
2. Oracle Net Manager
3. Oracle Net Configuration assistant
4. Command Line

Network Configuration Files:

1. Listener.ora
2. Tnsnames.ora
3. Sqlnet.ora

Server-Side Configuration: (listener.ora)

• The Oracle listener on the database server is responsible for accepting connection requests from clients. The listener configuration is defined in the listener.ora file.
• Listener.ora file contains server-side network configuration parameters. Also available in $ORACLE_HOME/network/admin

cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/ listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pearl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.204)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Role of Listener:

1. The Oracle listener running on the database server waits for incoming client connection requests.
2. When a client initiates a connection request (based on the details in tnsnames.ora or using EZCONNECT), the listener accepts the request and forwards it to the appropriate database instance.
3. Once the connection is accepted, the client and server communicate directly using the Oracle Net protocol (usually over TCP/IP).

The LSNRCTL tool provides several commands to control and monitor the listener's operation:

lsnrctl start
lsnrctl stop
lsnrctl status
lsnrctl reload
lsnrctl services
lsnrctl version
lsnrctl show log_directory
lsnrctl quit
lsnrctl help
lsnrctl set password
lsnrctl change_password

How to Configure the New listener use NETCA:

Oracle Net Configuration Assistant (NETCA) is a graphical tool used to configure network settings, including the Oracle Listener.

Step 1: Start NETCA

su – root
xhost +
su – oracle
. .bash_profile
netca

Step 2: GUI Mode Tools click the follow steps.

1. Select the option "Listener Configuration" and click Next.
2. Choose "Add" to configure a new listener and Click Next.
3. Listener Name: By default, the listener is named LISTENER. You can either keep this name or provide a custom listener name and Click Next.
4. Select the network protocol for the listener. TCP is the most common protocol used for Oracle database connections. Select TCP and click Next.
5. NETCA will ask for the Port Number. By default, the listener uses port 1521. You can keep the default or specify another port if required and Click Next.
6. NETCA will ask if you want to configure another listener. If you need only one listener, select No and Click Next.
7. Review the listener configuration summary and Click Finish to complete the listener configuration.

Client-Side Configuration: (tnsnames.ora)

1. On the client side, the tnsnames.ora file is used to define the network service name that maps to the database server's details. It contains client-side network configuration parameters. 
2. This file is typically located in $ORACLE_HOME/network/admin on the client machine.

cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora

PEARL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.204)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pearl)
    )
  )

How to configure the tnsnames:

Step 1: Start NETCA

su – root
xhost +
su – oracle
. .bash_profile
netmgr

Step 2: GUI Mode Tools click the follow steps.

Once Oracle Net Manager opens, you'll see several options on the left side for configuration.
Expand the option for Local → Service Naming.
Right-click on Service Naming and select Create.

Step 3: Define a New Service Name (TNS Entry)

Service Name: Enter a name for the connection. This will be the alias you use to connect to the database. Example: MYPEARL
Click Next.

Step 4: Define Address for the Service

Protocol: Select TCP/IP
Host: Enter the hostname or IP address of the database server.
Port: Enter the port number where the Oracle listener is running. By default, this is 1521.
Click Next.

Step 5: Configure Connect Data

Service Name: Enter the Service Name of the database (SID) you want to connect to and Click Next.

Step 6: Review and Finish

Review the settings you’ve entered.
Click Finish to save the TNS configuration entry.

Step 7: Save the Configuration

After finishing, you need to save the configuration changes.
Go to File → Save Network Configuration.

Step 8: Verify the TNSNAMES.ORA File

Establishing the Connection:
Once both the server and client are properly configured, you can establish a connection using SQL*Plus or any other Oracle client tool.

Without tnsnames.ora: (EZCONNECT connection string)

sqlplus user/password@hostname:port/service_name

With tnsnames.ora:

sqlplus muthu/muthu123@MYPEARL

Verify the connection:

tnsping MYPEARL

SQLNET.ORA File:

A configuration file that defines client or server-side network parameters.
It contains settings related to the Oracle Net layer, such as security, encryption, and preferred protocols.
This file is typically located in $ORACLE_HOME/network/admin directory.