|
-
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
------------------------------------------
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
|
Reply With Quote