External database connection fails with application server with ORA-12164

[Former Member]

Hi Experts,

External db is trying to coonect to application servers.

But terminating with ORA-12154.

Anu

[Former Member]

Hi,

This is TNS setting problems.

Check the followings

1. In the DB Server System:

a. Check the entry in listener.ora file in <ORACLE_HOME>/network/admin directory is given properly.

b. Check the listener status using the following command in the command prompt

lsnrctl status

If the listener is down then start it up

lsnrctl start

2. In the DB Client system i.e Application Server

a. Environment variable ORACLE_HOME is set properly or not

b. Environment variable TNS_ADMIN is set properly or not

c. Check tnsnames.ora file exist in the directory specified by the environment variable TNS_ADMIN.

d. Connection parameter (ORACLE_SID, host, port) is properly given in tnsnames.ora file or not.

e. Check the TNS entry in tnsnames.ora file is working or not using the following command

tnsping <tns entry name in tnsnames.ora file>

f. Check manually the DB connection from application server i.e DB Client server to DB server is working or not using the following command

sqlplus <db_user_id>/<password>@<SID>

If it is fail then check listener.log file in Database server and check the error.

3. This error may also come from the network. So check proper entries in hosts file in client and server machine. And test the connection using the following command

ping <database host name>

and see the result

Thanks

Manoj

[Former Member]

Hi Abhijit,

tnsnames.ora is same as that of CI.

Also NAMES.DIRECTORY_PATH does not exist in CI also.

Thanks,

Anu

[Former Member]

Hi,

Check the following link.

[http://www.dbmotive.com/oracle_error_codes.php?errcode=12154]

Was there any change to IP address or hostname?

Manoj

[former_member204746]

check if you have set TNS_ADMIN as an environment variable.

you say that TNSNAMES,ORA file is identical as of your CI? Then, you also need to check that file SQLNET.ORA is also identical.

[Former Member]

Hi,

In dev_w1 we get such error

create_con (con_name=SKSA)

B New connection 4 created

B Connect to SKSA as SAP_BW_ICD with SKSA

C Client NLS settings:

C Connecting as SAP_BW_ISD/<pwd>@SKSA on connection 2 (nls_hdl 0) ... (dbsl 700 250407)

C Nls CharacterSet NationalCharSet C EnvHp ErrHp ErrHpBatch

C 0 UTF8 1 0x113e8fbd0 0x113ea8108 0x113ea79b8

C Attaching to DB Server SKSA (con_hdl=2,svchp=0x11577bd88,srvhp=0x11577c008)

C *** ERROR => OCI-call 'OCIServerAttach' failed with rc=12154

[dboci.c 4313]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 11044]

B ***LOG BY2=> sql error 12154 performing CON [dbds#1 @ 1044] [dbds 1044 ]

B ***LOG BY0=> ORA-12154: TNS:could not resolve the connect identifier specified [dbds#1 @ 1044] [dbds 1044 ]

Thanks,

Anu

[former_member220071]

Hi ,

A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured.

- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

Abhijeet

[former_member220071]

Hi Anu ,

Can you please give detail error log.

Abhijeet