I have a problem with connecting (actually at the pre installing step) Informatica to the Oracle DB running on Docker. Because of my lack of experience and also knowledge I don’t know what may be the problem.
Has anyone had similar problem to mine?
Firstly I thought this could be problem, because I did not configure tnsnames.ora on my pc, on which Informatica will be installed, but after doing that - connection from cmd was possible, but Informatica still don't get it right.
So I decided to connect via custom jdbc url
jdbc:informatica:oracle:TNSNamesFile=PATHPATHPATH\tnsnames.ora;TNSServerName=XE
and then
jdbc:informatica:oracle://localhost:1521;ServiceName=xe
But it also doesn't work.
Oracle on Docker
listener.ora Network Configuration File:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/product/21c/dbhomeXE) (PROGRAM = extproc) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) ) )
tnsnames.ora Network Configuration File:
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
LISTENER_XE = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
XEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )
EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )
sqlnet.ora:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
Main pc, where Oracle 11g is and Informatica will be installed
tnsnames.ora
XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) )
sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
This works:
sqlplus INFA_DOM/INFA_DOM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))
tnsping (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=XE))) 10
**tnsping xe**
but
tnsping -T C:\Oracle\Win64\product\11.2.0\client_2\network\admin\tnsnames.ora xe 10
gives
Used parameter files:
C:\Oracle\Win64\product\11.2.0\client_2\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
In case this problem is something that keeps you up at night, a solution has been found Informatica doesn't work with all versions of Oracle, the desktop one I guess is no longer supported in the name of the cloud solution. Oracle on the client machine must have the right version to make it work with Informatica, but what is also important and what was the solution to the problem - the image docker version, also must be compatible (client: 11 will not work with 21 server) so if your client version is 11, then download oracleinanutshell/oracle-xe-11g from docker