-
Database link CONNECTION problem
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
-
-
select * from global_name@TESTZ;
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name
-
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.
-
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
-
well your tns set is wrong, you need to fix it regardless of what you say works, it is wrong
-
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]
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|