oracle - SSMA unable to find specified provider

Question

i've installed the full oracle client 12.2.0 and i'm trying to migrate some db from oracle to sql server with SSMA, but when i try to connect to oracle i get the folowing error:

Unable to find specified provider.
Compatible Oracle Data Access Connectivity libraries were not found on the machine.

I've already set the environment's variables HOME_ORACLE and the PATH was set by default during the installation, so i exclude that the problem is there. I've also searched about the ODAC, and all hints remind me on oracle istant provider, but since i've installed the client with an administrator installation (full) i thought that the installation would covered them too. Any suggestion?

EDIT: i tried to login with a standard mode and also with the tnsnames mode, but the results is the same

Answer 1

Looks like there is a bug in the Oracle Universal Installer 12.2, it does not register the ODP.NET components. You can run script below (modify folder names according to your system) in order to configure them properly.

set Oracle_x64=c:oracleproduct12.1Client_x64odp.net
set OraProvCfg_x64=%Oracle_x64%in4OraProvCfg.exe

set Oracle_x86=c:oracleproduct12.1Client_x86odp.net
set OraProvCfg_x86=%Oracle_x86%in4OraProvCfg.exe

set TNS_ADMIN=C:oracle
etworkadmin



IF EXIST "%Oracle_x64%managedcommonOracle.ManagedDataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%managedcommonOracle.ManagedDataAccess.dll
IF EXIST "%Oracle_x64%managedPublisherPolicy4Policy.4.121.Oracle.ManagedDataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%managedPublisherPolicy4Policy.4.121.Oracle.ManagedDataAccess.dll
IF EXIST "%Oracle_x64%managedPublisherPolicy4Policy.4.122.Oracle.ManagedDataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%managedPublisherPolicy4Policy.4.122.Oracle.ManagedDataAccess.dll

IF EXIST "%Oracle_x64%managedcommonOracle.ManagedDataAccess.dll" "%OraProvCfg_x64%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x64%managedcommonOracle.ManagedDataAccess.dll /set:settingsTNS_ADMIN:%TNS_ADMIN%
IF EXIST "%Oracle_x86%managedcommonOracle.ManagedDataAccess.dll" "%OraProvCfg_x86%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x86%managedcommonOracle.ManagedDataAccess.dll /set:settingsTNS_ADMIN:%TNS_ADMIN%

rem Oracle.ManagedDataAccessDTC was used in earlier versions, not used anymore
"%OraProvCfg_x64%" /action:ungac /providerpath:Oracle.ManagedDataAccessDTC

IF EXIST "%Oracle_x64%in2.xOracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%in2.xOracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy2.xPolicy.2.102.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy2.xPolicy.2.102.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy2.xPolicy.2.111.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy2.xPolicy.2.111.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy2.xPolicy.2.112.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy2.xPolicy.2.112.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy2.xPolicy.2.121.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy2.xPolicy.2.121.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy2.xPolicy.2.122.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy2.xPolicy.2.122.Oracle.DataAccess.dll

IF EXIST "%Oracle_x64%in4Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%in4Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy4Policy.4.112.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy4Policy.4.112.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy4Policy.4.121.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy4Policy.4.121.Oracle.DataAccess.dll
IF EXIST "%Oracle_x64%PublisherPolicy4Policy.4.122.Oracle.DataAccess.dll" "%OraProvCfg_x64%" /action:gac /providerpath:%Oracle_x64%PublisherPolicy4Policy.4.122.Oracle.DataAccess.dll

IF EXIST "%Oracle_x86%in2.xOracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%in2.xOracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy2.xPolicy.2.102.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy2.xPolicy.2.102.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy2.xPolicy.2.111.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy2.xPolicy.2.111.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy2.xPolicy.2.112.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy2.xPolicy.2.112.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy2.xPolicy.2.121.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy2.xPolicy.2.121.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy2.xPolicy.2.122.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy2.xPolicy.2.122.Oracle.DataAccess.dll

IF EXIST "%Oracle_x86%in4Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%in4Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy4Policy.4.112.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy4Policy.4.112.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy4Policy.4.121.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy4Policy.4.121.Oracle.DataAccess.dll
IF EXIST "%Oracle_x86%PublisherPolicy4Policy.4.122.Oracle.DataAccess.dll" "%OraProvCfg_x86%" /action:gac /providerpath:%Oracle_x86%PublisherPolicy4Policy.4.122.Oracle.DataAccess.dll

Update

According Oracle Doc ID 2272241.1 this seems to be by intention:

Applies to:
Oracle Data Provider for .NET - Version 12.1.0.2 and later
Microsoft Windows x64 (64-bit)
Microsoft Windows (32-bit)

Symptoms
When installing the 12.2 Oracle Data Provider for .Net, the provider is not automatically registered in the Global Assembly Cache (GAC).

Changes
Moved to the 12.2 version of the Oracle Provider for .Net

Cause
This is the expected behavior. Since so many environments are using multiple versions of ODP.Net side by side, starting with 12.2, the installer does not automatically register the ODP.Net provider in the GAC by default.

Solution
Manually register the dll into the GAC as outlined in Doc ID 1169890.1: HOWTO: Example use of OraProvCfg.exe to add/remove Oracle assemblies from the Global Assembly Cache

However, I would consider this as a bug. It is quite difficult to run multiple versions of ODP.Net side by side, because the version of Oracle.DataAccess.dll has to match exactly the version of the Oracle Client. Installing more than one Oracle Client (i.e. more than one each for 64-bit and 32-bit) typically causes a lot of problems and is rather uncommon.