[Oracle] AS OF TIMESTAMP 삭제한 데이터 복구

이번에 교육해 드리는 내용은 오라클 데이터베이스에서 AS OF TIMESTAMP를 통해 테이블의 과거 데이터를 조회해 보고 복구하는 방법에 대한 것입니다. AS OF TIMESTAMP 조회 및 복구 기능은 상당히 도움이 많이 되는 기능이니 꼭 기억해 두세요!

  • 저장된 데이터를 모르고, 또는 실수로 삭제해 버렸을 때 복구하고 싶으신 분!
  • 커밋까지 해버려서 발을 동동 굴러보신 적이 있으신가요?

AS OF TIMESTAMP

AS OF TIMESTAMP

실수로 데이터베이스의 내용을 저장하거나 수정, 또는 삭제한 경험이 있을 것입니다. 다시 롤백을 하면 되니까요. 그런데 어떤 이는 이러한 실수에 더해 커밋까지 하는 불상사를 겪기도 합니다. 그나마 저장한 데이터면 저장 시간을 기억해서 처리라도 할 수 있을 텐데, 데이터를 수정이나 삭제해 버리는 난감한 상황에서는 어떻게 조치를 해야 할지 당황할 수도 있을 것입니다. 이때에 도움이 되는 것이 오라클 데이터베이스의 AS OF TIMESTAMP 구문입니다.

삭제한 데이터 복구하기

타임스탬프 구문을 사용하여 데이터베이스를 조회하는 방법은 다음과 같습니다. 대상 테이블을 명시하고 AS OF TIMESTAMP 구문 다음에 원하는 타임스탬프를 입력하는 것입니다. 괄호를 입력하여도 되고 안 하여도 되지만, 보통 구분을 위해서 하는 편입니다.

SELECT * FROM EMP AS OF TIMESTAMP SYSTIMESTAMP;

--보통은 괄호로 구분하는 편
SELECT * FROM EMP AS OF TIMESTAMP (SYSTIMESTAMP);

특정 시간 데이터 조회하기

AS OF TIMESTAMP에서 타임스탬프 형식을 설정하여 특정 시간 데이터를 조회하는 방법입니다. 해당 예제는 2023년 3월 1일 00시 00분 00초의 EMP 테이블을 조회해 보는 예시입니다. 타임스탬프를 적당히 가공하여 YYYY-MM-DD 등 여러 포맷을 사용할 수 있습니다.

SELECT *
       FROM EMP
            AS OF TIMESTAMP TO_TIMESTAMP ('2023-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7839 KING                 PRESIDENT                     96/11/17       5000                    10
      7698 BLAKE                MANAGER                  7839 91/01/05       2850                    30
      7782 CLARK                MANAGER                  7839 99/09/06       2450                    10
      7566 JONES                MANAGER                  7839 01/02/04       2975                    20
      7788 SCOTT                ANALYST                  7566 03/06/17       3000                    20
      7902 FORD                 ANALYST                  7566 81/03/12       3000                    20
      7369 SMITH                CLERK                    7902 07/12/01        800                    20
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7844 TURNER               SALESMAN                 7698 81/08/09       1500          0         30
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20
      7900 JAMES                CLERK                    7698 81/03/12        950                    30
      7934 MILLER               CLERK                    7782 03/01/23       1300                    10

오라클 데이트타임 포맷에 맞추어 원하는 형식으로 설정할 수 있습니다.

SELECT *
  FROM EMP AS OF TIMESTAMP TO_TIMESTAMP ('2023-03-01', 'YYYY-MM-DD')

현재 시간을 기준으로 데이터 조회하기

수정이나 삭제를 한 다음에 과거 데이터를 조회하여 복구할 때에는 현재 시점이 중요할 것입니다. SYSTIMESTAMP를 통해 현재의 시스템 타임스탬프를 조회할 수 있습니다. 다음은 현재시간을 기준으로 한 시간 이전의 데이터를 조회하는 예제입니다. SYSTIMESTAMP에서 INTERVAL '1' HOUR를 뺀 시간을 뜻합니다. SECOND, MINUTE, HOUR, DAY 등을 사용할 수 있습니다.

SELECT *
  FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;
  
SELECT *
  FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' SECOND;

SELECT *
  FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' MINUTE;

SELECT *
  FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY;

AS OF TIMESTAMP 조회 및 복구하기

AS OF TIMESTAMP

오라클 차집합 연산자로 1시간 전 데이터와 현재 데이터를 비교하는 예제를 보여드리겠습니다. EMPNO가 7839인 사번을 실수로 삭제하고 커밋한 것을 가정하여, 1시간 전 데이터에는 있고, 현재 데이터에는 없어서 차집합에 조회가 되는 상황입니다. 이외에도 AS OF TIMESTAMP를 활용할 수 있는 방법은 많으니 연구해 보세요~

SELECT *
  FROM EMP AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
MINUS
SELECT * FROM EMP;

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7839 KING                 PRESIDENT                     96/11/17       5000                    10

undo_retention

AS OF TIMESTAMP는 undo 관련 시스템 파라미터를 보유하여야 사용이 가능합니다. AS OF TIMESTAMP 기능을 무조건 신뢰하지 마시고, 평소부터 커밋하기 전에 신중히 생각하는 습관을 가지셔야 합니다!

SELECT NAME, DESCRIPTION
       FROM V$PARAMETER
      WHERE NAME LIKE '%undo%';

NAME, DESCRIPTION
undo_management, instance runs in SMU mode if TRUE, else in RBU mode
undo_tablespace, use/switch undo tablespace
undo_retention, undo retention in seconds

오라클 삭제한 데이터 복구하기 SYSTIMESTAMP - INTERVAL

AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL

AS OF TIMESTAMP 명령어를 통해 특정 시간대의 데이터를 임시로 살펴볼 수 있습니다. 드라이브에서 데이터 삭제했을 때도 며칠 정도의 유예를 주듯이 비슷한 기능입니다. 하지만, 삭제한 데이터는 휴지통에 계속 남아있지 않기에 조회할 수 있는 시간 한계가 있다는 점을 꼭 주의해 주세요.

-- INSERT INTO ~ SELECT 로 복구 처리를 진행하고, 다시 조회해 봅시다.
SELECT *
  FROM tableName AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

댓글

댓글 쓰기