Sunday, July 08, 2012

Heterogeneous Connection

This document will guide you to establish a database link from Oracle Database server to SQL Server so as to fetch data from SQL Server into Oracle Database server.
  1. Infrastructure
  2. Setup Tasks
 
1. Infrastructure
 
  • Oracle Server (192.168.1.11): - henceforth would be referred as 'Oracle' server
  • MSSQL Server (192.168.1.12): - henceforth would be referred as 'SQL' server
 
2. Setup Tasks
 
Below is a series of steps that are needed to setup the HS service.
 
  1. First, a new user must be created in the 'SQL' server and necessary object privileges must be granted to access the objects residing in 'SQL' server. Because, the database link from 'Oracle' server will be created using this credential.
  1. Setup ODBC connection in 'SQL' server. Choose a custom Data Source Name (DSN). This DSN name will be used as "Instance Name" for 'SQL' database (e.g. sqldblive). In the entire HS configuration, prefer either a lowercase or an uppercase for any naming convention to avoid confusion.
  1. In the 'Oracle' server, ensure the GLOBAL_NAMES parameter is set to FALSE.

  2. SQL> select name, value from v$parameter where name like 'GLOBAL_NAMES%';
    
    NAME                VALUE
    ------------------- -------------------
    global_names        FALSE
  1. Install 'Oracle' (preferably 11g) server software in the 'SQL' Server. No need to create any database in this server.
  1. Setup HS file 'SQL' Server:

  2. - locate inithsodbc.ora file in ORACLE_HOME/hs/admin folder
    - Make a copy of this file in the same location and rename it as init.ora (e.g. initsqldblive.ora
    - Open 'initsqldblive.ora' and locate the following lines:
     
    HS_FDS_CONNECT_INFO = ''
    HS_FDS_TRACE_LEVEL = ''
     
    - Change as shown below:
     
    HS_FDS_CONNECT_INFO = sqldblive
    HS_FDS_TRACE_LEVEL = OFF
  1. Configure the LISTENER service in 'SQL' Server. For example:

  2. LISTENER_SQLDBLIVE =
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521))
        (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
    
    
    SID_LIST_LISTENER_SQLDBLIVE=
      (SID_LIST=
         (SID_DESC=
            (SID_NAME=sqldblive)
            (ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
            (PROGRAM=dg4odbc)
         )
      )
  1. Configure TNSNAMES.ORA in 'Oracle' Server. Please ensure that from 'Oracle' Server, you are able to telnet 'SQL' server port (1521) to which the listener serice lis listening (see step vi).

  2. sqldblive =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
        (CONNECT_DATA =
          (SID = sqldblive))
        (HS = OK)
      )
  1. From the 'Oracle' server, you should be able to tnsping 'sqldblive'
  1. Finally, create database link in 'Oracle' server. It's important that the username and password must be in double quotes.

  2. SQL> create database link sqldblive_lnk connect to "<username>" identified by "<password>" using 'sqldblive';
    SQL> select count(*) from @sqldblive_lnk;

    If you are able to see the count numbers, then it's done!
 
[ Top ]
 
Last modified: Jul 05, 2012 11:45 IST

No comments: