Oracle DBA Revision 5 — Lessons 33 to 42

Contents

Client namingEffective client configurationServices and sessionsService checksListener registrationConnection checksDedicated and shared serversShared-server measurements

Client naming

A connect identifier supplies, or resolves to, a listener address and connect data. The address identifies the host, port and protocol. The service name selects the database workload the client requests.

Easy Connect

Write the destination directly: //db.example:1521/reporting.

Local naming

Use an alias such as REPORTS. The client reads its descriptor from tnsnames.ora.

Directory naming

Resolve a name through a configured directory. The directory lookup supplies connection information; the database connection follows.

Example

REPORTS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
               (HOST = db.example)(PORT = 1522))
    (CONNECT_DATA = (SERVICE_NAME = reporting)))

If the listener uses port 1521, this alias still points to 1522. Changing the alias label does not correct the address. Compare the effective descriptor with the intended listener endpoint.

TNS_ADMIN can select the network-configuration directory for the client process. Otherwise the location depends on that client installation, including Oracle home, read-only home and Instant Client layouts. Check the executable and environment actually used by the application. Editing a different installation’s file leaves the active client unchanged.

File	Role
sqlnet.ora	Network profile, including NAMES.DIRECTORY_PATH
tnsnames.ora	Local aliases and connect descriptors
ldap.ora	Directory naming access information
listener.ora	Listener endpoints and optional static entries

Effective client configuration

Use the same executable and environment as the application connection. For a Linux SQL*Plus client:

command -v sqlplus
sqlplus -V
printenv TNS_ADMIN
printenv ORACLE_HOME
tnsping REPORTS

An unset TNS_ADMIN can be normal. Follow the search rules for that installation. TNSPING reports the naming adapter and resolved descriptor; its parameter-file list alone does not prove which file supplied the alias.

Example

A terminal resolves REPORTS to port 1522, while an application connects to port 1521. Compare the application driver, environment and descriptor with the terminal. A JDBC connection can use different configuration from an Oracle client installation.

Note. With naming order (TNSNAMES, LDAP, EZCONNECT), an alias found locally can prevent a directory lookup. If that local descriptor points to the wrong destination, a failed connection does not imply that directory naming will be retried to correct it.

Services and sessions

A service is a logical database workload identity. A service associated with a PDB routes a connection to that PDB. A local alias can have a different name from the service it requests. An instance identifier and a service name identify different things: the running instance and the requested workload.

Connect identifier
Resolve address + service→Listener
Select a handler→Database session
Authenticate in the target

With a running TCP listener, an available service and a valid application account, connect through SQL*Plus:

sqlplus /nolog
CONNECT app_reader@//db.example:1521/reporting
-- SQL*Plus prompts for the password.

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name,
       SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;

Compare the returned account, service and container with the application’s intended target. A familiar username can exist in a different PDB. A successful connection alone does not establish that the application reached the right data.

Note. Create and manage services through the deployment’s owning tool. Oracle Restart and Clusterware use SRVCTL. For an unmanaged single instance, DBMS_SERVICE supports service management; a PDB service is created in the appropriate PDB context. Creating a service and starting it are separate actions. Customer use of the SERVICE_NAMES parameter is deprecated in Oracle 19c.

Service checks

Service definition, activation, listener advertisement and an authenticated session are separate observations. An administrator with access to these views can inspect the current container:

SHOW CON_NAME
SELECT name, network_name, pdb FROM v$services;
SELECT name, network_name, con_id FROM v$active_services;
SHOW PARAMETER local_listener

V$SERVICES describes known services. V$ACTIVE_SERVICES identifies active services visible in this scope. Compare them with LSNRCTL SERVICES and a new application login. Dictionary visibility depends on privileges and container scope.

Example

The application requires BILLING, but its session reports service inventory_read and container INVENTORY. Check the descriptor and service association before running application work. Renaming a local alias alone does not change its destination.

For a service managed by Oracle Restart or Clusterware, use the deployment's SRVCTL management. In an unmanaged single instance, an administrator with the required DBMS_SERVICE privileges creates a PDB service in that PDB and starts it separately. Stopping a service and draining existing transactions are separate lifecycle concerns; verify the configured stop behavior.

Watch Oracle DBA Revision 5 — Lessons 33 to 42 on YouTube

Listener registration

The instance’s listener registration process, LREG, advertises services and connection handlers. A listener can accept network contact while a required service or handler is absent. A service needs the appropriate database/PDB state and a usable handler to accept the intended connection.

Dynamic

Instance → listenerLREG advertises services and handler state. Inspect the registered name and available handlers.

Static

listener.ora → listenerA configured entry can show UNKNOWN. That label alone does not prove that the database is stopped.

lsnrctl status LISTENER
lsnrctl services LISTENER

Run these where the intended listener can be inspected, substituting its configured name. STATUS includes endpoints and a service summary. SERVICES provides instance and handler details. Check the full service name, status and dedicated/dispatcher handlers. A READY entry indicates readiness to accept connections; verify the actual application login separately.

Dynamic registration normally targets a local TCP listener on port 1521. A nondefault endpoint requires a correctly resolved LOCAL_LISTENER address. That parameter tells the instance where to register; it does not create or start a listener. An authorized administrator can request immediate registration with ALTER SYSTEM REGISTER; after correcting the configuration. Static entries remain useful for workflows that need an entry before normal instance registration.

Connection checks

Error	Stage to inspect	Useful next evidence
ORA-12154	Connect identifier resolution	Effective naming method, alias spelling and client files
ORA-12541	Requested listener endpoint	Resolved host/port and listener availability there
ORA-12514	Service selection at the reached listener	Exact requested name, service/PDB availability and registration
ORA-01017	Database authentication	Account, credentials and intended database context

tnsping REPORTS
sqlplus /nolog
CONNECT app_reader@REPORTS

TNSPING checks whether the listener for the resolved address can be reached. It does not authenticate the database account or prove that the application service is usable. Follow it with the actual connection and session-context query. An operating-system ping tests a different network behavior and is not a database login test.

Example

A local administrator connection can use the bequeath path and bypass the listener. Its success can coexist with a failed remote application connection. Test the same naming method, endpoint, service and account type used by that application.

Dedicated and shared servers

In an ordinary dedicated connection, a session uses its associated server process. Subsequent requests use the established client/server connection. Shared server places a dispatcher between the client and a pool of server processes.

Client → dispatcher
Receive request→Common queue
Wait for an available process→Shared server
Perform request

The result returns through the dispatcher. The same session can use different shared server processes for successive calls. In shared mode, the user global area (UGA), which holds session state, resides in the SGA: in the large pool when configured, otherwise the shared pool. A dedicated session’s UGA resides in its server process’s PGA. Every shared server process still has its own private PGA.

-- A session with SELECT access to V_$SESSION:
SELECT sid, serial#, server, service_name, con_id
FROM v$session
WHERE sid = TO_NUMBER(SYS_CONTEXT('USERENV', 'SID'))
  AND con_id = TO_NUMBER(SYS_CONTEXT('USERENV', 'CON_ID'));

The SERVER column identifies the session’s server mode. Check the actual value; configurations can include modes beyond DEDICATED and SHARED. Ordinary application accounts may need an administrator to perform this inspection.

Note. A descriptor with SERVER=SHARED explicitly requests a dispatcher. If a suitable dispatcher is unavailable, the request can fail. Shared server does not automatically make every workload faster; long calls and queue waits affect other requests using the pool.

Shared-server measurements

In an authorized CDB root session with access to the required views, inspect dispatcher activity, common-queue waiting and shared-server work over the same interval:

SELECT name, status, busy, idle FROM v$dispatcher;
SELECT type, queued, wait, totalq FROM v$queue;
SELECT name, status, requests FROM v$shared_server;

QUEUED is the current queue length. WAIT and TOTALQ accumulate; compare two snapshots. For a common request queue, divide the change in WAIT by the change in TOTALQ to estimate average queue wait per arriving request. WAIT is measured in hundredths of a second. If no requests arrived, the ratio is undefined.

Example

One common queue, sampled 30 seconds apart

Counter	First	Second	Change
WAIT	800	920	120 hundredths of a second
TOTALQ	400	460	60 requests

120 ÷ 60 = 2 hundredths of a second = 0.02 seconds per arriving request. This is queue delay; query execution and network time are separate. Compare dispatcher busy/idle deltas and resource pressure over the same period. Long calls can occupy shared servers; adding servers can increase CPU contention.

Server pool

SHARED_SERVERS sets its initial/minimum size. MAX_SHARED_SERVERS constrains automatic growth; keep the limits consistent.

Session limits

SHARED_SERVER_SESSIONS limits shared user sessions. CIRCUITS limits available virtual circuits.

Session state

Budget SGA space for shared UGA. Use the large pool when configured; otherwise shared-pool pressure can increase.

Note. Enabling shared server does not convert existing dedicated sessions. Verify a fresh connection's SERVER value. RMAN backup, restore and recovery use dedicated server connections. A drain must allow existing shared clients to finish; forcing both SHARED_SERVERS and MAX_SHARED_SERVERS to zero can leave their requests queued.

References

• Client naming methods
• Local connect descriptors
• Testing connections
• Listener services and registration
• Oracle Net architecture
• Shared server connections
• Process architecture
• Session memory
• Session context
• Session server mode
• Service lifecycle
• Shared-server configuration and lifecycle
• Queue counters and units
• Active service visibility
• Service definitions

https://www.apps-dba.com/https://www.youtube.com/@EnhanceSkillsDaily