From: sybrandb@my-deja.com
Newsgroups: comp.databases.oracle.server
Subject: Re: Error: Could not resolve SID... Oracle 8.1.7
Date: Tue, 02 Jan 2001 05:48:51 GMT
Organization: Deja.com
Lines: 188
Message-ID: <92rq43$7mp$1@nnrp1.deja.com>
References: <3a514eae.24378784@news.pacbell.net>
NNTP-Posting-Host: 213.116.132.97
X-Article-Creation-Date: Tue Jan 02 05:48:51 2001 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows 98; Parool INTERNET)
X-Http-Proxy: 1.1 x67.deja.com:80 (Squid/1.1.22) for client 213.116.132.97
X-MyDeja-Info: XMYDJUIDsybrandb


In article <3a514eae.24378784@news.pacbell.net>,
  rugga@pacbell.net (Chuck ) wrote:
>
> I am having a very annoying problem and I have seen it come and go on
> Linux, but never on Solaris.
>
> I have a version of 8.1.7 (although this problem exists on 8.1.6)
> running on SuSE 7.0. I had this working fine but needed to scratch the
> box and rebuild.
>
> Here is my tnsnames.ora file: (Sorry so long, but I recreated this
> file making sure there were no hidden characters or other errors, as I
> have gotten corrupted files in the past copying accross different
> machines)
>
> # BEGIN
> EXTPROC_CONNECTION_DATA.RHUGGA.ORG =
>    (DESCRIPTION =
>      (ADDRESS_LIST =
>        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
>      )
>      (CONNECT_DATA =
>        (SID = PLSExtProc)
>        (PRESENTATION = RO)
>      )
>    )
>
> OPS1 =
>    (DESCRIPTION =
>      (ADDRESS_LIST =
>        (ADDRESS =
>           (PROTOCOL = TCP)
>           (HOST = canthus)
>           (PORT = 1521)
>        )
>      )
>      (CONNECT_DATA =
>           (SID = OPS1)
>      )
>    )
> # END tnsnames.ora
>
> Here is my listener.ora file:
> #BEGIN
> LISTENER =
>    (DESCRIPTION_LIST =
>       (DESCRIPTION =
>          (ADDRESS_LIST =
>             (ADDRESS =
>                (PROTOCOL = IPC)
>                (KEY = EXTPROC)
>             )
>          )
>          (ADDRESS_LIST =
>             (ADDRESS =
>                (PROTOCOL = TCP)
>                (HOST = canthus)
>                (PORT = 1521)
>             )
>          )
>       )
>       (DESCRIPTION =
>          (PROTOCOL_STACK =
>             (PRESENTTION = GIOP)
>             (SESSION = RAW)
>          )
>          (ADDRESS =
>             (PROTOCOL = TCP)
>             (HOST = canthus)
>             (PORT = 2481)
>          )
>       )
>    )
>
> SID_LIST_LISTENER =
>    (SID_LIST =
>       (SID_DESC =
>          (SID_NAME = PLSExtProc)
>          (ORACLE_HOME = /u01/app/oracle/product/8.1.7)
>          (PROGRAM = extproc)
>       )
>    )
> #END
>
> Now, if I try "sqlplus scott@OPS1" and then enter the password I get
> the following:
>
> Enter password:
> ERROR:
> ORA-12541: TNS:no listener
>
> If I try "sqlplus scott@OPS1."  (with a period)and then enter the
> password I get the following:
>
> Enter password:
> ERROR:
> ORA-12505: TNS:listener could not resolve SID given in connect
> descriptor
>
> HOWEVER, if I execute "tnsping OPS1." I get the following:
> canthus:~ # tnsping OPS1.
>
> TNS Ping Utility for Linux: Version 8.1.7.0.0 - Production on
> 01-JAN-2001 19:39:44
>
> (c) Copyright 1997 Oracle Corporation.  All rights reserved.
>
> Attempting to contact
> (ADDRESS=(PROTOCOL=TCP)(HOST=canthus)(PORT=1521))
> OK (10 msec)
>
> NOW, I have tried using "localhost", "canthus", and
> "canthus.rhugga.org" in the listener.ora and tnsnames.ora file. Before
> it worked as localhost.
>
> And YES, the listener is running:
>
> # ps -ef
> oracle    2687     1  0 19:27 ?        00:00:00 ora_pmon_OPS1
> oracle    2689     1  0 19:27 ?        00:00:00 ora_dbw0_OPS1
> oracle    2691     1  0 19:27 ?        00:00:00 ora_lgwr_OPS1
> oracle    2693     1  0 19:27 ?        00:00:00 ora_ckpt_OPS1
> oracle    2695     1  0 19:27 ?        00:00:00 ora_smon_OPS1
> oracle    2697     1  0 19:27 ?        00:00:00 ora_reco_OPS1
> oracle    2754     1  0 19:31 ?        00:00:00
> /u01/app/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit
>
> Here is the output of "lsnrctl status":
> canthus:~ # lsnrctl status
>
> LSNRCTL for Linux: Version 8.1.7.0.0 - Production on 01-JAN-2001
> 19:45:30
>
> (c) Copyright 1998 Oracle Corporation.  All rights reserved.
>
> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
> STATUS of the LISTENER
> ------------------------
> Alias                     LISTENER
> Version                   TNSLSNR for Linux: Version 8.1.7.0.0 -
> Production
> Start Date                01-JAN-2001 19:31:22
> Uptime                    0 days 0 hr. 14 min. 8 sec
> Trace Level               off
> Security                  OFF
> SNMP                      OFF
> Listener Parameter File
> /u01/app/oracle/product/8.1.7/network/admin/listener.ora
> Listener Log File
> /u01/app/oracle/product/8.1.7/network/log/listener.log
> Services Summary...
>   PLSExtProc            has 1 service handler(s)
> The command completed successfully
>
> Here are my ORACLE ENV variables:
> canthus:~ # env | grep ORA
> ORACLE_SID=OPS1
> ORACLE_HOME=/u01/app/oracle/product/8.1.7
> ORACLE_TERM=vt100
>
> I also add $ORACLE_HOME/lib to LD_LIBRARY_PATH
>
> I can usually screw around and get this working but the time has come
> to dig deeply into the irritating problem and find a 100% difintive
> cause and solution. (Of course I am assuming I have not made a
> seemingly bonehead mistake) I am no Sr. level DBA or anything but
> sheesh, this should be simple.
>
> I would greatly appreciate some insight into this.
>
> Thanks,
> Chuck
>
>
Your problem is the SID OPS1 does NOT occur in the sid_list_listener
block. Hence you have no service for TCP/IP set up and connecting using
TCP/IP will fail, as fallback from TCP to IPC has been disabled in 8i.

Hth,
--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com
http://www.deja.com/