시퀀스 DROP 삭제 시 시퀀스 복구 방법

haisins

2년 ago

Oracle 12c R2 버전 이상 복구 방법 입니다.

실수로 시퀀스를 DROP 했을 경우 입니다.

— 오후 1시에 drop 시퀀스문을 실행한 경우

select * from DBA_SEQUENCES as of timestamp(TO_DATE(‘2023/01/05 12:59:59′,’YYYY/MM/DD HH24:MI:SS’)) ;

이렇게 DBA 뷰 에 Flashback 기능을 접목 시켜 수행 하면

삭제 이전 시점의 시퀀스 Last number 마지막 숫자를 추출 할 수 있습니다.

이 것을 이용해 삭제된 시퀀스명 으로 새로운 시퀀스를 시작하는 숫자를 삭제 시점 시퀀스 마지막 숫자로 하여 만들면 번호가 중복되지 않고 복구가 가능합니다.

단 LAST number는 cache 값 만큼 선 확보되어서 중간 번호가 띠어진 상태 입니다.

오라클(ORACLE) 데이터를 TIMESTAMP를 사용하여 복구

데이터베이스를 이용하다보면 실수로 데이터를 삭제하거나, 잘못 업데이트를 하는 경우가 있습니다.

commit을 하기전이라면 상관이 없습니다.

하지만 commit을 한 이후에 문제를 파악하여 데이터를 복구해야 하는 경우가 생깁니다.

이럴때 ORACLE 의 TIMESTAMP를 이용하면 데이터를 복구 할 수 있습니다.

TIMESTAMP 사용조건

oracle 9i 이상부터 지원

DB의 설정에 따라서 시간이 오래지난 데이터는 TIMESTAMP로 복구가 불가능합니다.

TIMESTAMP 사용법

단위는 SECOND, MINUT, HOUR, DAY 로 지정해서 사용할 수 있습니다.

SYSTIMESTAMP 에서 설정한 시간을 입력하여 데이터를 보여줍니다.

<span class="hljs-comment">-- 10초전 데이터 조회</span> <span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> 테이블 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'10'</span> <span class="hljs-keyword">SECOND</span>)
<span class="hljs-keyword">WHERE</span> 컬럼 = <span class="hljs-string">'A'</span>; <span class="hljs-comment">-- 필요에 따라서 조건문</span>  <span class="hljs-comment">-- 10분전 데이터 조회</span> <span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> 테이블 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'10'</span> <span class="hljs-keyword">MINUTE</span>)
 
<span class="hljs-comment">-- 3시간전 데이터 조회 </span> <span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> 테이블 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'3'</span> <span class="hljs-keyword">HOUR</span>)

<span class="hljs-comment">-- 1일전 데이터 조회 </span> <span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> 테이블 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'1'</span> <span class="hljs-keyword">DAY</span>)

<span class="hljs-comment">-- 특정시간 기준으로 데이터 조회</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> USER_TB <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(<span class="hljs-keyword">TO_DATE</span>(<span class="hljs-string">'20200605000000'</span>, <span class="hljs-string">'YYYYMMDDHH24MISS'</span>));

-- 10초전 데이터 조회 SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' SECOND)

WHERE 컬럼 = 'A'; -- 필요에 따라서 조건문 -- 10분전 데이터 조회 SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)

-- 3시간전 데이터 조회 SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '3' HOUR)

-- 1일전 데이터 조회 SELECT * FROM 테이블 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '1' DAY)

-- 특정시간 기준으로 데이터 조회 SELECT * FROM USER_TB AS OF TIMESTAMP(TO_DATE('20200605000000', 'YYYYMMDDHH24MISS'));

TIMESTAMP 데이터 쉽게 비교하기

어떤 데이터가 변경이 되었는지 확인할 경우

아래와 같이 비교하시면서 보면 확인하기가 편합니다.

현재 데이터와 비교할 데이터를 UNION ALL로 묶어서 보기

<span class="hljs-comment">-- GOODS_NM 이 선풍기인 데이터를 현재 데이터와 10분전 데이터 비교</span>  <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> TEMP_1018 <span class="hljs-keyword">where</span> GOODS_NM = <span class="hljs-string">'선풍기'</span> <span class="hljs-keyword">UNION</span> <span class="hljs-keyword">ALL</span>  <span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> TEMP_1018 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'10'</span> <span class="hljs-keyword">MINUTE</span>) <span class="hljs-keyword">where</span> GOODS_NM = <span class="hljs-string">'선풍기'</span>;

<span class="hljs-comment">-- 10분전에는 GOODS_QTY 가 77개 인것을 확인</span>

-- GOODS_NM 이 선풍기인 데이터를 현재 데이터와 10분전 데이터 비교 SELECT * FROM TEMP_1018 where GOODS_NM = '선풍기' UNION ALL SELECT * FROM TEMP_1018 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) where GOODS_NM = '선풍기';

-- 10분전에는 GOODS_QTY 가 77개 인것을 확인

TIMESTAMP 데이터 생성, 수정 방법

<span class="hljs-comment">-- select insert 를 이용하여 삭제된 데이터 처리</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> TEMP_1018
<span class="hljs-keyword">SELECT</span>  *  <span class="hljs-keyword">FROM</span> TEMP_1018 <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'10'</span> <span class="hljs-keyword">MINUTE</span>) <span class="hljs-keyword">where</span> GOODS_NM = <span class="hljs-string">'선풍기'</span>;


<span class="hljs-comment">-- select update 를 이용하여 변경된 데이터 처리</span> <span class="hljs-keyword">UPDATE</span> TEMP_1018 a
<span class="hljs-keyword">SET</span> 
a.GOODS_QTY = (<span class="hljs-keyword">SELECT</span> B.GOODS_QTY <span class="hljs-keyword">FROM</span> TEMP_1018 B <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> <span class="hljs-built_in">TIMESTAMP</span>(SYSTIMESTAMP-<span class="hljs-built_in">INTERVAL</span> <span class="hljs-string">'10'</span> <span class="hljs-keyword">MINUTE</span>) <span class="hljs-keyword">where</span> a.GOODS_NM = b.GOODS_NM <span class="hljs-keyword">and</span>  b.GOODS_NM = <span class="hljs-string">'선풍기'</span>)
<span class="hljs-keyword">WHERE</span> a.GOODS_NM =<span class="hljs-string">'선풍기'</span>

-- select insert 를 이용하여 삭제된 데이터 처리 INSERT INTO TEMP_1018

SELECT * FROM TEMP_1018 AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) where GOODS_NM = '선풍기';

-- select update 를 이용하여 변경된 데이터 처리 UPDATE TEMP_1018 a

SET

a.GOODS_QTY = (SELECT B.GOODS_QTY FROM TEMP_1018 B AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE) where a.GOODS_NM = b.GOODS_NM and b.GOODS_NM = '선풍기')

WHERE a.GOODS_NM ='선풍기'