Qlik Replicate : issue while connecting Oracle as a source

[cbhajare444]

While doing testdrive of this tool, I am trying to connect to the Oracle database as a source, but getting the following error:

I checked for the tnsnames.ora and its path in the environmental variables, tried to connect from sqlplus and sql developer .Everything looks fine .But I'm still getting this error.

(I checked and followed the following documentation as well)

https://help.qlik.com/en-US/replicate/November2020-SR1/Content/Replicate/Main/Tutorial/AddSourceOra_...

can you please suggest what needs to be done in order to connect to the Oracle source database.

[lyka]

Hello,

What format are you using for the Connection string? Are you also trying to connect to ASM?

Have you tried with a different format?

You can type the connect string in any Oracle format, for example:
//host:port/service name

Where:

host: This is the name or IP address for the computer with the Oracle database that you are
using. For example, johnboy_W7 or 255.255.255.0.

port: (optional) This is the TNS Listener Port number for the computer with the Oracle
database that you are using. If you do not enter a port number the default Oracle TNS Listener
port is used.

service name: (optional) This is the service name for the computer with the Oracle database
you are using. If you do not enter a service name the default service name is used.

You can also enter an Oracle Net keyword-value pair. For example:
"(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=dlsun242) (PORT=5521))
(CONNECT_DATA=(SERVICE_NAME=bjava21)))"

Thanks,

Lyka

[mastercheng1980]

we are facing the same exact problem. we are using ASM. and it say failed connecting to ASM mycompanydomain.com:1625/+ASM

[Bill_Steinagle]

Hello,

Thank you for your post to the Replicate Forums. For issues connecting to Oracle Source please ensure you have the Oracle 64 Bit Full client installed along with confirming the connection to this Oracle Source can be made using the SQL Plus program within the Oracle client to this Source from the Replicate Server. For ASM access see the link below and ensure the ASM parameters are defined per the Prerequisites. Let me know if you can connect via the Oracle SQL Plus from the Replicate Server

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/Oracle/OralcleDB_sourcebinary_A...

Thank you!

Bill

[mastercheng1980]

Thank you Bill.

since you are linking a QR help file, https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedReplicateHDD/Oracl... did mention to install "Oracle Instant Client". So which one of you is right ? Full Client or Instant Client ?

[Bill_Steinagle]

Hi,

The Full Client has the SQL Plus component for testing outside of Replicate but both clients should be ok for the Replicate environment to connect. I suggest the Full Client as it has added options when debugging connectivity issues from the Server. Hope this helps and confirms.

Bill

[mastercheng1980]

Thanks Bill. Got it ! Full Client is for debugging purposes. 

Anyways, we are still encountering the same problem. Do we need to setup the TNSNAME.ora ? I don't see that Qlik Replicate is trying to use the TNSNAME.ora ? 

We setup TNSNAME.ora as

CJDBASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mycompanydomain)(PORT = 1625))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

But i don't think Qlik Replicate is using this right ? I don't see where we input CJDBASM. The ASM connection string is mycompanydomain.com:1625/+ASM. we tried mycompanydomain.com:1625/CJDBASM for ASM connection also cannot.

[Bill_Steinagle · Accepted solution]

Hi,

Can you share upload of the error shown and also can you update the below connection example below and use in your connection string to test? The tnsping to this IP or Machine.domain as noted in your update resolves to the ASM server? Is it defined in the listener to the connection? 

Here is example please change to your site specifics.

(DESCRIPTION=(FAILOVER = OFF)(LOAD_BALANCE = OFF)(TRANSPORT_CONNECT_TIMEOUT = 60)(CONNECT_TIMEOUT = 60)(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = my_host_name)(PORT = xxxx)))(CONNECT_DATA=(SERVICE_NAME = my_service_name)))

Thanks!

Bill

[Bill_Steinagle]

Hi,

Just wanted to follow up with you on the post to this request. When you installed the Full Oracle Client did you also cycle the Replicate Server/Service? Let us know.

Thanks!

Bill