설명
- 사용자의 논리적 장애를 복구하는데 효과적 (물리적인 장애는 사용 불가)
flashback 수준
- Row/Table/Database Level Flashback
flashback 방법 및 제약사항
- Undo data를 기반으로 복구
- Commit된 데이터만 가능,Undo segment재사용시 flashback불가
- Tablespace를 기반으로 복구
- Tablespace내 삭제된 스키마 영역을 새로운 데이터 인입으로 rewrite될 경우 flashback불가
복구 수준별 예시
Row Level
스크립트
- 변경이력 조회 (Flashback Version Query)
select versions_startscn st_scn,versions_endscn endscn, scn_to_timestamp(versions_startscn) Time,
versions_xid txid, versions_operation opt, col1
from dba_jyoh.test1 versions between scn minvalue and maxvalue where col3 =111;
- 복구쿼리 추출 (Flashback Transaction Query)
select undo_sql from flashback_transaction_query
where table_name = 'TEST1'
and commit_scn between 585830 and 585946
order by start_timestamp desc;
예시
-
더보기SQL> insert into dba_jyoh.test1 values ('aa','aa','111');SQL> insert into dba_jyoh.test1 values ('aa2','aa2','222');SQL> insert into dba_jyoh.test1 values ('aa3','aa3','333');SQL> update dba_jyoh.test1 set col1 ='bbb' where col1='aa';SQL> update dba_jyoh.test1 set col1 ='bbb2' where col1='aa2';SQL> --col3=111 인 row의 변경이력 (SCN) 조회SQL> select versions_startscn st_scn,versions_endscn endscn, scn_to_timestamp(versions_startscn) Time,versions_xid txid, versions_operation opt, col1from dba_jyoh.test1 versions between scn minvalue and maxvalue where col3 =111;ST_SCN ENDSCN TIME TXID OPT COL1-------- -------- ---------- ---------- ---------- ----------585946 13-MAR-22 06.47.57.0 000000 00000000 PM 1400080018 U bbb585830 585946 13-MAR-22 06.43.02.0 000000 00000000 PM 0C00130017 I aaSQL> --rollback 필요한 쿼리를 추출SQL> select undo_sql from flashback_transaction_querywhere table_name = 'TEST1'and commit_scn between 585830 and 585946order by start_timestamp desc;UNDO_SQL---------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------update "DBA_JYOH"."TEST1" set "COL1" = 'aa2' where ROWID = 'AAAMM6AAEAAAAAXAAB';update "DBA_JYOH"."TEST1" set "COL1" = 'aa' where ROWID = 'AAAMM6AAEAAAAAXAAA';delete from "DBA_JYOH"."TEST1" where ROWID = 'AAAMM6AAEAAAAAXAAB';delete from "DBA_JYOH"."TEST1" where ROWID = 'AAAMM6AAEAAAAAXAAC';delete from "DBA_JYOH"."TEST1" where ROWID = 'AAAMM6AAEAAAAAXAAA';
Table Level
스크립트
- DML 수준 (특정 시점으로 테이블 상태를 복구)
- SCN기반 복구
- alter table dba_jyoh.test1 enable row movement;
flashback table dba_jyoh.test1 to scn '585830';- Flashback Version Query를 이용하여 SCN정보 확인
- alter table dba_jyoh.test1 enable row movement;
- 시간 기반 복구 (alter table 발생시 이전으로 복구는 불가)
- flashback table dba_jyoh.test1 to timestmp (ststimestamp -interval '5' minute);
- SCN기반 복구
- DDL 수준
- 테이블 Drop복구
- flashback table student to before drop;
flashback table student to before drop rename to recv_student;
- flashback table student to before drop;
- IDX Drop복구
- alter index "BIN$2i5Ch1O4HFDgUAB/AQDOoQ==$0" rename to idx_recov;
- 테이블 Drop복구
예시 - DDL 수준
-
더보기-- 테이블 Drop복구SQL> drop table student;Table dropped.SQL> 삭제된 오브젝트 확인SQL> show recyclebinORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------STUDENT BIN$2i5Ch1O1HFDgUAB/AQDOoQ==$0 TABLE 2022-03-14:22:00:08SQL> flashback table student to before drop;Flashback complete.SQL> select * from student ;NO----------12-- IDX Drop복구SQL> set line 200SQL> col table_name for a10SQL> col column_name for a10SQL> col index_name for a30SQL> select table_name,column_name,index_namefrom user_ind_columnswhere table_name = 'STUDENT';TABLE_NAME COLUMN_NAM INDEX_NAME---------- ---------- ------------------------------STUDENT NO IDX_TESTSQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------...중략...STUDENT TABLESQL> drop table STUDENT;Table dropped.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------...중략...BIN$2i5Ch1O5HFDgUAB/AQDOoQ==$0 TABLESQL> flashback table student to before drop;Flashback complete.SQL> select table_name,column_name,index_namefrom user_ind_columnswhere table_name = 'STUDENT';TABLE_NAME COLUMN_NAM INDEX_NAME---------- ---------- ------------------------------STUDENT NO BIN$2i5Ch1O4HFDgUAB/AQDOoQ==$0SQL> alter index "BIN$2i5Ch1O4HFDgUAB/AQDOoQ==$0" rename to idx_recov;Index altered.SQL> select table_name,column_name,index_namefrom user_ind_columnswhere table_name = 'STUDENT';TABLE_NAME COLUMN_NAM INDEX_NAME---------- ---------- ---
Database Level
설명
- 기존 복구방식 (백업된 파일을 복원+리두/아카이브 로그 적용 복구) 과 달리 flashback log를 적용하여 복구
- 불완전 복구 방식처럼 OPEN시 SCN정보 불일치 문제 해소를 위한 resetlogs 필요
- 조건 : flashback log 설정 + 아카이브로그 모드+ flashback database mode 설정
- 사용케이스 : truncate, drop user 등..
스크립트
- parameterfile설정
- db_recovery_file_dest= /app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
db_flashback_retention_target=30
- db_recovery_file_dest= /app/oracle/flash_recovery_area
- DB설정 (mount상태)
- alter database archivelog;
alter database flashback on;
alter database open;
- alter database archivelog;
- flashback 설정 체크
- select flashback_on from v$database;
- flashback database 수행
- startup mount;
flashback database to timestamp (systimestamp - interval '5' minute);
alter database open resetlogs;
- startup mount;
'Oracle > 백업과 복구' 카테고리의 다른 글
[물리적 백업/복구] 복구- control file (0) | 2022.03.23 |
---|---|
[논리적 백업/복구] IMPORT/EXPORT & DATAPUMP (0) | 2022.03.23 |
[물리적 백업/복구] 복구 redo log file (0) | 2022.03.23 |
[물리적 백업/복구] 복구-data file (0) | 2022.03.23 |
[물리적 백업/복구] 백업 (0) | 2022.03.23 |