오라클(Oracle) UPDATE/DELETE 실수 복구하는 방법: 시간을 되돌리는 Flashback Query 완벽 가이드

 

DB에서 INSERT 문이나 UPDATE 문을 작성하다가 실수로 SELECT 문인 줄 알고 무심코 실행했던 아찔한 경험, 실무를 하시는 분들이라면 다들 한 번쯤 있으실 겁니다. 특히 정신을 똑바로 차려야 하는 환경에서 조건문(WHERE) 없이 전체 데이터에 UPDATE를 쳐버렸을 때의 그 등골 서늘함이란 말로 다 할 수 없습니다.

저 역시 오래전, 아무런 조건 없이 업데이트를 실행하고 잠이 확 깼던 잊지 못할 기억이 있습니다. 당시에는 오라클에 이런 유용한 복구 기능이 있는 줄도 몰랐기 때문에, DBA(데이터베이스 관리자)를 불러야 하나, 로그 파일로 밤새 복구해야 하나 발만 동동 굴렀었죠. 만약 그때 오라클의 '시간 돌리기' 쿼리문을 알았더라면 단 몇 분 만에 조치했을 텐데 말입니다.

오늘은 저와 같은 실수를 하신 분들을 위해, 오라클 DB에서 업데이트나 삭제 전의 시간으로 돌아가 데이터를 확인하고 안전하게 복구할 수 있는 마법 같은 기능에 대해 상세히 알아보겠습니다.

 

1. 오라클의 타임머신, 플래시백 쿼리(Flashback Query)란?
우리가 흔히 '시간 돌리기'라고 부르는 이 기능의 정확한 명칭은 **플래시백 쿼리(Flashback Query)**입니다. 이 기능은 말 그대로 과거 특정 시점의 데이터베이스 상태를 조회할 수 있게 해주는 아주 강력한 도구입니다.

이미 커밋(COMMIT)을 완료해 버려서 롤백(ROLLBACK) 명령어조차 먹히지 않는 절망적인 상황에서도, 이 기능을 사용하면 지워지거나 변경되기 전의 원본 데이터를 찾아낼 수 있습니다. 이 마법 같은 일이 가능한 이유는 오라클이 내부적으로 데이터 변경 이력을 관리하는 '언두(Undo) 데이터'를 저장하고 있기 때문입니다. 데이터가 변경될 때 이전 값을 언두 테이블스페이스라는 임시 공간에 보관해 두는데, 플래시백 쿼리는 바로 이 공간을 뒤져서 우리에게 과거 데이터를 보여주는 원리입니다.

 

 

2. AS OF TIMESTAMP 사용법 (과거 데이터 조회하기)
사용법은 생각보다 매우 직관적이고 간단합니다. 우리가 평소에 사용하는 일반적인 SELECT 문 뒤에 AS OF TIMESTAMP 절과 원하는 시간을 명시해 주기만 하면 됩니다.

예를 들어, 방금 치명적인 업데이트 실수를 했고 약 50분 전 상태의 원본 데이터 값이 필요하다고 가정해 보겠습니다. 쿼리문은 다음과 같이 작성합니다.

 

select * from  TABLE명 as of timestamp(systimestamp-interval '50' minute);

 

위 쿼리에서 'TABLE명' 부분에 조회하고 싶은 실제 DB 테이블의 이름을 작성하고, '50' 부분에 되돌리고 싶은 시간(분 단위)을 넣어주시면 됩니다.

만약 'INSA'라는 인사 정보 테이블에서 사원 번호(empno) 칼럼이 '004'인 특정 사용자의 50분 전 데이터만 콕 집어서 확인하고 싶다면, 평소처럼 WHERE 조건절을 추가하여 아래와 같이 응용할 수 있습니다.

select * from INSA as of timestamp(systimestamp-interval '50' minute) where empno='004';

 

3. 조회한 과거 데이터로 실제 복구 진행하기
과거 데이터를 조회하는 데 성공해서 안도의 한숨을 쉬셨다면, 이제 엎질러진 물을 다시 주워 담을 차례입니다. 조회한 과거 데이터를 바탕으로 기존 테이블에 덮어쓰거나 빈 곳을 다시 채워 넣을 수 있습니다.

만약 데이터를 통째로 날린 DELETE 실수였다면, 아래와 같이 INSERT 문과 결합하여 삭제되기 전의 데이터를 원상 복구할 수 있습니다.

INSERT INTO INSA (SELECT * FROM INSA AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '50' MINUTE) WHERE empno='004');

 

조건 없는 UPDATE를 친 경우라면 구조가 조금 더 복잡해질 수 있지만, 위 방법으로 과거 데이터를 조회해 임시 테이블(Temp Table)에 백업본을 만들어두고, 원본 테이블과 조인(Join)하여 올바른 값으로 다시 업데이트를 치는 방식으로 안전하게 복구할 수 있습니다.

 

 

4. 주의사항: 무한정 과거로 돌아갈 수는 없습니다!
플래시백 쿼리가 만능 동아줄 같지만, 치명적인 주의사항이 하나 있습니다. "그럼 일주일 전, 한 달 전에 실수한 것도 되돌릴 수 있나요?"라고 묻는다면 대답은 "아니요"입니다.
앞서 말씀드린 과거 기록 저장소인 '언두 데이터'는 무한정 데이터를 보관하지 않습니다. DB 용량의 한계가 있기 때문에 오라클 데이터베이스의 설정(Undo Retention 파라미터)에 따라 보관되는 시간이 정해져 있습니다. 시스템 환경마다 다르지만 보통 1~2시간(120분) 내외로 설정되어 있는 경우가 많습니다.
만약 이 보존 시간이 지나서 언두 데이터가 이미 덮어씌워져 삭제된 며칠 전의 과거를 조회하려고 하면 ORA-01555: snapshot too old 라는 에러 메시지를 만나게 됩니다. 따라서 실수를 인지했다면 당황하지 말고 최대한 빨리(골든 타임 내에) 쿼리를 실행하여 데이터를 확보하는 것이 가장 중요합니다.

 

 

마무리: 항상 쿼리 실행 전에는 확인, 또 확인!
저 역시 실무에서 매번 필요할 때마다 이 길고 복잡한 쿼리문을 완벽히 외우고 있지는 않습니다. 그래서 이렇게 제 블로그에 잘 정리해 둔 뒤, 필요할 때마다 접속해서 복사하고 붙여넣기 하여 상황에 맞게 가공해서 아주 유용하게 사용하고 있습니다.
바쁘게 업무를 쳐내거나 잠시 멍할 때는 정말 한순간의 클릭 한 번으로 어쩔 수 없는 대형 사고가 발생하곤 합니다. 하지만 제일 좋은 복구 방법은 사고를 미연에 방지하는 것이겠죠? 쿼리문을 실행할 때, 특히 실무 DB에서 UPDATE나 DELETE를 다룰 때는 항상 맑은 정신으로 조건문(WHERE)이 정확히 들어갔는지 두 번, 세 번 거듭 확인하는 습관을 들여야겠습니다.
이 글이 지금 이 순간에도 식은땀을 흘리며 검색창을 두드리고 계실 누군가에게 큰 도움이 되기를 바랍니다!