topic Re: Qlik Replicate : issue while connecting Oracle as a source in Qlik Replicate

Qlik Replicate : issue while connecting Oracle as a source

cbhajare444 — Tue, 06 Apr 2021 19:13:03 GMT

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_sample.htm

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

Re: Qlik Replicate : issue while connecting Oracle as a source

lyka — Fri, 09 Apr 2021 22:26:58 GMT

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

Re: Qlik Replicate : issue while connecting Oracle as a source

mastercheng1980 — Wed, 05 Oct 2022 13:12:33 GMT

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

Re: Qlik Replicate : issue while connecting Oracle as a source

Bill_Steinagle — Wed, 05 Oct 2022 13:29:26 GMT

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_AdvProps.htm

Thank you!

Bill

Re: Qlik Replicate : issue while connecting Oracle as a source

mastercheng1980 — Thu, 06 Oct 2022 07:17:53 GMT

Thank you Bill.

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

Re: Qlik Replicate : issue while connecting Oracle as a source

Bill_Steinagle — Thu, 06 Oct 2022 12:21:59 GMT

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

Re: Qlik Replicate : issue while connecting Oracle as a source

mastercheng1980 — Fri, 07 Oct 2022 04:20:23 GMT

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.

Re: Qlik Replicate : issue while connecting Oracle as a source

Bill_Steinagle — Fri, 07 Oct 2022 11:18:15 GMT

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

Re: Qlik Replicate : issue while connecting Oracle as a source

Bill_Steinagle — Tue, 08 Nov 2022 19:57:47 GMT

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