ORA-12154 TNS could not resolve the connect identfier

[Former Member]

Dear all ,

I am trying to run SQLPLUS comand

C:\Users\ssmadm>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 21 11:12:19 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: system

Enter password:

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

All services are running fine &please suggest

[Reagan · Accepted solution]

Hello

Did you check the TNS_ADMIN environment variable for ssmadm user ?

If yes, check the tnsnames.ora file in that location.

You probably need to add the below entry to that file.

LISTENER_SSM.WORLD =

  (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = MUNTFMSTSTBO)(PORT = 1537))

Cheers

RB

[Former Member · Accepted solution]

Hi All,

below the path i have set env and system varibales but still the error is same

PATH=D:\usr\sap\SSM\SYS\exe\uc\NTAMD64;D:\oracle\SSM\11202\bin;oracle/SID/11202/network/admin/tnsnames.ora

Yes this is a windows machine

TNS_admin env alaredy exsits and i have copied in TNSnames file above suggested.

Please suggest how to fix this issue

[Former Member · Accepted solution]

Hi,

Please set as follows..

PATH=D:\usr\sap\SSM\SYS\exe\uc\NTAMD64;D:\oracle\SSM\11202\bin;D:\oracle\SSM\11202\network\admin;$PATH

Your env variable is incorrect...

Just set as above and try.

You also need to restart the server to make the changes effective.

THanks and Regards,

Vimal

[Reagan · Accepted solution]

Hello

Provide the contents of tnsnames.ora file present from the below two locations:

D:\oracle\SSM\11202\network\admin

TNS_ADMIN

Cheers

RB

[Former Member · Accepted solution]

HI Vimal,

Now i am  able to connect the SQL plus

C:\Users\ssmadm>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 21 14:37:09 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

It means Databse conected right.

Now the issue is I am not able connect the database through ODBC.Please find the scrren shot.

Now i am checking through Netmanager configuration there is no service maintained  but i am not able create the service as well.

Please find the attachment and guide me .

Thanks

[Former Member · Accepted solution]

Hi,

Please check on the below steps and see whether it helps.

1.Go to Control Panel-> Administrator Tools and open "Data Sources (ODBC)" on the client machine.

2.Select the DSN for Oracle and click on Configure button.

3.Provide appropriate TNS Service Name in the "TNS Service Name" field in Oracle ODBC Driver Configuration window and click on OK.

Thanks and Regards,

Vimal

[Former Member · Accepted solution]

Hi Vimal,

Same thing I am doing but same error i am facing ,yestrday its worked perfect for me .

As per the the above Netmanger  screen shot its showing in service naming option there no service mainteined ,but here i am unbale to create as well.

Please suggest is their any other way to create service naming

[Former Member]

As <sid>adm user check TNS_ADMIN environment variable that point you to directory where tnsnames.ora should be. Is this file that you attach above?

Regards,

Roman

[Former Member]

Hi,

    Can you please attach the listener,tnsnames file ?

Please check whether listener service is running or not.. If not,try to start and check..

Also check whether hostname is pingable...

Thanks and Regards,

Vimal

[Former Member]

Hi Vimal,

I am able to ping the sytem.

Listner services also started .

Please find the attchemnt and suggest

Thanks

tnsnames.ora.zip

listener.ora.zip

[Former Member]

Please go through the below link too.

http://www.dba-oracle.com/t_ora_12154_tns_resolve_service_name.htm

[Former Member]

Hi,

I already gone through this document.

Please suggest me is their any otherway to trouble shoot.

[former_member188883]

Hi ,

Please check whether following entry exists in hosts file

127.0.0.1 localhost

If yes, remove it.

After this restart listener service.

Check tnsping <SID> results.

Regards,

Deepak Kori

[Former Member]

Hi,

Can you please check whether sqlnet.ora file exists in your /oracle/SID/11202/network/admin location???

You can try this also...it Might work for you.

1. Copy the files in /sapmnt/SID/profile/oracle  to /oracle/SID/11202/network/admin and see whether it works.

2. If not, please try to do it vice versa..

Always have a backup before changing.

3.Check if the environment variable 'PATH' has the path for tnsnames.ora file specified.

Sample Value of Environment Variable PATH:

/oracle/SID/11202/network/admin;$PATH

Make that the path is a valid path and there is no space.

Thanks and Regards,

Vimal

[Former Member]

Removed Local Host 127.0.0.1

Results

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 21-FEB-2
013 13:35:47

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
\\MUNTFMSTSTBO\sapmnt\SSM\SYS\profile\oracle\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP
.WORLD) (PROTOCOL = TCP) (HOST = MUNTFMSTSTBO) (PORT = 1537))) (CONNECT_DATA = (
SID = SSM) (GLOBAL_NAME = SSM.WORLD)))

[Former Member]

Hi Vimal,

Evironment varible is set to :

PATH=

D:\usr\sap\SSM\SYS\exe\uc\NTAMD64

can i change this one to above suggested /.

Please suggest

[former_member188883]

Hi,

Your listener is able to connect to database as per results from tnsping.

Could you check the issue and revert back.

Regards,

Deepak Kori

[Former Member]

Hi Vimal,

Network.admin location SQLnet.ora alredy exist

below the details

################

# Filename......: sqlnet.ora

# Created.......: created by SAP AG, R/3 Rel. >= 6.10

# Name..........:

# Date..........:

# @(#) $Id: //bas/720_REL/src/krn/tpls/ora/SQLNET.ORA#1 $

################

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

# 05.01.06 unsorported parameter now

#NAME.DEFAULT_ZONE = WORLD

# 05.01.06 set the default to 10

SQLNET.EXPIRE_TIME = 10

SQLNET.AUTHENTICATION_SERVICES = (NTS)

# 05.01.06 set to default

#TCP.NODELAY=YES

# 05.01.06 set to 32768

DEFAULT_SDU_SIZE=32768

[Former Member]

Hi Deepak,

Please suggest how can i trouble shoot this thing ?

any clues and ideas

[former_member188883]

Hi,

Please try sqlplus "/as sysdba"

paste the results

Regards,

Deepak Kori

[Former Member]

Hi,

Try to set the env as

PATH =/oracle/SID/11202/bin;/oracle/SID/11202/network/admin;$PATH

Try to set in both siadm as well as system variable environmental variables

Then restart the system and try again.

Please check and provide your feedback.

Thanks and Regards,
Vimal

[Former Member]

:\Users\ssmadm>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 21 14:12:19 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:

ORA-12560: TNS:protocol adapter error

Enter user-name: system

Enter password:

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

[Reagan]

Try to set the env as
PATH =/oracle/SID/11202/bin;/oracle/SID/11202/network/admin;$PATH
Try to set in both siadm as well as system variable environmental variable

This is a Windows machine.

Cheers

RB

[Reagan]

Hello

Check whether the listener is configured properly and also the contents of tnsnames.ora file.

Check the TNS_ADMIN parameter.

An R3trans -d output will provide you good information.

Cheers

RB

[Former Member]

Hi RB,

This is java system,TNS listner services are running fine.

where can i check the TNS_ADMIN parameter.

Please suggest