Re: Oracle 9i client 10g database

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 12 Apr 2004 15:21:17 +1000
Message-ID: <opr6bvtrem3d8uqx@news.optusnet.com.au>

On Sun, 11 Apr 2004 23:55:34 -0500, Jay <me_at_heyjay.com> wrote:

> Well, I know that I'm at least doing my names resolution, but I'm not
> finding my listener at the other end. Here is a listing of the files:
>
> # on the server
> SQL> select * from global_name;
>
> GLOBAL_NAME
> ----------------------------------------------------------------------------
> ----
> TSPRD
>
> $ cat listener.ora
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = /opt/oracle/product/10.1.0.2)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = tsprd)
> (ORACLE_HOME = /opt/oracle/product/10.1.0.2)
> (SID_NAME = tsprd)
> )
> )
>
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = abba)(PORT = 1521))
> )
> )
>
>
> $ cat tnsnames.ora
> # Generated by Oracle configuration tools.
>
> TSPRD =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = abba)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = tsprd)
> )
> )
>
> EXTPROC_CONNECTION_DATA =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> (CONNECT_DATA =
> (SID = PLSExtProc)
> (PRESENTATION = RO)
> )
> )
>
> ## now on the client
>
> cat tnsnames.ora
> # TNSNAMES.ORA Network Configuration File:
> /opt/oracle/product/901/network/admin/tnsnames.ora
> # Generated by Oracle configuration tools.
>
> tsprd =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = abba)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = tsprd)
> )
> )

Well, cutting to the chase, your client-side and server-side tnsnames.ora's are not identical, are they?

So, copy the one from the server to the client and see if that helps.

If it doesn't, then we have to first make sure that what your client thinks is "abba" is actually "abba". So ping abba from the client and check the ip address you get returned, and make sure that's actually the ip address of the server.

If that doesn't work, do you have automatic instance registration working?

Get rid of (ie, comment out) the SID_LIST section of your listener.ora, and leave only the LISTENER= section. Then use lsnrctl services to make sure that the instance is still known to the listener. Then try connecting from the client.

If that works, try adding back the SID_LIST section, but without the GLOBAL_NAMES bit.

Otherwise, all I can tell you is this:

C:\Documents and Settings\Howard>sqlplus scott/tiger_at_lx10

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 12 15:20:05 2004

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

... which is 9i client talking to 10g database, and a Windows client talking to a Linux database at that.

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================

Received on Mon Apr 12 2004 - 00:21:17 CDT