I was recently working in an environment where the requirement was to connect to an Oracle database using the On-Premise Gateway, so that we could either DirectQuery or import the data and then refresh it using the On-Premise Gateway. So the steps below detail how to complete this successfully.

The example below was connecting to an Oracle databased called TRID

Make sure that checkbox is selected so that Power BI can connect to your Oracle database. After the client is installed and ODAC is configured properly, we recommend using PowerBI Desktop or other test client to verify correct installation and configuration on the Gateway. Add a data source. Connect to an Oracle database with Power BI Desktop To connect to an Oracle database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. The Oracle client software you use depends on which version of Power BI Desktop you've installed: 32-bit or 64-bit. 'The recommended provider ('Oracle.DataAccess.Client') is not installed. You can continue with your current provider, however it has been deprecated and may not work properly.' I click on 'Learn more' hyperlink which led me to this document page.

Downloading and installing Oracle Data Access Components (ODAC)

The installation of the ODAC is fairly straight forward and already has been very well documented in the Power BI website.

You can view the link below in order to download and install the correct ODAC version for your Power BI Desktop Installation.

NOTE: Part of the installation is that you will need to setup and configure your tnsnames.ora file in order to successfully connect to the Oracle Database.

Configuration of tnsnames.ora

These steps below were configured by the Oracle DBA where I am currently working, I thought I would highlight what needs to be configured below in order to successfully connect to the Oracle Database.

I found this out when trying to connect to the Oracle database and it appears that this is what is required to ensure that I could connect successfully.

Below is a working version of my tnsnames.ora file

The important things that I needed to have configured were the following:

• The first part is the name which is highlighted in GREEN above with my server called TRID
• The other important part is to ensure that you have got the SERVICE_NAME
  configured and resolves back to the actual server with the Fully Qualified Domain Name (FQDN) highlighted in PURPLE.
  • As with my above example this was SERVICE_NAME=trid.domain.com.au

Setting the connection to the Oracle database in Power BI Desktop

The next step was for me to set up or create the connection to my Oracle Database.

NOTE: In my example below we connected using a database user and password and not integrated Windows Authentication.

• I clicked on Get Data and then selected Oracle database
• Then I clicked Connect.
• NOTE: In my instance I got the following window shown, which explains that I have an older version of ODAC, I clicked Ok to continue
• On the Oracle database window it now prompted me for the Server and if I am going to use the Import or DirectQuery Data Connectivity mode, as well as additional advanced options.
  • In my example I simply put in the Server Name and left it defaulted to Import
  • I then clicked Ok
• The first time I connected it prompted me for the Security Connection details.
  • As mentioned before I used the Database security connection details
  • I then clicked Connect.
• This then brought up the Navigator, which from then I could then connect to my required tables.
• And then loaded the data into my data model and completed my Power BI Desktop File.
• Once I had completed my Power BI Desktop file I then uploaded it into the Power BI Service.

Configuration of the Power BI Service Gateway

In the steps below I now will show how I created and configured the Gateway to connect to the Oracle database.

NOTE: I had already installed and configured the On-Premise Gateway within the domain I am working in. You can reference this installation guide: On-premises data gateway

• I logged into the Power BI Service and then clicked on Manage Gateways
• Now this is one of the MOST IMPORTANT configuration steps when I was configuring the Data Source Name
  • This Data Source Name MUST be identical to the Server Name I created in my Power BI Desktop File Data Source Connection.
  • As I did previously I used the Oracle Server name TRID
  • So now when I created my new Data Source I configured it with the following below, again NOTING that the Data Source Name is identical to my Power BI Desktop File Server Name as highlighted below.
  • Then I clicked Apply.
  • And as you can see above it says Connection Successful which means that my Gateway successfully connected to the Oracle database.
• The final step that I did was to give the required users access to the Gateway
  • NOTE: You must add the users in here that you want to be able to connect to the Gateway Data Source and configure the data refresh.

Configuring the dataset to use the Gateway connection

The final step that I had to complete was to configure my dataset to use the Gateway connection.

• I went into the Power BI Service.
• Then I clicked on Datasets
• I then went to my dataset that I had previously uploaded, where I wanted to connect to it using the Gateway connection.
  • I then expanded the Gateway connection
  • Now I had the option to Use a data gateway
  • And in here I selected my Gateway that I had previously configured.
  • I then clicked Apply.
• Then the final step was to test that it was all working and I did this by going into the Datasets and clicking Refresh now
• Once that had completed I went back into Settings, then Datasets and clicked on my dataset.
  • And then I could view that my refresh completed successfully.

This will hopefully guide you to get your Oracle connected with the On-Premise Gateway.

Another quick note is that the steps when connecting to a SQL Server are very similar, especially the steps in the Power BI Service.

Any comments or suggestions are welcome and please leave them in the section below.

The settings for specific versions of ODP.NET, can be configured in several ways for specific effects on precedence:

• The Windows registry entries are machine-wide settings for a particular version of ODP.NET.

  Windows registry based configuration is not supported for ODP.NET, Managed Driver, except for connection pool performance counters and event logging.

• The machine.config settings are .NET framework-wide settings that override the Windows registry values.

• The application or web config file settings are application-specific settings that override the machine.config settings and the Windows registry settings.

• Any attribute settings that are equivalent to the connection string override everything.

The application or web config file can be useful and sometimes essential in scenarios where more than one application on a computer use the same version of ODP.NET, but each application needs a different ODP.NET configuration. The Windows registry value settings for a given version of ODP.NET affect all the applications that use that version of ODP.NET. However, having ODP.NET configuration values in the application or web config file assure that these settings are applied only for that application, thus providing more granularities.

For example, if the application or web.config file has a StatementCacheSize configuration setting of 100, this application-specific setting forces the version of ODP.NET that is loaded by that application to use 100 for the StatementCacheSize and overrides any setting in the machine.config and in the registry. Note that for any setting that does not exist in a config file (machine.config or application/web config), the value in the registry for a loaded version of ODP.NET is used, as in previous releases.

Note that ODP.NET reads the machine.config files from the version of the .NET Framework on which ODP.NET runs, not from the version of ODP.NET.

ODP.NET only reads the Windows Registry and the XML configuration file when it is loaded into memory, thus any configuration changes made after that are not read or used until the application is re-started.

Note:

ODP.NET managed and unmanaged driver .NET configuration file settings for native Entity Framework 6 applications have a different format than the traditional .NET configuration file settings. For more information on this format, consult the ODP.NET README.

Oracle Data Provider for .NET, Unmanaged Driver Configuration

The following sections explain how to configure ODP.NET, Unmanaged Driver.

Refer to 'Oracle Data Provider for .NET, Managed Driver Configuration' for information about how to configure ODP.NET, Managed Driver.

Supported Configuration Settings

ODP.NET, Unmanaged Driver supports the configuration of an attribute as follows:

• In the Windows registry.

• In an XML file.

• Through a different mechanism such as a connection string or programmatically through an ODP.NET class, if applicable.

Table 2-1 describes each configurable attribute that is supported by ODP.NET. In the table, the term Configuration Support is followed by the types of configuration support (Windows registry, XML file, and so on) that are available for that attribute.

The table describes valid values as well as the default for each attribute.

Note:

The default values shown are the values used for an attribute if the registry key does not exist or if it is not configured anywhere.

Table 2-1 Configuration Attributes

Attribute/Setting Name	Description
CheckConStatus	Specifies whether the status of the connection is checked or not before putting the connection back into the connection pool. This registry entry is not created by the installation of ODP.NET. However, the default value 1 is used. Configuration Support: Windows Registry and XML file Valid Values: 1: Check the status of the connection. 0: Do not check the status of the connection. Default: 1
DbNotificationPort	Specifies the port number which ODP.NET listens to, for all notifications sent by the database for change notification, HA, or RLB features. ODP.NET does not throw any errors if an invalid or used port number is specified. The port can also be set to override the Windows registry and XML configuration file by setting the OracleDependency.Port static field. Configuration Support: XML file, and ODP.NET class Valid Values: -1: Open a random unused port to listen to. n>=0: Listen on port n. Default: -1
DemandOraclePermission	Specifies whether ODP.NET demands OraclePermission from the .NET application that is trying to access the database using ODP.NET. Configuration Support: Windows Registry and XML file Valid Values: 0: Disables demands for OraclePermission. 1: Enables demands for OraclePermission Default: 0
DllPath	Specifies the location where dependent unmanaged Oracle Client binaries load from. Configuration Support: Windows Registry and XML file Valid Values: The path where dependent unmanaged Oracle Client binaries reside. Default: ORACLE_BASEORACLE_HOMEbin
DynamicEnlistment	Specifies whether the application enlists in distributed transactions explicitly after an OracleConnection.Open method invocation through EnlistTransaction() or EnlistDistributedTransaction(). To configure ODP.NET to enable dynamic enlistment programmatically, the connection string must contain 'enlist=dynamic'. Configuration Support: Windows Registry, XML file, and 'enlist' connection string attribute Valid Values: 0: Disables ability to explicitly enlist in distributed transactions. 1: Enables ability to explicitly enlist in distributed transactions. Default: 0
FetchSize	Specifies the total memory size, in bytes, that ODP.NET allocates to cache the data fetched from a database round-trip. This value can be set on the OracleCommand and the OracleDataReaderFetchSize property as well. Configuration Support: Windows Registry, XML file, and ODP.NET class Valid Values: 0 <=n<= int.MaxValue: n is the size of the cache in bytes. Default: 131072
LegacyEntireLobFetch	Returns either OracleBlob and OracleClob types or OracleBinary and OracleString types from Oracle Database BLOB and CLOB columns. This setting only applies when InitialLobFetchSize is set to -1. Valid Values: 0: Returns OracleBlob and OracleClob 1: Returns OracleBinary and OracleString Default: 0
LegacyTransactionBindingBehavior	Specifies when a database connection detaches from a System.Transactions transaction. By default, connections detach from a transaction only when explicitly unbound as is the case when the connection closes or implicitly unbound when the transaction is disposed. Alternatively, this attribute can be set so that the connection detaches whenever the transaction ends (commits, aborts, or times out), the connection closes, or the transaction is disposed. In ODP.NET 11.2.0.3.20 and earlier releases, the latter was the default behavior. Oracle recommends using the current default behavior. In the earlier default behavior, when the timeout elapses before the transaction completes, the connection unbinds itself from the transaction and all subsequent executions on this connection execute in AutoCommit mode. Any operations prior to the timeout roll back, but operations performed after the timeout commit. In the current default setting, users receive an exception when the transaction times out and additional operations execute on the connection. Configuration Support: Windows Registry and XML file Valid Values: 0: Connections detach from transaction when the connection closes or the transaction is disposed. 1: Connections detach from transaction when the connection closes, the transaction is disposed, or the transaction completes (commits, rolls back, times out). Default: 0
MaxStatementCacheSize	Specifies the maximum number of statements that can be cached when self-tuning is enabled. Configuration Support: Windows Registry and XML file Valid Values: 0 to System.Int32.MaxValue. Default: 100
MetaDataXml	Specifies the name of the XML file that customizes the queries to obtain the metadata the ADO.NET 2.0 GetSchema method returns. MetaDataXml can only be set in a configuration file. Configuration Support: XML file only Valid Values: A complete file name for the XML file. Default: none
PerformanceCounters	Enables or disables publishing performance counters for connection pooling. Multiple performance counters can be obtained by adding the valid values. Configuration Support: Windows Registry and XML file Valid Values: 0: Not Enabled 1: Number of sessions being established with Oracle Database every second. 2: Number of sessions being severed from Oracle Database every second. 4: Number of active connections originating from connection pools every second. 8: Number of active connections going back to the connection pool every second. 16: Total number of active connections. 32: Number of inactive connection pools. 64: Total number of connections in use. 128: Total number of connections available for use in all the connection pools. 256: Number of pooled active connections. 1024: Number of non-pooled active connections. 2048: Number of connections that will be soon available in the pool. User has closed these connections, but they are currently awaiting actions, such transaction completion, before they can be placed back into the pool as free connections. 4095: All the above Default: 0
PromotableTransaction	Specifies the type of transaction to use when the first connection participates in the TransactionScope object. Configuration Support: Windows Registry, XML file, and promotabletransaction connection string attribute Valid Values: local: The first connection opened in the TransactionScope object uses a local transaction. promotable: The first connection and all subsequent connections opened in the same TransactionScope object enlist in the same distributed transaction. Default: promotable
SelfTuning	Specifies whether self-tuning is enabled for an ODP.NET application. Configuration Support: Windows Registry, XML file, and Self Tuning connection string attribute Valid Values: 0: Self Tuning is disabled. Used in the registry or XML file. false: Self Tuning is disabled. Used for the Self Tuning connection string attribute. 1: Self Tuning is enabled. Used in the registry or XML file. true: Self Tuning is enabled. Used for the Self Tuning connection string attribute. Default: 1
StatementCacheSize	Specifies the number of cursors or statements to be cached on the database for each connection. This setting corresponds to Statement Cache Size attribute in the connection string. A value greater than zero also enables statement caching. Configuration Support: Windows Registry, XML file, and Statement Cache Size connection string attribute Valid Values: 0 <=n<= the value of OPEN_CURSORS parameter set in init.ora database config file. n is the number to set. Default: 0
StatementCacheWithUdts	Specifies whether or not Oracle UDTs retrieved by executing a SELECT statement are cached along with the statement in the statement cache. This setting affects the memory usage and performance of the application. Configuration Support: Windows Registry and XML file Valid Values: 0: Oracle UDTs are not cached with statements. 1: Oracle UDTs are cached along with statements. Default: 1
ThreadPoolMaxSize	Specifies the default maximum size of worker threads for each available processor in a process. This value may affect the performance of ODP.NET connection creation, command execution timeout, and external procedures (extproc) that use the thread pool. However, unnecessarily increasing thread pool maximum size can also cause performance problems. Configuration Support: Windows Registry and XML file Valid Values: 0 <=n<= int.MaxValue: Allows ODP.NET to reset thread pool maximum size with the value n. The ODP.NET reset operation may be ignored if the value is invalid. For example, if n is less than the number of available processors of the system. In this case, the result is the same as the value -1. -1: Leave the thread pool max size as is. Default: -1 (this registry entry is not created by default) Note that prior to ODAC 2007 or version 11.1.0.6.20, ODP.NET resets the thread pool maximum size to int.MaxValue when the OracleCommand.CommandTimeout property is set to a value greater than 0. This erroneous behavior has been corrected. OracleCommand.CommandTimeout does not change thread pool maximum size.
TraceFileName	Specifies the file name to be used for logging trace information. Configuration Support: Windows Registry and XML file Valid Values: Any valid directory location and file name. Default: c:odpnet2.trc (for .NET Framework 2.0)
TraceLevel	Specifies the level of tracing in ODP.NET. Because tracing all the entry and exit calls for all the objects can be excessive, TraceLevel is provided to limit tracing to certain areas of the provider. Each valid value indicates a possible tracing level. Compounded tracing levels can be obtained by adding the valid values. Configuration Support: Windows Registry and XML file Valid Values: 0: None 1: Entry, exit, and SQL statement information 2: Connection pooling statistics 4: Distributed transactions (enlistment and delistment) 8: User-mode dump creation upon unmanaged exception 16: HA Event Information 32: Load Balancing Information 64: Self Tuning Information 127: All the above Default: 0 Note: ODP.NET does bit-wise checking on the value. When tracing is enabled, logging to the trace file can affect ODP.NET performance. Note: The user-mode dump creation requires dbghelp.dll version 5.1.2600.0 or later.
TraceOption	Specifies whether to log trace information in single or multiple files for different threads. If a single trace file is specified, the file name specified in TraceFileName is used. If the multiple trace files option is requested, a Thread ID is appended to the file name provided to create a trace file for each thread. Configuration Support: Windows Registry and XML file Valid Values: 0: Single trace file 1: Multiple trace files Default: 0
UdtCacheSize	Specifies the size of the object cache for each connection in kilobytes (KB) that ODP.NET uses to retrieve and manipulate Oracle UDTs. Configuration Support: Windows Registry and XML file Valid Values: 0 <=n<= 4194303, n is the number to set. Default: 4096
UDT Mapping	Specifies a mapping between a custom type and an Oracle UDT in the database. The mappings can be specified in configuration files and custom type factories. However, if the mapping is specified in both places, mappings specified in the configuration files takes precedence over mappings specified using custom type factories. Configuration Support: XML file and Custom Type Factory Classes Valid Values: Any valid mapping. Default: none

Windows Registry

Upon installation, ODP.NET creates entries for configuration and tracing within the Windows Registry. Configuration and tracing registry values apply across all ODP.NET applications running in that Oracle client installation. Individual ODP.NET applications can override some of these values by configuring them within the ODP.NET application itself (for example, FetchSize). Applications can also use the .NET configuration files to override some of the ODP.NET Windows Registry values.

The ODP.NET registry values are located under HKEY_LOCAL_MACHINESoftwareOracleODP.NETversion. There is one key for .NET Framework 3.5, and one key for .NET Framework 4 and later.

Note:

32-bit applications running on an x64-based version of Windows use the registry subkey, HKEY_LOCAL_MACHINESoftwareWOW6432node in place of HKEY_LOCAL_MACHINESoftware. If such applications use Oracle Data Provider for .NET (32-bit), then the ODP.NET registry values are located under HKEY_LOCAL_MACHINESOFTWAREWow6432NodeOracleODP.NETversion.

Configuration File Support

For customers who have numerous applications on a computer that depends on a single version of ODP.NET, the Windows Registry settings for a given version of ODP.NET may not necessarily be applicable for all applications that use that version of ODP.NET. To provide more granular control, ODP.NET Configuration File Support allows developers to specify ODP.NET configuration settings in an application config, web.config, or a machine.config file.

If a computer does not require granular control beyond configuration settings at the ODP.NET version level, there is no need to specify ODP.NET configuration settings through configuration files.

The following is an example of a web.config file for .NET Framework 2.0 and later:

The following is an example of app.config for ODP.NET, Unmanaged Driver using .NET Framework 2.0, which sets some additional attributes as well as two UDT type mappings:

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.

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:

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.

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.

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.

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.

• Only NTS authentication is supported. No RADIUS nor Kerberos5 authentication.

• 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.

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'.

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:

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:

• 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%networkadmin

  5. %ORACLE_HOME%ldapadmin

• 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

• 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

• SQLNET.AUTHENTICATION_SERVICES : Supported values are NTS, 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>NetworkAdmin, 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. This is different from ODP.NET, Unmanaged Driver, which supports TraceFileName.

• 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

There is no support for Oracle Advanced Security Option (ASO) encryption. Setting SQLNET.ENCRYPTION_SERVER to required is not supported, resulting in client connection establishment failure. Not setting SQLNET.ENCRYPTION_SERVER or setting it to any other valid value is supported.

ODP.NET, Managed Provider only supports the ASO NTS authentication adapter. The database server must be on Windows, support NTS external authentication, and have SQLNET.AUTHENTICATION_REQUIRED set to true, 1, or on. The ODP.NET, Managed Driver client must utilize NTS external authentication as well. All other ASO NTS connection scenarios will fail since the client and server will not be able to agree on an ASO authentication mechanism.

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

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:

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:

• Oracle Database Net Services Reference for more information on Directory Usage Parameters.

• Oracle Database Net Services Administrator's Guide for more information on Managing Network Address.

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:

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:

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.

edmMappings section

This section can appear only under a <version> section. Any information related to EDM mappings should appear in this section. The following is an example of an 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. Prior to 12c Release 1 (12.1), this section was mandatory for receiving ONS notifications. With 12.1, 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:

Note:

The configFile specified in .NET config should contain the same localport and remoteport values as specified in the ons.config

Oracle Dataaccess Dll Location

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:

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

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.

Oracle Dataaccess Dll

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:appuserproduct12.1.0opmnlogsmyons.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:appuserproduct12.1.0opmnconfssl.wltdefault
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 (#).

Using Oracle Services for MTS with Oracle Data Provider for .NET, Managed Driver

ODP.NET, Managed Driver incorporates Oracle Services for MTS entire enlistment and commit functionality, allowing those functions to execute using managed code. The Oracle Services for MTS recovery service remains unmanaged.

The Oracle.ManagedDataAccessDTC.dll is packaged with the ODP.NET, Managed Driver. This DLL includes the Oracle Services for MTS enlistment and commit functionality and must be deployed with Oracle.ManagedDataAccess.dll for any ODP.NET, Managed Driver applications using distributed transactions. It can run alongside the unmanaged Oracle Services for MTS version if unmanaged data access drivers, such as ODBC, are also using distributed transactions on the same machine.

There are two versions of Oracle.ManagedDataAccessDTC.dll, one for 32-bit .NET Framework and one for 64-bit .NET Framework, installed with ODP.NET, Managed Driver. Both Oracle.ManagedDataAccessDTC.dll are fully managed, but they are platform-specific because the Microsoft Distributed Transaction Coordinator (MS DTC) application programming interfaces are unmanaged code. Thus, ODP.NET, Managed Driver enlistment and commit calls must distinguish whether they are calling 32-bit or 64-bit MS DTC.

The DLLs are located in the following directories:

• For 32-bit .NET Framework: ORACLE_HOMEodp.netmanagedx86

• For 64-bit .NET Framework: ORACLE_HOMEodp.netmanagedx64

Deploy the Oracle.ManagedDataAccessDTC.dll from the directory appropriate for your .NET application's target platform, 32-bit or 64-bit.

Oracle.ManagedDataAccessDTC.dll should not be directly referenced by a .NET application. It will be implicitly loaded by ODP.NET, Managed Driver when using distributed transactions.

Install Oracle Dataaccess Client

The Oracle Services for MTS recovery service does not need to run in the same machine as ODP.NET, Managed Driver. Administrators can specify the host machine name that has a running recovery service in the application's .NET config file.

See Also:

Oracle Services for MTS for Oracle Services for MTS setup of recovery service for ODP.NET, Managed Driver

Oracle Dataaccess Vs Manageddataaccess

Configuration differences between ODP.NET, Managed Driver and ODP.NET, Unmanaged Driver

Table 2-4 lists other configuration differences between ODP.NET, Managed Driver and ODP.NET, Unmanaged Driver.

Power Bi Oracle Data Access Client Not Installed Supported

Table 2-4 Configuration Differences between ODP.NET, Unmanaged Driver and ODP.NET, Managed Driver

Power Bi Oracle Data Access Client Not Installed Windows 10

Feature Category	Difference compared to ODP.NET, Unmanaged Driver
Configuration	Configuration file format is different. See 'Oracle Data Provider for .NET, Managed Driver Configuration.'
Configuration	ConfigSchema.xsd file, shipped with ODP.NET, Managed Driver (when included as part of the schema (XML->Schemas) in Visual Studio) enables app.config intelli-sense.
Configuration	Windows Registry based configuration is not supported
Configuration	Oracle High Availability (HA) & Oracle RAC Load Balancing (RLB) notifications use Oracle Notification Service (ONS). Thus, to use HA or RLB, configure database and client to use ONS, rather than Oracle Database Advanced Queuing (AQ). Note that Continuous Query Notification will continue to use AQ.
Configuration Parameter	Edition is not supported.
Configuration Parameter	CheckConStatus is not supported.
Configuration Parameter	DllPath is not supported.
Configuration Parameter	SatementCacheWithUdts is not supported.
Configuration Parameter	ThreadPoolMaxSize is not supported.
Configuration Parameter	TraceFileName has been replaced by TraceFileLocation.
Configuration Parameter	UdtCacheSize is not supported.
Configuration Parameter	UDT Mapping is not supported.
Connection String	Context Connection is not supported.
Connection String	Enlist=dynamic will behave the same as Enlist=false.
Connection String	LegacyTransactionBindingBehavior setting will be ignored. It will always be set to the default value of 1.
Connection String	Promotable Transaction setting will be ignored. It will always be set to promotable and always support promotions.
Connection String	Statement Cache Purge is not supported.
Connectivity	Connection to Oracle Times Ten Database is not supported.
Performance Monitor	NumberOfStatisConnections performance counter is not supported.
Performance Monitor	Performance monitor category name is 'ODP.NET, Managed Driver'
Provider Types	Provider Types accept (via constructors) and generate (via ToString() methods) only culture-invariant strings
Tracing	Trace file name format is: <process_name>_PID_<process_id>_DATE_<date>_TIME_<time>.trc.
Tracing	Dynamic tracing is enabled by changing the TraceLevel setting in the app/web/machine.config. NOTE: For ASP.NET applications, doing so will recycle the application domain.
Tracing	An eventlog entry is generated indicating where the trace file is created or if ODP.NET, Managed Driver had issues creating one.