[Tibero/Oracle] 실수로 지우거나 업데이트 친 데이터 COMMIT 이후 복구 하기(FlashBack)

 개발을 하다보면 특히 SM개발자 라면 운영기와 개발기 DB에 커넥션을 맺어놓고 개발과 운영을 왔다 갔다하며 업무를 처리하는 경우가 종종 있다. 그러다보면 정신없이 개발기에 날려야 하는 SQL을 운영에 날리는 경우도 발생한다.

 

 

 최근에는 운영DB에 DML문(UPDATE, INSERT, DELETE , SELECT)을 날리지 못하게 하거나 사전 결재를 받은 DML문만 처리 되도록 장치들이 마련되어 있어서 그럴 가능성이 낮긴 하지만, 그럼에도 의도한 DML문을 COMMIT이후에 다시 복구해야 하는 경우들이 종종 있다.

 

 특히 대량 업데이트나 딜리트 문을 날리고 커밋을 치는 순간! 등뒤로 쎄한 느낌이 드는 경험이 한번쯤을 있을 텐데 진짜 멘붕이 아닐 수 없다. 특히 입사한지 얼마 되지 않았을 때 WHERE 절 조건을 잘못주거나 안넣어서 전체 테이블이 날아가거나 일괄로 업데이트가 되고나면 그 공포감을 이루 말할 수 없다. 이걸 어쩌지? 사직서를 써야하나? 라는 생각밖에 들지 않는다.

 다행히도 티베로/오라클은 일정 시간 이전의 데이터를 마치 윈도우의 휴지통 처럼 따로 기억을 하고 있다.  Flashback이라는 기능으로 불리는데 Tibero 4.0 SP1 / Oracle9i Database Release 2 버전 이후로 기능을 제공 한다.

 Keyword는 'as of timestamp' 를 사용한다.

 단, Flashback 기능은 Undo Tablespace를 이용하기 때문에 Undo Retention 사이즈(Default 900sec[15분]) 만큼만 조회가 가능하다.

Undo Tablesapce는 주로 commit이전 까지의 트랜잭션을 기록해 두는 공간이라고 보면 된다.

즉, 특정 시점부터 트랜잭션을 기록해서 최종적으로 commit을 치기 직전까지 데이터베이스가 어떻게 변화 될지에 대한 기록이다.

 

위에도 언급해 두었듯이 별도로 Undo Retention 셋팅을 변경하지 않으면 15분 내의 기록만 조회가 가능하다.

쎄하면? 15분 이내에는 조회해보는 것이 좋고, 가능하면 DBA에게 셋팅값을 물어봐 놓으면 혹시나 하는 때에 도움을 받을 수있다.

 

서론이 길었지만 사용법은 매우 간단하다.

◎ 10 분전의 데이터를 조회 하기

 SELECT * 
   FROM TABLE_NAME AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE)
   ;

※ INTERVAL 뒤의 숫자와 KEYWORD를 수정하여 시점을 조절 할 수 있다.

EX) SYSTIMESTAMP - INTERVAL '300' SECOND -- 300초 전

 

◎ 특정 시점의 데이터를 조회하기

 SELECT * 
   FROM TABLE_NAME AS OF TIMESTAMP(TO_DATE('2021-09-26 21:00:00', 'YYYY-MM-DD HH24:MI:SS'))
   ;

※ 원하는 시점으로 조회도 가능하다.

 

◎ DELTE 복구하기

INSERT INTO TABLE_NAME
SELECT * FROM TABLE_NAME AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE)
;

◎ UPDATE 복구하기

UPDATE
       (SELECT ORG.COL_NAME
             , BAK.COL_NAME AS BAK_COL
          FROM TABLE_NAME ORG
             , (SELECT * FROM TABLE_NAME AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '10' MINUTE)) BAK
         WHERE ORG.PK_COL = BAK.PK_COL
        )
   SET COL_NAME = BAK_COL
;

-- SUBQUERY나 EXIST, MERGE 등을 이용해도 가능하다

※ 당황하지 말고 복구하자.

 

신입사원 혹은 부사수가 실수했을 때도 걱정말라며 다독여주고 슥삭 복구한다면 선망의 눈길을 보낼지도 ㅎ