Printable View
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,
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?
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.
============================================================
Is the Database you are trying to link to in your tnsnames.ora file?
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
http://oracle.oreilly.com/news/oraclenet8_1200.html
Info on Global Names
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