How I create an oracle database link (dblink)
The link is one way only in direction. So you will have to create the
link on both databases if you want the communication to be
bi-directional.
Assuming:
We have two servers (server-1 and server-2). On server-1 , we have the
orcl-1 database and on server-2, we have the orcl-2 database.
Service name for orcl-1: orcl-1
Service name for orcl-2: orcl-2
In general you use the CREATE DATABASE LINK command to create a
database. You need to perform this command on both databases (orcl-1 and
orcl-2).
Step #1:
You need to make sure that in each server you can access to to the other
remote database. Add the tnsnames entries to both databases. Use the
tnsping command to test the connection strings.
Add the following connect
string information on server-1and server-2 to tnsnames.ora.
orcl-1
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server-1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl-1)
)
)
orcl-2
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server-2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl-2)
)
)
Step #2:
Test the connect string on both servers.
# tnsping orcl-1
# tnsping orcl-2
Step #3:
Now, you are ready to create your link on the orcl-1 and orcl-2
databases.
On orcl-1:
create [public] database link your_link2orcl-2
connect to user_name
identified by password
using
orcl-2
/
On orcl-2:
create [public] database link your_link2orcl-1
connect to user_name
identified by password
using
orcl-1
/
Good Luck!
|