DB 관리중에 실수로 데이타를 삭제하거나 데이타의 값을 잘못 변경 하는 실수가 가끔 발생을 합니다.
이러한 오류를 바로 인식할 경우는 Rollback이라는 명령으로 바로 전에 수행한 작업을 원상복귀시킬 수 있지만, COMMIT을 한 이후 시점이나, 한참 시간이 지난 후에 알았다면 간단하게 복구하기가 난감 합니다.
이러한 경우에 특정한 시간 또는 시점으로 되돌릴 수 있는 기능이 Oracle Flashback 기능 입니다.
간단하게 말해서 Flashback 기능은 특정한 과거시점의 질의를 실행할 수 있게 해 줍니다.
데이타베이스에 구조적인 변화를 가하지 않고 과거 일정 시점의 데이타 상태를 확인할 수 있는 기능 입니다..
일종의 오라클에서 지원하는 타임머신이라고 할 수 있죠...
◈ Flashback을 사용하기 위한 요구조건
- 자동 언두 관리 시스템을 사용해야 합니다. (UNDO_MANAGEMENT 파라미터를 AUTO로 설정)
.UNDO_MANAGEMENT = AUTO
- 이전의 어느 시점까지의 언두(UNDO)정보를 보유하여 Flashback Query를 수행할것인지
UNDO_RETENTION 파라미터를 설정해야 합니다.
.ALTER SYSTEM SET UNDO_RETENTION=1800
- 일반사용자가 Flashback 기능을 이용하기 위해서 DBMS_FLASHBACK패키지에 대한 EXECUTE권한이 있어야 합니다.
◈ Flashback 사용하기
Flashback의 사용 방법은 과거시점의 특정 시간으로 사용하는 방법과 SCN(System Change Number)을 사용하는 방법이 있습니다.
- 과거시점의 시간 사용: DBMS_FLASHBACK.ENABLE_AT_TIME(query_time IN TIMESTAMP);
- SCN 사용 : DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(query_scn IN NUMBER);
과거시점의 시간을 지정하여 Flashback 기능을 사용 할 경우 오라클은 내부적으로 이를 SCN으로 전환하여 처리 합니다. 시간 정보를 SCN으로 Mapping하는 시간이 필요한데 통상 5분 주기로 이루어 집니다.
따라서, 시간으로 지정할때는 현재보다 5분이상 차이가 나는 과거시점을 지정해야 합니다.
또한 Flashback 기능은 무한대로 이전의 데이터를 조회할 수 있는 기능이 아니고,
관리자가 UNDO_RETENTION 파라미터를 통해서 정해준 시간(초) 동안의 데이터를 조회할 수 있습니다.
- 디폴트 UNDO_RETENTION 시간은 10800(3시간) 입니다.
그리고 Flashback data를 참고하는 경우엔 DML, DDL등의 작업을 직접 수행 할 수 없습니다.
◈ Flashback 사용예제
C:\>SQLPLUS /NOLOG
▒▒▒▒▒▒ Flashback 사용을 위한 환경설정 시작 ▒▒▒▒▒▒
-- SYSDBA 권한으로 접속
SQL>CONN / AS SYSDBA
-- UNDO MANAGEMENT MODE 확인
SQL>SHOW PARAMETER UNDO;
NAME TYPE VALUE
--------------------------- ----------- ---------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
-- undo_management가 MANUAL로 되어있을경우 아래와 같이 변경하고 UNDO 테이블스페이스를 생성하고 지정합니다..
SQL>ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;
-- UNDO 테이블 스페이스 생성
SQL>CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE ’D:\oracle\oradata\oracle\UNDOTBS2.dbf’ SIZE 100M;
-- UNDO 테이블 스페이스 지정
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2
-- UNDO_RETENTION 시간을 변경하시면 실제 적용을 위해 5분정도 기다려야 합니다.
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800
-- scott유저에게 DBMS_FLASHBACK EXEUCTE 권한 부여
SQL>GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
▒▒▒▒▒▒ Flashback 사용을 위한 환경설정 끝 ▒▒▒▒▒▒
-- 테스트를 위해서 scott 유저에 접속을 합니다.
SQL>CONN scott/tiger
-- emp 테이블 14건의 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다...
-- 데이터 삭제하기전의 날짜를 확인 합니다.
-- Flashback을 이용하여 이 시점에서 데이터를 조회, 복구 할 것입니다.
SQL>
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,’YY
-------------------
2006-01-21 17:16:51
10G 에서 SYSTIMESTAMP
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
18-DEC-08 03.27.03.993567 PM +09:00
-- 데이터를 삭제 합니다.
SQL>DELETE FROM emp;
14 행이 삭제되었습니다.
-- commit 수행
SQL>COMMIT;
-- 데이터 확인
SQL>SELECT * FROM emp;
선택된 레코드가 없습니다.
-- Flashback 과거시점의 시간사용 모드 Enable로 데이터 확인
SQL>EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP(’2006-01-21 17:16:51’, ’YYYY-MM-DD HH24:MI:SS’))
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 데이터를 확인 할 수 있습니다.
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
-- Flashback Disable로 변경
SQL> EXEC DBMS_FLASHBACK.DISABLE;
PL/SQL 처리가 정상적으로 완료되었습니다.
-- Flashback Disable로 변경하면 데이터를 확인 할 수 없습니다.
SQL>SELECT * FROM emp;
선택된 레코드가 없습니다.
Flashback Disable 상태에서도 이전데이터를 보기위해서는 데이터 복구 작업을 진행해야 합니다.
아래는 삭제된 데이터 복구 예제 입니다.
◈ 삭제된 데이터 복구
Oracle9i Database Release 2이상 버전에서는 SELECT...AS OF 명령을 사용하여 쉽게 Flashback 데이터를 복구 할 수 있습니다.
Oracle9i Database Release1 버전에서는 DBMS_FLASHBACK프로시저를 이용해서 데이터를 복구해야 합니.
-- 삭제된 데이터 복구(오라클 버전 Release 9.2.0.1.0 실행)
SQL>INSERT INTO TEVENTLOG
(SELECT * FROM TEVENTLOG AS OF TIMESTAMP TO_TIMESTAMP('2008-12-17 15:00:00','YYYY-MM-DD HH24:MI:SS'))
-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
-- 위에 복구된 데이타를 Rollback으로 지우고 DBMS_FLASHBACK 패키지를 이용해서 복구해 봅니다.
-- 삭제된 데이터 복구
SQL>DECLARE
CURSOR emp_cursor is
SELECT * FROM emp;
v_emp emp%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2006-01-21 17:16:51', 'YYYY-MM-DD HH24:MI:SS'));
OPEN emp_cursor;
-- Flashback을 Disable했지만 커서(test_cursor)는 여전히 과거시점의 데이터를 가지고 있습니다.
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%NOTFOUND;
INSERT INTO emp VALUES (v_emp.empno, v_emp.ename,
v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm,
v_emp.deptno);
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 복구된 데이터 확인
SQL>SELECT * FROM emp;
14 개의 행이 선택되었습니다.
추가 풀래시백 테이블
SQL> select * from tab;
뭐 다른 것도 있겠지만 저것도 하나에 들어가겠죠?
오늘 여지없이 저걸 쳤더니 'BIN$N3+PSg4FkXfgQAB/AQASdw==$0' 이런 이름의 테이블이 보이더군요.
desc, select 다 됩니다. 가만 보니 제가 지웠던 테이블이네요.
깔끔떠는 성격에 가만 놔둘리없죠.
SQL> drop table BIN$N3+PSg4FkXfgQAB/AQASdw==$0;
ORA-00933: SQL command not properly ended |
뭐 이런 식으로 뜨더군요.
뭐야 이건...검색결과...
"Oracle recycle bin flashback table feature"라고 하더군요.
drop 열심히 날려도 휴지통(recycle bin) 기능이 생겨서 지워지지 않고
"BIN$~!#^$#*(()$" 이런 식으로 남아있는 겁니다.
자 저걸 지우고 싶다면...
| SQL> PURGE RECYCLEBIN; |
윈도우의 휴지통 비우기 정도 되겠습니다.
그럼 윈도우처럼 휴지통 보내지 말고 그냥 지우긴...
| SQL> DROP TABLE employee PURGE; |
employee 테이블을 지운다고 가정했습니다.
휴지통에 보냈다면 다시 살릴 수도 있겠죠?
| SQL> FLASHBACK TABLE employee TO BEFORE DROP; |
딱 3줄이면 요약되는거 장황하게 늘어놔봤습니다.
그럼...또다시 새로운 게 보이면 적도록 하겠습니다.
더 자세한 걸 원하신다면
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html
|
|
|
'ORACLE > ADMIN' 카테고리의 다른 글
| ALTER ～ SHRINK SPACE (0) | 2009.02.05 |
|---|---|
| oracle tablespace 사용량 확인 sql (0) | 2008.12.30 |
| DICTIONARY(DICT) 뷰 (0) | 2008.12.03 |
| oracle유저 이외의 유저가 sqlplus등을 사용하는 방법 (0) | 2008.11.27 |
| DBNAME, SID 변경하기. (0) | 2008.11.26 |
