Connection Pooling, DRCP Concepts, FAN/Application Continuity Awareness, and Session Initialization

Learning outcomes

A ServiceHub team adds a connection pool to remove login overhead. Later, Request B inherits Request A's NLS format and package/application context because the same physical database session was reused. Pooling is correct only when request boundaries, transaction cleanup, and session state are explicit.

1. Client pool and DRCP are different layers

An application pool can itself connect through DRCP. Adding both layers should be justified by measured open-client versus active-request behavior.

2. Reproduce pooled session-state leakage

import osimport oracledbpool = oracledb.create_pool(    user="servicehub_app",    password=os.environ["SERVICEHUB_DB_PASSWORD"],    dsn="localhost:1521/FREEPDB1",    min=1, max=1, increment=0)# Logical Request Awith pool.acquire() as conn:    with conn.cursor() as cur:        cur.execute("alter session set nls_date_format='YYYY'")        cur.execute("""          begin            dbms_application_info.set_module(              'SH27_POOL_LAB','REQUEST_A'            );          end;        """)# Logical Request B reuses the same DB session.with pool.acquire() as conn:    with conn.cursor() as cur:        cur.execute("""          select value          from nls_session_parameters          where parameter='NLS_DATE_FORMAT'        """)        print(cur.fetchone())pool.close()

With max=1 the second checkout reuses the first session, so NLS state can remain YYYY. Returning a connection to a pool is not a database logout.

3. Establish known request state on checkout

def initialize_request(conn, action):    with conn.cursor() as cur:        cur.execute(            "alter session set nls_date_format='YYYY-MM-DD'"        )        cur.callproc(            "dbms_application_info.set_module",            ["SERVICEHUB_API", action]        )with pool.acquire() as conn:    initialize_request(conn, "GET_WORK_ORDER")    # Run this request only after expected state is established.

Initialize only the state your application contract uses: NLS, time zone, edition/current schema, application context, module/action, client identifier, or other request-specific state. End/rollback the transaction before the connection returns.

4. RESET_STATE=LEVEL1 is a 26ai server-side safety control

At explicit request end, a service configured with RESET_STATE=LEVEL1 clears defined state such as open cursors, PL/SQL globals, session-duration temporary state/LOBs, session sequences, and DBMS_OUTPUT state. In 26ai it no longer depends on Application Continuity.

SELECT  name, con_id, failover_type,  failover_retries, failover_delay, reset_stateFROM v$active_servicesORDER BY name,con_id;

RESET_STATE=NONE proves only that the service is not doing LEVEL1 reset. The application may still perform correct checkout/cleanup.

5. Configure RESET_STATE only on an application service you control

BEGIN  DBMS_APP_CONT_ADMIN.ENABLE_RESET_STATE(    service_name => 'servicehub_app.example.com',    level        => 'LEVEL1'  );END;/

The full service must exist and the caller needs appropriate PDB administrative rights. Do not mutate a built-in/shared service just to match a tutorial. Clusterware-managed services should be managed through the supported service tooling.

6. DRCP is explicit server-side pooling

The database has a default SYS_DEFAULT_CONNECTION_POOL, but clients benefit only if DRCP is started/configured in the correct scope and connect using pooled-server routing. 26ai also supports multiple named DRCP pools.

SELECT name,valueFROM v$parameterWHERE name IN (  'enable_per_pdb_drcp',  'drcp_connection_limit',  'drcp_dedicated_opt')ORDER BY name;SELECT  connection_pool, status, minsize, maxsize,  incrsize, inactivity_timeout, max_think_timeFROM dba_cpool_infoORDER BY connection_pool;

ENABLE_PER_PDB_DRCP is static; do not toggle it casually. Determine CDB/PDB pool ownership from the current configuration first.

7. Optional Free DRCP exercise

BEGIN  DBMS_CONNECTION_POOL.START_POOL();END;/SELECT connection_pool,status,minsize,maxsizeFROM dba_cpool_info;

localhost:1521/FREEPDB1:POOLED

SELECT * FROM v$cpool_stats;SELECT * FROM v$cpool_conn_info FETCH FIRST 50 ROWS ONLY;

Direct DRCP client connections use TCP according to the current server guide. Evidence should show actual checkout/use; an enabled but unused server pool does not reduce your application's dedicated-session footprint.

8. Restore only what this lab changed

BEGIN  DBMS_CONNECTION_POOL.STOP_POOL();END;/SELECT connection_pool,statusFROM dba_cpool_info;

Never stop a shared/production pool as generic “cleanup.” Capture prior state and restore it exactly.

9. FAN is availability notification; AC/TAC is request replay

Fast Application Notification (FAN) lets compatible Oracle pools receive service up/down/drain events—typically through Oracle Notification Service (ONS) in RAC/Data Guard topologies—so dead connections can be removed or drained without waiting only on network timeouts. A standalone Free instance cannot reproduce a real clustered/standby FAN failover.

Application Continuity (AC) records/replays protected requests after recoverable failures. Transparent Application Continuity (TAC) automates more state tracking/request handling. In 26ai, JDBC client-side AC support is automatically used by driver data sources when the connected service is AC/TAC-enabled. Replay still depends on server-side service configuration and entitlement.

10. Deliberately wrong: return request state to the pool

If a request leaves NLS, package globals, contexts, client identifiers, temporary state, or an open transaction behind, the next borrower can receive wrong results or cross-user state. Repair with explicit transaction end, checkout initialization/cleanup, request boundaries, and RESET_STATE on eligible services.

11. Pool size is a concurrency policy

A pool size such as 200 is not a performance recommendation. It can convert a request spike into 200 simultaneous expensive SQL calls against Free's two CPU cores. Size from measured service time, DB capacity, queue wait SLO, session/PGA cost, and failover behavior.

12. Production judgment

Use long-lived application pools, acquire late/release early, commit or rollback before release, initialize session identity/state, and monitor pool wait/borrow/create/close metrics. Add DRCP for many mostly-idle connections when database process/session resources justify it. Add FAN/AC only with a compatible HA service/topology and test real planned/unplanned failure.

RESET_STATE is independent of AC in 26ai; per-PDB DRCP configuration can require a static initialization choice. The mandatory state-leak lab is Free-compatible; AC is not. Lesson 3 now moves values and large payloads efficiently through the borrowed session.

Authoritative references

• Managing Processes and DRCP — DRCP configuration and pooled routing
• Application Continuity / RESET_STATE — 26ai request reset and replay concepts
• V$ACTIVE_SERVICES — service reset/failover evidence
• Application Continuity for JDBC — driver/service AC behavior including DRCP
• Licensing Information — AC/Transaction Guard/RAC/ADG boundaries