How to Alias Connection String of a Oracle from TNSNAMES

[malapat1]

Hi Team,

we are using Oracle Source Endpoint in Qlik Replicate, and I want to specify the alias of the connection string which is specified in the tnsnames file in the server.  

Can you please help me what would be the syntax needs to be used in Oracle endpoint connection string. 

Eg: 

tnsnames.ora:

oracle_src1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host.unix.anz)(PORT = 4422)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracle_svc)))

I would like to specify "oracle_src1" in the oracle endpoint of qlik replicate. 

Thanks & Regards,

Ravi

[Dana_Baldwin]

Hi @malapat1 

You can simply use the tnsalias name itself, or enter the connect string information:

1. Type the Oracle Connection String for the Oracle database you want to work with. 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)))"

   Information note

   When working with a Multitenant environment, the connection string should specify a specific PDB.

   Copied from: Setting general connection properties #Setting general connection properties | Qlik Replicate Help

   Thanks,

   Dana

[malapat1]

Hi Dana, 

Thank you for the response.

Below Connection strings are working from Endpoint: 

format1:

(DESCRIPTION=(FAILOVER = OFF)(LOAD_BALANCE = OFF)(TRANSPORT_CONNECT_TIMEOUT = 60)(CONNECT_TIMEOUT = 60)(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 4422)))(CONNECT_DATA=(SERVICE_NAME = servicename)))

format2:

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =hostname)(PORT = 4422)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =servicename)))

But we would like to establish the connection using secure port 2484. and also configured the oracle wallet and SSL Certs. and tried using below connection string and getting the error :

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST =hostname)(PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =servicename)) (SECURITY = (SSL_SERVER_CERT_DN =CN=host,OU=UNIX,O=ORG,L=LOC,ST=Vic,C=AU)))

and getting below error :

• SYS-E-HTTPFAIL, Incorrect thread connection string: incorrect thread value 0.
• SYS,GENERAL_EXCEPTION,Incorrect thread connection string: incorrect thread value 0

But I am able to connect to the oracle using same connection string from the replicate server using tnsping.

and tried another way like created alias for the connection string in tnsnames and able to do the tnsping from the server,  and in Oracle Endpoing given Alis name instead of  connection string and getting below error.

• SYS-E-HTTPFAIL, ORA-28759: failure to open file.
• SYS,GENERAL_EXCEPTION,ORA-28759: failure to open file
  

Thanks,

Ravibabu