Database link CONNECTION problem

[zam]

More details:

Database A Oracle 8.1.7 (server 1) Listerner 10g
Database C Oracle 9.2.0 (server 1) Listerner 10g

Database B Oracle 9.2.0 (server 2) Listerner 10g


1. Login to A as sqlplus / and using Dblink to B - Ok.
2. Login to A as sqlplus user@A and using Dblink to B - Fails
3. Login to C as sqlplus user@A and using Dblink to B - Ok.

In shot using db link while login via client fails from 8.1.7 database.

Thanks in advance

[bhattnirav]

Post error message

[zam]

select * from global_name@TESTZ;
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name

[bhattnirav]

Have you chack as below :

ORA-12154 TNS:Could not resolve service name

Cause: The service name specified is not defined in the TNSNAMES.ORA file.


Action: Make the following checks and correct the error:
Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. See the operating system specific manual for details on the required name and location.
Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary.

Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. Any error in a TNSNAMES.ORA file makes it unusable. See Chapter 4 in the SQL*Net Administrator's Guide. If possible regenerate the configuration files using the Oracle Network Manager.

[zam]

I appreciate your time for writing the reply however if you look into my first post

1. Login to A as sqlplus / and using Dblink to B - Ok.
2. Login to A as sqlplus user@A and using Dblink to B - Fails

If there was something wrong with tnsnames it didn't work in a
first place. That's how I understand it.

Thanks

[davey23uk]

well your tns set is wrong, you need to fix it regardless of what you say works, it is wrong

[PAVB]

Please post your offending DBLink DDL, as I understand it this will be your dblink sitting on database A pointing to database B.

Please do and post full log:
1- Log into A box
2- set oracle_home and oracle_sid for A database
3- tnsping [database_name_used_in_offending_DBlink]

[abhaysk]

this could be one of the reasons.. while ur doing sqlplus /, you obiviously have set ORACLE_HOME to right path.. and that ORACLE_HOME/network/admin has right entry for instance on server 2..

in your second case, you may be using some other oracle_home and corresponding tnsnames.ora has wrong entry..


another possibility is that your using private db links and in you have wrongly defined the entry.


rgds
abhay

[zam]

Yes I agree and it 's obvious to me that login via client to database A (via Oracle listener) is resulted in loosing environment ($TNS_ADMIN) that 'is why tnsnames.ora could not be found.
We have 3 Oracle Home on this box.
TNS_ADMIN is set /u01/app/oracle/local/tnsadmin
I copied tnsnames.ora to all default destination
$ORACLE_HOME/network/admin for oracle 8.9 and 10. But no success ..

In the end I have done a work around by recreating db link and it works ...

CREATE PUBLIC DATABASE LINK ZAMTEST
CONNECT TO AAA IDENTIFIED BY AAA
USING '(description=(address=(protocol=TCP)
(host=xx.xx.xxx.xxx )(port=1521))(connect_data=(sid=DAISA01)))';

It would be good to get to the truth of it though ...

Thankyou