ORA-12514: TNS:listener does not currently know of service requested ..

[Former Member]

Dear All,

SAP is running on Windows 2008 Server. I tried to connect to Oracle database using SQL Plus. First of all I got this error:

ORA-12541: TNS:no listener

so I found files listener.ora and tnsnames.ora. Inside was only one port specified - 1527.

I added description for port 1521 and now I have an error:

ORA-12514: TNS:listener does not currently know of service requested in connect description

My files looks like that:

################
# Filename......: listener.ora
# Created.......: created by SAP AG, R/3 Rel. >= 6.10
# Name..........:
# Date..........:
# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#1 $
################
ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = S11.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = S11)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1527)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1521)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = S11)
      (ORACLE_HOME = D:\oracle\S11\102)
    )
  )

################
# Filename......: tnsnames.ora
# Created.......: created by SAP AG, R/3 Rel. >= 6.10
# Name..........:
# Date..........:
# @(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/TNSNAMES.ORA#1 $
################
S11.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1527)
        )
        (ADDRESS = 
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
       (SID = S11)
       (GLOBAL_NAME = S11.WORLD)
    )
  )

Each time I change files I restart service OracleS11102TNSListener but I still have that error.

lsnrctl status:

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 04-MAR-2011 09:37:11

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=S11.WORLD))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production
Start Date                04-MAR-2011 09:37:00
Uptime                    0 days 0 hr. 0 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\S11\102\network\admin\listener.ora
Listener Log File         D:\oracle\S11\102\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\S11.WORLDipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\S11ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SAPSVR)(PORT=1527)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SAPSVR)(PORT=1521)))
Services Summary...
Service "S11" has 1 instance(s).
  Instance "S11", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Does anyone know how to fix it? Thank you in advance.

[former_member198673]

HI Pawel,

Can you please help me out here. I am also getting the same error like you but may not be 100% similar.

"10901: Database error: ORA-12505: TNS:listener does not currently know of SID given in
connect descriptor . Contact your Business Objects administrator or database
supplier for more information. (Error: WIS 10901) "

I can explain more with you If yu need.

Thanks all in advance!

Thanks,

Mithun S

[Former Member]

Hi,

if you do a simple google search on

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

you will get hundreds of pages about this error that might help you quicker than on here..

Regards

Mark

[Former Member]

yes - hunderds, but noone helped me..

[TomCenens]

Hello

I saw you also posted in Netweaver Administrator but it looks your two questions are related and one is caused by the other.

Port 1527 is the default port so I suggest you remove 1521 out of your tnsnames.ora file.

You should check if your windows account user has environment variable TNS_ADMIN set and if it's the case check to which location on SAPSRV it is pointed at and make sure the sqlnet.ora, listener.ora and tnsnames.ora are the correct.

Check out Composite SAP note ORA-12514 SAP Note Number: 563574 which has good troubleshooting instructions for points you can verify.

Kind regards

Tom

[Former Member]

Thank for answer. I've already removed port 1521, but it didn't help.

sqlplus /as sysdba:

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 14:26:18 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

lsnrctl status:

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 05-MAR-2011 14:23:49

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=S11.WORLD))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Production
Start Date                05-MAR-2011 13:37:24
Uptime                    0 days 0 hr. 46 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\S11\102\NETWORK\ADMIN\listener.ora
Listener Log File         D:\oracle\S11\102\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\S11.WORLDipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\S11ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SAPSVR)(PORT=1527)))
Services Summary...
Service "S11" has 1 instance(s).
  Instance "S11", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

TNS_ADM variable point to "D:\oracle\S11\102\NETWORK\ADMIN"

listener.ora

ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = S11.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = S11)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1527)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = S11)
      (ORACLE_HOME = D:\oracle\S11\102)
    )
  )

sqlnet.ora:

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH = TNSNAMES
#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

tnsnames.ora

S11.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = SAPSVR)
          (PORT = 1527)
        )
    )
    (CONNECT_DATA =
       (SID = S11)
       (GLOBAL_NAME = S11.WORLD)
    )
  )

still the same problem...

[TomCenens]

Did you check the SAP note, for example the LOCAL variable setting, is it present?

How are you logged on by the way, as a Windows Domain User or as a Windows Local User? This can make or break the R3trans -D functionality.

All your Oracle services are running properly in Windows?

[Former Member]

Yes I checked the note. LOCAL variable is not present. I'm trying to play with .ora files now (regarding the note), but without results.

I log in using remote desktop and local windows account 'cr1adm'.

Oracle services:

- OracleJobSchedulerS11 - stopped

- OracleS11102iSQL*Plus - starting (it is in state starting all the time..)

- OracleS11102TNSListener - started

- OracleServiceS11 - started

[Former Member]

We've the clue here:

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 06-MAR-2011 10:45:28

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

Used parameter files:
D:\oracle\S11\102\NETWORK\ADMIN\sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=S11))(ADDRESS=(PROTOCOL=TCP)(HOST=strange_ip)(PORT=1521)))
TNS-12535: TNS:operation timed out

where HOST is not known for me - this is for sure not my IP, even not my country. Also port is set to 1521 - where is it set? In listener.ora and tnsnames.ora is only 1527.

Thanks!

[Former Member]

Did you restart the listener after removing port 1521?

[Former Member]

yes, even whole machine..

[Former Member]

There was a mistake in sqlnet.ora... Instead of

NAMES.DIRECTORY_PATH = TNSNAMES

should be:

NAMES.DIRECTORY_PATH = (TNSNAMES)

it helped with tnsping. Now I've got:

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 06-MAR-2011 14:40:08

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

Used parameter files:
D:\oracle\S11\102\NETWORK\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = SAPSVR) (PORT = 1527))) (CONNECT_DATA = (SID = S11) (SERVICE_NAME = S11) (GLOBAL_NAME = S11)))
OK (0 msec)

but... it doesn't change anything. SQLPlus still throws ORA-12514.

[Former Member]

You will not believe..

I found connection string which was being executed by EHPi:

connect system/******@S11

then I understood that inside my password I had 'at' (@) so connection string looked like that:

connect system/extra@strong@S11

- I changed password and now I can connect using SQLPlus without any errors...

[Former Member]

I feel your pain with that password problem.

Edited by: Chris O'Haire on Mar 7, 2011 4:46 AM

[Former Member]

You may want to remove following from the files and check.

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = SAPSVR)

(PORT = 1521)

)

[Former Member]

Ok, I've had a system backup and SYSTEM user is unlocked again. I think I have proper files, because in Net Manager when I do service test I've got

Attempting to connect using userid:  system
The connection test was successful.

but in SQL Plus:

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

any ideas?

[Former Member]

Set both Listener and TNSNAMES port with same entry and try doing tnsping <SID>. If tnsping is successful then your issue will be resolved.

[Former Member]

When I tried to connect by sqlplus I used 127.0.0.1 or SAPSVR as host string, but your post suggested me to use 'S11' instead and...

The results is:

ORA-28000: the account is locked

user which I tried is SYSTEM. What can I do now?

When I tried by hostname or IP address I've got 'No listener' error.

tnsping s11:

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 04-MAR-2011 12:17:45

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

Used parameter files:
D:\oracle\S11\102\NETWORK\ADMIN\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = SAPSVR) (PORT = 1527))) (CONNECT_DATA = (SID = S11) (GLOBAL_NAME = S11.WORLD)))
OK (0 msec)

[Former Member]

Hi,

have you TNS_ADMIN srt in your environment ??

Mark

[Former Member]

No. Should I set it to D:\oracle\S11\102\NETWORK\ADMIN ?

[Former Member]

Why did you put in another port ? Have you two Databases on the machine ??

Mark

[Former Member]

No. There is only one database, but SQLPlus connect by 1521, am I right? If I don't put this port then I got 'No listener' error.

[Former Member]

Hi,

You do not need an extra port.

Sounds like the format of the files was not correct.

Regenerate them Start => programs files = <Your Oracle system> configuration tools => Net client.

Make sure ou test the connection. Then go back to SQLPLUS and try again.

Mark

[Former Member]

If I regenerate files and set port 1527 like it was at the beginning I've got 'No listener' error.

If I regenerate using port 1521 I've got 'listener does not currently know of service requested..'

[Former Member]

Hi,

It's up to you which port you prefer.

But I think the structure of the files are incorrect E.G open closed brackets and format..

Did it ever work. Can you go back to a saved copy.?

Mark

[Former Member]

I've never used it before. I have to install EHP2 for NetWeaver and I have to specify database system user credentials. It didn't work and I found that I can't connect to oracle.

[Former Member]

OK.

But it is you listener.ora tnsnames.ora

the pair are not working and I think it's due to the format and this extra port you put in, the syntax is not correct hence the

error messages.

Regenerate them with one port, test the connection with the net client tool mentioned earlier.

fingers crossed it will work.

Mark