SQL*Plus connection problem

From: Van Messner <vmessner_at_bestweb.net>
Date: Fri, 29 Sep 2000 17:34:23 GMT
Message-ID: <zo4B5.1666$np1.244006@newshog.newsread.com>

I have an 81.6. enterprise edition NT server and a separate NT client. Both are using TCP/IP. and both have tnsnames.ora entries that look like this:

DREP.GLENHALL =
  (DESCRIPTION =
    (ADDRESS_LIST =

        (ADDRESS =


          (COMMUNITY = tcp.world)

          (PROTOCOL = TCP)

          (Host = ELVER)

          (Port = 1521)

        )

    )
    (CONNECT_DATA = (SID = DREP)
    )
  )
The server tnsnames.ora has a service name rather than a sid. On the server side the listener.ora has an entry for each database which varies only with sid_name and global_dbname. The listener and all SID services are running on the server. Both the server and client have only TCP entries in their tnsnames.ora and listener.ora - there is none for extproc. The intelligent agent has not been started up and there are no xxx.ro or xxx.rw files.

From the client I can go into SQL*Plus and connect as sys/passsword_at_drep.glenhall, or sys/password_at_prod.glenhall etc.

From the server I can use dos to set the ORACLE_SID=drep or prod or whatever, then connect as sys/password to the proper database.

BUT from the server I can connect only to drep using connect sys/password. Using sys/password_at_drep or sys/password_at_drep.glenhall fails with a series of TNS errors. I cannot connect to prod through SQL*Plus in any way. I checked the TNS error messages but they did not provide an answer.

Putting in entries for an extproc connection does not help. Bouncing the listener does not help. Trying various combinations on the client and server of service_names or sids does not help. Here's the only clue I have. If I do a lsnrctl status after booting the server I see one service handler for drep and one for prod. If I try to connect to the databases through SQL*Plus on the server, the number of service handlers for both databases increases which seems strange.

Any help appreciated.

Van Received on Fri Sep 29 2000 - 12:34:23 CDT