Creation of dblink from Oracle to SQL Server
Posted by Mir Sayeed Hassan on January 31st, 2018
Creation of dblink from Oracle to SQL Server
Step by step procedure to create a database link from Oracle Database 11g to SQL Server.
Database Version:
sys@testdb> select INSTANCE_NAME, VERSION from v$instance; INSTANCE_NAME VERSION ---------------- ----------- testdb 11.2.0.4.0
Check the unixODBC Version install init
[root@testdb ~]# rpm -qa unixODBC unixODBC-2.3.1-10.el7.x86_64 or unixODBC-2.2.14-14.el6.x86_64
Note:
The Microsoft ODBC Driver 11 for SQL Server is compatible with only unixODBC 2.3.0, not 2.2.x or 2.3.1 or later & you need to remove & install the unixODBC 2.3.0
To remove the rpm packages:
[root@testdb ~]# rpm -e unixODBC-devel -- This remove the dependency of unixODBC
[root@testdb ~]# rpm -e unixODBC -- This remove the unixODBC Package
INSTALLATION OF UNIXODBC 2.3.0
It requires 2.3.0 which requires compiling and installing from source
Download unixodbc 2.3.0 from below link:
https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/
Example:
Extract the files to a directory and install
[root@testdbbackup]#ls unixODBC-2.3.0.tar.gz
[root@testdbbackup]# tar xvzf unixODBC-2.3.0.tar.gz
[root@testdbbackup]#ls unixODBC-2.3.0
[root@testdb unixODBC-2.3.0]# ls acinclude.m4 config.h.in COPYING DRVConfig install-sh ltmain.sh mkinstalldirs README.INTERIX stamp-h1 aclocal.m4 config.log cur exe Interix m4 NEWS README.OSX vms AUTHORS config.status depcomp extras libltdl Makefile odbcinst README.QNX vmsbuild.com ChangeLog config.sub doc include libtool Makefile.am README README.SOLARIS ylwrap config.guess configure DriverManager ini log Makefile.in README.AIX README.VMS config.h configure.in Drivers INSTALL lst missing README.CYGWIN samples
Now install by using the below command prompt with root login
# CPPFLAGS=”-DSIZEOF_LONG_INT=8″ # export CPPFLAGS. # ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE # make # make install
Note:
If you find the this error “configure: error: C compiler cannot create executables in oracle linux”
Need some require rpm package for gcc, cpp etc ..
cpp x86_64 4.4.7-18.el6 base 3.7 M
gcc-c++ x86_64 4.4.7-18.el6 base 4.7 M
libgcc i686 4.4.7-18.el6 base 115 k
libgcc x86_64 4.4.7-18.el6 base 103 k
libgomp x86_64 4.4.7-18.el6 base 134 k
libstdc++ i686 4.4.7-18.el6 base 303 k
libstdc++ x86_64 4.4.7-18.el6 base 295 k
libstdc++-devel i686 4.4.7-18.el6 base 1.6 M
libstdc++-devel x86_64 4.4.7-18.el6 base 1.6 M
Reference Link:
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-driver-manager
INSTALLATION OF MICROSOFT ODBC DRIVER 11 FOR SQL SERVER
Download Microsoft ODBC Driver 11g for SQL Server for RedHat Linux directly from Microsoft below link:
https://www.microsoft.com/en-us/download/details.aspx?id=36437
Example:
Extract the files to a directory and install.
[root@testdb~]# cd /backup/msodbcsql/
[root@testdb msodbcsql]# ls msodbcsql-11.0.2270.0.tar.gz
[root@testdb backup]# tar –xvf msodbcsql-11.0.2270.0.tar.gz
[root@testdb msodbcsql]# cd msodbcsql-11.0.2270.0/
[root@testdb msodbcsql-11.0.2270.0]# ls bin build_dm.sh docs include install.sh lib64 LICENSE README WARNING
[root@testdb msodbcsql-11.0.2270.0]# ./install.sh
[root@testdb msodbcsql-11.0.2270.0]# ./install.sh verify Microsoft ODBC Driver 11 for SQL Server Installation Script Copyright Microsoft Corp. Starting install for Microsoft ODBC Driver 11 for SQL Server Checking for 64 bit Linux compatible OS ..................................... OK Checking required libs are installed ........................................ OK unixODBC utilities (odbc_config and odbcinst) installed ..................... OK unixODBC Driver Manager version 2.3.0 installed ............................. OK unixODBC Driver Manager configuration correct .............................. OK* Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND Install log created at /tmp/msodbcsql.15451.4295.11649/install.log.
[root@testdb msodbcsql-11.0.2270.0]# ./install.sh install CTL+C Enter YES to accept the license or anything else to terminate the installation: YES Enter YES to accept the license or anything else to terminate the installation: YES Checking for 64 bit Linux compatible OS..................................... OK Checking required libs are installed........................................ OK unixODBC utilities (odbc_config and odbcinst) installed ..................... OK unixODBC Driver Manager version 2.3.0 installed ............................. OK unixODBC Driver Manager configuration correct .............................. OK* Microsoft ODBC Driver 11 for SQL Server already installed............ NOT FOUND Microsoft ODBC Driver 11 for SQL Server files copied........................ OK Symbolic links for bcp and sqlcmd created................................... OK Microsoft ODBC Driver 11 for SQL Server registered................... INSTALLED Install log created at /tmp/msodbcsql.10488.28346.5933/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
NOTE: Installation puts the driver in /opt/microsoft/msodbcsql/11.0.2270.0. The driver and its support files must be in /opt/microsoft/msodbcsql/11.0.2270.0.
VERIFY THE ODBC DRIVER INSTALL ON LINUX SUCCESSFULLY
[root@testdbmsodbcsql-11.0.2270.0]# odbc_config --version 2.3.0
[root@testdb msodbcsql-11.0.2270.0]# odbcinst -j unixODBC 2.3.0 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
[root@testdb msodbcsql-11.0.2270.0]# odbcinst -q -d [ODBC Driver 11 for SQL Server]
[root@testdb msodbcsql-11.0.2270.0]# odbcinst -q -d -n "ODBC Driver 11 for SQL Server" [ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1
SETUP ODBC DSN FOR A MICROSOFT SQL SERVER DATABASE
[root@testdb 11.0.2270.0]# vi /etc/odbc.ini [ODBC Data Sources] mirdb=MS SQL Server [mirdb] Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Description=Microsoft ODBC Driver 11 for SQL Server Server=192.168.120.183,1434 Database=mirdb User=sa Password=aS123456 QuotedId=YES AnsiNPW=YES Threading=1 UsageCount=1 AutoTranslate=No :wq!
[root@testdb 11.0.2270.0]# cat /etc/odbcinst.ini [ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1
CREATE A DG4ODBC AGENT INITIALIZATION FILE
[root@testdb admin]# pwd /u01/app/oracle/product/11.2.0/dbhome_1/hs/admin
[root@testdbadmin]# ls extproc.ora initdg4odbc.ora_bkp_30jan18 initmirdb.ora listener.ora.sample tnsnames.ora.sample
Note: Copy the sample initdg4odbc.ora to initmirdb.ora & edit as shown below
[root@testdb admin]# cat initmirdb.ora # This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters # HS_FDS_CONNECT_INFO = mirdb HS_FDS_TRACE_LEVEL = 0 HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252 HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL HS_NLS_LENGTH_SEMANTICS = CHAR HS_NLS_NCHAR = UCS2 # # ODBC specific environment variables # set ODBCINI= /etc/odbc.ini # Environment variables required for the non-Oracle system # #set =
Note: To check the listening port in database
[root@DB msodbcsql-11.0.2270.0]# netstat –tulpn
[root@testdb unixODBC-2.3.0]# telnet 192.168.120.183 1434 Trying 192.168.120.183... Connected to 192.168.120.183. Escape character is '^]'
[oracle@testdb admin]$ cat listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.123)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle #The below listerner configuration was set for dblink from oracle to sqlserver LISTENER_mirdb = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) ) SID_LIST_LISTENER_mirdb = (SID_LIST = (SID_DESC = (SID_NAME = mirdb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM = /u01/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc) (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0:/usr/lib64:/u01/app/oracle/product/11.2.0/dbhome_1/lib) ) )
[oracle@testdb admin]$ cat tnsnames.ora testdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.123)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = testdb) (UR = A) ) ) #The below tnsnames configuration was set for dblink from oracle to sqlserver mirdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = mirdb)) (HS = OK) )
Check the status of default LISTENER & LISTENER_mirdb
[oracle@testdb admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 30-JAN-2018 16:00:49
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.123)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date 30-JAN-2018 15:58:02
Uptime 0 days 0 hr. 2 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ogg-test1/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.120.123)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb)(PORT=8087))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “primXDB” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
Service “testdb” has 1 instance(s).
Instance “testdb”, status READY, has 1 handler(s) for this service…
The command completed successfully
[oracle@testdbadmin]$ lsnrctl status LISTENER_mirdb
LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 30-JAN-2018 16:00:59
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER_mirdb
Version TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date 30-JAN-2018 15:59:32
Uptime 0 days 0 hr. 1 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ogg-test1/listener_mirdb/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary…
Service “mirdb” has 1 instance(s).
Instance “mirdb”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
sys@testdb> CREATE PUBLIC DATABASE LINK mirdb_dblink CONNECT TO "SA" IDENTIFIED BY "aS123456" USING 'mirdb'; Database link created.
[oracle@testdb~]$ isql -v mirdb "sa" "aS123456" +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
SQL> select count(*) from information_schema.columns; +------------+ | | +------------+ | 3 | +------------+ SQLRowCount returns 0 1 rows fetched
Note:
As you can see the count “3” from information_schema.columns, This count is fetch from the SQL Server user “sa” contain the 3 tables init
[oracle@testdb admin]$ sqlplus / as sysdba
sys@testdb> select * from dbo.test2@mirdb_dblink; test2 ---------- 1 2 2 2
This above table created in SQL Server & fetch from Oracle Database
Important Note on SQL Server:
Connect with the “sa” user & create one database with “mirdb”, Perform some DDL/DML Operation & verify from the Oracle Database