[Oralce] 트리거를 사용해서 Invalid Object 접근/사용 이력 기록하기
Audit Trail 기능을 사용해서는 Invalid Object의 접근/사용 이력을 기록할 수 없기 때문에 Schema Trigger를 생성해서 Invalid Object의 접근/사용 이력을 기록하는 방법을 알아보겠습니다.
- Invalid Object 현황 파악
- Invalid Object 재컴파일 및 현황 재파악
- 로그 기록용 테이블 및 권한 부여
- Trigger 생성
1. Invalid Object 현황 파악
- DBA_OBJECTS뷰와 DBA_USERS뷰를 사용해서 Invalid Object의 현황을 파악합니다.
-- 요약 SELECT O.OWNER, U.ACCOUNT_STATUS, O.OBJECT_TYPE, SUM(DECODE(O.STATUS, 'VALID', 1, 0)) AS VALID_CNT, SUM(DECODE(O.STATUS, 'VALID', 0, 1)) AS INVALID_CNT FROM DBA_OBJECTS O, DBA_USERS U WHERE O.OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW') AND O.OWNER = U.USERNAME(+) GROUP BY O.OWNER, U.ACCOUNT_STATUS, O.OBJECT_TYPE ORDER BY 1, 3; -- 상세(DBMS_METADATA.GET_DDL 생성 쿼리 포함) -- SQL*Plus 사용자는 GET_DDL 실행 전 "SET LONG 2000000 LINESIZE 200 PAGESIZE 0;" 설정 필수 SELECT O.OWNER, U.ACCOUNT_STATUS, O.OBJECT_TYPE, O.OBJECT_NAME, O.SUBOBJECT_NAME, O.STATUS AS OBJECT_STATUS, TO_CHAR(O.CREATED, 'YYYY-MM-DD HH:MI:SS') AS CREATED_DATE, TO_CHAR(O.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') AS DDL_DATE, 'SELECT DBMS_METADATA.GET_DDL(''' || CASE WHEN O.OBJECT_TYPE = 'PACKAGE BODY' THEN 'PACKAGE_BODY' ELSE O.OBJECT_TYPE END || ''', ''' || O.OBJECT_NAME || ''', ''' || O.OWNER || ''') AS GET_DDL FROM DUAL;' AS GET_DDL, 'SELECT * FROM DBA_SOURCE WHERE UPPER(TEXT) LIKE ''%' || O.OBJECT_NAME|| '%'';' AS SOURCE_CHECK_DML, 'SELECT * FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = ''' || O.OBJECT_NAME || ''';' AS DEPENDENCY_CHECK_DML FROM DBA_OBJECTS O, DBA_USERS U WHERE O.OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'XS$NULL', 'DIP', 'ORACLE_OCM', 'XDB', 'ANONYMOUS', 'CTXSYS', 'MDSYS', 'MDDATA', 'ORDDATA', 'ORDSYS', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'EXFSYS', 'APPQOSSYS', 'OWBSYS', 'OWBSYS_AUDIT', 'OLAPSYS', 'MGMT_VIEW') AND O.OWNER = U.USERNAME(+) ORDER BY 1, 3, 4; - DBA_OBJECTS 뷰의 STATUS 컬럼은 오브젝트 정의(DDL)의 문법적 유효성만을 나타냅니다. 특히 Database Link나 Synonym은 생성 시점에 원격 서버와의 물리적 연결성, 계정 인증 정보, 또는 참조하는 원본 객체의 존재 여부를 실시간으로 검증하지 않습니다. 따라서 실제 연결 가능 여부와 관계없이 VALID 상태를 유지하므로, 서비스 가용성을 보장하기 위해서는 반드시 별도의 DB Link 연결성 점검 및 Synonym 유효성 검증(Validation) 절차를 병행해야 합니다.
2. Invalid Object 재컴파일 및 현황 재파악
- 하나씩 ALTER … COMPILE을 하기보다는 오라클에서 제공하는 표준 유틸리티를 사용하여 일괄로 재컴파일함으로써 의존성(Dependency)을 자동 해결하고, 작업 시간을 단축할 수 있습니다. 컴파일을 수행할 때 해당 객체에 대해 Lock을 잡기 때문에 가급적 사용자가 적은 시간대나 점검 시간에 수행하는 것이 안전합니다.
-- 방법1: 스키마 전체를 한꺼번에 컴파일 -- 일반 개발자나 권한이 제한된 운영자가 본인 스키마만 정리할 때 사용 -- compile_all => FALSE : 실제로 `INVALID`인 것들만 골라서 컴파일(기본값) -- compile_all => TRUE : 상태에 상관없이 해당 유저의 모든 객체를 다시 컴파일 EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => '스키마', compile_all => FALSE); -- 방법2(권장): INVALID OBJECT만 병렬 컴파일(4 : 병렬 처리 스레드 수) -- 스키마를 생략하면 전체 DB를 대상으로 INVALID OBJECT를 병렬 컴파일 -- SYS 권한으로 실행하며, utlrp.sql과 같은 로직을 공유하여 가장 효율적인 재컴파일 순서 탐색 EXEC UTL_RECOMP.RECOMP_PARALLEL(4, '스키마'); -- 방법3: 특정 스키마가 아니라 DB 전체에 걸쳐 INVALID 객체 컴파일(병렬 처리를 지원하여 매우 빠름) -- @?/rdbms/admin/utlrp.sql (SQL*Plus에서 SYS 권한으로 실행) -- 아래 쿼리를 사용해서 재컴파일 세션이 어떤 오브젝트를 재컴파일하고 있는지 확인해 봅니다. SELECT A.SID, A.SERIAL#, B.OBJECT, B.TYPE FROM V$SESSION A, V$ACCESS B WHERE A.SID = B.SID AND B.OWNER = '스키마' AND A.TYPE <> 'BACKGROUND'; -- 작업이 멈춘 것 같다면 Library Cache Pin을 확인해 봅니다. SELECT SID, EVENT, WAIT_CLASS, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE EVENT LIKE 'library cache%';
- 1단계 쿼리를 사용해서 Invalid Object의 현황을 다시 파악합니다.
3. 로그 기록용 테이블 및 권한 부여
- 로그 기록용 테이블을 생성합니다.
CREATE TABLE 스키마.TB_LOG_INVALID_OBJ_ERR ( LOG_DATE DATE DEFAULT SYSDATE, DB_USER VARCHAR2(30), SESSION_USER VARCHAR2(30), TERMINAL VARCHAR2(100), IP_ADDRESS VARCHAR2(50), ERROR_CODE NUMBER, OBJ_OWNER VARCHAR2(30), OBJ_NAME VARCHAR2(128), OBJ_TYPE VARCHAR2(30), OBJ_STATUS VARCHAR2(10), ERROR_MSG VARCHAR2(1000), SQL_TEXT VARCHAR2(4000) ); - Trigger를 생성한 스키마에게 위에서 생성한 테이블의
INSERT, SELECT권한을 부여합니다.GRANT INSERT, SELECT ON COM.TB_LOG_INVALID_OBJ_ERR TO TGMADM;
4. Trigger 생성
- 로그 기록용 Schema Trigger를 생성합니다.
CREATE OR REPLACE TRIGGER "스키마"."TRG_LOG_INVALID_OBJ_ERR" AFTER SERVERERROR ON DATABASE DECLARE PRAGMA AUTONOMOUS_TRANSACTION; -- 독립적인 트랜잭션으로 처리하라고 지시하는 컴파일러 지시어 v_sql_text ORA_NAME_LIST_T; v_stmt VARCHAR2(4000); v_count PLS_INTEGER; v_msg VARCHAR2(1000); v_full_obj VARCHAR2(200); v_owner VARCHAR2(30); v_name VARCHAR2(128); v_type VARCHAR2(30); v_status VARCHAR2(10); -- 크기 약간 확장 (NOT_FOUND 대비) v_start NUMBER; v_end NUMBER; BEGIN -- 감시 대상 에러 코드 -- 4063/6575 : Invalid 객체 접근 -- 6550 : PL/SQL 코드 오류 -- 942 : 테이블이나 뷰가 없음 또는 권한 부족 -- 1542 : 테이블스페이스 오프라인 -- 376 : 데이터 파일 읽기 불가 -- 12154/12170 : 데이테베이스 링크가 연결되지 않음 -- 980 : 시노님이 유효하지 않음 IF (IS_SERVERERROR(4063) OR IS_SERVERERROR(6575) OR IS_SERVERERROR(6550) OR IS_SERVERERROR(942) OR IS_SERVERERROR(1542) OR IS_SERVERERROR(376)) OR IS_SERVERERROR(12154) OR IS_SERVERERROR(12170) OR IS_SERVERERROR(980) THEN v_msg := ORA_SERVER_ERROR_MSG(1); -- SQL 텍스트 추출 v_count := ORA_SQL_TXT(v_sql_text); IF v_count IS NOT NULL THEN FOR i IN 1..LEAST(v_count, 50) LOOP v_stmt := v_stmt || v_sql_text(i); END LOOP; END IF; v_stmt := SUBSTR(v_stmt, 1, 4000); -- 객체 타입 식별 (대소문자 무관하게 탐색) IF UPPER(v_msg) LIKE '%VIEW %' THEN v_type := 'VIEW'; ELSIF UPPER(v_msg) LIKE '%PROCEDURE %' THEN v_type := 'PROCEDURE'; ELSIF UPPER(v_msg) LIKE '%PACKAGE BODY %' THEN v_type := 'PACKAGE BODY'; ELSIF UPPER(v_msg) LIKE '%PACKAGE %' THEN v_type := 'PACKAGE'; ELSIF UPPER(v_msg) LIKE '%FUNCTION %' THEN v_type := 'FUNCTION'; ELSE v_type := 'UNKNOWN/PLSQL'; END IF; -- 따옴표 기반 객체명 파싱 v_start := INSTR(v_msg, '"', 1, 1); v_end := INSTR(v_msg, '"', 1, 2); IF v_start > 0 AND v_end > v_start THEN v_full_obj := SUBSTR(v_msg, v_start + 1, v_end - v_start - 1); IF INSTR(v_full_obj, '.') > 0 THEN v_owner := UPPER(SUBSTR(v_full_obj, 1, INSTR(v_full_obj, '.') - 1)); v_name := UPPER(SUBSTR(v_full_obj, INSTR(v_full_obj, '.') + 1)); ELSE v_owner := ORA_DICT_OBJ_OWNER; -- 추출 실패 시 현재 컨텍스트 활용 v_name := UPPER(v_full_obj); END IF; -- 객체 상태 조회 (DBA_OBJECTS 접근 권한 필요) BEGIN SELECT STATUS INTO v_status FROM DBA_OBJECTS WHERE OWNER = NVL(v_owner, OWNER) -- OWNER 파싱 실패 대비 AND OBJECT_NAME = v_name AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN v_status := 'NOT_FOUND'; WHEN OTHERS THEN v_status := 'ERROR'; END; END IF; -- 로그 기록 INSERT INTO COM.TB_LOG_INVALID_OBJ_ERR ( LOG_DATE, DB_USER, SESSION_USER, TERMINAL, IP_ADDRESS, ERROR_CODE, OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_STATUS, ERROR_MSG, SQL_TEXT ) VALUES ( SYSDATE, ORA_LOGIN_USER, SYS_CONTEXT('USERENV', 'SESSION_USER'), SYS_CONTEXT('USERENV', 'TERMINAL'), SYS_CONTEXT('USERENV', 'IP_ADDRESS'), ORA_SERVER_ERROR(1), v_owner, v_name, v_type, v_status, v_msg, v_stmt ); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; -- 자율 트랜잭션 롤백 NULL; END; /
