TNS issue on cloned system scale out App Server

[Former Member]

Hi all

I am creating a copy of a live CI and app server but am having trouble with TNS on the cloned app server

I'm getting this error from R3trans -d:

4 ETW000  [    dbsloci.,00000]  *** ERROR => CONNECT failed with SQL error '12547'                     11398  7.450822

4 ETW000  [     dev trc,00000]  -->oci_get_errmsg (con=0, rc=12547)                                     1210  7.452032

4 ETW000  [     dev trc,00000]     OCIErrorGet() -> SQL error code: 12547 116  7.452148

4 ETW000  [     dev trc,00000]  ORA-12547: TNS:lost contact

4 ETW000                                                                                                  42  7.452190

4 ETW000  [     dev trc,00000]  ocica() -> SQL error code 12547,12547                                     32  7.452222

4 ETW000  [     dev trc,00000]  SAPUSER error or connect error 99=DBSL_ERR_DB, oerr=12547. 11897  7.464119

4 ETW000  [     dev trc,00000]     DbSlConnect(con=0) -> orc=0, 99=DBSL_ERR_DB 35  7.464154

4 ETW000  [    dblink ,00000]  ***LOG BY2=>sql error 12547  performing CON                            34133  7.498287

4 ETW000  [    dblink ,00000]  ***LOG BY0=>ORA-12547: TNS:lost contact                                   35  7.498322

The TNS configuration I thought looked OK.

The listener is running on the source and I have updated tnsnames.ora, listener.ora  and sqlnet.ora files - they are copies of a live environment

I have updated these files with appropriate new CI host details and with updated "Invited_hosts" in sqlnet.ora

This is an app server so there is unfortunately no tnsping utility - it only has the oracle instantclient to connect to DBCI server.

Has anyone else had this one?

[thiago_cavalheiro · Accepted solution]

Hello Jamie,

My first recommendation is for you to check on the $ORACLE_HOME/install/make.log whether the RDBMS was properly linked. If there is any error, try to relink it as per note 97953. Also, check if your $ORACLE_HOME/bin/oracle has the proper permissions. Note 583861 can help with that.

Best regards,

Thiago

[Former Member · Accepted solution]

Hi,

Please check the below note .

Note 505630 - Composite SAP Note ORA-12547

Are you able to execute tnsping on the application server, successfully?

Is the DB server host name resolved from the application server.

Please provide the output from env command for sidadm user. Check that PATH variable has bin directory of oracle home (e.g. /oracle/SID/112_64/bin) and also check TNS_ADMIN environment variable.

Check 11.2 instant client (see SAP note 819829).

819829 - Oracle Instant Client Installation and Configuration on Unix

Thanks

Rishi abrol

[Former Member · Accepted solution]

User Environment for sidadm on this remote app server:

> env

HOME=/home/SIDadm

PATH=/oracle/SID/112_64/bin:/opt/IBMJava2-amd64-142/bin:.:/home/SIDadm:/usr/sap/SID/SYS/exe/run:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/opt/nettladm/bin:/usr/bin/X11:/usr/contrib/bin/X11:/usr/vue/bin:/opt/soeg/bin:/opt/perf/bin:/opt/upgrade/bin:/opt/hpnp/bin:/opt/lrom/bin:/usr/local/bin:/opt/openssh/bin:/opt/openssh/sbin:/opt/fcms/bin

LOGNAME=SIDadm

SHELL=/bin/csh

TERM=xterm

SAPSYSTEMNAME=SID

DIR_LIBRARY=/usr/sap/SID/SYS/exe/run

SHLIB_PATH=/usr/sap/SID/SYS/exe/run:/oracle/client/11x_64/instantclient

JAVA_HOME=/opt/IBMJava2-amd64-142

THREAD=NOPS

dbms_type=ORA

dbs_ora_tnsname=SID

dbs_ora_schema=SAPDAT

ORACLE_SID=SID

DB_SID=SID

ORACLE_BASE=/oracle

TNS_ADMIN=/sapmnt/SID/profile/oracle

NLS_LANG=AMERICAN_AMERICA.UTF8

SAPDATA_HOME=/oracle/SID

ORACLE_HOME=/oracle/SID/112_64

[Reagan · Accepted solution]

Hello

TNS_ADMIN=/sapmnt/SID/profile/oracle

Is the tnsnames.ora file in this location configured correctly ?

Also what is the output when you run R3trans -d ?

Regards

RB

[Former Member · Accepted solution]

The R3trans -d output is in the original message. In fact I have also run R3trans -d -t4 for full trace options too and nothing is clear.

Note this is not an App Server in fact, but a 2nd server for an export with Dist.Mon -- so there are no SAP profiles on this box yet (i.e. /sapmnt/SID/profile) - only the ORACLE bit (/sapmnt/SID/profile/oracle for TNS_ADMIN).

However I don't see any reason why an SAP profile is required for a R3trans remote connection - though I invite comment there.

I would love to have tnsping setup here to use --- what is the best way to test connections from remote app servers? The R3trans log even with full trace on isn't very revealing.

[Former Member · Accepted solution]

Ahh there is a 2nd system on the server I'm connecting to --- I'm looking at creating a 2nd listener with a bespoke alias to connect to...

[Former Member · Accepted solution]

OK resolved.

Now I did several things to re-set this up.

I recreated source listener as LISTENER_SID

and restarted (lsnrctl start LISTENER_SID)

I also disabled SQL*Net settings as this is a test setup:
*SQLNET.CRYPTO_SEED
*tcp.validnode_checking

Now connection from 2nd server with just a SAP kernel (no SAP profiles) and TNS_ADMIN location files is working like a beauty. Thanks for the responses SCN. As always the dialogue was invaluable to get me thinking, even if the solution didn't come until the end.