How Oracle Network Services Manages Database Connections in Oracle 23ai

Oracle Network Services manages database connections through a three-step process that intercepts every database request, resolves all connection identifiers through a layered naming architecture, validates security credentials, and establishes a managed server-side session. This module has covered each component of that process in detail across fifteen lessons — from the distributed database concepts and four-quadrant processing models of Lessons 2 through 6, through the service name and database link architecture of Lessons 7 through 9, through the TNS connection sequence and Oracle Net Services architecture of Lessons 10 through 14. This final lesson brings those components together into a complete picture of how Oracle Network Services orchestrates database connectivity in Oracle 23ai.

Step 1 — The Application Makes a Data Request

Every Oracle distributed database connection begins with an application issuing a SQL statement that references a remote object through a database link. The application does not need to know the physical location of the remote database, the network protocol in use, or the credentials required for authentication — Oracle Network Services resolves all of those details transparently.

-- Application issues a distributed query referencing a database link
SELECT *
FROM   sales_data@SALES_DB_LINK;

The @SALES_DB_LINK reference triggers Oracle Network Services to begin the connection management process. At this point the application has completed its responsibility — everything that follows occurs within Oracle Network Services, transparent to the calling application.

In Oracle 23ai, the application layer includes any client that communicates through Oracle Net — SQL*Plus, SQL Developer, JDBC applications, OCI-based tools, Python cx_Oracle, .NET ODP, ODBC drivers, and AI pipelines using VECTOR_DISTANCE queries across distributed Oracle databases. All of these application types enter the same Oracle Network Services connection management pipeline at this step.

Step 2 — Oracle Network Services Intercepts, Resolves, and Validates

The Oracle Network Services TNS Listener and Manager intercepts the database link reference and performs six sub-operations to resolve the connection and establish the session:

Sub-operation 1 — Resolve the Link Name through the Oracle Dictionary

Oracle looks up the database link name SALES_DB_LINK in its data dictionary — the Integrated Metadata Repository in Oracle 23ai. The dictionary returns the TNS service name specified in the link's USING clause and the authentication credentials stored in the link definition. For CONNECT TO CURRENT_USER links, the dictionary returns the current session's identity rather than stored credentials. For wallet-based links, the dictionary references the wallet location rather than an embedded password.

Sub-operation 2 — Look Up the Service Descriptor in TNSNAMES.ORA

The TNS service name returned by the dictionary is resolved through the configured naming method. In the Nano Banana 2 diagram, the TNSNAMES.ORA lookup returns the full connection descriptor — protocol, port, SID, and service name. In Oracle 23ai environments, this lookup may be performed through any of four naming methods in priority order as configured in sqlnet.ora:

• Local naming — tnsnames.ora file on the client machine
• Easy Connect Plus — inline parsing of the connect string with no file lookup
• Directory naming — LDAP or Oracle Internet Directory query
• Centralized Configuration Provider — JSON descriptor retrieved from OCI Object Storage, eliminating local tnsnames.ora maintenance entirely

ORA-12154 (TNS: could not resolve the connect identifier) fires at this sub-operation if the service name cannot be found through any configured naming method. This is the most common Oracle Net error and always indicates a failure at the naming resolution step — not a network connectivity problem.

Sub-operation 3 — Resolve the Host Address through DNS or Hosts File

The hostname extracted from the connection descriptor is passed to the OS name resolution service. On Linux and UNIX systems, /etc/hosts provides static hostname-to-IP mapping. In enterprise and OCI environments, DNS provides dynamic resolution. In OCI deployments, OCI DNS and Private DNS zones handle resolution for database endpoints within the Virtual Cloud Network. ORA-12543 (TNS: destination host unreachable) fires at this sub-operation if the hostname resolves but the IP address is unreachable on the network.

Sub-operation 4 — Form the Session Process

Oracle Network Services determines the appropriate server-side process type based on the connection descriptor and server configuration. Three process types are available in Oracle 23ai:

• Dedicated server process: A dedicated OS or container process is spawned for the exclusive use of this client connection. The dedicated process handles all OPI operations for its client for the duration of the session. This is the default mode and is appropriate for long-running sessions with sustained database activity.
• Shared server (dispatcher): The connection is routed to a dispatcher process that queues requests to a pool of shared server processes. Multiple client sessions share the server process pool, reducing per-connection memory overhead. Appropriate for environments with many concurrent short-lived connections.
• Database Resident Connection Pooling (DRCP): A pooled server process is assigned from a server-side connection pool managed by the Connection Broker. The pooled process is leased to the client for the duration of a work unit and returned to the pool on release. Particularly effective for mid-tier application servers and web applications establishing many short-duration connections.

In Oracle 23ai multitenant environments, the listener also routes the connection to the correct Pluggable Database (PDB) within the Container Database (CDB) at this step — container-aware routing implemented through Dynamic Service Registration (LREG) as covered in Lesson 14.

Sub-operation 5 — Security Validation

Oracle Network Services validates the client credentials before the database session is fully established. In Oracle 23ai, three credential validation paths are available:

• User ID and Password: The traditional authentication method. Passwords are transmitted over an encrypted TCPS connection using TLS 1.3 in Oracle 23ai. Passwords must use the 12C password version — the 10G version is desupported. Case-sensitive by default since Oracle 11g.
• OCI IAM Token (OAuth 2.0): A time-limited token issued by OCI Identity and Access Management replaces the static password. No password appears in the connection string or the database link definition. The token is validated against OCI IAM without requiring a database-stored password — the preferred authentication method for OCI and hybrid deployments.
• Oracle Wallet certificate: A client wallet containing certificates provides mutual TLS authentication without a password. The wallet is configured through WALLET_LOCATION in sqlnet.ora or referenced inline in an Easy Connect Plus string.

ORA-01017 (invalid username/password; logon denied) fires at this sub-operation when the network connection is established successfully but the credentials are rejected by the database.

Sub-operation 6 — Manage Connection Lifecycle and Credentials

Oracle Network Services manages the complete lifecycle of the established connection — maintaining the session state, monitoring connection health, detecting dead connections through Dead Connection Detection (DCD), and handling failover events. In Oracle 23ai, three lifecycle management features provide high availability without application code changes:

• Fast Connection Failover (FCF): Oracle Net receives Fast Application Notification (FAN) events from the database when a RAC instance or service fails. FCF immediately terminates connections to the failed instance and redirects new connection requests to surviving instances — without waiting for connection timeout to detect the failure.
• Transparent Application Failover (TAF): For in-flight queries at the time of a failure, TAF automatically re-executes the query on a surviving instance and returns the complete result set to the application. The application receives the full result set without awareness that a failover occurred mid-query.
• Transaction Guard and Application Continuity: Oracle 23ai's most advanced failover capability — Transaction Guard tracks the commit status of in-flight transactions at failure time, and Application Continuity replays uncommitted work on a surviving instance. Applications experience no data loss and no visible interruption even when a database instance fails during an active transaction.

Step 3 — Managed Connection Established

Once Oracle Network Services completes all six sub-operations, the managed connection is established. The server-side session or process manages all subsequent resource access on Oracle Database 23ai — executing SQL and PL/SQL, managing transaction state, enforcing row-level and column-level security, and returning result sets through the Oracle Net Foundation Layer to the calling application.

The Oracle Net Listener exits the communication path for dedicated server connections at this point — subsequent client-to-server communication occurs directly between the client Oracle Net layer and the server Oracle Net layer through the established TCP or TCPS connection. The listener returns to monitoring for new incoming connection requests on port 1521 (TCP) or port 2484 (TCPS).

Oracle Network Services Connection Management — Complete Feature Reference

The six connection management capabilities of Oracle Network Services that this module has covered are summarized in the following reference:

The Second Course in This Series

This module has presented the foundational concepts of Oracle distributed networking — distributed database theory, service names, database links, distributed joins, TNS architecture, Oracle Net Services features, SQL*Net evolution, and connection management. The second course in this series examines each of these components in greater operational detail — tnsnames.ora creation and maintenance, listener configuration, Oracle Net Manager, client-side configuration, connectivity testing with TNSPING, and advanced Oracle Net troubleshooting. The next lesson concludes this module.