Oracle Net Configuration Guide

Oracle Net is the network service that connects databases and clients, and it is a critical mechanism for enabling remote connections. This article explains the configuration procedures for both the DB server and the client, complete with specific examples, diagrams, and troubleshooting.

What is Oracle Net?

Oracle Net is the protocol that enables communication between the database and clients. It consists of the following three components:

Listener: A process on the server side that waits for connection requests.
Connection Identifier (Service Name or SID): A name used to identify the target database.
Client Configuration: Defines the connection information in the tnsnames.ora file on the client.

Configuration Overview (Diagram)

The following diagram shows the configuration relationship between the server and the client.

+-------------------+         +--------------------+
|   DB Server       |         |   Client Machine   |
|-------------------|         |--------------------|
| listener.ora      | <=====> | tnsnames.ora       |
| Database Instance |         | Application (SQL*Plus, etc.) |
+-------------------+         +--------------------+

The arrow indicates network communication (default TCP:1521). A connection is established if the settings on both ends match.

DB Server-Side Configuration

The tasks to be performed on the DB server side are as follows.

Step 1: Check and Edit listener.ora

listener.ora is the configuration file that controls the listener’s behavior. The following is a basic configuration example.

Sample: listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    )
  )

HOST: Server’s IP address or hostname
PORT: Port number to use (default: 1521)

Tip: localhost is only for local connections. Always specify the IP address for remote connections.

Step 2: Start the Listener

Run the following commands in order to start the listener.

lsnrctl start <listener_name>

*The listener name can be omitted for the default listener (named LISTENER).

Check Status

lsnrctl status <listener_name>

*The listener name can be omitted for the default listener (named LISTENER).

Step 3: Verify Dynamic Service Registration

To enable dynamic service registration, set the database initialization parameter.

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521))';

Verification:

SHOW PARAMETER SERVICE_NAMES

Client-Side Configuration

The client uses tnsnames.ora to define connection information.

Step 1: Edit tnsnames.ora

Sample: tnsnames.ora

DB_CONN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

DB_CONN: A user-defined connect identifier
HOST: Server’s IP address
SERVICE_NAME: Server’s service name (e.g., orcl)

Tip: Place the tnsnames.ora file in $ORACLE_HOME/network/admin/ on the client environment.

Step 2: Check Environment Variables

Check the environment variables on the client terminal.

echo $ORACLE_HOME
echo $TNS_ADMIN

If necessary, edit the .bash_profile to configure the settings.

How to Verify Connection

Verify the connection using the following commands.

TNSPING Command

tnsping DB_CONN

Expected output:

OK (10 msec)

Connection with SQL*Plus

sqlplus username/password@DB_CONN

Common Errors and Solutions

TNS-12541: TNS:no listener

Cause: The listener is not running.
Solution: Run the following on the server: lsnrctl start

ORA-12514: TNS:listener does not currently know of service requested

Cause: Service registration is incomplete on the server side.
Solution: Check the following on the server: lsnrctl services

Additional Information for Advanced Users

1. Configuring Multiple Listeners

To run multiple listeners on a single server, add extra configuration to listener.ora.

2. Configuring Load Balancing

To distribute connections across multiple databases, add the following configuration to tnsnames.ora.

LOAD_BALANCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Step 1: Check and Edit listener.ora
Step 2: Start the Listener
Step 3: Verify Dynamic Service Registration
Step 1: Edit tnsnames.ora
Step 2: Check Environment Variables
TNSPING Command
Connection with SQL*Plus
TNS-12541: TNS:no listener
ORA-12514: TNS:listener does not currently know of service requested
1. Configuring Multiple Listeners
2. Configuring Load Balancing

Summary

Summary

By following the procedures in this article, even beginners can understand Oracle Net configuration and set up remote connections.

DB Server Side: Configure and start the listener.
Client Side: Define the connection information.
Operation Check: Test the connection with tnsping and sqlplus.
Error Handling: Identify causes using logs and commands.

Please try following these steps. A successful configuration will be the first step toward expanding the possibilities of your Oracle Database!

[reference]
Oracle Database Database Net Services Reference, 19c