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

[Matthew_50]

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

[T G Manikandan]

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]

I've been tried this, still unable to connect though with db though tns

[T G Manikandan]

Can you please attach the files

listener.ora
tnsnames.ora
sqlnet.ora

Revert

[Matthew_50]

please refer as the attachment

[T G Manikandan]

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]

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

$lsnrctl stop
$lsnrctl start


REvert with the output

Thanks

[Matthew_50]

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]

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

?

seems like your listener isn't listening properly.

Massimo

[Anand_31]

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