SQL로 Oracle Alert Log 파일 조회

DBMS LOG용 디렉토리 생성

CREATE OR REPLACE DIRECTORY DIR_BDUMP AS '/oracle/admin/SID/bdump/';

DBMS LOG 외부 테이블 생성

CREATE TABLE DBMSLOG (
  LOG_TEXT VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DIR_BDUMP
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '~'
    MISSING FIELD VALUES ARE NULL (
      LOG_TEXT CHAR(4000)
    )
  )
  LOCATION ('alert_SID.log')
)
REJECT LIMIT UNLIMITED;

DBMS LOG 뷰 생성

CREATE OR REPLACE VIEW DBMSLOG_VI
AS
SELECT LOG_RNUM
, LAST_VALUE(LOG_LNUM IGNORE NULLS)
OVER(ORDER BY LOG_RNUM ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) LOG_SNUM
, LAST_VALUE(LOG_DATE IGNORE NULLS)
OVER(ORDER BY LOG_RNUM ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) LOG_DATE
, TRC_FILE
, LOG_TEXT
FROM (SELECT ROWNUM LOG_RNUM
, NVL2(LOG_DATE, ROWNUM, NULL) LOG_LNUM
, LOG_DATE
, TRC_FILE
, LOG_TEXT
FROM (SELECT CASE REGEXP_INSTR(LOG_TEXT,
'[[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}'
|| ' [[:digit:]]{4}')
WHEN 0 THEN NULL
ELSE TO_DATE(LOG_TEXT
, 'Dy Mon DD HH24:MI:SS YYYY'
, 'NLS_DATE_LANGUAGE=AMERICAN')
END LOG_DATE
, CASE REGEXP_INSTR(LOG_TEXT, '^Errors in file')
WHEN 1 THEN REGEXP_REPLACE(LOG_TEXT
, '^Errors in file (.*):$'
, '\1')
ELSE NULL
END TRC_FILE
, LOG_TEXT
FROM DBMSLOG));

COMMENT ON TABLE DBMSLOG_VI IS 'DBMS Alert 로그 뷰';

COMMENT ON COLUMN DBMSLOG_VI.LOG_RNUM IS 'DBMS Alert 로그 순번';
COMMENT ON COLUMN DBMSLOG_VI.LOG_SNUM IS 'DBMS Alert 로그 시작 순번 그룹';
COMMENT ON COLUMN DBMSLOG_VI.LOG_DATE IS 'DBMS Alert 로그 일시';
COMMENT ON COLUMN DBMSLOG_VI.TRC_FILE IS 'DBMS Alert Trace 파일';
COMMENT ON COLUMN DBMSLOG_VI.LOG_TEXT IS 'DBMS Alert 로그 내용';

DBMS LOG 파일 조회

SELECT A.LOG_DATE
, A.TRC_FILE
, A.LOG_TEXT
FROM (SELECT TO_CHAR(MIN(LOG_DATE), 'YYYY-MM-DD HH24:MI:SS') LOG_DATE
, SUBSTR(XMLAGG(XMLELEMENT(TEMP, CHR(10) || TRC_FILE)
ORDER BY LOG_RNUM)
.EXTRACT('//text()').GETSTRINGVAL(), 2) TRC_FILE
, SUBSTR(XMLAGG(XMLELEMENT(TEMP, CHR(10) || LOG_TEXT)
ORDER BY LOG_RNUM)
.EXTRACT('//text()').GETSTRINGVAL(), 2) LOG_TEXT
FROM DBMSLOG_VI
WHERE LOG_DATE BETWEEN TRUNC(SYSDATE - :DAY)
AND SYSDATE - :DAY
GROUP BY LOG_SNUM
ORDER BY LOG_SNUM DESC) A
WHERE LOG_TEXT LIKE '%' || :TEXT || '%';

참고

Query the Oracle Alert Log using SQL commands
Oracle Regular Expression(정규표현식)