[SQL] 데이터 복구하기 - FLASHBACK QUERY

FLASHBACK QUERY

백업을 복구하지 않고 과거 시점의 데이터를 조회할 때 FLASHBACK QUERY를 사용한다.

FLASHBACK은 최대 15분 전의 데이터를 조회할 수 있다.

-- 테이블의 N분전 데이터 검색
SELECT *
FROM TABLE_NAME
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL 'N' MINUTE)

 

EX ) 사원 테이블의 5분 전 KING 데이터

-- KING의 현재 월급 조회
SELECT *
FROM EMP
WHERE ENAME = 'KING';

-- KING의 월급을 0으로 변경한다.
UPDATE EMP
SET SAL = 0
WHERE ENAME = 'KING';

COMMIT;

SELECT *
FROM EMP
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE ENAME = 'KING';

테이블을 FLASHBACK할 수 있는 골든 타임은 기본이 15분이다. 이 시간은 데이터베이스의 파라미터인 UNDO_RETENTION으로 확인할 수 있다.

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME = 'undo_retention';

-- VALUE의 900은 900초(15분)를 의미한다. 

 

FLASHBACK TABLE

실수로 데이터를 DELETE하고 COMMIT했을 경우 FLASHBACK TABLE을 사용한다

FLASHBACK은 백업을 가지고 복구하는 것이 아니라 5분 전부터 현재까지 수행했던 DML작업을 반대로 수행하면서 과거로 되돌린다. 5분 전부터 현재까지 수행한 작업 중 DELETE가 있었다면, 반대로 INSERT를 수행하고 INSERT가 있었다면 반대로 DELETE를 수행한다. 성공적으로 FLASHBACK이 되었다면 데이터를 확인한 후 COMMIT을 해야 변경된 상태가 데이터베이스에 영구히 반영된다. 단, 과거 시점부터 현재 시점 사이에 DDL문이나 DCL문을 수행했다면 FLASHBACK명령어가 수행되지 않고 에러가 발생한다. 또한 FLASHBACK TABLE은 테이블이 FLASHBACK 가능한 상태인 테이블에 대해서만 가능하다.

FLASHBACK 가능 여부 확인 및 가능상태로 변경하기

-- FLASHBACK 가능 여부 확인 
SELECT ROW_MOVEMENT
FROM USER_TABLES
WHERE TABLE_NAME = 테이블 명;

-- FLASHBACK이 가능한 상태로 변경하기
ALTER TABLE 테이블 명 ENABLE ROW MOVEMENT;

 

EX ) 테이블을 5분전 상태로 되돌리기

-- FLASHBACK 가능 여부 확인
SELECT ROW_MOVEMENT
FROM USER_TABLES
WHERE TABLE_NAME = EMP;

-- 새로운 행 추가
INSERT INTO EMP (EMPNO, ENAME, SAL, HIREDATE, JOB) 
			 VALUES (2812, 'JACK', 3500, TO_DATE('2019/06/05','RRRR/MM/DD'), 'ANALYST');

-- 5분 전부터 현재까지 수행한 DML작업을 반대로 수행
FLASHBACK TABLE EMP TO TIMESTAMP (SYSTIMESTAMP - INTERVAL 5 MINUTE);

EX ) 특정 시점으로 되돌리기

FLASHBACK TABLE EMP TO TIMESTAMP
	TO_TIMESTAMP('19/06/30 07:20:59', 'RR/MM/DD HH24/MI/SS');

 

반응형

728x90

 

FLASHBACK DROP

실수로 테이블을 DROP했을 경우 FALSHBACK DROP TABLE 명령어로 복구한다.

오라클 10g버전부터 휴지통 기능이 생겨 테이블을 DROP할 경우 테이블이 휴지통에 들어가게 된다. 그래서 실수로DROP을 했을 때 다시 복구할 수 있다.

휴지통에 존재하는 확인하기

DROP TABLE EMP;

SELECT ORIGINAL_NAME, DROPTIME
FROM USER_RECYCLEBIN;

휴지통에서 복원하기

FLASHBACK TABLE 테이블명 TO BEFORE DROP; 

-- 복구할 때 테이블명을 변경할때
FLASHBACK TABLE 복구테이블명 TO BEFORE DROP RENAME TO 변경할테이블명;

 

FLASHBACK VERSION QUERY

과거부터 현재까지 테이블의 데이터가 어떻게 변경되어 있는지 이력을 확인할때 FLASHBACK VERSION QUERY를 사용한다.

EX ) 사원테이블의 과거 특정시점부터 현재까지의 변경 이력 정보 출력

SELECT VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
FROM EMP
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP(SYSTIMESTAMP, 'RRRR-MM-DD HH24:MI:SS')
    AND MAXVALUE
WHERE ENAME = 'KING'
ORDER BY VERSIONS_STARTTIME;

VERSIONS절에 변경 이력 정보를 보고 싶은 기간을 지정한다. TO_TIMESTAMP 변환 함수를 사용하여 시:분:초 까지 상세히 시간을 설정할 수 있다.

테이블 변경 이력 정보를 확인하는 방법

1. 현재 시간 확인하기

SELECT SYSTIMESTAMP FROM DUAL;

2. 데이터 변경하기

-- EX ) KING의 SAL 5000 -> 8000, DEPTNO 10 -> 20
-- 1) KING의 월급 정보 확인
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE ENAME = 'KING';

-- 2) 월급과 부서번호 변경 및 커밋
UPDATE EMP
SET SAL = 8000
WHERE ENAME = 'KING';

COMMIT;

UPDATE EMP
SET DEPTNO = 20
WHERE ENAME = 'KING';

COMMIT;

3. 데이터 변경 이력 정보 확인

SELECT ENAME, SAL, DEPTNO, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
FROM EMP
VERSIONS BETWEEN TIMESTAMP
    TO_TIMESTAMP('2023-01-21 01:27:06', 'RRRR-MM-DD HH24:MI:SS')
    AND MAXVALUE
WHERE ENAME = 'KING'
ORDER BY VERSIONS_STARTTIME;

4. 변경 이력을 확인한 후 EMP 테이블을 10분전으로 되돌리고 커밋

FLASHBACK TABLE EMP TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

COMMIT;

 

FLASHBACK TRANSACTION QUERY

특정 테이블을 과거의 특정 시점으로 되돌리기 위한 DML문을 추출하기 위해FLASHBACK TRANSACTION QUERY를 사용한다.

SCN

SCN은 System Change Numbers의 약자로 COMMIT할 때 생성되는 번호이다. FLASHBACK TRANSACTION QUERY를 사용할 때 이 SCN을 이용하여 과거 특정 시점으로 되돌릴 수 있다.

TRANSACTION QUERY의 결과를 보기 위해서는 데이터베이스 모드를 아카이브모드로 변경해야한다. 아카이브 모드로 변경하겠다는 것은 장애가 발생했을 때 DB를 복구할 수 있는 로그 정보를 자동으로 저장하게 하는 모드이다. 아카이브 모드로 변경하기 위해 DB를 한번 내렸다 올려야 한다.

 

EX ) KING의 월급을 8000, 부서번호를 20으로 변경한 후 변경이력 정보 확인하기

1. 아카이브 모드로 변경하기

-- SQL/PLUS MODE

-- SYS 유저로 접속
C:\\Users\\oracle> sqlplus "/as sysdba"

-- DB 종료
SHUTDOWN IMMEDIATE;
/*
database closed
Database dismounted.
ORACLE instance shut down.
*/

-- DATABASE를 MOUNT상태로 올리기
STARTUP MOUNT;
/*
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260048 bytes
Variable Size             817890224 bytes
Database Buffers          243269632 bytes
Redo Buffers                5517312 bytes
Database mounted.
*/

-- 아카이브 모드로 변경
ALTER DATABASE ARCHIVELOG;

-- DML문이 REDO LOG 파일에 저장될 수 있도록 설정
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

 

2. 데이터 변경

-- SQL 로그인 
-- USER_ID와 USER_PASSWARD자리에는 사용자의 아이디와 패스워드를 입력한다.
CONNECT USER_ID / USER_PASSWARD

-- 데이터 확인
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE ENAME = 'KING';

-- KING의 월급 변경
UPDATE EMP
SET SAL = 8000
WHERE ENAME = 'KING';

COMMIT;

-- KING의 부서번호 변경
UPDATE EMP
SET DPETNO = 20
WHERE ENAME = 'KING';

COMMIT;

 

3. 데이터 변경 이력 정보 확인

SELECT VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION, SAL, DEPTNO
FROM EMP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ENAME = 'KING';

본인는 월급과 부서번호 변경후 한번에 커밋해 한번의 변경정보만이 존재한다.

 

 

-- USER_ID 자리에는 사용자의 아이디를 입력한다.
SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE TABLE_OWNER = 'USER_ID' AND TABLE_NAME = 'EMP'
AND COMMIT_SCN BETWEEN MINVALUE AND MAXVALUE
ORDER BY START_TIMESTAMP DESC;

최근 UNDO(취소) 부터 정보를 출력한다. 출력된 결과를 보면 SAL과 DEPTNO가 변경되기 전의 정보를 알 수 있으며 ROWID, 즉 해당 로우의 물리적 주소를 확인할 수 있다.