[DB] Oracle에 MSSQL DB Link 생성

Oracle에서 mssql 서버를 연결하여 통신이 필요할 때 사용할 수 있습니다. 정보가 많이 없었는데 아래 출처의 블로그에 정리가 잘 되어있어 참고하여 작업하였습니다.

(참고 블로그 : https://positivemh.tistory.com/626)

 

해당 포스팅은 Oracle DB에 mssql DB Link를 생성하는 글이며, mssql에서 오라클 DB에 연결할 때에는 Linked Server를 생성하여 사용해야 합니다.

 

작업한 내용을 정리하기 위해 포스팅 합니다.

 

서버간 통신을 위해 ODBC 설정을 해야합니다. (방화벽 접근권한이 허용되어있어야 합니다.)

아래 내용을 오라클이 설치된 리눅스 OS에서 작업합니다.

 

1. unixODBC를 설치합니다.

# yum install unixODBC*

 

2. FreeTDS를 설치해줍니다.

freetds는 서버에서 mssql 통신을 도와주는 linux용 라이브러리입니다.

https://www.freetds.org/ 에서 tar파일을 linux에 설치하고 설치된 경로에서 다음 커맨드를 실행합니다.

#  tar -xvf freetds-1.3.18.tar.gz
#  cd freetds-1.3.18/
#  ./configure -prefix=/usr/local/freetds -enable-msdblib -with-gnu-ld
#  make
#  make install

 

mobaXterm을 사용하면 윈도우에서 간단하게 linux로 파일을 옮길 수 있습니다.

 

3. freetds.conf 파일을 수정합니다.

#  vi /usr/local/freetds/etc/freetds.conf

[msdb]
host = xxx.xxx.xxx.xxx # MsSQL Server IP Address
port = 1433 # MsSQL Server Port
tds version = 8.0
client charset = UTF-8

세팅 후 FreeTDS tsql 정상 동작 체크

#  /usr/local/freetds/bin/tsql -S msdb -U <DB계정> -P '<패스워드>'

locale is "ko_KR.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select 1;
2> go

1
(1 row affected)
1> quit

 

4. odbc.ini 파일 설정합니다.

#  vi /etc/odbc.ini

[msdb]
Description = msdb CONNECTION
Driver = FreeTDS
Servername = msdb
Database = master

isql 정상 동작 체크

#  /usr/bin/isql -v msdb <MSSQL계정> '<비밀번호>'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select top 5 * from table;

 

odbc 세팅이 완료되었다면 오라클 설정 파일을 세팅하여야 합니다.

 

5. 오라클에 로그인합니다.

# su - oracle

 

6. initmsdb.ora 파일 생성합니다 (init{odbc명}.ora)

$ vi $ORACLE_HOME/hs/admin/initmsdb.ora
HS_FDS_CONNECT_INFO = msdb
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/local/freetds/lib/libtdsodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.KO16MSWIN949
set ODBCINI=/etc/odbc.ini

 

7. listener.ora 수정

$ vi $ORACLE_HOME/network/admin/listener.ora

--다음 내용을 하단에 추가 
(ORACLE_HOME은 $ORACLE_HOME/hs/admin/listener.ora.sample 파일에 있음)

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=msdb)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
         (PROGRAM=dg4odbc)
      )
  )

 

8. 리스너 재시작

$ lsnrctl stop 
$ lsnrctl start

 

9. tnsnames.ora 수정

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

하단에 아래내용 추가 (Host는 localhost로 고정)
msdb_tns =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =(SID = msdb))
    (HS = OK)
  )

tnsping 체크 (OK 가 뜨면 정상)

$ tnsping msdb_tns

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 17-AUG-2023 14:55:10

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA =(SID = msdb)) (HS = OK))
OK (0 msec)

 

OS에서 mssql과 통신할 준비가 완료되었고, DB에서 DB Link를 생성해야합니다.

 

10. DBA계정으로 접속하여 DB Link 생성권한 부여

$ sqlplus '/as sysdba'
SQL> grant create database link to <Oracle계정>;

 

11. DB Link를 생성

create database link msserver connect to "<MSSQL 계정>" identified by "<패스워드>" using 'msdb_tns';

DB Link를 이용하여 쿼리를 실행해봅니다.

SELECT * FROM "test_table"@msserver;

 

추가로 t-sql 문법을 통해 mssql을 실행하는 프로시저 입니다.

create or replace PROCEDURE use_tsql
(
    l_sql       VARCHAR2
)
IS
  c             INTEGER;
  nr            INTEGER;

BEGIN
    c  := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@MSSERVER;
    DBMS_HS_PASSTHROUGH.PARSE@MSSERVER(c, l_sql);
    nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@MSSERVER(c);
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@MSSERVER(c);
    COMMIT;
END;

위 프로시저를 통해 Oracle에서 t-sql문을 사용할  수 있습니다.