Blog

Oracle 10g: Create Heterogenous DBLink to connect to other databases

To create an heterogenous database link (to another database):

1) set an odbc connection named testdb and in advanced put username/password (admin/admin)

2) put inittestdb.ora in D:\oracle\product\10.2.0\db_1\hs\admin (* see below)

3) put tnsnames.ora and listener.ora in D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN (* see below)

4) create a public database link with fixed user admin/admin:
In SYS AS SYSDBA execute:

CREATE PUBLIC DATABASE LINK "TESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM" CONNECT TO "ADMIN" IDENTIFIED by "ADMIN" USING 'TESTDB'

(select * from user_db_links;)

 

 

ATTENTION:
select * from test@”TESTDB” produces the following error:
ORA-02019: connection description for remote database not found

Use:
select * from test@”TESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM”

 

 

inittestdb.ora:

HS_FDS_CONNECT_INFO=TESTDB

 

 

tnsnames.ora:

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
 (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP) (HOST = vmoracle) (PORT = 1521) )
         (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = orcl)
     )
 )

EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1) )
     )
     (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
     )
 )

TESTDB =
 (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP) (HOST = vmoracle) (PORT = 1521) )
     )
     (CONNECT_DATA =
         (SERVICE_NAME = TESTDB)
     )
     (HS = OK)
 )

 

 

listener.ora:

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
 (SID_LIST =
     (SID_DESC =
         (GLOBAL_DBNAME = TESTDB)
         (PROGRAM = HSODBC)
         (SID_NAME = TESTDB)
         (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
     )
 )

LISTENER =
 (DESCRIPTION_LIST =
     (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1) )
         (ADDRESS = (PROTOCOL = TCP) (HOST = vmoracle) (PORT = 1521) )
     )
 )

 

Leave a Reply