Database LInk
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Database LInk

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Posts
    40
    Hi all,
    i am trying to create database link between two database on two different servers.
    when i try to create and use the database link,the link gets created but when i tyr to use it i get an error
    " ORA-12154: TNS:could not resolve service name".

    please help,
    i have also set the Globala names parameter to true in that instance to which link is to be created.

    is there nay other thing that have to be changed or is there any othe rway to create database link.

    please advise,
    thank you,

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Are you testing the link from the same Oracle Home used to create the link?

    Are you using a fully qualified name, i.e. database name and domain?
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3

    Smile

    From Metalink - Scroll down for your error message and possible solution!

    ============================================================
    Database Links: An Overview
    ---------------------------


    1) What are database links?

    Database links are connections between two databases on the same or different
    machines.

    2) What are database links used for?

    To query data on a different database (distributed query)
    To do DML on data on a different database (distributed transaction)
    To either query or do DML on a non Oracle database (transparrant
    gateway)

    3) The Anatomy of a Database Link

    A database link has 4 main parts:

    Owner
    Link name
    Username/password
    Host (Service Name)

    3.1) The database link OWNER

    Like most objects in an Oracle database ... database links have an
    owner (the user who creates the link) or may be owned by PUBLIC.

    If owned by PUBLIC ... the database link may be accessed by all
    users on the database.

    Syntax: CREATE public DATABASE LINK ...
    CREATE DATABASE LINK ...

    3.2) The database link NAME

    The database link name can be any valid Oracle name.

    If global_names = TRUE in the init.ora then the database link name
    must be the global name of the remote database
    (SELECT GLOBAL_NAME FROM GLOBAL_NAME).

    Syntax: CREATE DATABASE LINK kbcook ...

    3.3) The database link USERNAME

    The database link username/password is an OPTIONAL clause. If it
    is not specified then the current username/password for the local
    database are used to connect to the remote database.

    If a username is specified then all connections thru that link are
    connected as the user specified.

    Syntax: CREATE DATABASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger ...

    3.4) The database link HOST

    The Host is the entry in the TNSNAMES.ORA for the database that is
    being linked to.

    Syntax: CREATE DATABASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger
    USING ‘rtcsol1_v805.us.oracle.com’

    4) How do you locate the TNSNAMES.ORA?

    If the $TNS_ADMIN environment variable is defined then look in that
    directory for the TNSNAMES.ORA.

    If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
    directory. Alternatly the tnsnames may exist in */etc or
    /var/opt/oracle as well.

    5) How do I locate the HOST in the TNSNAMES.ORA?

    The TNSNAMES.ORA will look like:

    RTCSOL1_V805.US.ORACLE.COM =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
    (CONNECT_DATA = (SID = V805))
    )

    The host name is everything before the =

    Note: You can specify the full description as the host

    Syntax: CREATE DATBASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger
    using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
    (HOST = rtcsol1)(PORT = 1521))
    (CONNECT_DATA = (SID = V805))’

    6) How a database link works

    Step 1: SELECT * FROM TABLE@DBLINK

    Step 2: The database will resolve DBLINK to a host name ... it will
    use the TNSNAMES.ORA unless fully described

    Step 3: Naming resolution (DNS, NIS etc) will resolve the HOST to a
    TCP/IP address

    Step 4: A connection will be made to a listener at the TCP/IP address

    Step 5: The listener for the PORT will resolve the SID and finish
    the connection to the database (uses the listener.ora to
    determine if it is listening for connections to that sid)

    7) How do you locate the LISTENER.ORA?

    If the $TNS_ADMIN environment variable is defined then look in that
    directory for the LISTENER.ORA.

    If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
    directory. Alternatly the tnsnames may exist in */etc or
    /var/opt/oracle as well.

    8) How do I locate the HOST in the LISTENER.ORA?

    The LISTENER.ora will look like:

    LISTENER=
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
    )
    )
    (DESCRIPTION =
    (PROTOCOL_STACK =
    (PRESENTATION = GIOP)
    (SESSION = RAW)
    )
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))
    )
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=V805)
    (ORACLE_HOME=/u04/app/oracle/product/8.0.5)
    )
    )
    )

    9) The Syntax tree for CREATE DATABASE LINK

    /- SHARED -\ /- PUBLIC - | | | |
    CREATE -----------------------------> DATABASE LINK ---> --->


    /-> CURRENT USER ---------------------------- | |
    /-> CONNECT TO -+ /- AUTH -\ |- | | | CLAUSE | | |
    | \-> IDENTIFIED BY ----------/ |
    | |
    |---> AUTHENTICATED_CLAUSE --------------------------------------|
    | |
    ------------------------------------------------------------------->

    /-> USING '' --- | |
    ----------------------------------------->;

    10) Additional Clauses

    The SHARED and AUTHENTICATED BY clauses are used to establish a
    multi-threaded database connection and both are required to do so.

    11) Common Errors

    11.1) Bad host name

    ORA-12154

    "TNS:could not resolve service name"

    *Cause: The service name specified is not defined correctly 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 V2
    Administrator's Guide. If possible, regenerate the
    configuration files using the Oracle Network Manager.

    11.2) Bad port #

    ORA-12224

    "TNS:no listener"

    *Cause: The connection request could not be completed because the
    listener is not running.

    *Action: Ensure that the supplied destination address matches one
    of the addresses used by the listener - compare the
    TNSNAMES.ORA entry with the appropriate LISTENER.ORA file
    (or TNSNAV.ORA if the connection is togo by way of an
    Interchange). Start the listener on the remote machine.

    11.3) Bad sid

    From Meta link
    ============================================================
    Database Links: An Overview
    ---------------------------


    1) What are database links?

    Database links are connections between two databases on the same or different
    machines.

    2) What are database links used for?

    To query data on a different database (distributed query)
    To do DML on data on a different database (distributed transaction)
    To either query or do DML on a non Oracle database (transparrant
    gateway)

    3) The Anatomy of a Database Link

    A database link has 4 main parts:

    Owner
    Link name
    Username/password
    Host (Service Name)

    3.1) The database link OWNER

    Like most objects in an Oracle database ... database links have an
    owner (the user who creates the link) or may be owned by PUBLIC.

    If owned by PUBLIC ... the database link may be accessed by all
    users on the database.

    Syntax: CREATE public DATABASE LINK ...
    CREATE DATABASE LINK ...

    3.2) The database link NAME

    The database link name can be any valid Oracle name.

    If global_names = TRUE in the init.ora then the database link name
    must be the global name of the remote database
    (SELECT GLOBAL_NAME FROM GLOBAL_NAME).

    Syntax: CREATE DATABASE LINK kbcook ...

    3.3) The database link USERNAME

    The database link username/password is an OPTIONAL clause. If it
    is not specified then the current username/password for the local
    database are used to connect to the remote database.

    If a username is specified then all connections thru that link are
    connected as the user specified.

    Syntax: CREATE DATABASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger ...

    3.4) The database link HOST

    The Host is the entry in the TNSNAMES.ORA for the database that is
    being linked to.

    Syntax: CREATE DATABASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger
    USING ‘rtcsol1_v805.us.oracle.com’

    4) How do you locate the TNSNAMES.ORA?

    If the $TNS_ADMIN environment variable is defined then look in that
    directory for the TNSNAMES.ORA.

    If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
    directory. Alternatly the tnsnames may exist in */etc or
    /var/opt/oracle as well.

    5) How do I locate the HOST in the TNSNAMES.ORA?

    The TNSNAMES.ORA will look like:

    RTCSOL1_V805.US.ORACLE.COM =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
    (CONNECT_DATA = (SID = V805))
    )

    The host name is everything before the =

    Note: You can specify the full description as the host

    Syntax: CREATE DATBASE LINK kbcook
    CONNECT TO scott IDENTIFIED BY tiger
    using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)
    (HOST = rtcsol1)(PORT = 1521))
    (CONNECT_DATA = (SID = V805))’

    6) How a database link works

    Step 1: SELECT * FROM TABLE@DBLINK

    Step 2: The database will resolve DBLINK to a host name ... it will
    use the TNSNAMES.ORA unless fully described

    Step 3: Naming resolution (DNS, NIS etc) will resolve the HOST to a
    TCP/IP address

    Step 4: A connection will be made to a listener at the TCP/IP address

    Step 5: The listener for the PORT will resolve the SID and finish
    the connection to the database (uses the listener.ora to
    determine if it is listening for connections to that sid)

    7) How do you locate the LISTENER.ORA?

    If the $TNS_ADMIN environment variable is defined then look in that
    directory for the LISTENER.ORA.

    If $TNS_ADMIN is not set then look in the $ORACLE_HOME/network/admin
    directory. Alternatly the tnsnames may exist in */etc or
    /var/opt/oracle as well.

    8) How do I locate the HOST in the LISTENER.ORA?

    The LISTENER.ora will look like:

    LISTENER=
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 1521))
    )
    )
    (DESCRIPTION =
    (PROTOCOL_STACK =
    (PRESENTATION = GIOP)
    (SESSION = RAW)
    )
    (ADDRESS = (PROTOCOL = TCP)(HOST = rtcsol1)(PORT = 14000))
    )
    )

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=V805)
    (ORACLE_HOME=/u04/app/oracle/product/8.0.5)
    )
    )
    )

    9) The Syntax tree for CREATE DATABASE LINK

    /- SHARED -\ /- PUBLIC - | | | |
    CREATE -----------------------------> DATABASE LINK ---> --->


    /-> CURRENT USER ---------------------------- | |
    /-> CONNECT TO -+ /- AUTH -\ |- | | | CLAUSE | | |
    | \-> IDENTIFIED BY ----------/ |
    | |
    |---> AUTHENTICATED_CLAUSE --------------------------------------|
    | |
    ------------------------------------------------------------------->

    /-> USING '' --- | |
    ----------------------------------------->;

    10) Additional Clauses

    The SHARED and AUTHENTICATED BY clauses are used to establish a
    multi-threaded database connection and both are required to do so.

    11) Common Errors

    11.1) Bad host name

    ORA-12154

    "TNS:could not resolve service name"

    *Cause: The service name specified is not defined correctly 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 V2
    Administrator's Guide. If possible, regenerate the
    configuration files using the Oracle Network Manager.

    11.2) Bad port #

    ORA-12224

    "TNS:no listener"

    *Cause: The connection request could not be completed because the
    listener is not running.

    *Action: Ensure that the supplied destination address matches one
    of the addresses used by the listener - compare the
    TNSNAMES.ORA entry with the appropriate LISTENER.ORA file
    (or TNSNAV.ORA if the connection is togo by way of an
    Interchange). Start the listener on the remote machine.

    11.3) Bad sid

    ORA-12305

    "TNS:listener could not resolve SID given in connect descriptor"

    *Cause: The SID in the CONNECT_DATA was not found in the
    listener's tables.

    *Action: Check to make sure that the SID specified is correct. The
    SIDs that are currently registered with the listener can
    be obtained by typing "LSNRCTL SERVICES ".
    These SIDs correspond to SID_NAMEs in TNSNAMES.ORA, or
    db_names in INIT.ORA.

    *Comment: This error will be returned if the database instance has
    not registered with the listener; the instance may need
    to be started.
    ============================================================
    ------------------------------------------
    Shyami.S.Seelan, OCP 8i, 9i DBA
    Dublin, Ireland.
    http://www.geocities.com/shyamiseelan/OCP
    ------------------------------------------

  4. #4
    Join Date
    Oct 2002
    Posts
    23

    Tnsnames.ora

    Is the Database you are trying to link to in your tnsnames.ora file?
    Michellea
    "Live Life to the Fullest"

  5. #5
    Join Date
    Aug 2001
    Posts
    40
    Yes the Tns entry of the DataBase i am trying to connect is in my TNSNAMES.ORA File.

    what is this GLOBAL NAMES Parameter has to do with Database links,
    what if it is set True and what if its is set to False

  6. #6
    Join Date
    Oct 2002
    Posts
    23

    Global names web site

    Michellea
    "Live Life to the Fullest"

  7. #7
    Join Date
    Mar 2002
    Posts
    24
    Hi,

    Problem with your tnsnames.ora file..
    Please configure properly with names and domain name ....

    if you don't use domain name .. make global_names=false
    it works...

    Before creating a link just check by connecting to the remote server...


    Good Luck
    Laxman

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