10.2.1 About Oracle Connection Manager in Traffic Director Mode

Oracle Connection Manager in Traffic Director Mode is a proxy that is placed between supported database clients and database instances.

• Connection Modes
  Oracle Connection Manager in Traffic Director Mode supports pooled and non-pooled modes of operation.
• Performance and Security
  Oracle Connection Manager in Traffic Director Mode allows client connections to be routed to databases, providing high availability and connection multiplexing capabilities. It also supports enhanced security features, and provides zero application downtime for planned and unplanned database outages.
• Database Links
  Oracle Connection Manager in Traffic Director Mode supports database links, which extends high availability and failover to databases.
• Per-Service and Per-PDB Connection Pools
  Proxy Resident Connection Pooling (PRCP) provides connection pools on the Oracle Connection Manager in Traffic Director Mode (CMAN-TDM) server. You can configure CMAN-TDM to establish either per-service or per-PDB pooled connections.
• Implicit Connection Pooling
  Client applications that do not use an application connection pool can leverage connection pooling capabilities without making any application-level change or calling the pooling APIs.

10.2.1.1 Connection Modes

Oracle Connection Manager in Traffic Director Mode supports pooled and non-pooled modes of operation.

• Pooled connection mode:

  Supports applications using the following database client releases:

  • OCI and Open Source Drivers (11.2.0.4 and later)

  • JDBC (12.1 and later)

  • ODP.NET (12.2 and later) 

  In addition, applications must be Database Resident Connection Pool (DRCP) aware. This means specifying (SERVER=POOLED) in the application connect string.

• Non-pooled connection (or dedicated) mode:

  Supports applications using database client releases 11.2.0.4 and later. In this mode, some capabilities such as connection multiplexing are not available. 

Note:

For a connection from a client to the database using Oracle Connection Manager in Traffic Director Mode, the client and database versions should be a supported combination.

10.2.1.2 Performance and Security

Oracle Connection Manager in Traffic Director Mode allows client connections to be routed to databases, providing high availability and connection multiplexing capabilities. It also supports enhanced security features, and provides zero application downtime for planned and unplanned database outages.

Transparent Performance and Connection Multiplexing

• Statement caching, rows prefetching and result set caching are auto-enabled for all mode of operations.

• Database connection multiplexing (pooled mode only) using Proxy Resident Connection Pooling (PRCP, a proxy mode of Database Resident Connection Pooling). PRCP uses the Oracle Call Interface (OCI) Session Pooling feature.

  PRCP provides connection services for a large number of client connections that are routed using a connection pool, which comprises a fewer number of server connections to target databases. PRCP reduces connection load (connection memory usage) on the database tier, and provides runtime load balancing between database and Oracle Connection Manager in Traffic Director Mode.

High Availability

• Multiple Oracle Connection Manager in Traffic Director Mode instances: Applications get increased scalability through client-side connect time load balancing or with a load balancer (BIG-IP, NGINX, and others).

• Rolling upgrade of Oracle Connection Manager in Traffic Director Mode instances.

• Closure of existing connections from client to Oracle Connection Manager in Traffic Director Mode for planned outages.

• In-band notifications to Oracle Database release 18c and later clients. For earlier release clients, notifications are sent with the response of the current request.

Security and Isolation

• Database proxy supporting TCP/TCPS and protocol conversion.

• Firewall based on IP address, service name, and TLS wallets.

• Tenant isolation in a multitenant environment.

• Protection against denial-of-service and fuzzing attacks. 

• Secure tunneling of database traffic across on-premises database and Oracle Cloud.

Zero Application Downtime

• Planned database maintenance or pluggable database (PDB) relocation:

  • In a pooled mode, Oracle Connection Manager in Traffic Director Mode responds to Oracle Notification Service (ONS) events for planned outages and redirects work. The connections are drained from the pool on Oracle Connection Manager in Traffic Director Mode when the request completes. It also supports service relocation.

    Oracle Connection Manager in Traffic Director Mode responds to in-band notifications when a PDB is relocated, even when ONS is not configured (for server only).

  • In a non-pooled or dedicated mode, no request boundary information is received from the client. Oracle Connection Manager in Traffic Director Mode supports planned outage for many applications (as long as only simple session state and cursor state need to be preserved across the request or transaction boundaries). This involves the following:

    • Stop Service or PDB at a transaction boundary or leverage Continuous Application Availability to stop service at a request boundary.

    • Oracle Connection Manager in Traffic Director Mode leverages TAF Failover Restore to reconnect and restore simple states.

• Unplanned database outages:

  For both pooled and non-pooled (dedicated) modes, Oracle Connection Manager in Traffic Director Mode supports unplanned outage for read–mostly applications by leveraging TAF Failover Restore to restore simple session state or cursor state and replay SELECT statements and first DML statement.

10.2.1.3 Database Links

Oracle Connection Manager in Traffic Director Mode supports database links, which extends high availability and failover to databases.

Starting with Oracle Database 21c, Oracle Connection Manager in Traffic Director Mode (TDM) supports the following types of database links:

• Fixed User: The user connects to the remote database using the username and password specified during the creation of the database link.
• Connected User: The user connects to the remote database using the credentials of the user accessing the database link. These credentials can either be a username and password, or external, such as Kerberos ticket.

You can use database links with Traffic Director Mode in the following scenarios:

• Scenario 1:

  When a client connects directly to the database

  In this scenario, both fixed user and connected user database links work.

• Scenario 2:

  When a client connects to the database through the Traffic Director Mode

  In this scenario, only fixed user database link works. The connected user database link does not work as the session between TDM and the database is a proxy user session. Proxy user sessions are not allowed over a database link for security reasons.

  Note:

  For the database links to work with TDM, the TDM should not be running in Proxy Resident Connection Pool (PRCP) mode for the particular database service.

10.2.1.4 Per-Service and Per-PDB Connection Pools

Proxy Resident Connection Pooling (PRCP) provides connection pools on the Oracle Connection Manager in Traffic Director Mode (CMAN-TDM) server. You can configure CMAN-TDM to establish either per-service or per-PDB pooled connections.

Per-Service PRCP

In per-service PRCP, CMAN-TDM creates a single, dedicated pool for the requested database service based on the SERVICE_NAME parameter specified in the connect string. When a client application requests a new connection, CMAN-TDM establishes a pooled connection from this service pool. Every time the application requests a connection to this service, CMAN-TDM returns a matching connection from its service pool.

For example, for incoming connection requests with the specified service names as Service1_PDB1 and Service2_PDB1, CMAN-TDM creates two service pools, that is, PRCP_for_Service1_PDB1 to support the Service1_PDB1 connections and PRCP_for_Service2_PDB1 to support the Service2_PDB1 connections.

Per-PDB PRCP

Per-PDB PRCP works in a similar manner as per-service PRCP. In this mode, when a client application requests a new connection, CMAN-TDM again establishes a pooled connection to the requested service based on the SERVICE_NAME parameter value specified in the connect string. However, instead of creating a dedicated pool for this service, it creates a multi-service PDB pool that supports connections across all services registered with that PDB. Any available Oracle Connection Manager Gateway (CMGW) can accept a connection request to the service or PDB, based on load balancing.

For example, for incoming connection requests to Service1_PDB1 and Service2_PDB1 (both running on PDB1), CMAN-TDM creates a single PDB pool, that is, PRCP_for_PDB1 to support both the Service1_PDB1 and Service2_PDB1 connections.

Per-PDB PRCP has the following advantages over per-service PRCP:

• Reduces the number of connection pools on CMGW by consolidating multiple service pools into a single PDB pool. This helps in optimizing the database performance.

• Dynamically determines and refreshes the maximum size of a PDB pool based on the TDM_PERPDB_PRCP_CONNFACTOR value and the OCPU count allocated to each PDB.

  The per-PDB PRCP configuration is especially useful in multitenant environments like Autonomous Databases, where PDB administrators can configure and monitor PRCP on the database side instead of having to access CMAN-TDM hosts for PRCP configuration.

Note:

Oracle does not recommend switching between the per-service and per-PDB modes. If you do so, then the changes are applied only to the gateways that start after the reconfiguration.

Pool Size Configuration Settings

• For per-service PRCP, CMAN administrators specify the <session_pool> MAX_SIZE value in the oraaccess.xml on the CMAN-TDM host during the initial PRCP configuration.

• In the per-PDB PRCP mode, pool sizing is more autonomous. CMAN administrators specify a connection factor in the cman.ora file using the TDM_PERPDB_PRCP_CONNFACTOR parameter.

  The per-PDB PRCP setting determines the maximum size of a per-PDB PRCP pool based on the TDM_PERPDB_PRCP_CONNFACTOR parameter value and the Oracle Compute Unit (OCPU) count allocated to each PDB automatically. A background process automatically fetches these values and resizes the pool. This derived maximum size value overrides the <session_pool> MAX_SIZE parameter configured in the oraaccess.xml file.

  You must set the sqlnet.ora parameter TCP.ALLOWED_PROXIES on the Oracle Database server to list the allowed CMAN instances that can fetch the OCPU count.

V$TDM_STATS View

In the per-PDB PRCP mode, PDB administrators can query the V$TDM_STATS view on the database to display usage statistics for CMAN-TDM. In this view, you can analyze an aggregated data for all inbound connections, such as the number of client requests that have retrieved a session in the pool, the number of active client connections, the number of busy and free server connections, the maximum number of connections reached, and so on. This view helps in the monitoring and tuning of CMAN-TDM.

A separate background thread fetches the statistical data from each CMGW and uploads to PDB at regular intervals. You can configure this time interval using the cman.ora parameter TDM_STATS_FREQUENCY.

10.2.1.5 Implicit Connection Pooling

Client applications that do not use an application connection pool can leverage connection pooling capabilities without making any application-level change or calling the pooling APIs.

Overview

Starting with Oracle Database 23ai, Proxy Resident Connection Pooling (PRCP) supports implicit connection pooling. This feature enables the automatic assignment of PRCP servers to and from an application connection at runtime when the application starts and finishes database operations, even if the application does not explicitly release the connection.

With implicit connection pooling, a database session from PRCP automatically gets mapped to and unmapped from an application connection without the explicit session pool API calls from the client. PRCP implicitly detects an end of request boundary (statelessness of the session) and releases the session back to the connection pool. For example, for a transaction, the application can implicitly use or reuse an available session from a pool of stateless sessions and then release the session back to the pool after the transaction is complete.

Implicit connection pooling helps in reducing the size of PRCP pools required. It provides better scalability and an efficient use of database resources for applications that do not use connection pools, such as Oracle Call Interface (OCI) Session Pool or Java Database Connectivity (JDBC) Oracle Universal Connection Pool (UCP).

Statement and Transaction Boundaries

Implicit connection pooling uses time boundaries to release a session back to the connection pool. A time boundary is a point in time in the life-cycle of the application when an application session is released back to the pool.

You can enable implicit connection pooling by setting the POOL_BOUNDARY parameter in the connect string, Easy Connect syntax, or tnsnames.ora file. Using this parameter, you specify one of these time boundaries:

• STATEMENT: To release a session back to the PRCP pool when the session is implicitly stateless.

• TRANSACTION: To release a session back to the PRCP pool when a transaction ends implicitly or explicitly, or when a transaction is not available and the session is stateless.

Note the following:

• A session is implicitly stateless when all open cursors in a session have been fetched through to completion and there are no active transactions, temporary tables, or temporary LOBs. The release to the pool closes any active cursors, temporary tables, and temporary LOBs.

• After executing a statement or creating a temporary LOB, if you perform a commit or rollback within the TRANSACTION boundary, then:

  • Subsequent fetch operations may encounter an invalid cursor-related error, such as ORA-01001.

  • Subsequent use of a temporary LOB may encounter an ORA-22922.

  • Working with a persistent LOB can continue even after an implicit release. However, if an implicit operation lands on a different instance, then an ORA-43887 error may occur. In such cases, you must attempt the operation again.