[OracleDB] TIMESTAMP로 데이터 복구하기

TIMESTAMP

OracleDB를 사용하다가 실수로 잘못된 데이터를 UPDATE하고 COMMIT까지 해버렸을 경우, TIMESTAMP를 사용하여 일정 시간 이전의 테이블을 불러와 데이터를 복구하는데 사용할 수 있다.

 

이 기능을 Flashback 이라고 부른다.

 

사용 설정

1. UNDO_MANAGEMENT를 'AUTO'로 변경

UNDO 관리시스템을 자동으로 설정한다. 수동으로 변경하고 싶을 때는 MANUAL로 설정하면 된다.

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO;

 

2. UNDO_RETENTION 값 설정

UNDO_RETENTION은 과거 어느 시점까지의 UNDO 데이터를 보관할지 가리킨다. 단위는 초이며 기본값은 900초이다.

ALTER SYSTEM UNDO_RETENTION=1200

 

* UNDO_RETENTION 상세

https://sksggg123.github.io/db/undo-retention/

undo_retention 이슈 및 작업 정리

 

 

TIMESTAMP 사용

테이블명 뒤에 AS OF를 붙여 특정 TIMESTAMP 시점의 테이블 데이터를 불러올 수 있다. SYSTIME - INTERVAL '숫자' 시간단위 공식을 이용하여 현재 시간으로부터 특정 시간 이전의 테이블을 불러온다.

SELECT 컬럼명
  FROM 테이블명
 AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '숫자' 시간단위);

-- 5분 전의 EMPLOYEES 테이블의 전체 컬럼을 조회
SELECT *
  FROM EMPLOYEES
 AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE);

 

UPDATE employees
   SET last_name = (SELECT last_name
                      FROM employees
                           AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE)
                     WHERE department_id IS NULL)
 WHERE department_id IS NULL;

 

* 시간 단위

SECOND	초
MINUTE	분
HOUR	시간
DAY	일

 

 

* 사용 예시

-- 사원 급여를 1.1배 올리려고 했으나 11배나 올려버린 경우
UPDATE employees
   SET salary = salary * 11;
-- 커밋까지 해버렸을 때
COMMIT;

-- 다음과 같이 5분 전의 급여 내용으로 복구할 수 있다.
UPDATE employees e 
   SET salary = (SELECT salary
                   FROM employees
                        AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE)
                  WHERE employee_id = e.employee_id);