Re: SQLPlus & TNSListener

From: Malderez Cédric <cedric.malderez_at_answork.com>
Date: Wed, 10 Sep 2003 09:42:01 +0200
Message-ID: <3f5ed59a$0$13273$626a54ce@news.free.fr>

> Are you doing tests while logged onto the server?
>

Yes, I'm using a local domain account with administrator rights

> Are you doing tests while logged onto another machine/client-pc and does
it
> have the correct tnsnames.ora file?
>

I've tried running the client on an other computer. Using the DBAStudio,
I've added BASE1 in the Database list.
I've never touch the tnsnames.ora file directly

> Can you ping (using TCP/IP ping command) the server from the client-pc?
>

Yes, the network is working well

> Can you tnsping the databases ? Of course, sqlplus is the real/ultimate
test
> if you have complete connectivity, but it could be useful info if you have
> good test with tnsping and not good tests with sqlplus.
>

I've tried the following command line "tnsping BASE1". It seems to be working
Here is the dump:
> D:\oracle\ora81\bin>tnsping TSEDF
> TNS Ping Utility for 32-bit Windows: Version 8.1.7.0.0 - Production on
10-SEP-2003 09:01:33
> (c) Copyright 1997 Oracle Corporation. All rights reserved.
> Attempting to contact

(ADDRESS=(PROTOCOL=TCP)(HOST=172.31.0.17)(PORT=1521))
> OK (30 msec)

> Do you have any other *.ora files in the directory where you store the
> tnsnames.ora (other than listener.ora on the server)? Or, do you have any
> *.ora files anywhere else on the client-pc or server?
In the D:\oracle\ora81\network\admin I have the following files:

- listener.ora
- snmp_ro.ora
- snmp_rw.ora
- sqlnet.ora
- tnsnames.ora

> Are you mistakenly pointing to another location for tnsnames.ora other
than
> the default? Do you have any TNS_ADMIN entries in the registry? Do you
have
> any environment variable named TNS_ADMIN? Is your tnsnames.ora stored in
the
> default location (ORACLE_HOME\network\admin)?
>

I don't have any TNS_ADMIN env set
When I'v used DBAStudio in order to add a new database in the list, the ORACLE_HOME\network\admin\tnsnames.ora was modified

> Include the listener.ora and tnsnames.ora files (and other *.ora files) in
> your reply if possible. If the tnsnames.ora is large, then maybe just
> cut/paste the databases you are having problems with.
>

Here is the listener.ora file
# LISTENER.ORA Network Configuration File: D:\oracle\ora81\network\admin\listener.ora # Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
      )

    )
    (DESCRIPTION =

      (PROTOCOL_STACK =
        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 2481))

    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =

      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora81)
      (PROGRAM = extproc)

    )
    (SID_DESC =

      (GLOBAL_DBNAME = BASE1)
      (ORACLE_HOME = D:\oracle\ora81)
      (SID_NAME = BASE1)

    )
    (SID_DESC =

      (GLOBAL_DBNAME = BASE2)
      (ORACLE_HOME = D:\oracle\ora81)
      (SID_NAME = BASE2)

    )
  )

And here is the tnsnames.ora file:
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora81\network\admin\tnsnames.ora # Generated by Oracle configuration tools.

BASE2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))     )
    (CONNECT_DATA =
      (SID = BASE2)
 (SERVER = DEDICATED)
    )
  )

BASE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))     )
    (CONNECT_DATA =

      (SID = BASE1)
      (SERVER = DEDICATED)

    )
  )

####################################

# Date..........: Tue Sep 09 15:14:29 CEST 2003

####################################

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))     )
    (CONNECT_DATA =

      (SERVER = SHARED)
      (SERVICE_NAME = BASE2)
      (PRESENTATION = http://admin)

    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )
    (CONNECT_DATA =

      (SID = PLSExtProc)
      (PRESENTATION = RO)

    )
  )

Thanks for your help :) Received on Wed Sep 10 2003 - 02:42:01 CDT