Inter-Database Communication in Oracle

In Oracle 23c Network Services, the Net Listener (also known as the Oracle Listener) listens by default on TCP port 1521.
Default Listener Port:

• Port: 1521
• Protocol: TCP
• Configuration File: listener.ora (located in $ORACLE_HOME/network/admin)

Example `listener.ora` snippet:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.example.com)(PORT = 1521))
    )
  )

Notes:

• The port can be customized in listener.ora if 1521 is unavailable or a different port is preferred for security or compliance reasons.
• Oracle supports multiple listeners, each potentially on a different port.

To check the current listener port from the server side in Oracle 23c, you can use one of the following methods:
✅ Method 1: Use "lsnrctl status"
This is the most common way.

lsnrctl status

Sample Output:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

➡️ Look for:`Listening Endpoints Summary`, the `(PORT=xxxx)` line tells you the current listener port.
✅ Method 2: View `listener.ora` directly

cat $ORACLE_HOME/network/admin/listener.ora

Look for a block like:

(ADDRESS = (PROTOCOL = TCP)(HOST = your-hostname)(PORT = 1521))

✅ Method 3: Use `netstat` or `ss` (Linux only)
To verify the listener process is active on a port:

sudo netstat -tulnp | grep tnslsnr
# or
sudo ss -tulnp | grep tnslsnr

This will show the actual port used by the `tnslsnr` process.

An Oracle DBA can automate the check for the Oracle Net Listener port in Oracle 23c using a Bash shell script that leverages "lsnrctl status" and parses the output. This is useful for monitoring, auditing, or generating daily reports.
✅ Step-by-Step Script: Automated Listener Port Checker

#!/bin/bash

# Set ORACLE_HOME if not already set
export ORACLE_HOME=/u01/app/oracle/product/23c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# Output file for logging/reporting
OUTPUT_FILE="/tmp/oracle_listener_port_report.txt"

# Run lsnrctl and capture the listener port
LISTENER_STATUS=$(lsnrctl status)

# Extract port number using grep and sed
LISTENER_PORT=$(echo "$LISTENER_STATUS" | grep -Eo "\(PORT=[0-9]+\)" | sed 's/[^0-9]*//g' | head -1)

# Write to output file
{
    echo "Date: $(date)"
    echo "Oracle Listener Port Detected: $LISTENER_PORT"
    echo "----------------------------------------"
    echo "$LISTENER_STATUS"
} > "$OUTPUT_FILE"

# Optional: Email or alert integration
# mail -s "Oracle Listener Port Report" dba@example.com < "$OUTPUT_FILE"

echo "Listener port check completed. See: $OUTPUT_FILE"

Script Highlights

• Detects the first TCP listener port (assumes default listener).
• Stores full lsnrctl status output for audit trail.
• Can be cron-scheduled (e.g., daily at 8 AM):
  

  0 8 * * * /path/to/listener_port_check.sh
  

• Easy to extend with email alerts, log rotation, or multi-listener checks.

🔐 Permissions Note:
Run the script as the Oracle OS user (`oracle`) or with proper environment sourcing (`. oraenv`) to ensure `lsnrctl` works correctly.

At this point, you are ready for an overview of the Net architecture, beginning with a review of the listener. The Net listener is a process whose job is to receive connection requests on behalf of an application. Listeners are configured to "listen on" an address specified in the listener.ora file for a database or non-database service. Once started, the listener will receive client-connect requests on behalf of a service, and respond by bequeathing the session to a new dedicated server process. The listener process is started on each remote Oracle server with the lsnrctl start UNIX command. On a UNIX server, you can look at the processes by using the UNIX ps command. View the code below to examine the result.
The following script discusses 1) tnsnames.ora and 2) listener.ora files that eliminate the legacy `BEQ` protocol and are suitable for Oracle 23c, using TCP/IP and IPC connections instead of "PROTOCOL=beq".
1. "tnsnames.ora" (Refactored for Oracle 23c)

# Location: $ORACLE_HOME/network/admin/tnsnames.ora

ORCL23C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl23c)
    )
  )

ORCL23C_LOCAL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  -- Uses IPC, not BEQ
    (CONNECT_DATA =
      (SERVICE_NAME = orcl23c)
    )
  )

2. "listener.ora" (Refactored for Oracle 23c)

Location: $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl23c)
      (ORACLE_HOME = /u01/app/oracle/product/23c/dbhome_1)
      (PROGRAM = extproc)  -- Optional if using external procedures
    )
  )

• Includes both TCP and IPC for broader support
• IPC is used for local clients in place of BEQ
• SID_LIST is needed for static registrations (like extproc)

3. Sample `sqlplus` Connection Commands
For local IPC (no BEQ):

sqlplus user/password@ORCL23C_LOCAL

For standard TCP:

sqlplus user/password@ORCL23C

Or using EZConnect:

sqlplus user/password@localhost:1521/orcl23c

🔒Notes for Security and Modernization

• BEQ is not needed when IPC or TCP is correctly configured.
• Oracle 23c is cloud-first, so listener-based connections are preferred.
• ORCL23C_LOCAL via IPC allows fast, secure local connections without sqlplus / as sysdba relying on bequeath.

• This diagram replaces legacy BEQ connections with modern IPC-based local access and/or TCP/IP (if used in other versions of the diagram).
• It promotes better monitoring, scalability, and cloud-readiness for Oracle 23c environments.

You will only see these server processes when you are not using the Multi-Threaded Server (MTS). With MTS, Oracle has provided a dedicated dispatcher process to manage the database connections. We will cover MTS in the second course in this series. The code reveals that 10 connections have been bequeathed by the listener, and that there are two databases on this host: CUSTOMER and ORDER. You can also see which connections are originating on the server (LOCAL=YES), and which are coming in from a remote client (LOCAL=NO). We will return to this listing in a later module. Meanwhile, if you have an Oracle UNIX server, try running this command.
Another way to see evidence of these Net connections inside the Oracle system is to look at the v$session and v$process views.
View the Code below to see an example.

Following is the SQL query that generated the syntax you just looked at:

select
      substr(a.spid,1,5) pid,
      substr(b.sid,1,5) sid,
      substr(b.serial#,1,5) ser#,
      substr(b.machine,1,6) box,
      substr(b.username,1,10) username,
      substr(b.osuser,1,8) os_user,
      substr(b.program,1,30) program
from v$session b, v$process a
 where
b.paddr = a.addr
and type='USER'
order by spid;

If you have an Oracle server at your site, copy this script and execute it on your server.
Now that we haved seen the processes, in the next lesson we will take a look at how database links are used.