이번에 교육해 드리는 내용은 오라클 데이터베이스에서 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 조회 및 복구하기
오라클 차집합 연산자로 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 10undo_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 명령어를 통해 특정 시간대의 데이터를 임시로 살펴볼 수 있습니다. 드라이브에서 데이터 삭제했을 때도 며칠 정도의 유예를 주듯이 비슷한 기능입니다. 하지만, 삭제한 데이터는 휴지통에 계속 남아있지 않기에 조회할 수 있는 시간 한계가 있다는 점을 꼭 주의해 주세요.
-- INSERT INTO ~ SELECT 로 복구 처리를 진행하고, 다시 조회해 봅시다.
SELECT *
FROM tableName AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);