잘못 삭제하거나 수정한 데이터 복구하는 방법(FLASHBACK 설정과 사용)

데이터를 수정하려다가 잘못하는 경우나 이상이 있어 과거의 데이터를 보려고 할 때가 있습니다.

데이터 수정시 실수 많이 하는 유형은 DELETE나 UPDATE에서 WHERE절 실수로 벌어지는 경우가 많습니다.

평소에는 분명이 커밋하기 전에 확인하는데 실수할 때는 뭐에 홀린 듯이 확인도 안 하고 커밋을 합니다.

COMMIT메시지를 보는 순간 정신이 돌아오게 되지만 이미 늦은 경우가 대부분입니다.

또는 실수가 아니여도 데이터베이스 관리자 및 개발자들은 종종 과거의 데이터를 조회해야 할 때가 있습니다.

관련 설정이 되어 있는 경우 오라클 데이터 베이스에서는 이런 경우 쿼리를 사용하여 과거 데이터를 조회할 수 있습니다.

무한정은 아니고 설정된 메모리에 따라서 저장이 되므로 변경이 적은 테이블은 오래된 데이터가 남아있을 수 있지만

빈번하게 변경되는 테이블은 몇 시간 전의 데이터도 없을 수 있습니다.

이번 포스팅에서는 아래와 같은 내용을 다루고 있습니다.

1. 오라클 테이블 과거 데이터 조회하기
2. 오라클 오브젝트 과거 데이터 조회하기
3. 과거 데이터 조회시 발생하는 오류
4. FLASHBACK 관련 설정

 

 

---

1. 오라클 테이블 과거 데이터 조회하기

 

1-1. 지금 시간 기준 과거 데이터 조회하기

SELECT * FROM TABLE_NAME AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) WHERE 1=1

 

 

HOUR 대신 SECOND, MINUTE, DAY를 사용하여 더 짧은 시간 또는 더 긴 시간을 조건으로 줄 수 있습니다.

위 조건은 지금 시간을 기준으로 몇 초, 분, 시간, 일 전을 조회하는 쿼리입니다.

하지만 5분전 데이터를 보고 있는데 시간이 지나버리면 다시 6분 전, 7분 전처럼 바꿔야 하는 불편함이 있습니다.

이런 경우 특정한 시간을 아래처럼 지정하여 조회할 수 있습니다.

 

1-2. 특정 시간 지정하여 데이터 조회하기

 

SELECT * FROM TABLE_NAME AS OF TIMESTAMP (TO_DATE ( '20230329150000', 'YYYYMMDDHH24MISS' ))

 

이렇게 조회한 데이터를 이용하여 현재 테이블의 데이터를 맞추면 됩니다.

만약 데이터를 삭제한 경우이거나 업데이트 한 컬럼이 많아서 복잡하다면 INSERT구문을 이용하여 복구할 수 있습니다.

 

INSERT INTO TABLE_NAME 
(SELECT * FROM TABLE_NAME 
AS OF TIMESTAMP (TO_DATE ( '20230329150000', 'YYYYMMDDHH24MISS' )))

 

 

---

2. 오라클 오브젝트 과거 데이터 조회하기

테이블이 아닌 경우도 과거 데이터를 조회할 수 있습니다.

이런 경우에는 SYS계정이 필요합니다.

 

SELECT * FROM DBA_SOURCE AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MIUTE)
WHERE TYPE = 'PROCEDURE'
AND NAME = 'PROC_NAME'

 

타입에 PROCEDURE가 아닌 FUNCTION, PACKAGE, TRIGGER 등을 써주면 다른 오브젝트도 조회 가능합니다.

 

 

 

---

3. 과거 데이터 조회시 발생하는 오류

조회하고자 하는 과거 데이터가 너무 오래된 경우 ORA-01555(snapshot too old) 오류가 발생합니다.

해당 시점의 데이터는 저장되어 있지 않다는 뜻입니다.

아래 링크에 해당 오류가 소개되어 있습니다.

 

오라클 오류 코드 모음, 오류 메세지 두번째

 

아니면 ORA-08180(no snapshout found based on specified time) 오류가 발생합니다.

어떤 오류던지 저 메세지들을 보는순간 복구가 힘들어 지므로 안보고 싶은 오류중 하나입니다.

 

이러지마 ㅠㅠ

 

만약 이러한 오류를 발생시키지 않으려면 시스템 값을 조정해 주어야 합니다.

 

 

---

4. FLASHBACK 관련 설정

 

SYS권한이 있는 계정으로 로그인한 후 아래 쿼리를 실행시키면 FLASHBACK관련 파라미터를 보여줍니다.

관련 설정을 확인 후 수정하시면 됩니다.

 

SELECT name, value FROM v$parameter WHERE name LIKE 'undo%';

 

• undo_management : Undo Data를 자동으로 관리할지 수동으로 관리할지를 결정하는 파라미터입니다. FlashBack 기능을 사용하려면 AUTO로 설정해야 합니다.
• undo_tablespace : Undo Data를 저장할 테이블스페이스를 지정하는 파라미터입니다. 테이블스페이스의 크기와 성능에 따라 FlashBack 기능의 효율이 달라질 수 있습니다.
• undo_retention : Undo Data를 보존할 시간을 초 단위로 지정하는 파라미터입니다. 이 값이 클수록 더 오래된 시점의 데이터를 조회할 수 있습니다. 하지만 너무 큰 값으로 설정하면 테이블스페이스가 부족해질 수 있으므로 주의해야 합니다.