[Oracle] 오라클 LogMiner — 실습 케이스 (시간/SCN 기반) & 자동 복구 SQL 생성 스크립트

오라클 LogMiner — 실습 케이스 (시간/SCN 기반) & 자동 복구 SQL 생성 스크립트

1️⃣ 목적 및 요약

이 문서는 운영 중 실수로 데이터가 삭제(DELETE)되었을 때 LogMiner를 이용해 해당 트랜잭션을 찾아내고, SQL_UNDO 또는 SQL_REDO를 기반으로 자동으로 복구용 INSERT 문을 생성하는 실습 예제와 검증(검토) 절차를 제공합니다. 실무 적용 전 반드시 복제 DB/테스트 DB에서 검증하세요.

2️⃣ 실습 전제 (샘플 환경)

• 데이터베이스: Oracle 19c (예시 명령은 SQL*Plus/rlwrap 환경 가정)
• 대상 테이블: HR.EMP (샘플) — PK: EMP_ID
• 삭제 발생 시간(예시): 2025-10-01 10:23:45
• 대상 SCN 범위(예시): STARTSCN=120000, ENDSCN=120500
• 딕셔너리: DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG 사용(동일 DB에서 분석)

주의: 실무에서는 START/END를 시간 또는 SCN으로 좁게 지정하세요. 범위가 넓으면 처리 시간이 길어지고 리소스를 많이 사용합니다.

3️⃣ 단계별 실습 절차 (시간 기반 → SCN 보조)

1. 대상 로그 파일 확인
   아카이브 로그 또는 online redo 로그 중 분석 대상 파일을 확인합니다.

   -- 아카이브 로그 리스트(예시)
   SELECT NAME FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME;

2. LogMiner에 로그 파일 등록

   -- SQL*Plus에서 SYS로 실행 (예시)
   -- 1) 필요한 로그 파일 ADD (여러개 가능)
   EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/app/oracle/oradata/DB/arch1_001.arc', OPTIONS => DBMS_LOGMNR.NEW);
   EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/app/oracle/oradata/DB/arch1_002.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

3. LogMiner 시작 (시간 기준 예)

   -- 시간 기준으로 시작/종료 지정 (예시)
   EXEC DBMS_LOGMNR.START_LOGMNR(
     STARTTIME => TO_TIMESTAMP('2025-10-01 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     ENDTIME   => TO_TIMESTAMP('2025-10-01 11:00:00','YYYY-MM-DD HH24:MI:SS'),
     OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY
   );

   또는 SCN 기준:

   EXEC DBMS_LOGMNR.START_LOGMNR(STARTSCN => 120000, ENDSCN => 120500, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

4. 삭제(DELETE) 트랜잭션 검색

   -- 예: HR.EMP 테이블에서 DELETE가 발생한 트랜잭션 조회
   SELECT SCN, TIMESTAMP, OPERATION, SQL_REDO, SQL_UNDO, USERNAME
   FROM V$LOGMNR_CONTENTS
   WHERE OPERATION = 'DELETE'
     AND SEG_OWNER = 'HR'
     AND SEG_NAME = 'EMP'
   ORDER BY SCN;

4️⃣ 실무 예제: 특정 SCN에서 SQL_UNDO 추출 및 INSERT 생성 (수동 방식)

먼저 관심 SCN(예: 123456)의 SQL_UNDO를 확인합니다.

-- 1) SCN으로 대상 레코드 찾기
SELECT SCN, TIMESTAMP, OPERATION, SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SCN = 123456
  AND SEG_OWNER = 'HR'
  AND SEG_NAME = 'EMP';

-- 2) SQL_UNDO 예시 출력 (예: SQL_UNDO가 'INSERT INTO "HR"."EMP" (...) VALUES (...)' 형태로 존재)
-- 3) SQL_UNDO(INSERT)를 복제 DB에서 먼저 실행하여 검증 후 운영 반영

검증 권장: SQL_UNDO를 그대로 실행하기 전 반드시 제약조건(FK/PK), 시퀀스, 트리거 등을 확인하고, 테스트 DB에서 실행 검증하세요.

5️⃣ 자동화: SQL_UNDO → 검증용 INSERT 생성 스크립트 (PL/SQL + 스풀)

아래 스크립트는 LogMiner 실행 상태에서 V$LOGMNR_CONTENTS의 SQL_UNDO를 추출하여 파일로 스풀(spool)하고, 검증용(검토용) INSERT 파일을 생성합니다. 이 스크립트는 바로 운영에 적용하지 말고 복제 DB/스테이징에서 먼저 테스트하십시오.

-- 1) SQL*Plus에서 실행 (예: saved_restore.sql)
-- 사용 예: sqlplus / as sysdba @saved_restore.sql 123456 123500 /u01/tmp/restore_emp.sql

-- saved_restore.sql
-- 인자: &1 = START_SCN, &2 = END_SCN, &3 = OUTPUT_FILE
SET ECHO OFF
SET FEEDBACK OFF
SET LONG 200000
SET PAGESIZE 0
SET LINESIZE 1000
SET TRIMSPOOL ON

SPOOL &3

-- 헤더
PROMPT -- Generated by LogMiner Undo Extractor
PROMPT -- SCN RANGE: &1 - &2
PROMPT SET DEFINE OFF;

-- 실제 추출 쿼리: SQL_UNDO 컬럼을 그대로 출력
SELECT SQL_UNDO || ';' AS undo_sql
FROM V$LOGMNR_CONTENTS
WHERE SCN BETWEEN &1 AND &2
  AND OPERATION = 'DELETE'
  AND SEG_OWNER = 'HR'
  AND SEG_NAME = 'EMP'
  AND SQL_UNDO IS NOT NULL
ORDER BY SCN;

SPOOL OFF
EXIT

위 결과 파일(/u01/tmp/restore_emp.sql)은 기본적으로 INSERT ...; 문들의 모음이 됩니다. 복구 적용 전 아래 절차로 자동/수동 검증하세요.

1. 파일 백업: 원본 결과 파일을 안전한 위치로 보관
2. 문법 검사: grep -n "INSERT" restore_emp.sql | wc -l 등으로 INSERT 수 확인
3. 테스트 실행(읽기 전용 검증): 복제 DB에서 SET AUTOCOMMIT OFF 및 트랜잭션 시작 후 실행
4. 검증 완료 시 운영 적용: change window를 잡아 운영 DB에 적용 (변경관리 필요)

중요: SQL_UNDO가 항상 완전한 INSERT를 제공하지 않을 수 있습니다(보조로깅 미설정, NOLOGGING 등). 따라서 자동 실행은 위험할 수 있으며 항상 수동 리뷰/검증을 선행하세요.

6️⃣ 고급: SQL_REDO 기반 차액 복구(INSERT 대신 MERGE 사용 권장) — 예시 스니펫

DELETE 시점의 행이 다른 트랜잭션에 의해 변경된 경우 단순 INSERT로는 무결성 문제가 발생할 수 있습니다. 안전하게 적용하려면 MERGE 또는 존재 여부 체크를 포함한 SQL을 생성하는 것이 좋습니다.

-- 간단한 MERGE 패턴 예 (사전: 복구 대상 테이블의 PK 컬럼이 EMP_ID라고 가정)
MERGE INTO HR.EMP tgt
USING (SELECT /* values from extracted SQL_UNDO as inline values */ FROM DUAL) src
ON (tgt.EMP_ID = src.EMP_ID)
WHEN NOT MATCHED THEN
  INSERT (EMP_ID, NAME, SALARY, ...) VALUES (src.EMP_ID, src.NAME, src.SALARY, ...)
WHEN MATCHED THEN
  UPDATE SET tgt.NAME = src.NAME, tgt.SALARY = src.SALARY;

자동화 도구(스크립트)에서 SQL_UNDO 구문을 파싱해 MERGE 템플릿으로 변환하면 적용 안전성을 높일 수 있습니다.

7️⃣ 기본 오류 대응 4단계

1. 외부 노출 확인 — LogMiner 분석 산출물(redo/archivelog)이 외부에 노출되었는지 확인
2. 역할 비활성화 — 분석 중 불필요한 DBA/OS 계정 접근 제한
3. 포트 차단 — 외부에서 DB로의 불필요한 접속을 일시 차단
4. 로그 모니터링 — LogMiner 접근 이력, V$SESSION 접속 로그 집중 모니터링

8️⃣ 운영 체크리스트 (요약)

• 복구 전: 전체 DB/테이블 백업 또는 복제 DB에서 검증
• LogMiner 옵션: 가능한 경우 DICT_FROM_ONLINE_CATALOG 대신 flat dictionary 파일을 사용해 안정성 확보
• 보안: 생성된 복구 스크립트(INSERT 파일)는 민감 정보가 포함될 수 있으므로 암호화/권한 제한
• 테스트: 복구 전 반드시 복제 DB에서 dry-run(rollback 후 검토) 수행
• 변경관리: 운영 적용은 변경관리 절차(스케줄, 승인, 롤백 계획) 준수

9️⃣ 예시 워크플로 (요약)

1. 운영에서 삭제 발견 → 즉시 복제 DB로 해당 시간 범위/SCN의 로그 복사
2. 복제 DB에서 LogMiner로 SQL_UNDO/SQL_REDO 추출 → restore_emp.sql 생성
3. DBA가 파일 검토(문법, FK, 시퀀스) → 테스트 적용(복제 DB)
4. 검증 완료 시 운영 변경 윈도우 안에 적용 → 적용 후 모니터링

🔟 부록: 자동 변환 시 고려할 파싱 규칙(개발자용)

자동으로 SQL_UNDO를 INSERT로 변환할 때 흔히 필요한 파싱/정규화 처리

• 문자열 리터럴 이스케이프 처리 (예: 싱글쿼트 내부의 싱글쿼트)
• 데이터 타입별 NULL/TO_DATE/TO_TIMESTAMP 처리
• 시퀀스 기반 PK인 경우 시퀀스 값 재작성 또는 시퀀스 조정 로직
• LOB 또는 BFILE 타입의 복구는 별도 추출/처리 필요

FAQ (짧게)

• Q: SQL_UNDO가 없으면 복구 불가능한가?
  A: 경우에 따라 SQL_REDO/아카이브 로그의 행값을 파싱하거나 백업에서 재구성해야 합니다. NOLOGGING/NOARCHIVE 작업이면 복구가 제한됩니다.
• Q: 자동 스크립트를 바로 운영에서 실행해도 되나?
  A: 절대 권장하지 않습니다. 반드시 복제 DB에서 검증 후 운영 적용하세요.