Conecting to Oracle - TNS problems

From: Mike Brodbelt <m.brodbelt_at___NO_SPAM_PLEASE__acu.ac.uk>
Date: Thu, 04 Oct 2001 12:16:39 +0100
Message-ID: <3BBC4517.1025F31F@__NO_SPAM_PLEASE__acu.ac.uk>

Hi,

I have an Oracle 8i database (8.1.5) running on a Sun machine. I have a number of clients which need to connect to this machine. Most of these are Windows machines, and for these, I have simply installed the Oracle supplied Net8/ODBC clients for Windows, and can connect fine.

I also have a couple of Linux machines which I want to be able to connect to the database. I searched around, and found someone who had some pre-packaged rpm's containing enough Oracle files to allow me to compile the Perl DBD::Oracle module, and both the machines in question can run Perl scripts that successfully connect to the database and retrieve data.

However, I cannot successfully connect to the Oracle instance from SQLPlus, or other applications I'm trying to use (TOra). I get this error:-

$ export ORACLE_HOME=/opt/oracle
$ export ORACLE_SID=SCORPIO    
$ sqlplus

SQL*Plus: Release 8.0.5.0.0 - Production on Thu Oct 4 12:41:22 2001

Enter user-name: system
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name:

All the information I've seen leads me to believe that this is usually caused by problems with the TNS setup. I've tried various tnsnames files, but to no avail. IP level connectivity is fine, and tnsping works:-

$ /usr/local/oracle/bin/tnsping scorpio

TNS Ping Utility for Linux: Version 8.0.5.0.0 - Production on 04-OCT-01 12:48:27

Attempting to contact
(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=sol.acu.ac.uk)) OK (30 msec)

I'd appreciate any help and pointers on where to go next. The packaged Oracle files I installed on my Linux machines are version 8.0.5, whereas the database is version 8.1.5 - is that important. If so, where do I get requisite files from - the Oracle client CD is have doesn't include Linux specific drivers - should I be asking my vendor for these? Oracle don't seem to make them available for download....

Thanks,

Mike.

Config file details follow.....

The tnsnames.ora on the client is like this:-

# /opt/oracle/network/admin/tnsnames.ora

SCORPIO.ACU.AC.UK =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = sol.acu.ac.uk))
    )
    (CONNECT_DATA =
(SERVICE_NAME = SCORPIO.ACU.AC.UK)
    )
  )

The client sqlnet.ora:-

# /opt/oracle/network/admin/sqlnet.ora

NAMES.DEFAULT_DOMAIN = ACU.AC.UK SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME) On the server, the listener.ora is like this:-

# Generated by Oracle Net8 Assistant

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )


      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = sol)(PORT = 1521))
      )

)
(DESCRIPTION =
(PROTOCOL_STACK =

        (PRESENTATION = GIOP)
        (SESSION = RAW)
      )


      (ADDRESS = (PROTOCOL = TCP)(HOST = sol)(PORT = 2481))

)
)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/8.1.5)
(PROGRAM = extproc)

    )
    (SID_DESC =
(GLOBAL_DBNAME = SCORPIO.acu.ac.uk)
(ORACLE_HOME = /u01/app/oracle/product/8.1.5)
(SID_NAME = SCORPIO)

    )
  ) Received on Thu Oct 04 2001 - 06:16:39 CDT