Configure Drivers for Continuous Availability

This article describes how to configure drivers for continuous availability.

Configure JDBC Thin Driver

  1. Ensure all recommended patches are applied at the client. Refer to the MOS note Client Validation Matrix for Application Continuity (Doc ID 2511448.1).

  2. Use JDBC Statement Cache for Coverage and Performance

    For best coverage and performance, use the JDBC driver statement cache in place of an application server statement cache. This allows the driver to know that statements are closed and memory to be freed at the end of requests.

    To use the JDBC statement cache, use connection property oracle.jdbc.implicitStatementCacheSize (OracleConnection.CONNECTION_PROPERTY_IMPLICIT_STATEMENT_CACHE_SIZE). The statement cache is per connection. The value for the cache size matches your number of open_cursors. For example:

    oracle.jdbc.implicitStatementCacheSize=nnn where nnn is typically between 10 and 100 and is equal to the number of open cursors your application maintains.

  3. Tune the Garbage Collector

    For many applications the default Garbage Collector tuning is sufficient. For applications that return and keep large amounts of data you can use higher values, such as 2GB or larger. For example:

    java -Xms3072m -Xmx3072m 

    It is recommended to set the memory allocation for the initial Java heap size (ms) and maximum heap size (mx) to the same value. This prevents using system resources on growing and shrinking the memory heap.

  4. JDBC Concrete Classes

    For JDBC applications, Oracle does not support deprecated oracle.sql concrete classes BLOB, CLOB, BFILE, OPAQUE, ARRAY, STRUCT or ORADATA. (See MOS note 1364193.1, New JDBC Interfaces). Use ORAchk -acchk on the client to know if an application passes. The list of restricted concrete classes for JDBC Replay Driver is reduced to the following starting with Oracle JDBC-thin driver version 18c and later: oracle.sql.OPAQUE, oracle.sql.STRUCT, oracle.sql.ANYDATA

  5. Configure Fast Connection Failover (FCF)

    This corresponds to enabling subscription to FAN events.

    For client drivers 12c and later:

    • Use the recommended URL for auto-ons
    • Check that ons.jar (plus optional WALLET jars, osdt_cert.jar, osdt_core.jar, oraclepki.jar) are on the CLASSPATH
    • Set the pool or driver property fastConnectionFailoverEnabled=true
    • UCP (recommended) or third-party connection pools
    • Open port 6200 for ONS (6200 is the default port, a different port may have been chosen)

    For client drivers prior to 12c use the addresses provided:

    • Set oracle.ons.nodes =XXX01:6200, XXX02:6200, XXX03:6200

Configure OCI (Oracle Call Interface) Driver

  1. Ensure all recommended patches are applied at the client. Refer to the MOS Note Client Validation Matrix for Application Continuity (Doc ID 251148.1).

  2. Replace OCIStmtPrepare with OCIStmtPrepare2. OCIStmtPrepare() has been deprecated since 12.2. All applications should use OCIStmtPrepare2(). TAC and AC allow OCIStmtPrepare but do not replay this statement.

  3. To use FAN for OCI-based applications, do the following:

    • aq_ha_notifications is preset on the services
    • Use the recommended Connection String for auto-ons
    • Set auto_config, events, and wallet_location (optional) in oraaccess.xml, as described in Configure Clients for FAN Including Optional Wallets.
    • Link the application with the O/S client thread library
    • Open port 6200 for ONS (6200 is the default port, a different port may have been chosen). For client drivers prior to 12c use the addresses provided in oraccess.xml.

Configure ODP.NET Unmanaged Provider Driver

  1. Ensure all recommended patches are applied at the client. Refer to the MOS Note Client Validation Matrix for Application Continuity (Doc ID 251148.1).

  2. To use FAN for Oracle Call Interface based applications, do the following:

    • aq_ha_notifications is preset on the services
    • Use the recommended Connection String for auto-ons
    • Set onsConfig and wallet_location (optional) in oraaccess.xml, as described in Configure Clients for FAN Including Optional Wallets.
    • Open port 6200 for ONS (6200 is the default port, a different port may have been chosen)
    • Set FAN, in the connection string: "user id=oracle; password=oracle; data source=HA; pooling=true; HA events=true;".
    • (optional) Set Runtime Load Balancing, also in the connection string: "user id=oracle; password=oracle; data source=HA; pooling=true; HA events=true; load balancing=true;".

Enable Service Attributes for Failover

Transparent Application Continuity is enabled by default for the predefined services tp_tls, tp, tpurgent_tls and tpurgent, so you need to do nothing if your are using one of them. Additionally for them, the DEFAULT value for FAILOVER_RESTORE is AUTO.

You can change the failover type offered on your service by using the package DBMS_APP_CONT_ADMIN. Use this API to enable Application Continuity, Transparent Application Continuity or Transparent Application Failover (TAF), or to disable failover altogether. New sessions will use the new failover type. You must be an administrator to use these procedures.

To enable Transparent Application Continuity for a service:

execute DBMS_APP_CONT_ADMIN.ENABLE_TAC('HIGH');

To enable Application Continuity for a service:

execute DBMS_APP_CONT_ADMIN.ENABLE_AC('TPURGENT');

To enable TAF SELECT for a service:

execute DBMS_APP_CONT_ADMIN.ENABLE_TAF('LOW');

To enable TAF BASIC for a service:

execute DBMS_APP_CONT_ADMIN.ENABLE_TAF('MEDIUM', 'SESSION');

To disable failover for a service:

execute DBMS_APP_CONT_ADMIN.DISABLE_FAILOVER('HIGH');

If you wish to use TAF without modifying services, use the older client-side configuration for TAF in your connection string:

(FAILOVER_MODE=(TYPE=select)(METHOD=basic)(OVERRIDE=TRUE)) 

Configure Service Attributes for Maintenance

When planned maintenance starts, sessions that need to be drained are marked to be drained. Idle sessions are released gradually. Active sessions are drained when the work executing in that session completes. Draining of sessions is in wide use with Oracle connection pools and mid-tiers configured for Fast Application Notification (FAN). Starting with Oracle Database 18c, the database itself drains sessions when databases are stopped or relocated. Draining is always the best solution for hiding planned maintenance.

Failover solutions such as Application Continuity are the fallback when work will not drain in the time allocated.

A drain timeout, in seconds, and a stop option can be set on a service using the package DBMS_APP_CONT_ADMIN. You must be an administrator to use these procedures.

To adjust drain timeout to 600 seconds and set stop option to IMMEDIATE:

execute DBMS_APP_CONT_ADMIN.SET_DRAINING('HIGH', 600, 'IMMEDIATE' );

To adjust only drain timeout :

execute DBMS_APP_CONT_ADMIN.SET_DRAINING('TPURGENT', 600);

Configure Clients for FAN Including Optional Wallets

Wallet-based authentication is an option for FAN when using Autonomous Database. Use the same wallet as for the TNS connection.

For JDBC Applications

  1. Ensure the following jar files are present in the application’s CLASSPATH:

    (ons.jar, osdt_cert.jar, osdt_core.jar, oraclepki.jar) 
  2. Specify the wallet for FAN in one of the following ways:

    • To use auto-configured ONS with wallets, set the following Java system properties:

      "-Doracle.ons.walletfile=/replace this with host path/onswallet"
      "-Doracle.ons.walletpassword=myONSWalletPassword"

      Note that these cannot be set on a per-pool or per-connection basis.

    • To explicitly set ONS do one of the following:

      • Set explicitly using an UCP XML Configuration file. For example:

        <!--?xml version="1.0" encoding="UTF-8"? -->
        <ucp-properties>
          <connection-pool
            connection-pool-name="UCP_pool1"
            user="dbuser"
            password="dbuserpasswd"
            connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
            initial-pool-size="10"
            min-pool-size="5"
            max-pool-size="15"
            validate-connection-on-borrow="true"
            connection-wait-timeout="900"
            max-connections-per-service="50"
            sql-for-validate-connection="select 1 from dual" 
            url="jdbc:oracle:thin:@(DESCRIPTION =(CONNECT_TIMEOUT= 120)(RETRY_COUNT=20) (RETRY_DELAY=3) (TRANSPORT_CONNECT_TIMEOUT=3)(ADDRESS_LIST =(LOAD_BALANCE=on)(ADDRESS = (PROTOCOL = TCP)(HOST=primary-scan)(PORT=1521)))(ADDRESS_LIST =(LOAD_BALANCE=on)(ADDRESS = (PROTOCOL = TCP)(HOST=standby-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = MY-SERVICE)))"
            fastConnectionFailoverEnabled="true" 
            onsConfiguration="nodes=primary-scanhost:6200,secondary-scanhost:6200\nwalletfile=/replace_with_host_path/onswallet\nwalletpassword=myWalletPassword">
          </connection-pool>
        </ucp-properties>
      • Set programmatically from within UCP, using a call to setONSConfiguration(), for example:

        pds.setONSConfiguration("nodes=primary-scanhost:6200,secondary-scanhost:6200\nwalletfile=/replace_this_with_host_path/onswallet\nwalletpassword=myWalletPassword");

For Oracle Call Interface (OCI) Applications Using Oracle Driver Version 12.2 or More Recent

Add the following to the <default_parameters> section of the oraaccess.xml file:

<default_parameters>
      (Other settings may be present in this section)
   <events>
      true
   </events>
   <ons>
      <auto_config>true</auto_config>
      <wallet_location>/my_path/onswallet</wallet_location>       
   </ons>
</default_parameters>

The <wallet_location> path should be the name of the directory containing the wallet.

Other parameters may be set in the ons section of oraaccess.xml, including <hosts>, <max_connections>, and <subscription_wait_timeout>.

Drivers that support native event setting controls may omit the <events> section and use the driver setting instead.

By default connections will be established to the database even if ONS fails. If you prefer connections to fail in this scenario, you can add a section to the same level as <events> and <ons>:

<fan> 
   <subscription_failure_action> 
      error 
   </subscription_failure_action> 
</fan> 

Place the oraaccess.xml file in the same directory as the tnsnames.ora and sqlnet.ora network files. For example, when using Oracle Instant Client these files might be in the default directory network/admin. Alternatively, all network configuration files can be put in another accessible directory. Then set the environment variable TNS_ADMIN to that directory name.

ODP.Net Managed Provider

Use the application.config file to specify ONS configuration and wallet location. For example:

<oracle.manageddataaccess.client>
    <version number="*">
      <onsConfig mode="remote">
        <settings>
          <setting name="Protocol" value="TCPS" />
          <setting name="WALLET_LOCATION" value="C:\myPath\ONS_SSLWallet" />
        </settings>
        <ons database="atp01db">
          <add name="nodeList" value="racNode1:6205,racNode2:6205,racNode3:6205" />
        </ons>
      </onsConfig>
    </version>
</oracle.manageddataaccess.client>