Windows에서 postgresql Oracle DB Link 사용 (Oracle foreign table)

(중요) - 자세히 읽어보세요.

윈도우 서버 2016(windows server 2016 std)기반의 PostgreSQL 10.1 에서 Oracle DB를 연결하기 위한 방법임.

제목은 DB link라고 썼으나 실제로는 외부테이블을 만들어서 연동하는 방법.

오라클 인스턴스 클라이언트를 사용하여 셋팅하였음.

*오라클 인스턴스 클라이언트 설정

1) 오라클 인스턴스 클라이언트 다운로드 ( oracle instance client download)

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

-> basic, sqlplus 버전2개 받기

-> 압축풀기(원하는 경로에 풀면 됨) 나는 C:\instantclient_11_2 풀었음.

 

2) 환경변수 등록하기

ORACLE_HOME=C:\instantclient_11_2

TNS_ADMIN=C:\instantclient_11_2

PATH=C:\instantclient_11_2 (기존거에 추가)

LD_LIBRARY_PATH=C:\instantclient_11_2

NLS_LANG =>KOREAN_KOREA.KO16MSWIN949

 

****시스템 변수에 PATH추가(샘플)

C:\Program Files\Bandizip\;C:\instantclient_11_2\;C:\Program Files\PostgreSQL\10\bin;C:\Program Files\PostgreSQL\10\lib;C:\Windows\System32;C:\Windows;C:\Windows\System32\wbem;C:\Windows\System32\WindowsPowerShell\v1.0;C:\Users\Administrator\AppData\Local\Microsoft\WindowsApps

 

3)tnsnames.ora 파일 작성

 -기존거 복사해서 붙이고 테스트 할 것. (메모장으로 단순하게 만들면 텍스트파일로 인식되서 안됨)

 

4)중요: sqlplus 아이디/패스워드@tns이름으로 접속테스트

  -> 안되면 재부팅 => 경험상 재부팅 후 제대로 접속되었음

 

*오라클 fdw설정 (오라클 외부테이블로 연결하기) 

1) fdw 다운로드 받기 (postgresql 버전과 32/64bit중에 자신에게 맞는것을 다운로드)

https://github.com/laurenz/oracle_fdw/releases    => 압축풀고 복사 => C:\Program Files\PostgreSQL\10 (postgresql 설치경로)

 

2) SQL  sell 열기 -> postgresql 접속

 

3) oracle_fdw extenstion을 생성한다.

create extension oracle_fdw;

  

 

4) 오라클 연결(외부서버로 설정하여 외부테이블로 연결하기) -query tool 에서 실행해도 됨.

-오라클 외부서버 연결

 CREATE SERVER 서버이름 FOREIGN DATA WRAPPER

 oracle_fdw OPTIONS

 (dbserver '//아이피:포트/SID');  <==오라클 접속정보

 

(예)

CREATE SERVER oradb FOREIGN DATA WRAPPER

 oracle_fdw OPTIONS

 (dbserver '//192.168.1.000:1521/ORAGIS');  <==오라클 접속정보

 

 5) 사용자에게 오라클외부서버 권한 주기

 GRANT USAGE ON FOREIGN SERVER oradb TO 포스트그레사용자;    <==권한주기 외부서버

 

6) 오라클 서버 사용자 매핑

 CREATE USER MAPPING FOR hsmart

 SERVER oradb

 OPTIONS (user '아이디', password '패스워드');    <==사용자 매핑

 

7) 외부테이블 만들기

  =>데이터 타입등을 잘 고려하여 만들 것.

  CREATE FOREIGN TABLE oradb_USER_INFO (

        user_id        character varying(30)    NOT NULL, -- 사용자아이디

user_name      character varying(30)    NULL    -- 사용자이름

 ) SERVER oradb OPTIONS (table '실제오라클에 있는 테이블명');

 

 8) 조회하여 테스트

select * from oradb_USER_INFO;

 

9)  pgadmin 통해서 확인하기