Clicky Web Analytics

Clicky

Mar
4
Wed
Posted By ritzy on Wednesday, March 04, 2009
14146 Views 7 Comments


Using Oracle's heterogenous services also known as HS, we can make a connection to non-Oracle databases and access the data remotely. I used MS SQL server as the target non-Oracle database on Windows and used HS to access from Oracle database residing on Linux. Here, we'll see step by step instructions on how to set up a connection from Oracle database to Microsoft SQL server.

In this example, I've used the following:
Source Oracle database on Linux:test-oralin
Target SQL server on Windows:test-mssqlwin
DSN name:MSORA

Our objective is to make a connection from test-oralin to test-mssqlwin and access the SQL server database from Oracle. Here are the steps:

1. On test-mssqlwin, install Oracle Home. Example: E:\OraHome
Note: Only Oracle binaries are needed here so you need not create a database.

2. On test-mssqlwin, install ODBC drivers so that Oracle can access SQL server

3. On test-mssqlwin, configure ODBC by setting up a system DSN(Data Source Name)
Typically you can find this at Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC) however depending on the Windows version, this may differ slightly.
On System DSN tab, click Add button and then choose the driver you want to use to connect to SQL server. Continue with the ODBC configuration by entering the details of the SQL server you wish to connect to along with necessary credentials. For this test, I've chosen "MSORA" as my DSN name.

4. On test-mssqlwin, setup the heterogenous services init file.
Look for inithsodbc.ora in $ORACLE_HOME/hs/admin/ which in our case would be E:\OraHome\hs\admin. Make a copy of it and rename it to initMSORA.ora. Remember we had chosen "MSORA" as our DSN name. Open the file and enter the DSN name that you just created recently i.e "MSORA". This is how E:\OraHome\hs\admin\initMSORA.ora should look like:
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSORA
HS_FDS_TRACE_LEVEL = OFF

5. On test-mssqlwin, configure listener.ora. Take a sample copy from E:\OraHome\hs\admin\listener.ora.sample and copy it to E:\OraHome\network\admin\listener.ora
This is how your E:\OraHome\network\admin\listener.ora should look like:

LISTENER_MSORA =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=test-mssqlwin)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MSORA=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=MSORA)
         (ORACLE_HOME = E:\OraHome)
         (PROGRAM=hsodbc)
      )
  )

Note: SID_NAME should be your DSN name. PROGRAM=hsodbc tells Oracle to use heterogenous services. Restart your listener to make sure the settings are in effect.

We have completed our configuration on the target MS SQL server i.e test-mssqlwin. Now let us move to our source Oracle database i.e test-oralin from where we wish to access test-mssqlwin

6. On test-oralin, configure your $ORACLE_HOME/network/admin/tnsnames.ora
MSORA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-mssqlwin)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = MSORA)
    )
   (HS=OK)
  )
Note:SID should be the DSN name created on test-mssqlwin. PORT should be same as what was configured for the listener on Oracle Home that was installed on test-mssqlwin. HS=OK means Oracle will use heterogenous services.

7. Verify the connection from test-oralin to test-mssqlwin by doing a tnsping MSORA from test-oralin.

8. We are all set now so let us access the SQL server database now from Oracle.
On test-oralin, connect to the Oracle database from where you want to access SQL server database located on test-mssqlwin
Make sure that global_names parameter is set to false
SQL> sho parameter global_names

NAME               TYPE             VALUE
------------------ ---------------- -------
global_names       boolean          FALSE

Create a database link:
CREATE DATABASE LINK <<dblinkname>> CONNECT TO <<username>> IDENTIFIED BY <<password>> USING '<<connection string>>';

Example:
SQL> CREATE DATABASE LINK MSORA CONNECT TO REPL IDENTIFIED BY REPL USING 'MSORA';

Database link created.

Note: REPL is a user on SQL server database i.e test-mssqlwin with REPL as it's password. 'MSORA' was given as a connection string in tnsnames.ora. Here, we have chosen MSORA as dblinkname, you could have any name.

We have completed our configuration. You may start accessing the data now.
Example:
SQL> desc emp@msora
 Name          Null?      Type
 ------------- --------   --------------
 EMPID         NOT NULL   NUMBER(1)
 EMPNAME                  VARCHAR2(60)
 CREATED                  DATE

Categories

Rants & Raves Minimize

  • Gravatar
    Subodh Thursday, March 12, 2009 at 1:50 AM
    Re: Heterogenous connection from Oracle database to Microsoft SQL server
    You have definitely made making the connections look easy. Reading, Writing data to and from tables across databases is another challenge that I think you have already solved. You should write more about that, about executing stored procs across winking

    • Gravatar
      Ritzy Thursday, March 12, 2009 at 2:08 AM
      Re: Heterogenous connection from Oracle database to Microsoft SQL server
      Oh Yeah, I remember those good old days when we together solved so many umpteen issues with accessing data remotely. Unfortunately we never documented those. As you said, its a good idea indeed. Shall test out all those cases on my test bed soon.

      • Gravatar
        Subodh Thursday, March 12, 2009 at 3:32 AM
        Re: Shall test out all
        Yep, We should have documented all that. But then again, we never had time did we? happy

    • Gravatar
      prathap Thursday, August 09, 2012 at 6:06 AM

      hi Subodh,

      i am facing one error when i am retriving data from oracle to sql server via heterogeneous services.
      even though i am able to ping also my tns entry and mu listner also working fine.
      please find this error :

      ERROR at line 1:
      ORA-28545: error diagnosed by Net8 when connecting to an agent
      Unable to retrieve text of NETWORK/NCR message 65535
      ORA-02063: preceding 2 lines from MSSQL07

      and in another server i did same way that one is working fine........and we are able to fetching data from sql server. in this server my ODBC drive i am using SQL Native Client 10.0 version.

      please help out me...it is urgency......



  • Gravatar
    Muhammad Wednesday, April 10, 2013 at 1:33 AM

    Hello Ritzy.

    What if I want to reverse the process i.e. access oracle from SQL server?

    Thanks

    • Gravatar
      Ritzy Saturday, April 13, 2013 at 2:55 PM

      You can use linked server from SQL Server Management Studio

  • Gravatar
    Arpan Tuesday, June 25, 2013 at 5:49 PM


    I got the mentioned error when i connected from Oracle 11g to SQL server.

    Error:

    SQL> select countstar from taxonomy@MSORA;
    select countstar from taxonomy@MSORA
    *
    ERROR at line 1:
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from MSORA

    Gateway executable being used was HSODBC which is the name for the gateway executable for version 10 and lower. It should be DG4ODBC for version 11g and higher.

    Fix: Change this portion of the gateway listener.ora file

    From this

    SID_LIST_LISTENER_MSORA=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=MSORA)
    (ORACLE_HOME = U:\app\sergio.giraldo\product\11.2.0\dbhome_2)
    (PROGRAM=hsodbc)))

    To this

    SID_LIST_LISTENER_MSORA=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=MSORA)
    (ORACLE_HOME = U:\app\sergio.giraldo\product\11.2.0\dbhome_2)
    (PROGRAM= dg4odbc)))

    Works fine now:

    SQL> select countstar from taxonomy@MSORA;

    COUNTstar
    ----------
    742518

Recommended Oracle DBA Books Minimize

     

Tag Cloud Minimize


Archive Posts Minimize
 
Monthly
    Yearly

    Disclaimer:
    This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

    This posting has nothing to do with my present or past employer.