Accessing to Sql Server from Oracle Database

Accessing to Sql Server from Oracle Database

جهت برقراری ارتباط از دیتابیس اوراکل به اس کیو ال از طریق دیتابیس لینک اقدامات زیر را انجام دهید:

نصب و تنطیم ODBC Driver

  1. نصب rpm های زیر روی سیستم عامل دیتابیس اوراکل:

    • unixODBC
    •  unixODBC-devel
    • freetds
  2. اعتبار سنجی rpm های نصب شده:

    • # odbcinst -j
      unixODBC 2.3.1
      DRIVERS…………: /etc/odbcinst.ini
      SYSTEM DATA SOURCES: /etc/odbc.ini
      FILE DATA SOURCES..: /etc/ODBCDataSources
      USER DATA SOURCES..: /root/.odbc.ini
      SQLULEN Size…….: 8
      SQLLEN Size……..: 8
      SQLSETPOSIROW Size.: 8
      #
    • # tsql -C
      Compile-time settings (established with the “configure” script)
                                  Version: freetds v0.95.81
                   freetds.conf directory: /etc
           MS db-lib source compatibility: yes
              Sybase binary compatibility: yes
                            Thread safety: yes
                            iconv library: yes
                              TDS version: 4.2
                                    iODBC: no
                                 unixodbc: yes
                    SSPI “trusted” logins: no
                                 Kerberos: yes
                                  OpenSSL: no
                                   GnuTLS: yes
      #
  3. فایل etc/odbcinst.ini/ را باز کرده و مطمئن شوید دارای اطلاعات و پارامتر های زیر می باشد:

    • [FreeTDS]
      Description=FreeTDS unixODBC Driver
      Driver=/usr/lib64/libtdsodbc.so.0
      Setup=/usr/lib64/libtdsodbc.so.0
      TDS_Version=7.2
      UsageCount=1
  4. فایل etc/odbc.ini/ را باز کرده و data source را مقدار دهی نمایید:

    • [ODBC Data Sources]
      SQLSERVER = MSSQL Server
      [SQLSERVER]
      # Reference driver from the “/etc/odbcinst.ini” file.
      # Driver          = FreeTDS
      # Or use a direct driver reference, rather than reference
      # one in the “/etc/odbcinst.ini” file.
      Driver=/usr/lib64/libtdsodbc.so.0
      Description     = MSSQL Server
      Trace           = No
      Server          = IVR (اسم دی ان اس مربوط به اس کیو ال سرور)
      ( اسم دیتابیس اس کیو ال)Database        = MEBDb
      Port            = 1433
      TDS_Version     = 7.2
      #QuotedId=YES
      #AnsiNPW=YES
      #VarMaxAsLong=YES
    • [Default]
      Driver          = /usr/lib64/libtdsodbc.so.0
  5. تست Data Source Name:

    1. osql -S SQLSERVER -U BIuser -P BItelpass
    2. [oracle@dwlivelinux ~]$ osql -S SQLSERVER -U BIuser -P BItelpass
      checking shared odbc libraries linked to isql for default directories…
      strings: ”: No such file
              trying /tmp/sqlH … no
              trying /tmp/sqlL … no
              trying /etc … OK
      checking odbc.ini files
              reading /home/oracle/.odbc.ini
      [SQLSERVER] not found in /home/oracle/.odbc.ini
              reading /etc/odbc.ini
      [SQLSERVER] found in /etc/odbc.ini
      found this section:
              [SQLSERVER]
              # Reference driver from the “/etc/odbcinst.ini” file.
              # Driver          = FreeTDS
              # Or use a direct driver reference, rather than reference
              # one in the “/etc/odbcinst.ini” file.
              Driver=/usr/lib64/libtdsodbc.so.0
              Description     = MSSQL Server
              Trace           = No
              Server          = IVR
              Database        = MEBDb
              Port            = 1433
              TDS_Version     = 7.2
              #QuotedId=YES
              #AnsiNPW=YES
              #VarMaxAsLong=YES
      looking for driver for DSN [SQLSERVER] in /etc/odbc.ini
        found driver line: ”  Driver=/usr/lib64/libtdsodbc.so.0″
        driver “/usr/lib64/libtdsodbc.so.0” found for [SQLSERVER] in odbc.ini
      found driver named “/usr/lib64/libtdsodbc.so.0”
      /usr/lib64/libtdsodbc.so.0 is an executable file
      “Server” found, not using freetds.conf
      Server is “IVR”
      Configuration looks OK.  Connection details:
                         DSN: SQLSERVER
                    odbc.ini: /etc/odbc.ini
                      Driver: /usr/lib64/libtdsodbc.so.0
             Server hostname: IVR
                     Address: 172.20.34.171
      Attempting connection as BIuser …
      + isql SQLSERVER BIuser BItelpass -v
      +—————————————+
      | Connected!                            |
      |                                       |
      | sql-statement                         |
      | help [tablename]                      |
      | quit                                  |
      |                                       |
      +—————————————+
      SQL>

تنظیمات اوراکل

  1. اضافه کردن مقادیر زیر در فایل ORACLE_HOME/hs/admin/initSQLSERVER.ora$ :

    • HS_FDS_CONNECT_INFO = SQLSERVER
      HS_FDS_TRACE_LEVEL = ON
      HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
      #
      # ODBC specific environment variables
      #
      set ODBCINI=/etc/odbc.ini
      HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
      HS_NLS_NCHAR=UCS2
      HS_LANGUAGE=AMERICAN_AMERICA.UTF8
      HS_RPC_FETCH_REBLOCKING= OFF
      HS_FDS_FETCH_ROWS = 1
  2. فایل tnsnames.ora را باز کرده و مقادیر زیر را وارد کنید:

    • SQLSERVER =
                (DESCRIPTION =
                (ADDRESS = (protocol= TCP)(host= (آپی لوکال دیتابیس اوراکل))(port= 1521))
                (connect_data = (sid= SQLSERVER))
                (hs=ok)

      )

  3. فایل  listener.ora را باز کرده و مقادیر زیر را اضافه نمایید:

    •   (SID_DESC =
          (SID_NAME = SQLSERVER)
            (ORACLE_HOME = /u01/app/oracle/product/11.2.0/WDB)
            (PROGRAM = dg4odbc)
                )
  4. lsnrctl reload

  5. ساختن دیتابیس لینک:

    • CREATE DATABASE LINK SQLSERVER1 CONNECT TO “MyUser” IDENTIFIED BY “MyPassword” USING ‘SQLSERVER’;
موفق باشین
محسن صفابخش

 

In English

Install, Configure ODBC Driver

1.install unixODBC unixODBC-devel freetds rpm’s

2.Validate Installation

# odbcinst -j
unixODBC 2.3.1
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8
#
# tsql -C
Compile-time settings (established with the “configure” script)
                            Version: freetds v0.95.81
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI “trusted” logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
#

3.Add the FreeTDS driver into the “/etc/odbcinst.ini” driver list.

[FreeTDS]
Description=FreeTDS unixODBC Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsodbc.so.0
TDS_Version=7.2
UsageCount=1

4.Create Data Source Name (DSN)
vi /etc/odbc.ini

[ODBC Data Sources]
SQLSERVER = MSSQL Server
[SQLSERVER]
# Reference driver from the “/etc/odbcinst.ini” file.
# Driver          = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the “/etc/odbcinst.ini” file.
Driver=/usr/lib64/libtdsodbc.so.0
Description     = MSSQL Server
Trace           = No
Server          = IVR– (DNS name)–
Database        = MEBDb –(DB name)–
Port            = 1433
TDS_Version     = 7.2
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
[Default]
Driver          = /usr/lib64/libtdsodbc.so.0

5.Test Data Source Name (DSN)

osql -S SQLSERVER -U BIuser -P BItelpass
[oracle@dwlivelinux ~]$ osql -S SQLSERVER -U BIuser -P BItelpass
checking shared odbc libraries linked to isql for default directories…
strings: ”: No such file
        trying /tmp/sqlH … no
        trying /tmp/sqlL … no
        trying /etc … OK
checking odbc.ini files
        reading /home/oracle/.odbc.ini
[SQLSERVER] not found in /home/oracle/.odbc.ini
        reading /etc/odbc.ini
[SQLSERVER] found in /etc/odbc.ini
found this section:
        [SQLSERVER]
        # Reference driver from the “/etc/odbcinst.ini” file.
        # Driver          = FreeTDS
        # Or use a direct driver reference, rather than reference
        # one in the “/etc/odbcinst.ini” file.
        Driver=/usr/lib64/libtdsodbc.so.0
        Description     = MSSQL Server
        Trace           = No
        Server          = IVR
        Database        = MEBDb
        Port            = 1433
        TDS_Version     = 7.2
        #QuotedId=YES
        #AnsiNPW=YES
        #VarMaxAsLong=YES
looking for driver for DSN [SQLSERVER] in /etc/odbc.ini
  found driver line: ”  Driver=/usr/lib64/libtdsodbc.so.0″
  driver “/usr/lib64/libtdsodbc.so.0” found for [SQLSERVER] in odbc.ini
found driver named “/usr/lib64/libtdsodbc.so.0”
/usr/lib64/libtdsodbc.so.0 is an executable file
“Server” found, not using freetds.conf
Server is “IVR”
Configuration looks OK.  Connection details:
                   DSN: SQLSERVER
              odbc.ini: /etc/odbc.ini
                Driver: /usr/lib64/libtdsodbc.so.0
       Server hostname: IVR
               Address: 172.20.34.171
Attempting connection as BIuser …
+ isql SQLSERVER BIuser BItelpass -v
+—————————–———-+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+—————————–———-+
SQL>

6. Create a file $ORACLE_HOME/hs/admin/initSQLSERVER.ora wich contains:

HS_FDS_CONNECT_INFO = SQLSERVER
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1

7.Add the following entry to the “tnsnames.ora” file.

SQLSERVER =
          (DESCRIPTION =
          (ADDRESS = (protocol= TCP)(host= localhost)(port= 1521))
          (connect_data = (sid= SQLSERVER))
          (hs=ok)
          )

8.Add the relevant version-specific entry into the “listener.ora” file. Remember to adjust

your ORACLE_HOME setting appropriately.

  (SID_DESC =
    (SID_NAME = SQLSERVER)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/WDB)
      (PROGRAM = dg4odbc)
          )

9.lsnrctl reload

10.Create a database link as follows. Remember to follow any rules regarding username/password case for the remote server.

CREATE DATABASE LINK SQLSERVER1 CONNECT TO “MyUser” IDENTIFIED BY “MyPassword” USING ‘SQLSERVER’;

 

 

Good luck
Safabakhsh Mohsen

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *