Oracle Data Provider for .NET, Managed Driver Configuration

ODP.NET, Managed Driver supports .NET configuration file-based settings in machine.config, application.config, and web.config. It does not support Windows registry based configuration. ODP.NET, Managed Driver settings in .NET configuration files are similar to ODP.NET, Unmanaged Driver settings to make porting easier.

The ODP.NET, Managed Driver configuration file section name is <oracle.manageddataaccess.client> as compared to <oracle.dataaccess.client> in ODP.NET, Unmanaged Driver. A typical .NET config that uses ODP.NET, Managed Driver has some or all of the following subsections nested within a <version> subsection under <oracle.manageddataaccess.client> section. Note the tag names are case sensitive, while the attribute names are case insensitive.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        ...
        ...
      </dataSources>
      <settings>
        ...
        ...
      </settings>
      <LDAPsettings>
        ...
        ...
      </LDAPsettings>
      <implicitRefCursor>
        ...
        ...
      </implicitRefCursor>
      <edmMappings>
        ...
        ...
      <edmMappings>
    </version>
    <version number="4.112.3.60">
      <dataSources>
        ...
        ...
      </dataSources>
      <settings>
        ...
        ...
      </settings>
      <LDAPsettings>
        ...
        ...
      </LDAPsettings>
      <implicitRefCursor>
        ...
        ...
      </implicitRefCursor>
      <edmMappings>
        ...
        ...
      <edmMappings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

The ODP.NET, Managed Driver configuration and settings are described in the following sections. Many of the attributes are the same as ODP.NET, Unmanaged Driver. See Table 2-1 for detailed attribute descriptions.

version Section

All the information required by an application should be grouped under the version subsections. Each <version number="X"> section contains parameters applicable for version X of the ODP.NET, Managed Driver. For example, <version number="4.112.3.60"> section parameters will be applicable only for those applications using ODP.NET, Managed Driver assembly 4.112.3.60.

Apart from version specific sections, there can also be a generic section <version number="*">. This section's parameters are applicable for all ODP.NET, Managed Driver versions. Parameters in the version specific section take precedence over the parameters of the generic section. The following is an example of a version section:

<oracle.manageddataaccess.client>
  <version number="*">
     <settings>
          <setting name="TraceOption" value="1"/>
          <setting name="PerformanceCounters" value="0" />
     </settings>
  </version>
  <version number="4.112.3.60">
     <settings>
          <setting name="PerformanceCounters" value="4095" />
     </settings>
  </version>
</oracle.manageddataaccess.client>

An application referencing ODP.NET, Managed Driver 4.112.3.60 has the following values set:

  • TraceOption = 1

  • PerformanceCounters= 4095

dataSources Section

This section can appear only under a <version> section. The mapping between the different data source aliases and corresponding data descriptors should appear in this section. The following is an example.

<dataSources>
  <dataSource alias="inst1" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)......)))"/>
  <dataSource alias="inst2" descriptor="(DESCRIPTION= ......)))"/>
</dataSources>

The following precedence order is followed to resolve the data source alias specified in the Data Source attribute in the connection string.

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file.

  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.

  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.

Note:

The "data source" connection string attribute can alternatively be set to a full descriptor or Easy Connect syntax rather than a data source alias. The above precedence rules apply to resolving a data source alias only.

Requirements for connecting to a local database without specifying "data source" connection string attribute:

  • The listener must be up and running.

  • ORACLE_SID environment variable must be set appropriately.

Note:

When "data source" connection string attribute is not specified, protocol defaults to 'tcp' and port defaults to '1521'.

The ODP.NET managed driver reads and caches all the alias entries from the app.config, web.config, machine.config, and from a tnsnames.ora file that is found at application start-up time. However, aliases that are defined in LDAP servers are resolved and cached on demand. This means for each unique alias that is used by the application, an alias resolution query is executed against an LDAP server and the full descriptor associated with the alias will be cached once it is fetched.

For developers that need to change or add alias settings while developing applications, one may consider using OracleDataSourceEnumerator.GetDataSources() rather than restarting the application. Invoking this method will first wipe out existing cache entries that were read from the tnsnames.ora file and all aliases obtained from the LDAP Server. Then, the tnsnames.ora is re-parsed and all its entries will be cached again. Please note that the app.config, web.config, and machine.config entries are read only once at application start-up time and thus their contents are maintained and not re-parsed even if OracleDataSourceEnumerator.GetDataSources() is invoked.

The OracleDataSourceEnumerator.GetDataSources() method invocation has an impact on the connection pool. This is because a connection pool, which is created for each unique connection string, will cache the resolved full descriptor information after the first connection is created for a given connection pool. After that, the connection pool uses the cached full descriptor information for all subsequent connection creations. Thus, for applications that have their tnsnames.ora or LDAP entries modified during the execution of an application where an alias points to a different database than before, one should call the OracleDataSourceEnumerator.GetDataSources() method to remove old cached entries. This should be followed by the invocation of the ClearPool(OracleConnection) instance method or the ClearAllPools() static method to remove existing connections and also have it obtain a new full descriptor value that was read by the invocation of OracleDataSourceEnumerator.GetDataSources(). Following this scheme will assure that all the connections in the connection pool uses the new full descriptor that is now associated with the alias and all connections in a connection pool is established to the same database.

The following keywords are supported within the descriptor setting:

  • ADDRESS

  • ADDRESS_LIST (Note: only failover supported)

    Oracle recommends using SCAN listener and Runtime Load Balancing to balance the load when connecting to an Oracle RAC database.

  • DESCRIPTION

  • DESCRIPTION_LIST (Note: Failover supported; Address_list load balancing not supported)

  • HOST (Note: <hostname>, <IPv6 literal>, and <IPv4 literal> are supported)

  • IP (Note: "loopback" is supported)

  • PROTOCOL (Note: tcp and tcps are supported)

  • SDU (Note: 256 to 65536 are supported)

  • SECURITY: SSL_VERSION (Note: overrides sqlnet.ora:ssl_version)

  • TRANSPORT_CONNECT_TIMEOUT (Note: overrides tcp.connect_timeout)

Note:

  • SSL is now supported via method MCS and FILE.

  • Both Kerberos5 and NTS authentication are supported. RADIUS is not supported.

  • Only SQL*Net, Easy Connect naming, and LDAP (namely, Active Directory and Oracle Internet Directory) are supported.

  • No bequeath (beq) support. Default address is instead TCP loopback with port 1521 and Oracle service name from environment (ORACLE_SID)

See Also:

Oracle Database Net Services Reference for a detailed description of the attributes.

Though managed ODP.NET does not support TNS descriptor based load balancing, it does support failover through both an ADDRESS_LIST and DESCRIPTION_LIST.

Note that you need not specify either the LOAD_BALANCE or the FAILOVER directive, because only failover is supported. The directives are ignored.

The following examples demonstrate TNS descriptors utilizing failover:

(DESCRIPTION=
   (ADDRESS_LIST=                            
     (ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1630))
     (ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1630))
     (ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=Sales.us.example.com)))
 
(DESCRIPTION_LIST=
 (DESCRIPTION=
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales1.example.com)))
 (DESCRIPTION=
  (ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2a-svr)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2b-svr)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=sales2.us.example.com))))

settings section

This section can appear only under a <version> section. Any ODP.NET, Managed Driver specific settings should appear in this section. The following is an example of a settings section:

<settings>
  <setting name="TraceLevel" value="7" />
  <setting name="TraceOption" value="1"/>
  <setting name="TNS_ADMIN" value="C:\oracle\work"/>
</settings>

A new default behavior has been introduced for ODP.NET Release 12.1.0.2 and higher when InitialLobFetchSize is set to -1. The new default value is LegacyEntireLOBFetch = 0. To use the old behavior, set LegacyEntireLobFetch = 1 in the ODP.NET configuration. Refer to "Setting InitialLONGFetchSize to -1" for more information.

ODP.NET, Managed Driver configuration settings that are supported:

  • BindByName

  • DbNotificationPort

  • DemandOraclePermission

  • FetchSize

  • LDAP_ADMIN: Specifies the ldap.ora location. The LDAP_ADMIN setting works in conjunction with the TNS_ADMIN setting to set ldap.ora search order.

    ldap.ora is searched in the following order:

    1. TNS_ADMIN in app/web.config

    2. LDAP_ADMIN in app/web.config

    3. current working directory

    4. %ORACLE_HOME%\network\admin

    5. %ORACLE_HOME%\ldap\admin

  • LegacyEntireLOBFetch

  • MaxStatementCacheSize

  • MetaDataXml

  • NAMES.DIRECTORY_PATH: The default search order is TNSNAMES and EZCONNECT. TNSNAMES, LDAP, and EZCONNECT are the only name resolution methods supported, but their precedence order can be modified.

  • NAMES.LDAP_AUTHENTICATE_BIND

  • NAMES.LDAP_CONN_TIMEOUT

  • NODELAY

  • ORA_DEBUG_JDWP: Allows Oracle PL/SQL Debugger and database to connect automatically without application code changes. Value is set as host=<IP_address or host_name>;port=<debugging port number>. Ex. host=localhost;port=1234

  • ORACLE_SID

  • PerformanceCounters

  • RECEIVE_BUF_SIZE: Sets TCP SO_RECVBUF, the total buffer space associated with the local side of a TCP socket

  • SelfTuning

  • SEND_BUF_SIZE: Sets TCP SO_SENDBUF, the total buffer space associated with the local side of a TCP socket

  • ServiceRelocationConnectionTimeout

    Whenever a database service becomes unavailable, such as due to a service being relocated, an application can encounter numerous connectivity errors during this time. To avoid unnecessary connection attempts to an unavailable service which will result in an error, ODP.NET, Managed and Unmanaged Drivers block any connection attempts until the service is up or until this property's specified time limit expires from the time when the service DOWN event was received, whichever comes first. Once the specified time elapses, all the connection attempts to the specific service which is known to be down will no longer be blocked. Those requests will be sent to the server. ServiceRelocationConnectionTimeout is only operational in conjunction with Oracle Fast Connection Failover (HA Events = true). It works with planned and unplanned outages. In seconds. (Default = 90)

  • SQLNET.AUTHENTICATION_SERVICES: Supported values are kerberos5, NTS, tcps, null, empty, or not-specified.

  • StatementCacheSize

  • SSL_VERSION

  • TNS_ADMIN: Location where either one or more of tnsnames.ora, ldap.ora, and sqlnet.ora are located.

    Note:

    When installing ODP.NET, Managed Driver and an Oracle Home together, the installer automatically creates a TNS_ADMIN setting in the machine.config file of the .NET Framework. The TNS_ADMIN setting will be set to the directory: <ORACLE_HOME>\Network\Admin\, providing a default machine location to host the tnsnames.ora, sqlnet.ora, and ldap.ora files.

  • TraceFileLocation: Trace file destination directory, for example, D:\traces. The default TraceFileLocation is <Windows user temporary folder>\ODP.NET\managed\trace.

  • TraceLevel: 1 = public APIs; 2 = private APIs; 4 = network APIs/data. These values can be ORed. To enable everything, set TraceLevel to 7. Errors will always be traced.

  • TraceOption

  • TCP.CONNECT_TIMEOUT

  • WALLET_LOCATION: Microsoft Certificate Store (MCS) and file system wallets are supported.

  • SQLNET.ENCRYPTION_CLIENT = Negotiates whether to turn on encryption. Supported values are accepted, rejected, requested, or required.

  • SQLNET.ENCRYPTION_TYPES_CLIENT = Encryption algorithm(s) to use.

The following table lists the valid encryption algorithms for ODP.NET, Managed Driver.

Algorithm Name Legal Value
AES 128-bit key AES128
AES 192-bit key AES192
AES 256-bit key AES256
RC4 128-bit key RC4_128
RC4 256-bit key RC4_256
2-key 3DES 3DES112
3-key 3DES 3DES168

For more information on data encryption settings, refer to the Oracle Database Security Guide.

ODP.NET, Managed Provider only supports the NTS authentication adapter, including network encryption. The database server must be on Windows and support NTS external authentication. The ODP.NET, Managed Driver client must utilize NTS external authentication as well. All other NTS connection scenarios will fail since the client and server will not be able to agree on a database security authentication mechanism.

NTS-based external authentication only supports Microsoft NT LAN Manager (NTLM). Kerberos NTS is not supported.

See Also:

Oracle Database Net Services Reference

LDAPsettings section

This section can appear only under a <version> section. Any ODP.NET, Managed Driver specific LDAP settings should appear in this section. The following is an example of a <LDAPsetting> subsection under the <LDAPsettings> section:

<LDAPsettings>
  <LDAPsetting name="DIRECTORY_TYPE" value="AD" />
  <LDAPsetting name="DEFAULT_ADMIN_CONTEXT" value="dc=Oracle,dc=com"/>
</LDAPsettings>

Lightweight Directory Access Protocol

ODP.NET, Managed Driver supports TNS alias resolution through a LDAP server/service, specifically Microsoft Active Directory and Oracle Internet Directory (OID). TNS alias resolution occurs when using the LDAPsettings section or ldap.ora file settings. The LDAPsettings section settings take precedence over ldap.ora settings.

For Active Directory, only the DIRECTORY_TYPE and DEFAULT_ADMIN_CONTEXT parameters are required in ldap.ora. When the DIRECTORY_SERVERS parameter is missing or has no value, the default LDAP server for the current domain will be used.

For OID, all ldap.ora parameters must be set with valid values to complete configuration.

ODP.NET, Managed Driver and ODP.NET, Unmanaged Driver support the same level of security when using LDAP for name resolution.


 Microsoft Active Directory
Encryption None SSL
Authentication Anonymous Not Supported
Domain User


Oracle Internet Directory
Encryption None SSL
Authentication Anonymous Anonymous
Wallet

Note: Wallet based authentication for Oracle Internet Directory is not supported for this release

See Also:

implicitRefCursor section

This section can appear only under a <version> section. Any information about REF CURSOR parameters that need to be bound implicitly should appear in this section. The following is an example of an <implicitRefCursor> section:

<implicitRefCursor>
  <storedProcedure schema="USERREFCUR" name="TestProc1">
    <refCursor name="Param3">
     <bindInfo mode="Output"/> 
     <metadata columnOrdinal="0" columnName="DEPTNO" baseColumnName="DEPTNO" baseSchemaName="USERREFCUR" baseTableName="DEPT" nativeDataType="number" providerType="Int32" dataType="System.Int16" columnSize="2" allowDBNull="true" />
     <metadata columnOrdinal="1" columnName="DNAME" baseColumnName="DNAME" baseSchemaName="USERREFCUR" baseTableName="DEPT" nativeDataType="varchar2" providerDBType="String" columnSize="30" />
    </refCursor>
    <refCursor name="param2">
      <bindInfo mode="Output"/>
      <metadata columnOrdinal="0" columnName="EMPNO" baseColumnName="EMPNO" baseSchemaName="USERREFCUR" baseTableName="EMP" nativeDataType="number" providerType="Int32" dataType="System.Int16" columnSize="4" allowDBNull="false" />
    </refCursor>
   </storedProcedure>
 
   <!--Next stored procedure information-->
   <storedProcedure name="TestProc2">
      ...
      ...
   </storedProcedure>
</implicitRefCursor>

distributedTransaction section

This section can appear only under a <version> section. Any information about distributed transactions should appear in this section. The following is an example of a distributedTransaction section:

<distributedTransaction>
  <setting name="OMTSRECO_IP_ADDRESS" value="my-pc" />
  <setting name="OMTSRECO_PORT" value="2040" />
  <setting name="ORAMTS_SESS_TXNTIMETOLIVE" value="240" />
</distributedTransaction>

  • OMTSRECO_IP_ADDRESS: Specifies the machine name (or IP address) that the OraMTS Recovery service will be running on to resolve database in-doubt transactions. The default is the local machine name.

  • OMTSRECO_PORT: Specifies the port that the OraMTS Recovery service will be listening on to resolve database in-doubt transactions. The default is 2030.

  • ORAMTS_SESS_TXNTIMETOLIVE : Specifies the time in seconds that the transaction can remain inactive after it has been detached or delisted from the database. Once this time expires, the transaction is automatically terminated by the provider. The default is 120 seconds.

  • UseManagedDTC: When set to false and using .NET Framework 4.5.2 or higher, ODP.NET uses .NET Framework for distributed transaction support. In all other instances, ODP.NET uses Oracle Services for Microsoft Transaction Server to support distributed transactions. Boolean (Default = false) for ODP.NET, Managed Driver only.

  • UseOraMTSManaged: When set to true and using .NET Framework 4.5.2 or higher, ODP.NET uses managed code for distributed transactions. If set to true, but .NET 4.5.1 or lower is used, an exception will be thrown. If set to false, ODP.NET uses Oracle Services for Microsoft Transaction Server to support distributed transactions. Boolean (Default = false) for ODP.NET, Unmanaged Driver only.

edmMappings section

This section can appear only under a <version> section. Any information related to EDM mappings should appear in this section. Refer to Oracle Number Default Data Type Mapping and Customization for more examples on edmMappings section.

onsConfig section

Oracle Notification Service (ONS) can be configured using either local or remote configuration. Remote configuration is the preferred configuration for standalone client applications. For releases earlier than Oracle Database 12c, this section is mandatory for ODP.NET to receive ONS notifications. With Oracle Database 12c and later, this section is optional and the information about the ONS daemons is received from the server itself. However, ODP.NET will also listen for events from any <host:port> pairs that is provided by the user in this section in addition to the <host:port> pairs received from the server.

For local configuration, please ensure that ONS is configured and available on the node where ODP.NET is running, so that ODP.NET can receive events directly from the local ONS daemon. For client side ONS daemon configuration, refer to "Client Side ONS Daemon Configuration.". The following is a sample format for the local configuration:

<onsConfig configFile="C:\temp\test.config" mode="local">
</onsConfig>

Note:

The configFile specified in .NET config should contain the same localport and remoteport values as specified in the ons.config used by the local ONS daemon. This will enable the application to receive events from the local ONS daemon.

Remote configuration is used in scenarios where the application directly receives ONS events from the ONS daemons running on remote machines. One of the advantages of this configuration is that no ONS daemon is needed on the client end and, therefore, there is no need to manage this process.

The following is a sample format for remote configuration:

  <onsConfig mode="remote">
        <ons database="db1">
          <add name="nodeList" value="racnode1:4100, racnode2:4200" />
        </ons>
        <ons database="db2">
          <add name="nodeList" value=" racnode3:4100, racnode4:4200" />
        </ons>
      </onsConfig>

In case of remote configuration, the application has to specify the <host>:<port> values for every potential database that it can connect to. The <host>:<port> value pairs represent the ports on the the different Oracle RAC nodes where the ONS daemons are talking to their remote clients.

Client Side ONS Daemon Configuration

ONS configuration is controlled by the ONS configuration file, ORACLE_HOME/opmn/conf/ons.config. This file tells the ONS daemon how it should behave. The SRVCTL utility can be used to start and stop the ONS daemon. It is installed on each node by default during server install. Refer to Oracle Real Application Clusters Administration and Deployment Guide for more information on the SRVCTL utility.

Configuration information within ons.config is defined in simple name and value pairs. An example of ONS.config is given below

# This is an example ons.config file
#
# The first three values are required
localport=4100
remoteport=4200
nodes=racnode1.example.com:4200,racnode2.example.com:4200

Some parameters in the ons.config file are required and some are optional. Table Table 2-2 lists the required ONS configuration parameters and Table 2-3 lists the optional ONS configuration parameters.

Table 2-2 Required ONS Configuration Parameters

Parameter Explanation

localport

The port that ONS binds to on the local host interface to talk to local clients.

For example, localport=4100

remoteport

The port that ONS binds to on all interfaces for talking to other ONS daemons.

For example, remoteport=4200

nodes

A list of other ONS daemons to talk to. Node values are given as a comma-delimited list of either host names or IP addresses plus ports. The port value that is given is the remote port that each ONS instance is listening on. In order to maintain an identical file on all nodes, the host:port of the current ONS node can also be listed in the nodes list. It will be ignored when reading the list.

For example, nodes=myhost.example.com:4200,123.123.123.123:4200

The nodes listed in the nodes line correspond to the individual nodes in the Oracle RAC instance. Listing the nodes ensures that the middle-tier node can communicate with the Oracle RAC nodes. At least one middle-tier node and one node in the Oracle RAC instance must be configured to see one another. As long as one node on each side is aware of the other, all nodes are visible. You need not list every single cluster and middle-tier node in the ONS configuration file of each Oracle RAC node. In particular, if one ONS configuration file cluster node is aware of the middle tier, then all nodes in the cluster are aware of it.

Table 2-3 Optional ONS Configuration Parameters

Parameter Description

loglevel

The level of messages that should be logged by ONS. This value is an integer that ranges from 1, which indicates least messages logged, to 9, which indicates most messages logged. The default value is 3.

For example, loglevel=3

logfile

A log file that ONS should use for logging messages. The default value for log file is $ORACLE_HOME/opmn/logs/ons.log.

For example, logfile=C:\app\user\product\12.1.0\opmn\logs\myons.log

walletfile

The wallet file used by the Oracle Secure Sockets Layer (SSL) to store SSL certificates. If a wallet file is specified to ONS, then it uses SSL when communicating with other ONS instances and require SSL certificate authentication from all ONS instances that try to connect to it. This means that if you want to turn on SSL for one ONS instance, then you must turn it on for all instances that are connected. This value should point to the directory where your ewallet.p12 file is located.

For example, walletfile=C:\app\user\product\12.1.0\opmn\conf\ssl.wlt\default

useocr

The value, reserved for use on the server-side, to indicate ONS whether it should store all Oracle RAC nodes and port numbers in Oracle Cluster Registry (OCR) instead of the ONS configuration file or not. A value of useocr=on is used to store all Oracle RAC nodes and port numbers in Oracle Cluster Registry (OCR).

Do not use this option on the client-side.

The ons.config file allows blank lines and comments on lines that begin with the number sign (#).