[Oracle] FLASHBACK

HYEOB KIM·2022년 11월 21일
oracle
2

Oracle

목록 보기
18/58

이전 포스트 : [Oracle] Network와 Oracle Net Service

FLASHBACK

oracle에서 발생하는 장애는 크게 물리적/논리적인 장애로 분류할 수 있습니다.
물리적인 장애는 디스크 장애, 서버 장애 등으로 인한 파일 손상을 의미합니다. 파일이 삭제된다든지 파일을 덮어 쓴다든지 하는 장애입니다.
논리적인 장애는 사용자의 실수나 오류로 발생한 장애를 의미합니다. 예를 들어, drop table을 잘못했다든지, 잘못된 update를 수행 후 commit한 경우 등을 의미합니다. oracle 입장에서는 정상적인 명령어 수행으로 장애가 아니나 사람의 실수로 발생한 장애입니다.

FLASHBACK은 논리적인 장애를 아주 빠르게 복구해낼 수 있는 방법입니다.

1. Flashback의 종류

Flashback 명령어는 3가지 Level이 있습니다.

  • Row Level Flashback
  • Table Level Flashback
  • Database Level Flashback

Row Level Flashback

특정 테이블의 특정 Row만 Flashback 해주는 기능입니다. 이 기능은 Undo Data의 정보를 이용해서 복구합니다.
flashback 기능은 commit된 데이터만 복구가 가능합니다.
사용자가 Row Level Flashback을 요청하면 undo segment를 찾아서 해당 row를 flashback하게 됩니다.

실습을 통해 Row Level Flashback 기능을 확인해봅시다.

아래와 같은 테이블을 생성합니다.

nameaddresstel
ParkIncheon111
KimGasan222
LeeEarth333

과정

  • Park -> Hong 변경 후 commit
  • Kim -> Lee 변경 후 commit
  • Hong의 변경 전의 값을 찾아서 원래 값으로 변경
SQL> conn khyup/khyup
Connected.

SQL> create table member
(name varchar2(10),
address varchar2(10),
tel varchar2(15));

SQL> insert into member values('Park','Incheon',111);

1 row created.

SQL> insert into member values('Kim','Gasan',222);

1 row created.

SQL> insert into member values('Lee','Earth',333);

1 row created.

SQL> commit;

SQL> select * from member;

NAME       ADDRESS    TEL
---------- ---------- ---------------
Park       Incheon    111
Kim        Gasan      222
Lee        Earth      333

SQL> update member
  2  set name='Hong'
  3  where name='Park';

1 row updated.

SQL> commit;

Commit complete.

SQL> update member
  2  set name='Lee'
  3  where name='Kim';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from member;

NAME       ADDRESS    TEL
---------- ---------- ---------------
Hong       Incheon    111
Lee        Gasan      222
Lee        Earth      333

만약 Hong의 이전 데이터가 Park인지 모른 상태에서 고객이 Hong을 단순히 이전 데이터로 돌려달라고 한다면 난감하겠죠. 이때 해당 데이터의 과거 변경 이력을 전부 찾아주는 쿼리가 바로 Flashback Version Query입니다.

SQL> select versions_startscn st_scn, versions_endscn endscn,
versions_xid txid, versions_operation opt, name
from member versions between scn minvalue and maxvalue
where tel=111;

    ST_SCN     ENDSCN TXID             O NAME
---------- ---------- ---------------- - ----------
    578798            08001100A0010000 U Hong
    578779     578798 07001A00AF010000 I Park

만약 여러 사용자가 해당 데이터에 대해 변경하면 여러 개의 변경 이력이 남겠죠. 이때는 변경 시간을 조회하면 됩니다.
특정 SCN에 대해 변경 시간을 알 수 있는 함수는 scn_to_timestamp(<endscn>)입니다.

SQL> select scn_to_timestamp(<endscn>) from dual;

SCN_TO_TIMESTAMP(578798)
---------------------------------------------------------------------------
25-NOV-22 02.57.36.000000000 PM

변경 사항을 취소시켜 이전 값으로 돌려주는 쿼리를 찾아주는 쿼리가 바로 Flashback Transaction Query입니다. Transaction Query으로 데이터를 이전 값으로 다시 되돌리는 쿼리를 찾아서 되돌리면 됩니다. 그런데 Transaction Query는 아주 복잡해서 위와 같이 변경 내역을 찾은 후 직접 원래 데이터로 update해도 됩니다.

SQL> update member
  2  set name='Park'
  3  where name='Hong';

1 row updated.

SQL> select * from member;

NAME       ADDRESS    TEL
---------- ---------- ---------------
Park       Incheon    111
Lee        Gasan      222
Lee        Earth      333

Table Level Flashback

특정 테이블에 DML 에러가 발생했을 때 사용하는 방법과 특정 테이블이 drop table 되었을 때 사용하는 방법이 있습니다. 장애가 난 table 전체의 내용이 변경됩니다.

DML 에러 복구법

아래와 같은 테이블이 있습니다.

I_CODEI_NAMEQTY
100aa100
101ab50
102ac20
105ad50
SQL> create table ibgo
(I_CODE varchar2(15),
I_NAME varchar2(15),
QTY varchar2(15));

SQL> insert into ibgo values(100,'aa',100);

1 row created.

SQL> insert into ibgo values(101,'ab',50);

1 row created.

SQL> insert into ibgo values(102,'ac',20);

1 row created.

SQL> insert into ibgo values(105,'ad',50);

1 row created.

SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             ac              20
105             ad              50

SQL> update ibgo
  2  set I_NAME='bb'
  3  where I_CODE=102;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             bb              20
105             ad              50

SQL> update ibgo
  2  set I_NAME='bd'
  3  where I_CODE=105;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             bb              20
105             bd              50

SQL> delete from ibgo where I_CODE=105;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             bb              20

bd가 지워지기 전으로 복구해 보겠습니다.
(같은 명령어를 여러 번 수행하는 이유는 현재 시간(systimestamp)은 계속 흐르고 있기 때문입니다)

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;

SQL> flashback table ibgo to timestamp(systimestamp - interval '5' minute);

SQL> select * from ibgo;

SQL> flashback table ibgo to timestamp(systimestamp - interval '5' minute);

SQL> select * from ibgo;

ORA-08189 에러 발생 시 아래 명령을 통해 테이블 설정 변경

ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

SQL> alter table ibgo enable row movement;

Table altered.


`interval` 뒤의 분, 초는 모두 가능합니다.
초로 시간을 줄 경우 `second`를 이용하면 됩니다.

```sql
SQL> flashback table ibgo to timestamp (systimestamp - interval '30' second);

drop table 복구하기

oracle에서 drop table을 수행하게 되면 휴지통에서 보관됩니다.
flashback 명령어를 이용해 휴지통에서 삭제된 table을 복구할 수 있습니다.

-- 현재 ibgo 테이블이 있습니다.
SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             bb              20
105             bd              50

-- 실수로 삭제...
SQL> drop table ibgo;

Table dropped.

-- 삭제된 테이블은 조회되지 않습니다.
SQL> select * from ibgo;
select * from ibgo
              *
ERROR at line 1:
ORA-00942: table or view does not exist

-- 휴지통을 조회합니다.
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
IBGO             BIN$7kaKktzhQe/gUzKJqMDaZA==$0 TABLE        2022-11-25:15:22:08

-- flashback 명령어로 ibgo 테이블을 복구합니다.
SQL> flashback table ibgo to before drop;

Flashback complete.

-- 다시 살아났습니다.
SQL> select * from ibgo;

I_CODE          I_NAME          QTY
--------------- --------------- ---------------
100             aa              100
101             ab              50
102             bb              20
105             bd              50

recyclebin 관리하기

휴지통은 Tablespace마다 있습니다. 사용자가 table을 지우게 되면 table은 휴지통으로 옮겨집니다. 메모리로 점유하고 있던 공간은 다른 테이블이 사용할 수 있도록 할당 해제하지만 그 공간에 데이터는 그대로 보존하고 있습니다. 그러다가 만약 다른 테이블이 지워졌던 테이블의 공간을 사용할 상황이 오면 그때 공간을 할당해주면서 데이터를 지웁니다. 이렇게 되면 지워진 테이블은 더이상 복구가 불가능하게 됩니다.

purge 명령어를 이용하면 휴지통을 비울 수 있습니다.

-- 특정 테이블을 휴지통에서 비우기
SQL> purge table <table_name>;

-- 휴지통 전체 비우기
SQL> purge recyclebin;

-- 휴지통에 넣지 않고 테이블 바로 삭제
SQL> drop table <table_name> purge;

기본적으로 SYS 사용자의 table은 휴지통으로 들어가지 않고 바로 purge됩니다.
하지만 SYS 사용자가 다른 사용자 소유의 table을 삭제할 경우 해당 사용자의 user_recyclebin으로 테이블이 들어갑니다.

SQL> conn / as sysdba;
Connected.
SQL> create table khyup.tbl100 (no number);

Table created.

SQL> insert into khyup.tbl100 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table khyup.tbl100;

Table dropped.

SQL> select object_name, original_name, type, droptime
  2  from dba_recyclebin
  3  where original_name='TBL100';

OBJECT_NAME                    ORIGINAL_NAME   TYPE            DROPTIME
------------------------------ --------------- --------------- -------------------
BIN$7kbcIn+ITdDgUzKJqMCjFQ==$0 TBL100          TABLE           2022-11-25:15:44:56

SQL> conn khyup/khyup;
Connected.
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TBL100           BIN$7kbcIn+ITdDgUzKJqMCjFQ==$0 TABLE        2022-11-25:15:44:56

Database Level Flashback

전통적인 DB 복구 방식은 장애 발생 시 백업된 Data File을 복원해서 Redo Log와 Archive Log를 적용시켜 복구하는 반면, flashback database 명령어 방식은 장애 난 Data File에 Flashback Log를 바로 적용시켜 복구합니다.

Redo Log와 Archive Log를 적용시키는 방법은 백업 파일을 복원하는 과정이 있기 때문에 Flashback Log를 이용하는 방법보다 오래 걸립니다.

flashback database 명령어는 별도의 설정이 있어야만 사용 가능합니다.

환경 설정

초기화 파라미터 파일에 db_recovery_file_dest, db_recovery_file_dest_size, db_flashback_retention_target가 설정되어 있어야 합니다. db_flashback_retention_target는 새로 추가합니다.

$ cd $ORACLE_HOME/dbs

$ vi 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!

설정 값을 변경했으면 적용을 시켜야겠죠.
mount까지만 startup을 해준 뒤, archivelog, flashback를 모두 켜준 뒤 open합니다.

$ sqlplus / as sysdba

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1660940992 bytes
Fixed Size                  8897216 bytes
Variable Size             956301312 bytes
Database Buffers          687865856 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

이제 flashback log가 쌓이는 경로를 확인해보겠습니다.

$ cd $ORACLE_BASE/fast_recovery_area
[oracle@khyup fast_recovery_area]$ ls
ORACLE19
[oracle@khyup fast_recovery_area]$ cd ORACLE19
[oracle@khyup ORACLE19]$ ls
archivelog  autobackup  control02.ctl  flashback
[oracle@khyup ORACLE19]$ cd flashback
[oracle@khyup flashback]$ ls -al
total 409616
drwxr-x---. 2 oracle dba        60 Nov 25 16:21 .
drwxr-x---. 5 oracle dba        80 Nov 25 16:21 ..
-rw-r-----. 1 oracle dba 209723392 Nov 25 16:22 o1_mf_kr0v89h5_.flb
-rw-r-----. 1 oracle dba 209723392 Nov 25 16:21 o1_mf_kr0v8cyg_.flb

Oracle 10g 부터 백업 및 복구에 관련된 파일은 한 곳에서 관리하는 것을 지원하는데 그 경로가 바로 db_recovery_file_dest의 값입니다. 이곳에 저장되는 파일은 flashback log, archive redo log file, RMAN backup file 등이 있습니다. 이 공간의 크기는 db_recovery_file_dest_size입니다. 이 크기가 작을 경우 에러 메세지가 발생하기 때문에 크기를 잘 관리해주는 것이 중요합니다.

현재 설정되어 있는 db_recovery_file_dest_size를 확인하는 방법은 아래와 같습니다.

SQL> select * from v$recovery_file_dest;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
--------------- ----------- ---------- ----------------- --------------- ----------
/ORA19/app/orac  8232370176  440827904          10698752               4          0
le/fast_recover
y_area

운영 중인 db_recovery_file_dest의 크기를 변경하고 싶다면 alter system set 명령어를 이용합니다.

SQL> alter system set db_recovery_file_dest_size=100m;

SQL> select * from v$recovery_file_dest;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
--------------- ----------- ---------- ----------------- --------------- ----------
/ORA19/app/orac   104857600  440827904          10698752               4          0
le/fast_recover
y_area

db_recovery_file_dest 공간의 각 영역별 사용량을 조회하는 방법입니다.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                           .25                       .12               2          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         4.88                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

truncate table 장애 복구하기

flashback database 명령어로 복구해야 하는 대표적인 장애는
1. drop user
2. truncate
입니다.

flashback database를 사용하기 위해서는 위의 환경 설정(archive log mode / flashback on)이 모두 되어 있어야 합니다.

먼저 실습용 테이블을 생성합니다.

SQL> conn khyup/khyup
Connected.
SQL> show user;
USER is "KHYUP"
SQL> select * from tab;

no rows selected

SQL> create table snack
  2  (no number,
  3   name varchar2(10),
  4   price varchar2(10));

Table created.

SQL> insert into snack values(1,'aa',500);

1 row created.

SQL> insert into snack values(2,'bb',400);

1 row created.

SQL> insert into snack values(3,'cc',800);

1 row created.

SQL> select * from snack;

        NO NAME            PRICE
---------- --------------- ----------
         1 aa              500
         2 bb              400
         3 cc              800

truncate 장애를 유발합니다.

SQL> truncate table snack;

Table truncated.

SQL> select * from snack;

no rows selected

flashback database 명령어를 이용해서 복구해보겠습니다. 이 명령어는 DB 전체를 과거 시점으로 돌리는 것이기 때문에 반드시 Mount 상태에서 해야 하며 dba 권한을 가지고 수행해야 합니다.

-- dba 권한이 아닐 경우 에러 메시지
SQL> flashback database to timestamp (systimestamp - interval '5' minute);
flashback database to timestamp (systimestamp - interval '5' minute)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"

-- mount 상태가 아닌 상태에서 flashback database 명령어 수행할 경우 에러 메시지
SQL> flashback database to timestamp (systimestamp - interval '5' minute);
flashback database to timestamp (systimestamp - interval '5' minute)
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

flashback database는 모든 Data File을 불완전복구처럼 과거 특정 시간을 돌리게 됩니다. data file과 redo log file, control file의 SCN 정보가 다르게 된다는 의미입니다. 따라서 resetlogs 옵션을 주면서 DB를 open해야 합니다.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1660940992 bytes
Fixed Size                  8897216 bytes
Variable Size             956301312 bytes
Database Buffers          687865856 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> flashback database to timestamp (systimestamp - interval '10' minute);

Flashback complete.

-- resetlogs 옵션을 주지 않고 open 할 경우 발생하는 에러 메시지
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- resetlogs 옵션을 주면서 open
SQL> alter database open resetlogs;

Database altered.

테이블을 조회해봤지만 여전히 복구가 되지 않았다면 다시 shutdown 후 mount 단계로 startup 하여 더 이전 시점으로 flashback database를 진행합니다.

SQL> select * from khyup.snack;

no rows selected

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1660940992 bytes
Fixed Size                  8897216 bytes
Variable Size             956301312 bytes
Database Buffers          687865856 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> flashback database to timestamp (systimestamp - interval '15' minute);

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from khyup.snack;

        NO NAME            PRICE
---------- --------------- ----------
         1 aa              500
         2 bb              400
         3 cc              800

다시 한 번 언급드리지만 flashback database는 DB 전체를 과거 시점으로 돌리는 것이기 때문에 데이터가 손실될 수 있어 주의하셔야 합니다.

2. Flashback Data Archive

Row Level Flashback, Table Level Flashback은 Undo Data를 활용하며 다른 누군가가 Undo Segment를 재활용 해버리면 해당 Flashback 기능을 사용할 수 없습니다.

이와 같은 점을 보완하기 위해 11g부터는 마치 Online Redo Log를 Archive하듯 undo segment에 있는 commit된 내용을 특정 Tablespace에 Archive하여 영구적으로 저장하는 기능을 제공하는데, 그 기능을 FBDA(Flashback Data Archive)라고 합니다.

이에 따라, undo segment의 내용을 비정기적으로 특정 Tablespace로 Archive하는 작업을 수행하는 Background Process인 FBDA가 새로 만들어졌습니다.

FBDA의 특징은 아래와 같습니다.

  • 이 기능이 활성화되면 FBDA가 undo segment의 내용을 모두 기록하기 전까지는 해당 undo segment가 재활용되지 않습니다.
  • 평소에는 sleep하고 있다가 특정 시간이 되면 자동으로 활성화됩니다. 기본값은 5분으로 설정되어 있습니다.
  • 해당 데이터는 자동으로 파티셔닝되며, 관리자도 그 내용을 변경할 수 없습니다.
  • retention time을 설정하여 데이터를 관리합니다. retention time이 지나면 해당 데이터는 자동으로 삭제됩니다.
  • 대량의 DML이 발생할 경우 병목 현상이 생길 우려가 있기에, FBDA Process는 최대 10개까지 동시에 작업할 수 있습니다.

Flashback Database Archive 활성화하기

  1. Flashback history table을 저장할 Tablespace를 생성
  2. Flashback data archive를 관리할 관리자 계정 생성
  3. 관리자 계정에 권한 할당
  4. 관리자 계정으로 로그인 후 flashback history table을 생성
SQL> create tablespace ts_fda01
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/ts_fda01.dbf' size 5M;

Tablespace created.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

TABLESPACE_NAME         MB FILE_NAME
--------------- ---------- --------------------------------------------------
SYSTEM                 700 /ORA19/app/oracle/oradata/ORACLE19/system01.dbf
SYSAUX                 550 /ORA19/app/oracle/oradata/ORACLE19/sysaux01.dbf
UNDOTBS1               200 /ORA19/app/oracle/oradata/ORACLE19/undotbs01.dbf
USERS                    5 /ORA19/app/oracle/oradata/ORACLE19/users01.dbf
KHYUP                  100 /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf
TS_FDA01                 5 /ORA19/app/oracle/oradata/ORACLE19/ts_fda01.dbf

6 rows selected.

SQL> create user fbadmin
  2  identified by fbpwd
  3  default tablespace ts_fda01
  4  quota unlimited on ts_fda01;

User created.

SQL> grant resource, connect to fbadmin;

Grant succeeded.

SQL> grant flashback archive administer to fbadmin;

Grant succeeded.

SQL> conn fbadmin/fbpwd;
Connected.
SQL> create flashback archive fda01 tablespace ts_fda01
  2  retention 30 day;

Flashback archive created.


SQL> select owner_name, flashback_archive_name, retention_in_days, status
  2  from dba_flashback_archive;

OWNER_NAME      FLASHBACK_ARCHI RETENTION_IN_DAYS STATUS
--------------- --------------- ----------------- -------
SYS             FDA01                          30

flashback archive administer 권한

  • Flashback Data Archive를 관리하기 위한 특별한 시스템 권한
  • Flashback History Table을 생성하고 관리할 수 있습니다.
  • Tablespace를 추가, 삭제할 수 있습니다.
  • Flashback History Table Retention Time을 관리할 수 있습니다.

Flashback Data Archive 사용하기

  1. 현재 undo 상태 확인
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace undo_fda
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/undo_fda01.dbf' size 256k;
  
SQL> alter system set undo_tablespace=undo_fda;

SQL> alter system set undo_retention=3;

SQL> show parameter undo;

SQL> select owner_name, flashback_archive_name, retention_in_days, status
  2  from dba_flashback_archive;

OWNER_NAME      FLASHBACK_ARCHI RETENTION_IN_DAYS STATUS
--------------- --------------- ----------------- -------
SYS             FDA01                          30
  1. khyup 사용자로 test3 테이블을 생성, FDA(Flashback Data Archive)를 fda01을 사용하도록 설정
SQL> conn khyup/khyup
Connected.
SQL> create table test3 (no number, name varchar2(10)) flashback archive fda01;
create table test3 (no number, name varchar2(10)) flashback archive fda01
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL> show user;
USER is "KHYUP"
SQL> conn / as sysdba
Connected.
SQL> grant flashback archive on fda01 to khyup;

Grant succeeded.

SQL> conn khyup/khyup;
Connected.
SQL> create table test3 (no number, name varchar2(10)) flashback archive fda01;

Table created.

SQL> insert into test3 values (1,'AAA');

1 row created.

SQL> insert into test3 values (2,'BBB');

1 row created.

SQL> insert into test3 values (3,'CCC');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test3;

        NO NAME
---------- ---------------
         1 AAA
         2 BBB
         3 CCC
  1. update 장애 발생
SQL> update test3
  2  set name='DDD';

3 rows updated.

SQL> select * from test3;

        NO NAME
---------- ---------------
         1 DDD
         2 DDD
         3 DDD
  1. 다른 창에서 SYS로 로그인하여 khyup 사용자의 undo segment 확인
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM                         SYS    SYSTEM                         ONLINE
_SYSSMU1_2686184207$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU2_933461940$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU3_966737822$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU4_4201684153$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU5_4092847558$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU6_3134895182$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU7_2929289075$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU8_1580314892$           PUBLIC UNDOTBS1                       ONLINE
_SYSSMU9_137409288$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU10_2359664804$          PUBLIC UNDOTBS1                       ONLINE

11 rows selected.

SQL> select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
  2  from v$session s, v$transaction t, v$rollname r
  3  where s.taddr=t.addr and t.xidusn=r.usn;

SQL> commit;
  1. userb 계정으로 test4 생성 후 undo segment를 덮어쓰기
SQL> create tablespace example
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/example01.dbf' size 10M;

Tablespace created.

SQL> create user userb
  2  identified by userb
  3  default tablespace example
  4  quota unlimited on example;

User created.

SQL> grant resource, connect to userb;

Grant succeeded.

SQL> conn userb/userb
Connected.
SQL> create table test4 (no number, name varchar2(10));

Table created.

SQL> begin
  2  for i in 1..1000 loop
  3    insert into test4 values (i, 'No name');
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.
  1. 다른 창에서 userb 사용자가 사용 중인 undo segment를 조회하고 덮어쓰기
SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> select count(*) from test4;

  COUNT(*)
----------
      3000
  1. khyup 계정에서 flashback table 명령어로 test3 복구
SQL> alter table test3 enable row movement;

Table altered.

SQL> flashback table test3 to timestamp (systimestamp - interval '20' minute);

Flashback complete.

SQL> select * from test3;

        NO NAME
---------- ---------------
         1 DDD
         2 DDD
         3 DDD

SQL> flashback table test3 to timestamp (systimestamp - interval '30' minute);

Flashback complete.

SQL> select * from test3;

        NO NAME
---------- ---------------
         1 AAA
         2 BBB
         3 CCC

FDA를 사용하는 Table 삭제하기

SQL> drop table test3;
drop table test3
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

SQL> alter table test3 no flashback archive;

Table altered.

SQL> drop table test3;

Table dropped.

Flashback History Table 조회하기

SQL> select * from dba_flashback_archive_tables;

3. Flashback 명령어 주의사항

다음과 같은 상황에선 Flashback 명령어를 사용할 수 없습니다.

  • 물리적인 파일이 삭제된 경우
  • System Tablespace에 있던 Table이 삭제되면 flashback table to before drop 불가능
  • Control File이 재생성되었을 경우
  • Tablespace가 drop되었을 경우
  • Data File이 shrunk 되었을 경우
  • Alter table로 Table 구조가 변경 되었을 경우 Version Query로 복구 안됨.
  • System Table과 통계정보는 flashback으로 복구 불가능
  • Purge된 테이블
  • 별도로 삭제된 index
  • Mview

참고

  • <오라클 관리 실무> - 서진수
profile
HYEOB KIM
Devops Engineer
이전 포스트

[Oracle] Network와 Oracle Net Service

0개의 댓글