Installing ODP.NET for Power BI and checking an Oracle connection
Register the ODP.NET provider with OraProvCfg, then test the Oracle connection and listener.
Power BI connects to Oracle through the Oracle Data Provider for .NET (ODP.NET). This page describes how to register the provider on the workstation, then lists a few commands to check the connection and the listener.
Registering ODP.NET
- Add the assembly to the Global Assembly Cache (GAC):
C:\oracle\product\19.0.0\client_1\<ODP.NET_folder>\OraProvCfg.exe /action:gac /providerpath:C:\oracle\product\19.0.0\client_1\<ODP.NET_folder>\Oracle.DataAccess.dll
- Add the ODP.NET unmanaged client entries to
machine.config:
C:\oracle\product\19.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /providerpath:C:\oracle\product\19.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll
| Option | Purpose |
|---|---|
/action:gac |
Adds the assembly to the GAC |
/action:config |
Writes the provider entries to machine.config |
/force |
Forces the configuration to be written |
/product:odp |
Product: ODP.NET |
/frameworkversion:v4.0.30319 |
Targeted .NET Framework version (4.x) |
Testing the connection
From a command prompt with SQL*Plus available:
sqlplus "<user>/<password>@<host>:1521/<service>"
Checking the Oracle server
On Linux or Unix, the following commands check the processes, the environment and the listener configuration:
ps -ef | grep tnslsnr
ps -ef | grep pmon
env | grep ORA
lsnrctl status LISTENER
cat $ORACLE_HOME/network/admin/tnsnames.ora
Useful queries from SQL*Plus:
select * from v$listener_network;
select name from dba_services;
Auditing connections
The following query summarises, per user, the connections recorded in dba_audit_session, with the first and the last date.
select sys_context ('USERENV','server_host') server_host,du.username,os_username,userhost,terminal,count(*) as nbr ,max(timestamp) lastdate ,min(timestamp) firstdate
from dba_users du
left outer join dba_audit_session das on das.username = du.username
group by du.username,os_username,userhost,das.terminal