In Oracle : Oracle Heterogeneous Services

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Heterogeneous Services

 

 Oracle Heterogeneous Services

 

Heterogeneous services within oracle database allows to connection with non-oracle database systems.

Oracle provides two types of Heterogeneous Services agents:

                                   Oracle Transparent Gateway agent -> specific to a database better flexibility than the generic one; better performance; you have to pay extra for it

                                   Generic Connectivity agent -> low-end solution that makes use of ODBC or OLE-DB drivers for accesing the externat databases.

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 = )

Oracle Heterogeneous Services

 

 

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

Oracle Heterogeneous Services

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

Oracle Heterogeneous Services

4. Name:               MySQLServerDS                                                                                       (DS from Data Source)

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

    Server:              CATA1\SqlExpress 

 

Click on "Next".

Oracle Heterogeneous Services

 

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

Click on "Next".

Oracle Heterogeneous Services

 

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

Oracle Heterogeneous Services

 

7. Click on "Finish".

 

The following screen will appear:

Oracle Heterogeneous Services

 

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

Oracle Heterogeneous Services

 

Click on "OK".

 

You will see the new System Data Source Name added:

Oracle Heterogeneous Services

 

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

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Heterogeneous Services

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.