Using Oracle 19c ODAC for On-Premise Gateway

[cgroeneveld]

I have seen lots of links about how to get oracle data source working and everything points back to using the 12.1c installer as it has the option to default a machine wide config for .NET. This is great unless you want to use the newer ODAC installers for 12.2 and above where oracle has removed the option from the installer to do machine wide configuration.

So here is what is needed after an install of the newer ODAC x64 clients:

Create a config.bat file under the following path <oracleroot>\product\19.0.0\<oraclehome>\odp.net\bin\4

add the following to that bat file

.\OraProvCfg /action:gac /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\odp.net\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:config /product:odp /frameworkversion:v4.0.30319 /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\odp.net\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:config /product:odp /component:oraclepermission /frameworkversion:v4.0.30319 /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\odp.net\bin\4\Oracle.DataAccess.dll"

.\OraProvCfg /action:register /product:odp /component:perfcounter /providerpath:"<oracleroot>\product\19.0.0\<oraclehome>\odp.net\bin\4\Oracle.DataAccess.dll"

find and replace your Oracle Root directory for <oracleroot> and Oracle Home folder for <oraclehome> in that config.bat

Run the config.bat as administrator

The script will register the DataAccess dll in the GAC and add entries to the machine.config file for datafactory and providers

Next we need to make sure that the On-Premise Gateway has rights to read the tnsnames.ora file

We will add the Service Account that your data gateway is configured with to the ORA_<oraclehome>_SVCSIDS local windows group

By default the account you add would be "NT Service\PBIEgwService" from the local computer. If you are using a custom domain account you would use that instead.

Once this is completed and you have put a tnsnames.ora file in the "<oracleroot>\product\19.0.0\<oraclehome>\Network\Admin" you can now restart the On-Premise DataGateway Service through powershell / services.msc / sc.exe / Gateway UI

Restart-Service -Name PBIEgwService

After all this the data gateway should be able to use the installed ODAC driver.

Rinse and Repeat if you have a cluster of Data Gateways.

Hope this is helpful for others running into the same issues as I did and finding the documentation lacking by MS and Oracle.

[cgroeneveld]

One option is to use Environment variable of "TNS_ADMIN" and point it to a UNC path to which you host / update the TNSNAMES.ora file. I don't know what would happen though if there were any network disruptions and the gateway servers couldn't access the remote location. I would guess you would get intermittant failures which may not be acceptible. 

[lbendlin]

@cgroeneveld   I think if the gateway cluster members cannot see the local network then they cannot see the on premise Oracle databases either. So this would be an acceptable limitation. Thank you for the idea.

[lbendlin]

Interesting.  Our 12.2 installer still allows for machine wide .Net configuration. We always checked that thoughtlessly, I didn't know it would actually make a difference.

Having to maintain a TNSNAMES.ORA file across dozens of gateway cluster members is a royal pain though.  Any thoughts on how to improve that?