Row Level Flashback
사전 환경 구성
SQL> create tablespace test
2 datafile '/ORA19/app/oracle/oradata/ORACLE19/test01.dbf' size 10M;
SQL> create user scott
2 identified by tiger
3 default tablespace test
4 quota unlimited on test
5 quota 0m on system;
SQL> grant resource, connect to scott;
SQL> conn scott/tiger;
SQL> create table member
2 (name varchar2(10),
3 addr varchar2(10),
4 tel varchar2(20))
5 tablespace test;
SQL> insert into member values ('kim','sangam',111);
SQL> insert into member values ('park','gasan',222);
SQL> insert into member values ('jung','sinchon',333);
SQL> commit;
SQL> select * from member;
SQL> update member
2 set name='lee'
3 where name='kim';
SQL> commit;
SQL> select * from member;
SQL> update member
2 set name='choa'
3 where name='park';
SQL> commit;
SQL> select * from member;
Flashback Version Query
-- Flashback Version Query
SQL> select versions_startscn st_scn, versions_endscn endscn, versions_xid txid, versions_operation opt, name
2 from member versions between scn minvalue and maxvalue
3 where tel=111;
SQL> select scn_to_timestamp(<SCN>) from dual;
Flashback Transaction Query
SQL> conn / as sysdba;
SQL> grant select any transaction to scott;
SQL> conn scott/tiger;
-- Flashback Transaction Query
SQL> select undo_sql from flashback_transcation_query
2 where table_name='member'
3 and commit_scn between <st_scn> and <end_scn>
4 order by start_timestamp desc;
UNDO_SQL
--------------------------------------------------------------------------------
Row Level Flashback 진행
Row Level Flashback을 진행할 때는 Flashback Transaction Query로 뽑아낸 쿼리를 써도 되고, Flashback Version Query로 확인한 이전 데이터와 현재 데이터를 보고 직접 update를 수행할 수도 있습니다.
SQL> select * from member;
SQL> <Flashback Transaction Query로 뽑아낸 update 쿼리>
SQL> select * from member;
Table Level Flashback
SCN으로 DML 에러 복구하기 - undo data 사용
사전 환경 구성
SQL> conn scott/tiger;
SQL> create table ibgo
2 (i_code varchar2(10),
3 i_name varchar2(10),
4 qty varchar2(10))
5 tablespace test;
SQL> insert into ibgo values (100,'shrimp',100);
SQL> insert into ibgo values (101,'octopus',50);
SQL> insert into ibgo values (102,'squid',20);
SQL> insert into ibgo values (103,'fish',50);
SQL> commit;
SQL> select * from ibgo;
SQL> conn / as sysdba;
SQL> grant dba to scott;
SQL> conn scott/tiger;
SQL> select current_scn from v$database;
SQL> update ibgo
2 set i_name='big_shrimp'
3 where i_name='shrimp';
SQL> commit;
SQL> select * from ibgo;
SQL> select current_scn from v$database;
SQL> update ibgo
2 set i_name='big_octopus'
3 where i_name='octopus';
SQL> commit;
SQL> select * from ibgo;
SQL> select current_scn from v$database;
SQL> delete from ibgo;
SQL> commit;
SQL> select * from ibgo;
SQL> select current_scn from v$database;
Table Level Flashback 진행
SQL> flashback table ibgo to scn '<복구를 원하는 시점의 scn>';
SQL> select * from ibgo;
시간으로 DML 에러 복구하기 - undo data 사용
사전 환경 구성
SQL> conn scott/tiger;
SQL> create table ibgo
2 (i_code varchar2(10),
3 i_name varchar2(10),
4 qty varchar2(10))
5 tablespace test;
SQL> insert into ibgo values (100,'shrimp',100);
SQL> insert into ibgo values (101,'octopus',50);
SQL> insert into ibgo values (102,'squid',20);
SQL> insert into ibgo values (103,'fish',50);
SQL> commit;
SQL> select * from ibgo;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> update ibgo
2 set i_name='big_shrimp'
3 where i_name='shrimp';
SQL> commit;
SQL> select * from ibgo;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> update ibgo
2 set i_name='big_octopus'
3 where i_name='octopus';
SQL> commit;
SQL> select * from ibgo;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
SQL> delete from ibgo;
SQL> commit;
SQL> select * from ibgo;
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
Table Level Flashback 진행
SQL> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
SQL> select * from ibgo;
SQL> flashback table ibgo to timestamp (systimestamp - interval '4' minute);
SQL> select * from ibgo;
-- 항상 현재 시점에서 몇 분 전으로 되돌리는 것이므로 같은 5분 전을 수행해도 다른 결과가 나타날 수 있습니다.
SQL> flashback table ibgo to timestamp (systimestamp - interval '5' minute);
SQL> select * from ibgo;
너무 오래 전으로 되돌리면?
-- 에러 발생 SQL> flashback table ibgo to timestamp (systimestamp - interval '70' minute);
- 이를 보아
undo data를 사용한다는 것을 알 수 있습니다. undo parameter 중 undo_retention 값을 초과하면 더이상 undo data를 사용할 수 없습니다.
Table Level Flashback의 문제점
:alter명령어로 테이블 구조가 변경되면 변경 이전의 데이터는 복구할 수 없습니다.
: 다른 사용자가 undo segment를 덮어쓴다면 역시undo data를 사용할 수 없기 때문에 복구할 수 없습니다.
Drop table 복구하기 - 휴지통(recyclebin) 기술 활용
반드시 해당 테이블을 소유한 스키마로 접속해서 show recyclebin을 수행해야 휴지통이 정상적으로 보입니다.
SQL> conn scott/tiger;
-- 휴지통엔 아무것도 없습니다.
SQL> show recyclebin;
SQL> drop table ibgo;
SQL> select * from ibgo;
-- drop된 ibgo 테이블이 있습니다.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
IBGO BIN$8bmjwpKMGPrgVQIMKUMlTg==$0 TABLE 2023-01-08:12:56:47
SQL> flashback table ibgo to before drop;
SQL> select * from ibgo;
휴지통(recyclebin) 관리 방법
-- 특정 테이블 휴지통에서 지우기
SQL> purge table <TABLE_NAME>;
-- 휴지통 전체 비우기
SQL> purge recyclebin;
휴지통에 넣지 않고 영구 삭제
SQL> drop table <TABLE_NAME> purge;
index가 있는 테이블 drop 후 복구하기
SQL> conn scott/tiger;
SQL> create table ibgo
2 (i_code varchar2(10),
3 i_name varchar2(10),
4 qty varchar2(10))
5 tablespace test;
SQL> insert into ibgo values (100,'shrimp',100);
SQL> insert into ibgo values (101,'octopus',50);
SQL> insert into ibgo values (102,'squid',20);
SQL> insert into ibgo values (103,'fish',50);
SQL> commit;
SQL> select * from ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 shrimp 100
101 octopus 50
102 squid 20
103 fish 50
SQL> create index idx_ibgo_name on ibgo(i_name);
SQL> set line 200
SQL> col table_name for a10
SQL> col column_name for a10
SQL> col index_name for a30
SQL> select table_name, column_name, index_name
2 from user_ind_columns
3 where table_name='IBGO';
TABLE_NAME COLUMN_NAM INDEX_NAME
---------- ---------- ------------------------------
IBGO I_NAME IDX_IBGO_NAME
SQL> save index.sql
Created file index.sql
SQL> drop table ibgo;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
IBGO BIN$8bnvOMwGHUPgVQIMKUMlTg==$0 TABLE 2023-01-08:13:17:53
SQL> @index
no rows selected
SQL> flashback table ibgo to before drop;
Flashback complete.
-- 복구를 했지만 index 이름은 recyclebin의 이름으로 되어있습니다.
SQL> @index
TABLE_NAME COLUMN_NAM INDEX_NAME
---------- ---------- ------------------------------
IBGO I_NAME BIN$8bnvOMwFHUPgVQIMKUMlTg==$0
SQL> alter index "BIN$8bnvOMwFHUPgVQIMKUMlTg==$0" rename
2 to idx_ibgo_name;
Index altered.
SQL> @index
TABLE_NAME COLUMN_NAM INDEX_NAME
---------- ---------- ------------------------------
IBGO I_NAME IDX_IBGO_NAME
같은 이름일 경우 복구 우선 순위
같은 이름의 테이블이 여러 개 recyclebin에 존재할 경우, 나중에 지워진 테이블이 먼저 복구됩니다.
SQL> conn scott/tiger;
SQL> create table test1
2 (no1 number)
3 tablespace test;
SQL> insert into test1 values (1);
SQL> commit;
SQL> select * from test1;
NO1
----------
1
SQL> drop table test1;
SQL> create table test1
2 (no2 number)
3 tablespace test;
SQL> insert into test1 values (2);
SQL> commit;
SQL> select * from test1;
NO2
----------
2
SQL> drop table test1;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1 BIN$8bnvOMwKHUPgVQIMKUMlTg==$0 TABLE 2023-01-08:13:43:46
TEST1 BIN$8bnvOMwJHUPgVQIMKUMlTg==$0 TABLE 2023-01-08:13:43:16
SQL> flashback table test1 to before drop;
SQL> select * from test1;
NO2
----------
2
이미 같은 이름의 테이블이 존재하는 상황에서 테이블을 복구하려 한다면 에러가 발생합니다. 이때는 이름을 바꿔서 복구하면 됩니다.
SQL> show recyclebin;
SQL> flashback table test1 to before drop;
flashback table test1 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> flashback table test1 to before drop rename to test3;
SQL> select * from test3;
NO1
----------
1
Database Level Flashback
전통적인 복구 방식은 redo log, archive log를 이용해 DB를 복구하지만 flashback database의 경우 flashback log를 이용해 복구합니다. 백업 파일을 복원하는 과정을 생략하기에 훨씬 빨리 복구할 수 있습니다.
Flashback Database의 경우 특별한 설정이 있어야만 사용가능합니다. 파라미터 파일에 db_recovery_file_dest와 db_recovery_file_dest_size, db_flashback_retention_target가 설정되어 있어야 합니다.
이후 파라미터 파일 설정 적용을 위해 shutdown 후 startup mount를 해준 뒤,
alter database flashback on을 통해 flashback 기능을 켜줍니다.
$ vi $ORACLE_HOME/dbs/init<SID>.ora
...
*.db_recovery_file_dest='/ORA19/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=7851m
*.db_flashback_retention_target=30
:wq!
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
-- flashback log 경로 확인
$ cd <db_recovery_file_dest 경로>/<SID>/flashback
$ ls -alrt
truncate table 장애 복구하기
SQL> conn scott/tiger;
SQL> create table ibgo
2 (i_code varchar2(10),
3 i_name varchar2(10),
4 qty varchar2(10))
5 tablespace test;
SQL> insert into ibgo values (100,'shrimp',100);
SQL> insert into ibgo values (101,'octopus',50);
SQL> insert into ibgo values (102,'squid',20);
SQL> insert into ibgo values (103,'fish',50);
SQL> commit;
SQL> select * from ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 shrimp 100
101 octopus 50
102 squid 20
103 fish 50
SQL> truncate table ibgo;
SQL> select * from ibgo;
SQL> conn /as sysdba;
-- 마운트 단계에서 flashback database를 수행해야 합니다.
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to timestamp (systimestamp - interval '5' minute);
-- 불완전복구처럼 과거 특정 시점으로 복구하는 것이기 때문에 resetlogs로 open합니다.
SQL> alter database open resetlogs;
-- 결과가 없다면 복구를 더 이전 시점으로 돌려야 하고, 다시 mount 단계로 가서 수행해야 합니다.
SQL> select * from scott.ibgo;
no rows selected.
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to timestamp (systimestamp - interval '10' minute);
SQL> alter database open resetlogs;
SQL> select * from scott.ibgo;
I_CODE I_NAME QTY
---------- ---------- ----------
100 shrimp 100
101 octopus 50
102 squid 20
103 fish 50
참고
- <실전 오라클 백업과 복구> - 서진수
Devops Engineer