Accessing to Sql Server from Oracle Database
جهت برقراری ارتباط از دیتابیس اوراکل به اس کیو ال از طریق دیتابیس لینک اقدامات زیر را انجام دهید:
نصب و تنطیم ODBC Driver
-
نصب rpm های زیر روی سیستم عامل دیتابیس اوراکل:
- unixODBC
- unixODBC-devel
- freetds
-
اعتبار سنجی rpm های نصب شده:
-
# odbcinst -junixODBC 2.3.1DRIVERS…………: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /root/.odbc.iniSQLULEN Size…….: 8SQLLEN Size……..: 8SQLSETPOSIROW Size.: 8#
-
# tsql -CCompile-time settings (established with the “configure” script)Version: freetds v0.95.81freetds.conf directory: /etcMS db-lib source compatibility: yesSybase binary compatibility: yesThread safety: yesiconv library: yesTDS version: 4.2iODBC: nounixodbc: yesSSPI “trusted” logins: noKerberos: yesOpenSSL: noGnuTLS: yes#
-
-
فایل etc/odbcinst.ini/ را باز کرده و مطمئن شوید دارای اطلاعات و پارامتر های زیر می باشد:
-
[FreeTDS]Description=FreeTDS unixODBC DriverDriver=/usr/lib64/libtdsodbc.
so.0 Setup=/usr/lib64/libtdsodbc.so.0 TDS_Version=7.2UsageCount=1
-
-
فایل 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 ServerTrace = NoServer = IVR (اسم دی ان اس مربوط به اس کیو ال سرور)( اسم دیتابیس اس کیو ال)Database = MEBDbPort = 1433TDS_Version = 7.2#QuotedId=YES#AnsiNPW=YES#VarMaxAsLong=YES -
[Default]Driver = /usr/lib64/libtdsodbc.so.0
-
-
تست Data Source Name:
- osql -S SQLSERVER -U BIuser -P BItelpass
-
[oracle@dwlivelinux ~]$ osql -S SQLSERVER -U BIuser -P BItelpasschecking shared odbc libraries linked to isql for default directories…strings: ”: No such filetrying /tmp/sqlH … notrying /tmp/sqlL … notrying /etc … OKchecking odbc.ini filesreading /home/oracle/.odbc.ini[SQLSERVER] not found in /home/oracle/.odbc.inireading /etc/odbc.ini[SQLSERVER] found in /etc/odbc.inifound 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.0Description = MSSQL ServerTrace = NoServer = IVRDatabase = MEBDbPort = 1433TDS_Version = 7.2#QuotedId=YES#AnsiNPW=YES#VarMaxAsLong=YESlooking for driver for DSN [SQLSERVER] in /etc/odbc.inifound driver line: ” Driver=/usr/lib64/libtdsodbc.so.0″driver “/usr/lib64/libtdsodbc.so.0” found for [SQLSERVER] in odbc.inifound driver named “/usr/lib64/libtdsodbc.so.0”/usr/lib64/libtdsodbc.so.0 is an executable file“Server” found, not using freetds.confServer is “IVR”Configuration looks OK. Connection details:DSN: SQLSERVERodbc.ini: /etc/odbc.iniDriver: /usr/lib64/libtdsodbc.so.0Server hostname: IVRAddress: 172.20.34.171Attempting connection as BIuser …+ isql SQLSERVER BIuser BItelpass -v+—————————————+| Connected! || || sql-statement || help [tablename] || quit || |+—————————————+SQL>
تنظیمات اوراکل
-
اضافه کردن مقادیر زیر در فایل ORACLE_HOME/hs/admin/
initSQLSERVER.ora$ : -
HS_FDS_CONNECT_INFO = SQLSERVERHS_FDS_TRACE_LEVEL = ONHS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so## ODBC specific environment variables#set ODBCINI=/etc/odbc.iniHS_KEEP_REMOTE_COLUMN_SIZE = LOCALHS_NLS_NCHAR=UCS2HS_LANGUAGE=AMERICAN_AMERICA.
UTF8 HS_RPC_FETCH_REBLOCKING= OFFHS_FDS_FETCH_ROWS = 1
-
-
فایل tnsnames.ora را باز کرده و مقادیر زیر را وارد کنید:
-
SQLSERVER =(DESCRIPTION =(ADDRESS = (protocol= TCP)(host= (آپی لوکال دیتابیس اوراکل))(port= 1521))(connect_data = (sid= SQLSERVER))(hs=ok)
)
-
-
فایل listener.ora را باز کرده و مقادیر زیر را اضافه نمایید:
-
(SID_DESC =(SID_NAME = SQLSERVER)(ORACLE_HOME = /u01/app/oracle/product/11.2.
0/WDB) (PROGRAM = dg4odbc))
-
-
lsnrctl reload
-
ساختن دیتابیس لینک:
- 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