Database link CONNECTION problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Database link CONNECTION problem

  1. #1
    Join Date
    Jun 2007
    Posts
    59

    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

  2. #2
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    Post error message

  3. #3
    Join Date
    Jun 2007
    Posts
    59
    select * from global_name@TESTZ;
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve service name

  4. #4
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    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.

  5. #5
    Join Date
    Jun 2007
    Posts
    59
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well your tns set is wrong, you need to fix it regardless of what you say works, it is wrong

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Jun 2007
    Posts
    59
    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
  •  



Click Here to Expand Forum to Full Width