Re: ORA-12154: TNS:could not resolve service name in sql+ only

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 20 Sep 2002 21:41:23 +1000
Message-ID: <1IDi9.36572$g9.104798@newsfeeds.bigpond.com>

"Debbie" <DEBBIELOKE_at_YAHOO.CO.UK> wrote in message news:eec445c4.0209200100.2f26a1ac_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<x8ui9.36219$g9.103421_at_newsfeeds.bigpond.com>...
> > "Debbie" <DEBBIELOKE_at_YAHOO.CO.UK> wrote in message
> > news:eec445c4.0209190644.47df6697_at_posting.google.com...
> > > A bit of history;

[snip]
> > >
> > > I get this error message when I try to connect to SQL+ on the second
> > > pc. I am connected to the database (prod1), can see all information
> > > in the database and make changes to it (that appear on both PC's) via
> > > Enterprise manager. It is only when trying to launch SQL+ that this
> > > message appears.
> > >
> >
> > Nope... you've gone off the deep end on this one. It's not clear from
your
> > description what's happening, what you're typing, and a goodly much else
> > besides.

>
> I have Oracle installed onto PC2 and am trying to connect to the
> database on PC1.

Fine.

>I have therefore set up a database called Prod1 on
> PC2 with the same details as the Prod database on PC1 so I can see all
> the data on the Prod database from PC2.

Nope, you've lost me again. If you want to connect to PROD on PC1, why on Earth would you want to create a completely different database on PC2?? You *can* do this: it's called 'database links', and lets you, whilst connected to PROD1, do a 'select * from emp_at_PROD'.

But I don't think that this is what you are after, since database links seem a bit more complex than you're ready to go for just now.

>Im basically connecting to
> the database on PC1 through PC2, however if I try to launch SQL+ on
> PC2 and connect to the database I get the error.

> >
> > I presume you are saying that, whilst seated at PC2 (which is running
the
> > PROD1 database) you are trying to connect, via SQL*Plus, to the PROD
> > database on PC1. Is that right?

>
> This is correct, sorry for not making it clear.

> >
> > > Here is my tnsnames.ora file, which I can’t see any errors with;
> > >
> > > # TNSNAMES.ORA Network Configuration File:
> > > C:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
> > > # Generated by Oracle configuration tools.
> > > PROD1.ORANZ.CO.UK =
> > > (DESCRIPTION =
> > > (ADDRESS_LIST =
> > > (ADDRESS = (PROTOCOL = TCP)(HOST = oranz02)(PORT = 1521))
> > > )
> > > (CONNECT_DATA =
> > > (SERVER = DEDICATED)
> > > (SERVICE_NAME = prod)
> > > )
> > > )
> > >
> >
> > See, this is where I get confused. You've a service alias called
PROD1....
> > which is connecting to the service PROD. Yet earlier, I thought you said
you
> > had a database called PROD running on your first machine, and PROD1
running
> > on your second machine. Who can say what you're trying to connect to and
> > from where... 'cos I can't tell.

>
> I am trying to connect to the prod database on PC1 from PC2, and this
> is the only way I can think of doing it.  I can view the data from PC2
> and make changes, but can&#8217;t connect through SQL+

> >
> > Is this the TNSNAMES.ORA file from the first machine, or the second?

>
> This is from PC2.

> >
> > > When I type the following command line into SQL+ -
> > > Connect mis_staging/mis_staging_at_PROD1.ORANZ.CO.UK;
> > > I get the nasty message;
> > > ORA-12154: TNS:could not resolve service name.
> > >
> >
> > Check that you haven't got multiple copies of the tnsnames.ora floating
> > around on your hard disk. Check that sqlnet.ora doesn't have a line such
as
> > "SQLNET.DEFAULT_DOMAIN" (delete it if so, and try again).

>
> It did have this line, and I have now removed it thank you!

> >
> > Otherwise, write back with more precision as to what you are typing,
where,
> > and attempting to do what.

>
> I am just trying to connect to a database called prod on PC1 from PC2
> and am not sure how to do this effectively.  Im sorry for the
> confusion and greatly appreciate all of your help.  Thank you again!!

> >

Still not nearly enough information to really go on. I'm going to assume that what we've referred to as "PC1" is actually called 'oranz01' and PC2 is therefore 'oranz02'.

You need a tnsnames.ora on oranz02 which correctly identifies how to get to the Listener on oranz01, since that is the listener handling connections for PROD. You also need a listener.ora on oranz01, correctly listening on port 1521 and aware of the database it's supposed to be listening for.

On oranz01, the listener.ora would look like this:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oranz01)(PORT = 1521))
      )

)
)

I've not got a SID LIST here, because we should be able to get automatic instance registration working.

To check that it is indeed automatically working, make sure the listener.ora reads as above, then at a DOS prompt (still on oranz01), type:

lsnrctl services

You should get a display a bit like this one:

C:\Documents and Settings\Howard>lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 20-SEP-2002 21:35:51

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=britten)(PORT=1521))) Services Summary...
Service "brit.aldeburgh.local" has 2 instance(s). Instance "brit", status UNKNOWN, has 1 handler(s) for this service... Handler(s):

      "DEDICATED" established:0 refused:0
         LOCAL SERVER

Instance "brit", status READY, has 1 handler(s) for this service... Handler(s):

      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

What you need to see here is the bit that looks like my "brit.aldeburgh.local" -that's the service name, which we'll need in the tnsnames.ora.

On oranz02, your tnsnames needs to read:

PROD =
(DESCRIPTION =

     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = oranz01)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = PROD.ORANZ.CO.UK )
     )

)

The HOST must be the first PC -the machine running the PROD database you first created. The other key line is the SERVICE_NAME one: that needs to read exactly what the listener on the other machine has just told you the precise service name actually is.

You should then be able to type sqlplus system/<password>@PROD and get connected.

For the sake of simple one-PC-to-another connectivity like this, there was no need to confuse the situation by creating the second database. All you needed on oranz02 was the Oracle client.

Let us know how it goes. If it still doesn't work, give us *full* details, including all machine names, IP addresses, listener.ora, tnsnames.ora, sqlnet.ora and init.ora.

Regards
HJR
> > Regards
> > HJR
> >
> > > I am at a complete loss with this. I have only been using Oracle a
> > > couple of weeks and we are testing it to see if it is going to work
> > > for us in a development capacity. I therefore purchased the media
> > > pack but no support with the database and this is proving to be a real
> > > nightmare. Please help!
Received on Fri Sep 20 2002 - 06:41:23 CDT