Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Matthew_50
Valued Contributor

Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

So, strangs. everything is fine before yesterday, my listener.ora as follows.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)
)

tnsnames.ora as follows
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
CONNECT_TIMEOUT_LISTENER = 10
MRTG=
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= 10.25.10.228)(Port= 1521))
(CONNECT_DATA = (SID = MRTG))
)

my env variable setting as follows
HOME=/
HZ=100
IFS=

LOGNAME=root
MAIL=/var/mail/root
MAILCHECK=600
NLS_LANG=American_America.UTF8
OPTIND=1
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/8.1.7
ORACLE_SID=mrtg
ORA_NLS33=/oracle/app/oracle/product/8.1.7/ocommon/nls/admin/data
PATH=/usr/sbin:/usr/bin:/usr/local/bin:/oracle/app/oracle/product/8.1.7/bin
PS1=#
PS2=>
SHELL=/sbin/sh
TERM=vt100
TZ=ROC

each time I execute
sqlplus mrtg/password@mrtg

always apear
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 1 18:52:53 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
ERROR:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Enter user-name:

Result of tnsping
TNS Ping Utility for Solaris: Version 8.1.7.0.0 - Production on 01-SEP-2003 19:05:49

(c) Copyright 1997 Oracle Corporation. All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(Host=10.25.10.228)(Port=1521))
OK (10 msec)

before this, everything is working fine, then one day, it apears the
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("sharedpool","TRIGGER$","sga heap","state objects")
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

The 00604 and 04031 already been solve, but 12505 keep bug me for about two weeks ago.
even re-install oracle db can't solve this problem, do anyone here have any idea to solve this ?
your help will be appreicate
14 REPLIES
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

you could have posted before re-install.

you should have similar entries in the tnsnames.ora file located at $ORACLE_HOME/network/admin which sits along with listener.ora.

Entries should be like

mrtg=
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= 10.25.10.228)(Port= 1521))
(CONNECT_DATA = (SID = mrtg)
)

Revert with the file entries.

Thanks
Matthew_50
Valued Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

I've been tried this, still unable to connect though with db though tns
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Can you please attach the files

listener.ora
tnsnames.ora
sqlnet.ora

Revert
Matthew_50
Valued Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

please refer as the attachment
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

is your database on this machine or remote machine.

If this exists on this machine then I would like two changes

1.In this line in listener.ora file
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
you have specified host as test.

Is test your hostname of the machine.
If not change it properly to the hostname or correct ip.

2.Add mrtg in the listener.ora file so that the listener for that database comes active.


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = //test//)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/app/oracle/product/8.1.7)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = mrtg)
(ORACLE_HOME = /opt/app/oracle/product/8.1.7 )
)
)



Revert on the same
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

After the changes do not forget to re-start the listener

$lsnrctl stop
$lsnrctl start


REvert with the output

Thanks
Matthew_50
Valued Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

the same, tnsping is okay, then it should be okay. but very strang, i can use

`sqlplus mrtg/password` to login, but unable to use `sqlplus mrtg/password@test` to login,
Massimo Bianchi
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Hi,
what is the output of the command:
lsnrctl status

?

seems like your listener isn't listening properly.

Massimo
Anand_31
Advisor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Hi,

First, please change your .profile or environment variable to upper case,

ORACLE_SID=MRTG

Next, also add the entry for the MRTG instance in listener.ora but don't use GLOBAL_DBNAME (unless you are using domains, etc.). Use the SID_NAME=MRTG.

I think the other members have also posted a sample entry for both files. They should have consistent values as they have suggested. Also, the output of 'lsnrctl services' can help us tell you more.

regards
anand
Anand_31
Advisor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Hi,

First, please change your .profile or environment variable to upper case,

ORACLE_SID=MRTG

Next, also add the entry for the MRTG instance in listener.ora but don't use GLOBAL_DBNAME (unless you are using domains, etc.). Use the SID_NAME=MRTG

I think the other members have also posted a sample entry for both files. They should have consistent values as they have suggested. Also, the output of 'lsnrctl services' can help us tell you more.

regards
anand
Anand_31
Advisor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Hi,

Apologies for the double posting. There is a fundamental error that you are committing. as per your description,

Quote tnsping is okay, then it should be okay. but very strang, i can use

`sqlplus mrtg/password` to login, but unable to use `sqlplus mrtg/password@test` to login Unquote.

You must use mrtg/password@MRTG and not mrtg/password@test as you have put here.

Just ensure you take out the GLOBAL_DBNAME and use SID_NAME in the listener.ora entry.

regards
anand
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

DId you change the entry "test" to the hostname of the machine?

Revert
Matthew_50
Valued Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

the hostname is `test`. sid name is `mrtg`.
T G Manikandan
Honored Contributor

Re: Question about ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Do you have a proper name resolution for the hostname.Better use the ip-address.

instead of test use the ip-address.
I find that the ip is 10.25.10.228

restart the listener.

if this still does not work.

It is better that you enable the GUI Netassistant tool.

$DISPLAY=<0.0>
$export DISPLAY
$$ORACLE_HOME/bin/netasst

Remove all the listener's and database listening locations and add it afresh.

revert on further help


Thanks