예전에 고객사 요청으로 이리저리 찾아보고 했던 내용이 있어서 정리해봤다.
============================================================
1. freetds 관련 패키지 및 툴 설치
============================================================
Install unixODBC-devel
yum install unixODBC
yum install unixODBC-devel
Install Development Tools
yum groupinstall “Development Tools”
Install freeTDS
tar zfvx freetds-stable.tgz
cd freetds-0.91
./configure –prefix=/usr/local/freetds –with-tdsver=8.0 –enable-msdblib –with-gnu-ld
make
make install
============================================================
2. /usr/local/freetds/etc/freetds.conf 수정
============================================================
[SQLSERVERADDRESS]
host = mssql서버 IP주소 # MsSQL Server IP Address
port = 1433 # MsSQL Server Port
tds version = 8.0 # Tds version for SQL Server 2008
client charset = UTF-8 # For support of Turkish characters
============================================================
3. /etc/odbc.ini , /etc/odbcinst.ini수정
============================================================
/etc/odbc.ini
[SQLDSN]
Description = SQLDSN CONNECTION
Driver = /usr/local/freetds/lib/libtdsodbc.so (freetds설치 시 깔리는 .so파일)
Servername = SQLSERVERADDRESS (freetds.conf의 설정 상단이름)
Database = DBNAME (sqlserver DBNAME)
[ODBC Data Sources]
SQLDSN=FreeTDS
###############################
/etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS 8.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
FileUsage=1
============================================================
4. isql tool을 통한 접속 테스트
============================================================
/usr/bin/isql -v odbc.ini상단[name] sqlserverlogin계정 'login계정패스워드)
/usr/bin/isql -v SQLDSN dgist 'dgistaccess!'
============================================================
5. 오라클 환경 설정
============================================================
cd $ORACLE_HOME/hs/admin
vi initSQLDB.ora
HS_FDS_CONNECT_INFO=SQLDSN(odbc.ini상단[name])
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_FMS_REMOTE_DB_CHARSET=KO16MSWIN949
HS_RPC_FETCH_REBLOCKING=OFF
HS_FDS_FETCH_ROWS=100
set ODBCINI=/etc/odbc.ini
============================================================
cd $ORACLE_HOME/network/admin
vi listener.ora
MSSQL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS= (PROTOCOL= TCP)(Host= ICK-DB)(Port= 1522))
(ADDRESS= (PROTOCOL= IPC)(KEY=EXTPROC1522))
)
)
SID_LIST_MSSQL =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/oracle/app/product/11.2.0.4)
(SID_NAME=ORACLE_SID)
)
(SID_DESC =
(ORACLE_HOME=/oracle/app/product/11.2.0.4)
(SID_NAME=SQLDB)
(PROGRAM=dg4odbc)
(ENVS= LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:$ORACLE_HOME/lib)
)
)
lsnrctl start MSSQL -- 별로 리스너 생성 및 START.
============================================================
tnsnames.ora에 추가
vi $ORACLE_HOME/network/admin/tnsnames.ora
Add below lines.
SQLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SID = SQLDB)
)
(HS = OK)
)
tnsping SQLDB
(ok)
============================================================
6.db link 생성
============================================================
DB접속
sqlplus userid/passwd
CREATE DATABASE LINK link이름 CONNECT TO “sqlserver로그인이름” IDENTIFIED BY “로그인패스워드” USING ‘SQLDB’;
select * from 테이블@SQLDB