Description:
What are the prerequisites to connect from erwin DM to an Oracle DBMS?
Solution:
For databases which are connected natively (e.g. Oracle, SQL Server, Sybase, DB2, etc.) erwin first checks for Client Connectivity Software. Specific to Oracle, erwin DM needs to find OCI.DLL which is Oracle's gateway DLL to allow third party applications (e.g. erwin DM) to connect to Oracle. erwin DM first looks at the following:
-
OCI.DLL Path: The connectivity process goes to the Path to look for OCI.DLL under the \bin folder in Oracle Home. If it is not found using the path, it gives the error (Ora7nt.dll not found) which means it cannot find Oracle Client Connectivity Software. In case there are multiple Clients of Oracle installed, the first part is to see which Oracle Client is mentioned first in the Path as erwin is looking in that to find OCI.DLL.
-
Bit-size Compatibility: erwin DM and Oracle Client Connectivity, both should either be 32-bit or 64-bit (Oracle database bit-size does not matter). So, if your installation have both, Oracle 32-bit and 64-bit clients, and your erwin is 64-bit, then make sure that the 64-bit Client is first in the path. Oracle Tools, e.g. SQL*Plus, SQL Developer connect directly and do not need to reference this OCI.DLL, so the path order is not important for them. It is a requirement for third party applications, like erwin, so make sure the referenced client is first in the path. This is to clarify why sometimes SQL Developer may be able to connect, but not erwin DM.
-
Host and TNSNames.ORA: erwin goes on to the \bin folder to search for the OCI.DLL from where it gets the information for Hostdef. So, if the path is correct and compatibility matches, it finds the Hostdef information. Once this is accounted for, the connectivity process then concatenates other information, e.g. User Name, Password, Connect String and passes that to Oracle process which then connects the same way as it does for SQL*Plus. So, another way to check the connectivity is to make sure that you can connect using SQL*Plus as erwin DM also uses the same process. Of course, it will also check whether the Oracle database services, listener are running and also that the reference in TNSNames.Ora file is pointing to the correct Connect String.
Once this is taken care for, you should be able to connect successfully from erwin DM.
