need help creating db links
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: need help creating db links

  1. #1
    Join Date
    Jun 2003
    Posts
    22

    need help creating db links

    Hi all,
    I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
    ora1 service name = ora1
    ora1 user = scott
    ora2 service name = ora2
    ora2 user = mary

    If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
    SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
    Is this correct?

    When I try to access the emp table I get the following error:
    SQL> select count(*) from scott.emp@ora1.linbox;
    ORA-12154: TNS: could not resolve service name

    My tnsnames.ora file has the following entries:
    ORA1.LINBOX =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ora1)
    )
    )

    Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks

  2. #2
    Join Date
    May 2001
    Posts
    736
    Do the following

    1. Make the global_names parameter as false on both the databases.

    2.SQL> create public database link ora1 connect to scott identified by tiger using 'ora1';

    Hope this will solve your problem

  3. #3
    Join Date
    Jun 2003
    Posts
    22
    global_names param is set to false on both dbs and I tried the create db link with ora1 using 'ora1' as suggested above but still having the same probs.

    SQL> select * from scott.emp@ora1;
    ORA-12154:TNS could not resolve service name
    SQL> select * from scott.emp@ora1.linbox;
    ORA-02019:connection description for remote database not found

    Using LSNRCTL status I can see the listener is listening for both ora1 and ora2.

    My tnsnames.ora file:

    ORA2.LINBOX =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ora2)
    )
    )

    ORA1.LINBOX =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ora2)
    )
    )

    My sqlnet.ora file:
    NAMES.DEFAULT_DOMAIN= localdomain
    NAMES.DIRECTORY_PATH= (TNSNAMES)

    My listener.ora file:
    SID_LIST_ORA1_LIST =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = ora1.linbox)
    (ORACLE_HOME = /u01/apps/oracle/product/8.1.6)
    (SID_NAME = ora1)
    (SID_DESC =
    (GLOBAL_DBNAME = ora2.linbox)
    (ORACLE_HOME = /u01/apps/oracle/product/8.1.6)
    (SID_NAME = ora2)
    )
    )

    ORA1_LIST =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linbox)((PORT = 1521))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    )

    init.ora params for ora1 db:
    db_name = "ora1"
    db_domain = linbox
    instance_name = ora1
    service_names = ora1
    remote_login_passwordfile = exclusive
    global_names = false

    init.ora params for ora2 db:
    db_name = "ora2"
    db_domain = linbox
    instance_name = ora2
    service_names = ora2
    remote_login_passwordfile = exclusive
    global_names = false

    Both dbs are on the same machine and I want to be able to access on db from another. Are there any other parameters I need to consider? It seems like it should be a very simple thing but I'm stumped.

  4. #4
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

    check for the value in global_name

    select * from global_name;
    Nagesh

  5. #5
    Join Date
    Jun 2003
    Posts
    22
    select * from global_domain; resulted in ->

    GLOBAL_NAME
    ------------------
    ORA2.LOCALDOMAIN

    What do I do/check next?

  6. #6
    Join Date
    Oct 2003
    Posts
    4

    Re: need help creating db links

    Originally posted by padawin
    Hi all,
    I have 2 dbs ora1 and ora2 on the same linux box (host = linbox) running 8.1.6.
    ora1 service name = ora1
    ora1 user = scott
    ora2 service name = ora2
    ora2 user = mary

    If I want to create a public db link from mary's account (create public db link privilege already granted to mary) to scott's schema to acces the emp table. I tried the following:
    SQL> create public database link ora1.linbox connect to scott identified by tiger using 'ora1';
    Is this correct?

    When I try to access the emp table I get the following error:
    SQL> select count(*) from scott.emp@ora1.linbox;
    ORA-12154: TNS: could not resolve service name

    My tnsnames.ora file has the following entries:
    ORA1.LINBOX =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = linbox)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ora1)
    )
    )

    Am I doing something majorly wrong? Also how would scott grant select on emp to mary (user in anohter db)? Thanks
    I have got exactly the same error message when I tried some time back.
    I have replaced the connection string with the full address and it worked so you replace 'ora1' with the exact address in tnsnames.ora file.
    Ganesh

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