0

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?

enter image description here

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
Improve this question
2
  • Shouldn't it be jdbc:oracle:thin:@TNS_ENTRY? Also ensure that TNS_ADMIN environment variable is set to point to tnsnames.ora Commented Apr 21, 2023 at 18:07
  • using jdbc:oracle:thin:@TNS_ENTRY gives error "Test Connection Failed." TNS_ADMIN is set to path "C:\Oracle\Win64\product\11.2.0\client_2\network\admin\tnsnames.ora". Also tried with "C:\Oracle\Win64\product\11.2.0\client_2\network\admin", but it also does not work. Commented Apr 24, 2023 at 7:49

2 Answers 2

Reset to default
0

This may be access issue.
Login to the machine using an user which is running informatica. Use sqlplus and test the connection to the Oracle DB. If you can its fine, then check if you have defined all details in tnsnames.ora and mentioned proper connection info like user pass in infa connection.

If you cant, check if you can ping the oracle DB host, if you cant pls connect to oracle DB admin to open up port or whitelist the ip etc. so informatica can connect to Oracle DB.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

After login: > tnsping xe Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (source_route=yes) (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=XE))) OK (0 msec) > sqlplus INFA_DOM/INFA_DOM@localhost:1521/xe can connect; can see some sample data So I assume that tnsnames.ora is correct.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.