Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cgroeneveld
Regular Visitor

Using Oracle 19c ODAC for On-Premise Gateway

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

cgroeneveld_0-1626900154095.png

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.

3 REPLIES 3
cgroeneveld
Regular Visitor

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. 

@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
Super User
Super User

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors