Error: Could not resolve SID... Oracle 8.1.7

From: Chuck <rugga_at_pacbell.net>
Date: Tue, 02 Jan 2001 03:56:33 GMT
Message-ID: <3a514eae.24378784@news.pacbell.net>

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_at_OPS1" and then enter the password I get the following:

Enter password:
ERROR:
ORA-12541: TNS:no listener

If I try "sqlplus scott_at_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.

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

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 Received on Mon Jan 01 2001 - 21:56:33 CST