[Oracle] DATABASE LINK 연결 상태를 점검하는 방법

Database Link는 생성 시점에 원격지와의 물리적 연결성이나 계정 정보의 정확성을 검증하지 않기 때문에, DBA_OBJECTS 뷰에서는 연결 실패 여부와 상관없이 VALID 상태를 유지합니다. 따라서 DB Link의 가용성을 보장하기 위해서는 별도의 상태 점검이 반드시 필요합니다.

1. 참고로 $ORACLE_HOME/network/admin/tnsnames.ora 파일에 등록된 TNS Alias 정의(TNS 별칭 등록)을 확인해 봅니다.
   • 기본 단일 서버(Single Instance) 양식 : 가장 일반적인 형태입니다. 한 개의 호스트와 한 개의 서비스를 바라봅니다.

     [TNS_ALIAS_NAME] =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = [IP_주소_또는_호스트명])(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = [서비스_이름])
         )
       )
     

   • RAC 및 Failover(2중화) 양식 : 두 개의 노드를 바라보며 장애 시 자동 전환되도록 설정하는 방식입니다.

     [TNS_ALIAS_NAME] =
       (DESCRIPTION =
         (ADDRESS_LIST =
           (LOAD_BALANCE = ON)   -- 두 서버에 부하 분산 (선택)
           (FAILOVER = ON)       -- 한쪽 죽으면 다른 쪽 접속
           (ADDRESS = (PROTOCOL = TCP)(HOST = [IP_주소_또는_호스트명])(PORT = 1521))
           (ADDRESS = (PROTOCOL = TCP)(HOST = [IP_주소_또는_호스트명])(PORT = 1521))
         )
         (CONNECT_DATA =
           (SERVICE_NAME = [서비스_이름])
           (FAILOVER_MODE =
             (TYPE = SELECT)     -- 조회 도중 장애 시 세션 유지
             (METHOD = BASIC)
           )
         )
       )
     

2. dba_db_links 뷰를 통해 등록된 DB LINK의 접속 정보(host)를 확인하고, host는 tnsnames.ora 파일에 등록되어 있어야 tnsping host 명령어를 사용해서 연결 여부를 확인할 수 있습니다. DB Link 생성 시 USING ‘(DESCRIPTION=…’를 사용해서 직접 연결 정보를 지정한 경우에는 별도로 점검해야 합니다.

   #!/bin/bash
   
   echo "--------------------------------------------------------------------------------"
   echo "  DB LINK TNS RESOLUTION CHECK (tnsping test)"
   echo "--------------------------------------------------------------------------------"
   printf "%-12s | %-35s | %-20s | %-5s\n" "OWNER" "DB_LINK_NAME" "HOST" "TNS STATUS"
   echo "--------------------------------------------------------------------------------"
   
   sqlplus -s / as sysdba << EOF | while IFS=',' read -r OWNER LINK HOST; do
   SET FEEDBACK OFF
   SET PAGESIZE 0
   SET LINESIZE 32767
   SET TRIMSPOOL ON
   SET LONG 20000
   SELECT owner || ',' || db_link || ',' || REGEXP_REPLACE(host, '[[:space:]]', '') FROM dba_db_links;
   EXIT;
   EOF
   
       [ -z "$OWNER" ] && continue
       if [[ $HOST == \(* ]]; then
           TNS_STATUS="$HOST"
       else
           if tnsping "$HOST" > /dev/null 2>&1; then
               TNS_STATUS="OK"
           else
               TNS_STATUS="NO"
           fi
       fi
   
       printf "%-12s | %-35s | %-20s | %-5s\n" "$OWNER" "$LINK" "${HOST:0:15}" "$TNS_STATUS"
   done
   echo "--------------------------------------------------------------------------------"
   

3. 아래 쿼리를 사용해서 SQL 뷰, 소스코드 뷰, 의존성 뷰를 통해 사용여부를 체크합니다.

   SELECT OWNER, DB_LINK, 
          'DROP DATABASE LINK ' || DB_LINK | | ';', 
          'SELECT sql_text, last_active_time FROM v$sql WHERE sql_text LIKE ''%' || DB_LINK ||  '%'';' AS SQL_VIEW_CHECK,
          'SELECT * FROM dba_source WHERE UPPER(text) LIKE ''%'|| DB_LINK || '%'';' AS SOURCE_CHECK,
          'SELECT * FROM dba_dependencies WHERE name LIKE ''%' || DB_LINK ||  '%'' OR referenced_name LIKE ''%' || DB_LINK ||  '%'' OR referenced_link_name LIKE ''%' || DB_LINK ||  '%'';' AS DEPENDENCY_CHECK
   FROM   dba_db_links
   WHERE  db_link LIKE '%';
   

4. Private Database Link는 소유자만이 관리 권한을 가지므로, 고유 권한을 가진 SYS 계정이라 하더라도 타 스키마의 Private DB Link를 직접 삭제할 수 없습니다. 따라서 이를 처리하기 위해서는 해당 소유자 계정으로 직접 접속하여 삭제하거나, SYS 계정에 Proxy 접속 권한을 부여하여 해당 유저로 세션을 전환한 후 삭제를 수행해야 합니다. 모든 사용자가 공용으로 사용하는 Public Database Link를 삭제할 때는 SYS나 PUBLIC DATABASE LINK 생성 권한이 있는 계정에서만 가능합니다.

   SQL> DROP [PUBLIC] DATABASE LINK DB_LINK_이름;