GOAL This document describes how to create a Database Link using IPC protocol instead of TCP. SOLUTION A Database Link (DBLink) can be configured to use the IPC protocol instead of TCP protocol when the source and the target databases are on the same machine. This can be useful in situations where there might be load on the TCP protocol (due to connection loads, etc.) and IPC can be used to significantly speed up transfer between DBs. It can only be done when the two DBs are local to one another, due to IPC being a "local only" protocol.
An example configuration is shown below: In this example, a DBLink is shown to be created using the IPC protocol from a 9i database named DB9 to a 10g database named DB10. DB9 and DB10 are only used as sample names for the purpose of distinguishing the origin database name from the target. listener.ora (The listener version must always be at or higher than the version of the databases, so we're using the version 10 listener here) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))
(ADDRESS= (PROTOCOL = IPC)(KEY=DB10))
)
)
) SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB9)
(ORACLE_HOME = D:\app\oracle9)
(SID_NAME = DB9)
)
(SID_DESC =
(GLOBAL_DBNAME = DB10)
(ORACLE_HOME = D:\app\oracle10)
(SID_NAME = DB10)
)
)
tnsnames.ora DB9 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>))
)
(CONNECT_DATA =
(SERVICE_NAME = DB9)
)
) DB10 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port>)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL10g) ) ) IPC_TNS_ALIAS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(key=DB10)) ) (CONNECT_DATA = (SERVICE_NAME = DB10) ) ) sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
You can verify the configuration using tnsping and sqlplus for this tns service alias 'IPC_TNS_ALIAS' in the tnsnames.ora file as below (and confirm that it uses IPC): C:\>tnsping IPC_TNS_ALIAS
TNS Ping Utility for 32-bit Windows: Version 9.2.0.4.0 - Production on 12-MAY-2004 16:52:30
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
D:\app\oracle9\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)
(key = DB10))) (CONNECT_DATA = (SERVICE_NAME = DB10)))
OK (40 msec)
C:\sqlplus <username>/<password>@IPC_TNS_ALIAS SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 12 16:53:05 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle10i Enterprise Edition Release 10.1.0.1.0 - Beta With the Partitioning, OLAP and Data Mining options
SQL> select instance_name from v$instance;
INSTANCE_NAME ---------------- DB10 Connect to the source database named DB9 and create the DBLink to the target database DB10 as shown below: SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
DB9
SQL> create database link <dblink_name> connect to <username>
identified by <password> using 'IPC_TNS_ALIAS';
Database link created. SQL> select instance_name from v$instance@<dblink_name>;
INSTANCE_NAME
----------------
DB10 |