Re: Problems with tnsnames.ora/linking to remote db's in 8i

From: Paul Rech <rechp_at_thebestisp.com>
Date: 2000/04/23
Message-ID: <3902ED79.7BE2CD1F@thebestisp.com>#1/1

jawa803_at_yahoo.com wrote:

> Hey folks.
>
> I have two databases, let's give them SID's of TESTMAST and TESTPROD.
> I have two accounts, let's call them user1 and user2.
>
> In user1's .bash_profile I have all your standard Oracle stuff,
> including:

Linux platform, then?
Or do you download BASH to all your UNIX machines?

>
>
> ORACLE_SID=TESTMAST
> export ORACLE_SID
>
> ORACLE_HOME=/database/oracle/8i/u01/app/oracle/product/8.1.5
> ORACLE_BASE=/database/oracle/8i/u01/app/oracle

This should be dynamic.
export ORAENV_ASK=NO
export ORACLE_SID=CRUD
. /usr/local/bin/oraenv # link to real oraenv,make sure /usr/local/bin is in your path
export ORAENV_ASK=YES

>
> export ORACLE_HOME ORACLE_BASE
> NLS_LANG='english_united kingdom.we8iso8859p1'
> ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
> ORACLE_TERM=vt100
> LD_LIRBARY_PATH=$ORACLE_HOME/lib
> PATH=$PATH:$ORACLE_HOME/bin
> export NLS_LANG ORA_NLS33 PATH LD_LIBRARY_PATH
> export JAVA_HOME=/usr/local/jre
> export PATH=$JAVA_HOME/bin:$PATH
>
> user2 has a similar .bash_profile but has a SID set to TESTPROD.
>
> When I login as user2, I create a database link to TESTMAST by doing
> the following in sqlplus:
>
> CREATE DATABASE LINK TEST_MASTER
> CONNECT TO user1 IDENTIFIED BY user1password
> USING 'TEST_MASTER';
>
> But when I try to make calls to TEST_MASTER, such as:
>
> select * from table_at_TEST_MASTER;

Did you start sqlplus like this:

    . oraenv
    TEST_MASTER
     sqlplus

or:

    sqlplus dude_at_TEST_MASTER

The first one is a direct connection that does not use sqlnet. So you can be
in the database working happily without sqlnet working at all. Use the 2nd one to make sure.

>
>
> I get the error:
>
> ERROR at line 1:
> ORA-12154: TNS:could not resolve service name
 

>
> The database link looks like it was created okay, but I am guessing
> that there's a problem with my tnsnames.ora or listener.ora file in my
> $ORACLE_HOME/network/admin

Oracle claims you should not edit these by hand, but that's all I have ever done
without a problem. You could try generating them from SQLNET Easy config and putting them in place.

Also do: echo $ORACLE_HOME at the command line to make sure it is what you think it is.

>
>
> My tnsnames.ora looks like:
>
> TEST_MASTER =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = HOST_IP_ADDRESS)
> (Port = 1521)
> )
> )
> (CONNECT_DATA = (SID = TESTMAST)
> )
> )
>
> TEST_PROD =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = HOST_IP_ADDRESS)
> (Port = 1521)
> )
> )
> (CONNECT_DATA = (SID = TESTPROD)
>
> My listener.ora is:
>
> LISTENER =
> (ADDRESS_LIST =
> (ADDRESS =
> (PROTOCOL = TCP)
> (Host = HOST_IP_ADDRESS)
> (Port = 1521)
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = TESTMAST)
> (ORACLE_HOME = /database/oracle/8i/u01/app/oracle/product/8.1.5)
> )
> (SID_DESC =
> (SID_NAME = TESTPROD)
> (ORACLE_HOME = /database/oracle/8i/u01/app/oracle/product/8.1.5)
> )
> )
>
> STARTUP_WAIT_TIME_LISTENER = 0
> CONNECT_TIMEOUT_LISTENER = 10
> TRACE_LEVEL_LISTENER = OFF
>
> Is there another file I am missing in my $ORACLE_HOME/network/admin
> that I need?
>

No.

I avoid using aliases myself.  They are fine but too easy to type the
wrong one in the wrong place.   You could change TEST_MASTER to TEST_MAST
and see if that is a problem.  Like I said, not sure, I never do that.

Need to recreate the link then.

I like to use the TNS_ADMIN environment variable for sqlnet. export TNS_ADMIN=/fleb/home/oracle/tns

Then put tnsnames.ora and listener.ora in there and then create links in the
$ORACLE_HOME/network/admin dir to these files. Then if you have more than one version on the machine, there is no confusion about which tnsnames.ora you are pointing at.

Search your machine and make sure there is only one tnsnames.ora and one listener.ora.

find / -name tnsnames.ora -print
then substitute in listener.ora

>
> I want to try to test my tnsnames.ora using tnsping, but when I go to
> $ORACLE_HOME/bin and try running tnsping, I get:

Don't waste time with this.
Even simpler and more certain is to use sqlplus in an xterm:

    sqlplus system_at_TEST_MASTER

If you connect using this then sqlnet is working, try your select using the link again.

>
>
> tnsping: error in loading shared libraries: libclntsh.so.8.0: cannot
> open shared object file: No such file or directory
>
> So I tried to re-establish the link to the shared library by running
> genclntsh. It looks like it does something, but I still can't run
> tnsping and hence can't figure out why I can't make calls to remote
> databases.

Paul Rech
Oracle DBA Received on Sun Apr 23 2000 - 00:00:00 CDT