[Oracle] DB LINK 생성 및 권한 부여

※ 이 글은 Oracle Database 11g Release 11.2.0.4.0 - 64bit 버전을 기준으로 작성된 글입니다.

 

원격지(B)에 있는 서버의 데이터를 조회하고자 할 때 로컬(A) 서버에서 사용하는 방법

• DBLINK의 소유자(OWNER)는 생성 후 수정이 불가능함.

 

DBLINK 생성 권한 부여 및 확인

GRANT CREATE DATABASE LINK TO [사용자명]
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='사용자명';

• 해당 명령은 SYS 계정으로 사용자한테 부여해주면 됨

 

DBLINK 생성/삭제 및 조회

CREATE DATABASE LINK [링크명] CONNECT TO [원격지 사용자명] IDENTIFIED BY "[원격지 사용자 비밀번호]" USING '[인스턴스명]';
SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='[링크명]';

DROP DATABASE LINK [링크명];

•  DBLINK 를 잘못 만들었을 땐 DROP DATABASE LINK [링크명] 을 실행하면 됨

   ※ 만약 ORA-12154 오류가 출력될 경우 해결할 수 있는 방법은 두 가지가 있다.

1.  DBLINK  재생성

        이 경우는, 아예 인스턴스명 대신 원격지 IP 주소, 포트를 DBLINK 생성할 때 같이 넣고 생성하는 방법이다.

CREATE DATABASE LINK [링크명] CONNECT TO [원격지 사용자명] IDENTIFIED BY "[원격지 사용자 비밀번호]"
USING '(DESCRIPTION =
               (ADDRESS_LIST =
                   (ADDRESS = (PROTOCOL = TCP)(HOST = [원격지 IP주소])(PORT = [원격지 포트]))
                )
                (CONNECT_DATA =
                    (SERVER=dedicated)
                    (SERVICE_NAME=[인스턴스명])
                )
            )';

 

    2.  tnsnames.ora 수정

        로컬 서버의 tnsnames.ora에 원격지의 인스턴스가 등록이 안되어 있으므로 tnsnames.ora에 인스턴스를 등록해준다.

su
cd $TNS_ADMIN
vi tnsnames.ora

• tnsnames.ora는 관리자 계정으로만 수정이 가능함.
• 보통 환경변수에 $TNS_ADMIN 이 등록되어 있으므로 해당 경로 사용하면 됨. 
  • 없는 경우 직접 경로를 찾아서 들어가야 함. 필자는 /u01/app/12.1.0.1/grid/network/admin 이었음

    ** tnsnames.ora 에 아래와 같이 문구 삽입 

KIS02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 203.xxx.xxx.xxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = KIS02)
    )
  )

• 수정해야할 부분 : 인스턴스명 (KIS02), HOST - 원격지 호스트 IP, PORT - 원격지 호스트 포트, SERVICE_NAME - 인스턴스명 으로 대체.

 

DBLINK 정상동작 확인

SELECT * FROM [원격지 테이블명]@[DBLINK명];

• SELECT * FROM KPAPER@L_K_PAPER; 이런 식으로 SQL 문장을 실행해보면 정상적으로 데이터를 불러오는 것을 확인할 수 있음