ORA-12505: TNS:listener does not currently know

[big]

Hi,

DB 11.2.0.4 on AIX

I connect to mydb from client 19.3.0.0.0 on a Linux

$tnsping mydb

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = server.com) (PORT = 1533))) (CONNECT_DATA = (SID = mydb)))

OK (10 msec)

But when :

$sqlplus user/pass@mydb

I receive:

ERROR:

ORA-12505: TNS:listener does not currently know of SID given in connect

descriptor

Thanks.

[ErmanArslansOracleBlog]

1)send me the lsnrctl status output (for the related listener)

2)send me the tns entry named mydb (mask the sensitive info befor doing a copy/paste to here..for security reasons..)

3)send me the following outputs;

Connect to the serve where your run sqlplus and try to connect to the database (with the related OS user)

which sqlplus
echo $ORACLE_HOME
echo $TNS_ADMIN
the location of your tnsnames.ora file..

4)Is the database that you are try to connect to, a pluggable database?

[big]

Hi,
Thank you.

Let me informe you that 2 servers are concerned (1: AIX server on which is mydb , 2: Linux server client side)
1)send me the lsnrctl status output (for the related listener)(on DB server AIX)

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCmydb)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server)(PORT=1533)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "mydb" has 1 instance(s).
  Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


2)send me the tns entry named mydb (mask the sensitive info befor doing a copy/paste to here..for security reasons..)

mydb =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS =
    (PROTOCOL = TCP)
 
     (HOST = 10.14.2.23)
    (PORT = 1533)
   )
  )
  (CONNECT_DATA =
   (SID = mydb)
 )
)


3)send me the following outputs;

Connect to the serve where your run sqlplus and try to connect to the database (with the related OS user)

which sqlplus
/server/product/oracle/distrib/193000/client/bin/sqlplus
$ echo $ORACLE_HOME
/server/product/oracle/distrib/193000/client
 echo $TNS_ADMIN
/server/product/oracle/distrib/193000/client/network/admin

the location of your tnsnames.ora file..

/server/product/oracle/distrib/193000/client/network/admin

4)Is the database that you are try to connect to, a pluggable database? It is in 11.2.0.4 then plugable is not applied.

Regrads.

[ErmanArslansOracleBlog]

tnsping doesn't prove anything about this..
It doesn't produce any errors even if the SID is not known by the listener.

tnsping doesn't check if the listener know the SID or not..

*Send me the contents of your "listener.ora". (again mask your IP info!)
*also check with sqlplus easy connect -- using that tns in the command line:

sqlplus user/password@' (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)
(HOST = your_ip_address) (PORT = 1533) )) (CONNECT_DATA =(SID = mydb)))

Does it work?

Example:

[applmgr@ebstestdb admin]$ sqlplus apps/apps'@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebstestdb)(PORT=1530))(CONNECT_DATA=(SID=TEST)))'

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jan 30 12:11:55 2021

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


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

SQL>

[big]

Hi,
Thanks. Here are what you asked:
==Listener.ora===================
# Net8 definition for Database listener
#

mydb =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCmydb))
        (ADDRESS= (PROTOCOL= TCP)(Host= server.com )(Port= 1533))
  )

SID_LIST_mydb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /db-xxxx-dev/product/oracle/distrib/ora11204/0717)
      (SID_NAME = mydb)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /db-xxxx-dev/product/oracle/distrib/ora11204/0717)
      (PROGRAM = extproc)
    )
  )

STARTUP_WAIT_TIME_mydb = 0
CONNECT_TIMEOUT_mydb = 10
TRACE_LEVEL_mydb = OFF

LOG_DIRECTORY_mydb = /db-xxxx-dev/product/oracle/distrib/ora11204/0717/network/admin
LOG_FILE_mydb = mydb
TRACE_DIRECTORY_mydb = /db-xxxx-dev/product/oracle/distrib/ora11204/0717/network/admin
TRACE_FILE_mydb = mydb
ADMIN_RESTRICTIONS_mydb = OFF


IFILE=/db-xxxx-dev/product/oracle/distrib/ora11204/0717/network/admin/mydb_server/listener_ifile.ora

=====================listener_ifile.ora=======
it is an empty file

===============easy connect==================
sqlplus user/password@' (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)
(HOST = ip_address) (PORT = 1533) )) (CONNECT_DATA =(SID = mydb)))

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

SQL>
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
mydb

Regards.

[ErmanArslansOracleBlog]

This proves that your tns entry is okay..
Probably, sqlplus is reading the TNS entry from wrong tnsnames.ora.. sqlplus reaches "a" listener, but that listener doesn't listen for the SID that your sqlplus provides.. You may also be reaching the wrong listener..

1)send me strace output for sqlplus (from the sqlplus (Linux node))  -> "strace sqlplus apps/apps@mydb"

2)from the sqlplus (Linux node) send me the output of "find /db-xxxx-dev/product -name tnsnames.ora"

3)From the db node -> ps -ef |grep inh

--Upload your outputs as files.. Don't copy paste the outputs directly.. The outputs may be very long..

[big]

Hi,
I put directly what you suggested for easy connect .i.e.

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)
(HOST = ip_address) (PORT = 1533) )) (CONNECT_DATA =(SID = mydb)))

In tnsnames.ora and connection is now possible without any error.

Then we may stop here and let the users try on monday.

Thanks again.

[ErmanArslansOracleBlog]

ok, as you wish..