Connecting to Oracle: SQL*Plus, SQLcl, EZConnect, tnsnames.ora and the listener

Remote connections to Oracle always go through the listener, a separate process (default name LISTENER, default port 1521) that accepts the connection and hands it to the database. Clients name the database by its service name; in a CDB every PDB registers its own service.

Local administrative login

On the database server, as the Oracle software owner, operating-system authentication needs no password and no listener:

export ORACLE_SID=ORCLCDB
sqlplus / as sysdba

Easy Connect (EZConnect)

Easy Connect puts the address straight into the connect string, so no configuration file is needed. The basic form is host[:port][/service_name]; the port defaults to 1521.

sqlplus [email protected]:1521/orclpdb1.example.com
sqlplus appuser@//db1.example.com/orclpdb1.example.com
sql [email protected]:1521/orclpdb1.example.com     # SQLcl

Leave the password out of the command line so the tool prompts for it; command lines show up in process lists and shell history. Recent releases extend this syntax as “Easy Connect Plus” with protocol prefixes, several hosts and ?parameter=value options.

tnsnames.ora (local naming)

A tnsnames.ora file maps short aliases to full connect descriptors. Its usual place is $ORACLE_HOME/network/admin; the TNS_ADMIN environment variable points clients to another directory.

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orclpdb1.example.com)))

sqlplus appuser@ORCLPDB1

Which naming methods a client tries, and in which order, is set by NAMES.DIRECTORY_PATH in sqlnet.ora, for example NAMES.DIRECTORY_PATH=(tnsnames, ezconnect).

SQL*Plus and SQLcl

• SQL*Plus (sqlplus) ships with the database and with Oracle Instant Client. It is the classic tool for scripts and administration.
• SQLcl (sql) is Oracle’s newer Java-based command line. It accepts the same connect syntax (sql user@connect_identifier, sql / as sysdba, sql /nolog) and is designed to run SQL*Plus-style scripts and commands.

sqlplus /nolog
SQL> CONNECT [email protected]:1521/orclpdb1.example.com
SQL> SHOW USER
SQL> SHOW CON_NAME
SQL> EXIT

The listener: lsnrctl

Run these on the database server as the Oracle software owner. Without a name, commands act on the listener called LISTENER.

lsnrctl status            # is it running, which addresses, which services
lsnrctl services          # more detail per service
lsnrctl start
lsnrctl stop
lsnrctl reload            # re-read listener.ora

lsnrctl start only works on the machine where the listener runs. A healthy status output ends with a services summary like this:

Services Summary...
Service "orclpdb1.example.com" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

• READY: the instance registered itself dynamically and accepts connections.
• BLOCKED: registered but not accepting connections (for example during startup).
• UNKNOWN: the service is listed statically in listener.ora; the listener cannot tell whether the instance is up.

Warning: lsnrctl stop refuses all new remote connections until the listener is started again; existing sessions keep running. Check lsnrctl status and plan a short window before stopping it on a production server.

Quick troubleshooting map

• ORA-12541: nothing is listening at that host and port. Check lsnrctl status, the port and firewalls.
• ORA-01017: the listener and service were found, but the credentials were rejected.
• ORA-28000: the account is locked.

Official documentation

• Oracle Database 19c Net Services: Configuring naming methods
• Oracle Database 19c Net Services Reference: Listener Control utility
• Oracle SQLcl: Starting and leaving SQLcl