고정 헤더 영역
상세 컨텐츠
본문
★ 플래시백 쿼리 flashback query
- 불완전한 복구 (타임 베이스 리커버리) 를 하지 않기 위해서 등장
- 특정 시간을 기준으로 쿼리를 수행할 수 있다.
- select 문의 as of 라는 절을 이용해서 데이터를 확인할 시간 기록을 지정할 수 있다.
- 데이터 불일치 분석에 유용하다.
현재 SCN 번호와 시간 확인
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
---------------------------------------------------------------------------
2285745 30-JAN-24 06.59.02.444147 PM +09:00
테이블 생성하기
SYS@ora11g> create table hr.emp_31 as select * from hr.employees where department_id = 30;
Table created.
테이블에 업데이트 작업하기 (사원 급여 수정)
SYS@ora11g> update hr.emp_31 set salary = 30000 where employee_id = 114;
1 row updated.
커밋
SYS@ora11g> commit;
Commit complete.
현재 SCN 번호와 시간 확인
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
---------------------------------------------------------------------------
2285956 30-JAN-24 07.03.22.821016 PM +09:00
SYS@ora11g> select salary from hr.emp_31 where employee_id=114;
SALARY
-------------------
30000
아무튼 114번 사원의 월급을 조회해봤는데 30000 인 걸 보고
그제서야 저 사원의 월급을 잘못 수정했음을 깨달았다고 하자. 이럴 때는
SYS@ora11g>
select salary from hr.emp_31 as of timestamp to_timestamp('2024-01-30 19:01:00', 'yyyy-mm-dd hh24:mi:ss') where employee_id = 114;
SALARY
-----------------
11000
커밋 이전의 시간대를 지정해서
그 시간의 값을 볼 수 있다:
이게 바로 특정한 시간대를 지정해서 데이터를 검색하는 flashback query
(다만 이 값은 undo_retention 이 보장하는 시간 동안만 볼 수 있고 그 이후에는 볼 수 없다)
SYS@ora11g> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900 --- 900 : 900초 동안 이전 값을 볼 수 있다는 뜻
★ 플래시백 버전 쿼리 flashback version query
- version 절을 사용해서 point-in-time 또는 두 SCN 사이에 존재하는 행의 모든 버전을 검색할 수 있다.
(as of time 은 딱 지정 시간만 검색하지만, version 절은 두 개의 시점 혹은 두 SCN 사이를 검색 가능 = 시간대 검색)
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
---------------------------------------------------------------------------
2287236 30-JAN-24 07.26.10.850065 PM +09:00
115번 사원의 월급을 1.1배로 (잘못) 올려주고
SYS@ora11g> update hr.emp_31 set salary = salary * 1.1 where employee_id = 115;
116번 사원의 정보를 테이블에서 아예 (잘못) 지워버린 다음
SYS@ora11g> delete from hr.emp_31 where employee_id = 116;
커밋
SYS@ora11g> commit;
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------------------------------------------------------------------------------
2287444 30-JAN-24 07.31.16.309171 PM +09:00
그런데 뒤늦게서야 DML 조작을 잘못했다는 것을 깨달았다.
그래서 원래대로 되돌리기 위해 115번 사원의 올리기 전 월급과
116번 사원의 지우기 전 월급 정보가 필요해졌다고 하자.
아래는 플래시백 쿼리, 그냥 특정 시점의 데이터만 보여준다.
(QUESTION. 플래시백 쿼리는 정확히 딱 지정한 시점의 데이터를 보여주는 거야,
아니면 그 시점보다 1초라도 이전의 데이터를 보여주는 거야?
근데 내가 묻고도 별로 안중요한 질문 같긴 하네)
(ANSWER. 지정한 시점의 데이터를 보여준다.)
SYS@ora11g> select employee_id, last_name, salary from hr.emp_31 as of timestamp to_timestamp('2024-01-30 19:26:00', 'yyyy-mm-dd hh24:mi:ss');
EMPLOYEE_ID LAST_NAME SALARY
------------------------------------------------------------------
114 Raphaely 11000
115 Khoo 3100
116 Baida 2900
117 Tobias 2800
118 Himuro 2600
119 Colmenares 2500
아무튼 이렇게 플래시백 쿼리로 검색할 수도 있고
SYS@ora11g>
select versions_xid, employee_id, last_name, salary from hr.emp_31 versions between scn minvalue and maxvalue;
VERSIONS_XID EMPLOYEE_ID LAST_NAME SALARY
---------------- ----------- ------------------------- ------------------------------------------------
0A0009002C060000 116 Baida 2900
0A0009002C060000 115 Khoo 3410
0A0009002C060000 114 Raphaely 30000
114 Raphaely 11000
115 Khoo 3100
116 Baida 2900
117 Tobias 2800
118 Himuro 2600
119 Colmenares 2500
SCN 번호를 가지고도 검색할 수 있다.
(xid 는 트랜잭션 번호,
between scn minvalue and maxvalue 는 직접 SCN 번호를 찾아서 검색해도 되지만
그러기보다는 가장 낮은 SCN 번호와 가장 높은 SCN 번호 사이의 데이터를 알아서 좀 검색해달라는 뜻)
(QUESTION. 가장 낮은 SCN 번호와 가장 높은 SCN 번호는 각각 뭘 말하는 거야...?
가장 낮은 SCN 번호는 hr.emp_31이 생긴 시점의 SCN 번호이고 가장 높은 SCN 번호는 commit 시점의 SCN 번호인가?
그런데 그럼 commit 이 여러 번 일어났을 경우에는 어떤 게 필요한 데이터인지 어떻게 알지?
일단 보관하고 있는 undo_retention 값을 기준으로 결정하나?
undo 가 생긴 시점의 SCN 번호가 가장 낮은 SCN 번호일까? 그럼 가장 높은 번호는 뭐지? 가장 최근 시점의 SCN 번호?)
(ANSWER. 언두에 남아있는 min, max 기준)
또는 시간대를 기준으로 찾아볼 수도 있다.
아까 아무 일도 안일어났을 때 시간이 오후 7시 26분 10초였고
115번 사원의 월급을 잘못 올리고 116번 사원의 데이터를 잘못 지운 이후에
커밋까지 했을 때의 시간이 7시 31분 16초였으니까
그 사이의 시간대만 검색하면
select versions_xid, employee_id, last_name, salary from hr.emp_31 versions between scn minvalue and maxvalue;
SYS@ora11g> select versions_xid, employee_id, last_name, salary from hr.emp_31 versions between timestamp to_timestamp('2024-01-30 19:26:26', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2024-01-30 19:31:00', 'yyyy-mm-dd hh24:mi:ss');
(QUESTION. 헐 나 이거 왜 오류나? 아까 수업 중에는 멀쩡히 잘만 돌아갔는데!!!!!!!!
근데 GPT 한테 물어봤더니 지도 모른대... 대체 뭐가 문제인 거야!!!!!!!!!!)
(ANSWER. 시간을 잘못 입력했을 수 있다. 언두에서 이미 데이터가 사라진 후이면 데이터 검색이 안됨)
SYS@ora11g> select versions_xid, employee_id, employee_id, last_name, salary from hr.emp_30 versions between timestamp to_timestamp('2024-01-30 16:28:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2024-01-30 16:29:00', 'yyyy-mm-dd hh24:mi:ss');
혹시 몰라서 수업시간에 썼던 쿼리문도 일단은 보관해놓음.
VERSIONS_XID EMPLOYEE_ID EMPLOYEE_ID LAST_NAME SALARY
---------------- ----------- ----------- ------------------------- ----------
114 114 Raphaely 30000
115 115 Khoo 3410
117 117 Tobias 2800
118 118 Himuro 2600
119 119 Colmenares 2500
이제 이걸 보면서
만약 삭제를 잘못했다면 다시 인서트하는 등의 작업을 하면 되는데
한꺼번에 (예를 들어 100만 건) 수정, 삭제, 삽입을 해버린 경우: 되돌리기가 너무 힘들다.
그래서 그냥 테이블을 이전 시점으로 되돌리고 싶을 수가 있는데 그럴 때는
★ 플래시백 테이블 flashback table
- 특정 시간대로 테이블 되돌리기 (일종의 타임 베이스 리커버리)
- 백업으로 복원하지 않고, 테이블을 특정 시점으로 리커버리하는 것
- 데이터베이스는 온라인 상태를 유지한다. (DB 가 올라와 있는 상태 중에 테이블 리커버리가 가능)
- flashback table 작업을 수행하기 위해 언두 테이블스페이스에서 데이터를 사용한다.
- 단, flashback table 을 하려면 권한이 필요하다.
- grant flashback on hr.emp_30 to scott;
scott 이라는 유저에게 hr.emp_30 테이블을 플래시백할 수 있는 객체 권한 부여
- grant flashback any table to hr;
hr 에게 어떤 테이블이라도 플래시백할 수 있는 시스템 권한 부여
위에서 scott 은 hr.emp_30 이라는 테이블에 대해서만 플래시백할 수 있었지만
이렇게 시스템 권한을 부여하면 - 모든 테이블에 대해 플래시백할 수 있다.
- 플래시백 테이블에 대한 행(row) 이동이 활성화되어 있어야 한다.
SYS@ora11g> select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
---------------------------------------------------------------------------
2290019 30-JAN-24 08.27.38.176379 PM +09:00
현재 SCN 번호와 시간 확인
SYS@ora11g> delete from hr.emp_30;
5 rows deleted.
(QUESTION. 이거 뭐야... 또 필기 똑바로 안해놨네... 퍼티에서도 못찾겠어... ㅠㅠ)
(ANSWER. 필기 맞게 함.)
테이블 지우기 : 이전 값은 undo 에 들어가 있게 된다. (undo_retention 까지 보장)
SYS@ora11g> commit;
Commit complete.
커밋
SYS@ora11g> select * from hr.emp_30;
no rows selected
테이블을 플래시백하려면 행 이동이 활성화되어 있어야 하므로
활성화 작업 먼저 하기
SYS@ora11g> alter table hr.emp_30 enable row movement;
Table altered.
SYS@ora11g> flashback table hr.emp_30 to timestamp to_timestamp('2024-01-30 16:46:00', 'yyyy-mm-dd hh24:mi:ss');
Flashback complete.
맨 끝 괄호에는 row 들이 이동해야 할 시간 (아까 timestamp 로 찍어놓은 부분) 써주기
SYS@ora11g> select salary from hr.emp_30;
SALARY
----------
30000
3410
2800
2600
2500
이제 테이블을 조회하면: 데이터가 나온다.
(다만 언두 리텐션이 살아 있는 동안만 - 언두 리텐션이 지났다면 로그 마이너로 분석 작업을 하는 수밖에는 없다)
이제 행 이동을 할 필요가 없으므로 다시 행 이동 비활성화하기
SYS@ora11g> alter table hr.emp_30 disable row movement;
Table altered.
