[Oracle] DB Link 사용 방법(생성, 삭제, 조회, ORA-12154, ORA-01017에러)

1. DB Link란?

• A 데이터베이스에서 B 데이터베이스의 객체에 접근할 수 있게 하는 연결고리
• 데이터를 직접 전송하거나 API를 사용하지 않고 SQL 쿼리를 활용해 다른 서버의 데이터에 접근 가능
• 어떤 계정에 DB Link를 거느냐에 따라 계정이 객체에 대해 가지고 있는 권한이 다름
• 데이터를 옮기거나, select/update/insert/delete, A 데이터베이스와 B 데이터베이스의 형상 비교 등에 유용함

# 예를 들어 DBDEV : select/update/insert/delete 가능 / DBSEL : select 가능 하다면
# DB Link를 DBDEV계정에 연결하는지, DBSEL에 연결하는지에 따라 가능한 SQL이 달라짐

 

 

---

 

★ 실습: PROD DB의 USER1 계정이 TEST DB 객체들 바라보는 DB Link 생성 ★

 

2. DB Link 생성

  2-1. 권한 부여

• 사용자ID에 해당하는 계정에 권한 부여가 우선 되어야 DB Link 생성이 가능함.
• CREATE DATABASE LINK 권한은 SYSTEM 또는 ROOT가 부여 가능(보통 프로젝트에서는 DBA가 생성)

SQL> GRANT CREATE DATABASE LINK TO 사용자ID;

 

---

• 실습
• PROD DB에서 실행(system 또는 root권한 이용)

SQL> GRANT CREATE DATABASE LINK TO USER1;

---

 

  2-2. DB Link 생성 

• DB Link의 이름은 프로젝트마다 작성 방법이 있음
• USING에는 DB의 TNS명이 들어가는데, 이는 tnsnames.ora 파일에 있음(linux에서 확인 가능)
  • tnsnames.ora 파일 위치 : {oracle client 설치 경로}\network\admin\tnsnames.ora
• USING에는 DB의 TNS명의 내용이 올 수 있음

SQL> CREATE DATABASE LINK DBLink명
   > CONNECT TO 원격서버 사용자ID IDENTIFIED BY "원격서버 사용자 password"
   > USING 'DB의 TNS명';
   
SQL> CREATE DATABASE LINK DBLink명
   > CONNECT TO 원격서버 사용자ID IDENTIFIED BY "원격서버 사용자 password"
   > USING '(DESCRIPTION=
               (ADDRESS_LIST=
                  (ADDRESS=(PROTOCOL={프로토콜})(HOST={호스트IP1})(PORT={호스트 포트1}))
                  (ADDRESS=(PROTOCOL={프로토콜})(HOST={호스트IP2})(PORT={호스트 포트2}))
                )
               (CONNECT_DATA=
                  (SERVICE_NAME={서비스명}) // SID명으로 대체 가능
                )
             )';

 

 

 

---

• 실습
• TESTDB의 DBDEV계정은 테이블들을 SELECT/UPDATE/INSERT/DELETE 할 수 있는 권한을 가지고 있음

SQL> CREATE DATABASE LINK TESTDB_DBDEV_DBLINK
   > CONNECT TO "DBDEV" IDENTIFIED BY "DBDEV의 password"
   > USING 'TESTDB';

---

 

---

 

3. DB Link 조회 및 검증(사용)

• DB Link 조회는 DBA만 가능, 사용은 계정별로 생성하면 사용 가능

  3-1. 조회

• 어떤 DB + 어떤 계정과 연결되었는지 확인 가능(DBA만 확인 가능)

SQL> SELECT * FROM DBA_DB_LINKS;

 

  3-2. 검증 및 사용

• 생성한 DB + 계정에서 확인

SQL> SELECT * FROM "OWNER"."TABLE_NAME"@DB_LINK명;

 

---

• 실습
• PROD에서 USER1 계정으로 접속한 후에 아래 쿼리를 실행하면 TESTDB의 schema=owner=USER2, table_name=USER_TABLE 을 PROD에서 확인할 수 있음

SQL> SELECT * FROM USER2.USER_TABLE@TESTDB_DBDEV_DBLINK;

---

 

---

 

4. DB Link 삭제

• 이때, DROP DATABASE가 아닌 DROP DATABASE LINK임.
• DROP DATABASE하면 데이터베이스 자체가 다 날라감.

SQL> DROP DATABASE LINK "DB_LINK명";

 

• 삭제 후 DBA_DB_LINK테이블에서 확인했을 때 없으면 삭제된 것(DBA만 확인 가능)

SQL> SELECT * FROM DBA_DB_LINKS;

 

---

 

5. DB Link 발생 에러

  5-1. ORA-12154 : TNS: 지정된 접속 식별자를 분석할 수 없음

1. ORACLE_HOME 환경변수확인
2. tnsnames.ora 파일 확인
3. 방화벽 열기
4. 위의 방법이 다 안되면, tnsnames.ora 추가 없이 DB Link를 설정

SQL> CREATE DATABASE LINK DBLink명
   > CONNECT TO "원격서버 사용자ID" IDENTIFIED BY "원격서버 사용자 password"
   > USING '(DESCRIPTION=
               (ADDRESS_LIST=
                  (ADDRESS=(PROTOCOL={프로토콜})(HOST={호스트IP1})(PORT={호스트 포트1}))
                  (ADDRESS=(PROTOCOL={프로토콜})(HOST={호스트IP2})(PORT={호스트 포트2}))
                )
               (CONNECT_DATA=
                  (SERVICE_NAME={서비스명}) // SID명으로 대체 가능
                )
             )';

 

 

  5-2. ORA-01017 : invalid username/password; login denied

          ORA-02063 : Preceding Line From

• DB Link 생성 시 원격서버 사용자ID가 없거나, 원격서버 사용자 password가 틀린 경우
• 비밀번호에 특수문자가 포함되어있으면 비밀번호를 쌍따옴표("password")로 감싸야함