오라클 FLASHBACK QUERY로 데이터 복구하기

 

오라클 데이터베이스에서는 데이터 손실 또는 실수로 인해 잘못된 데이터 변경이 발생했을 때 이를 복구할 수 있는 다양한 기능을 제공합니다. 그중 FLASHBACK QUERY는 특정 시점의 데이터를 조회하여 손쉽게 복구 작업을 수행할 수 있는 강력한 도구입니다. 이 글에서는 FLASHBACK QUERY의 개념과 사용 방법, 실제 활용 사례를 살펴보겠습니다.

---

1. FLASHBACK QUERY란?

FLASHBACK QUERY는 과거의 데이터 상태를 조회할 수 있는 오라클의 기능입니다.
데이터베이스의 UNDO 데이터를 활용해 특정 시점(Timestamp)이나 특정 SCN(System Change Number)으로 데이터를 복원할 수 있습니다.

이 기능은 주로 다음과 같은 상황에서 유용합니다.

• 실수로 데이터를 삭제했을 때
• 잘못된 업데이트나 트랜잭션이 실행되었을 때
• 특정 시점의 데이터 상태를 감사하거나 비교해야 할 때

---

2. FLASHBACK QUERY 사용법

기본 구문

SELECT 컬럼명
FROM 테이블명
AS OF TIMESTAMP (TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS'));

또는 SCN을 기준으로 조회할 경우:

SELECT 컬럼명
FROM 테이블명
AS OF SCN <SCN_NUMBER>;

---

주요 포인트

1. UNDO 데이터 보존 기간:
   FLASHBACK QUERY는 UNDO 테이블스페이스에 의존하므로, UNDO 데이터가 보존되는 기간 안에서만 사용할 수 있습니다.
   기본적으로 UNDO_RETENTION 설정값에 따라 보존 기간이 결정됩니다.
2. SHOW PARAMETER UNDO_RETENTION;
3. AS OF 절:
   FLASHBACK QUERY는 AS OF 절을 사용해 원하는 시점의 데이터를 조회합니다.
   SCN(System Change Number) 또는 TIMESTAMP(날짜/시간)을 기준으로 설정할 수 있습니다.
4. 시간 형식 변환:
   날짜와 시간을 변환하기 위해 TO_TIMESTAMP 함수를 자주 사용합니다.

---

3. FLASHBACK QUERY 활용 예제

예제 1: 실수로 삭제된 데이터 복구

-- 현재 테이블 데이터 확인
SELECT * FROM EMPLOYEES;

-- 특정 시점의 데이터를 조회
SELECT * 
FROM EMPLOYEES 
AS OF TIMESTAMP (TO_TIMESTAMP('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));

-- 삭제된 데이터를 복구
INSERT INTO EMPLOYEES
SELECT * 
FROM EMPLOYEES 
AS OF TIMESTAMP (TO_TIMESTAMP('2024-12-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'))
WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM EMPLOYEES);

예제 2: 잘못된 업데이트 복구

-- 실수로 모든 직원의 급여를 10% 줄임
UPDATE EMPLOYEES
SET SALARY = SALARY * 0.9;

-- 복구를 위해 이전 데이터를 조회
SELECT EMPLOYEE_ID, SALARY
FROM EMPLOYEES
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

-- 원래 급여로 복구
MERGE INTO EMPLOYEES E
USING (SELECT EMPLOYEE_ID, SALARY 
       FROM EMPLOYEES
       AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)) OLD
ON (E.EMPLOYEE_ID = OLD.EMPLOYEE_ID)
WHEN MATCHED THEN
UPDATE SET E.SALARY = OLD.SALARY;

예제 3: 특정 시점의 데이터 감사

-- 현재와 과거 데이터를 비교해 변경된 내용 확인
SELECT A.EMPLOYEE_ID, A.SALARY AS CURRENT_SALARY, B.SALARY AS OLD_SALARY
FROM EMPLOYEES A
LEFT JOIN EMPLOYEES AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) B
ON A.EMPLOYEE_ID = B.EMPLOYEE_ID
WHERE A.SALARY != B.SALARY;

---

4. FLASHBACK QUERY의 한계

FLASHBACK QUERY는 강력한 도구지만 몇 가지 제약이 있습니다.

1. UNDO 테이블스페이스 제한:
   UNDO_RETENTION 설정값에 따라 과거 데이터를 조회할 수 있는 범위가 제한됩니다.
2. DDL 변경 사항:
   테이블에 DDL 변경(예: 컬럼 추가, 삭제)이 발생한 경우, FLASHBACK QUERY가 실패할 수 있습니다.
3. 성능:
   대규모 데이터를 FLASHBACK QUERY로 조회할 경우 성능에 영향을 줄 수 있으므로 주의가 필요합니다.

---

5. 결론

FLASHBACK QUERY는 데이터 복구와 감사 작업에 매우 유용한 도구입니다.
특히 실수로 데이터를 삭제하거나 잘못 수정했을 때 복구할 수 있는 안전망을 제공합니다.
다만, UNDO 데이터 보존 기간과 성능 문제를 고려하여 사용하는 것이 중요합니다.

데이터베이스 관리자는 FLASHBACK QUERY를 활용하여 실수로 인한 데이터 손실을 최소화하고, 신뢰성 높은 데이터베이스 운영을 지원할 수 있습니다.
궁금한 점이나 추가적인 사례가 필요하다면 댓글로 남겨주세요! 😊