Home  >  Oracle Database Administration (DBA)  >  Architecture  >  Oracle Heterogeneous Services 

Note: The article ( named Oracle Heterogeneous Services ) was taken from www.in-oracle.com.

Example of how to use the Generic Connectivity agent

(connection to the SQL Server, both Oracle and SQL Server are on the same PC on Windows)

Steeps to implement it:

A. Create the DSN (Data Source Name)

B. Configure Oracle to connect to SQL Server

A. Create the DSN (Data Source Name)

1. Go to Start -> Control Panel -> Administravive tools -> Data Sources (ODBC) -> System DSN         (DSN = )

2. Create the DSN that will connect to the SQL Server database.  First click on "Add..."

3. Choose the driver to use for the SQL Server. Click on "Finish".

4. Name:               MySQLServerDS                                                                                       (DS from Data Source)

    Description:       ... put your description here ...

    Server:              CATA1\SqlExpress 

Click on "Next".

5. You can choose a Windows or SQL Server authentication. I will choose the first option.

Click on "Next".

6. Choose the database you want to connect to. In my case is sql.  Click on "Next".

7. Click on "Finish".

The following screen will appear:

Click on "Test Data Source..." to test the data source. The following screen should appear:

Click on "OK".

You will see the new System Data Source Name added:

B. Configure Oracle to connect to SQL Server

This point B. includes:

        a) Create a heterogeneous service configuration file

        b) Configure the listener.ora for listening to this service

Here are the steps including a) and b) :

1. Open the ORACLE_HOME\hs\admin\initdg4odbc.ora  file within a text editor (this is the default init file for using ODBC conections)

2. In this file you must add:

    HS_FDS_CONNECT_INFO = <ODBC Data Source Name>

    HS_FDS_TRACE_LEVEL = <trace level>

    for instance,

    HS_FDS_CONNECT_INFO = MySQLServerDS
    HS_FDS_TRACE_LEVEL = 0

3. Configure the listener.ora

Add in the ORACLE_HOME\network\admin\listener.ora the following:

(SID_DESC=
    (SID_NAME=mySQLservice)
    (ORACLE_HOME=D:\Oracle11gR2\OH)
    (PROGRAM=dg4odbc)
)

In this case the listener.ora will be:

SID_LIST_LISTENER =
  (SID_LIST =
        (SID_DESC =
                 (SID_NAME = CLRExtProc)
                 (ORACLE_HOME = D:\Oracle11gR2\OH)
                 (PROGRAM = extproc)
                 (ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle11gR2\OH\bin\oraclr11.dll")
         )

         (SID_DESC=
                 (SID_NAME=mySQLservice)
                 (ORACLE_HOME=D:\Oracle11gR2\OH)
                 (PROGRAM=dg4odbc)
          )
    )

LISTENER =
    (DESCRIPTION_LIST =
                 (DESCRIPTION =
                         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
                         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
)

ADR_BASE_LISTENER = D:\Oracle11gR2

4. Reload the listener configuration

C:\> lsnrctl reload

5. Check if the listener see that new service (mySQLservice).

C:\Documents and Settings\Administrator>lsnrctl stat

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 06-FEB-2011 10:54:43

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 06-FEB-2011 08:49:44
Uptime 0 days 2 hr. 5 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle11gR2\OH\network\admin\listener.ora
Listener Log File d:\oracle11gr2\diag\tnslsnr\cata1\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "db11" has 1 instance(s).
Instance "db11", status READY, has 1 handler(s) for this service...
Service "db11XDB" has 1 instance(s).
Instance "db11", status READY, has 1 handler(s) for this service...
Service "mySQLservice" has 1 instance(s).
Instance "mySQLservice", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Note: The article ( named Oracle Heterogeneous Services ) was taken from www.in-oracle.com.