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

From: <jawa803_at_yahoo.com>
Date: 2000/04/20
Message-ID: <8dnihv$2au$1@nnrp1.deja.com>#1/1

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:

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 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;

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

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?

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:

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.

I have looked around metalink and other sites and tried a few suggestions, but I am running out of ideas. Any help would be greatly appreciated. I need to get this thing working ASAP.

Thanks for any assistance.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 20 2000 - 00:00:00 CDT