[아이티윌 오라클 DBA 과정 91기] 260114 TIL

2026. 1. 14. 21:43ㆍCourses/아이티윌 오라클 DBA 과정

Flashback Query

  • commit된 데이터를 과거의 특정 시점에 존재했던 상태로 볼 수 있음
  • 지정된 point-in-time의 모든 데이터를 query할 수 있음
  • select 문을 as of 절과 함께 사용하면 시간 또는 scn을 통해 과거 시점을 참조할 수 있음
  • 단 undo_retention까지만 보장
    • 초 단위, 기본값은 900
    • undo retention이 보장되려면 undo free 공간이 있어야 함
    • 무조건 보장해야 한다면 alter tablespace undotbs1 retention guarantee;
    • 만약 undo free 공간이 부족할 경우 새로운 트랜잭션이 실패할 수 있음
SYS@ora19c> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

flashback query

SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      4400

SYS@ora19c> select current_scn, checkpoint_change#, scn_to_timestamp(checkpoint_change#), systimestamp from v$database;

CURRENT_SCN CHECKPOINT_CHANGE# SCN_TO_TIMESTAMP(CHECKPOINT_CHANGE#)                                        SYSTIMESTAMP
----------- ------------------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------
    3141471            3129597 13-JAN-26 03.54.34.000000000 PM                                             14-JAN-26 10.54.41.455465 AM +09:00

데이터 수정

SYS@ora19c> update hr.employees set salary = salary * 1.2 where employee_id = 200;

1 row updated.

SYS@ora19c> commit;

Commit complete.

SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      5280

시간을 기준으로 flashback query

SYS@ora19c> select salary
from hr.employees as of timestamp to_timestamp('2026-01-14 10:54:41', 'yyyy-mm-dd hh24:mi:ss')
where employee_id = 200;  2    3

    SALARY
----------
      4400

scn을 기준으로 flashback query

select salary
from hr.employees as of scn 3141471
where employee_id = 200;

flashback version query

  • 과거와 현재 값을 같이 보고 싶을 때
  • versions 절을 사용하여 두 point-in-time 또는 두 scn 사이에 존재하는 행의 모든 값을 검색할 수 있음

시간을 기준으로 flashback version query

SYS@ora19c> select versions_xid, employee_id, salary
from hr.employees versions between timestamp to_timestamp('2026-01-14 10:54:41', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2026-01-14 11:06:00', 'yyyy-mm-dd hh24:mi:ss')
where employee_id = 200;  2    3

VERSIONS_XID     EMPLOYEE_ID     SALARY
---------------- ----------- ----------
07001B00B3040000         200       5280
                         200       4400
  • versions_xid는 슈도 컬럼

scn을 기준으로 flashback version query

SYS@ora19c> select versions_xid, employee_id, salary
from hr.employees versions between scn minvalue and maxvalue
where employee_id = 200;  2    3

VERSIONS_XID     EMPLOYEE_ID     SALARY
---------------- ----------- ----------
07001B00B3040000         200       5280
                         200       4400

과거 값으로 변경

SYS@ora19c> update hr.employees
set salary = (select salary
                        from hr.employees as of timestamp to_timestamp('2026-01-14 10:54:41', 'yyyy-mm-dd hh24:mi:ss')
                        where employee_id = 200)
where employee_id = 200;

1 row updated.

SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      4400

SYS@ora19c> commit;

Commit complete.

데이터 삭제 복구

장애 유발 : 100번 사원 삭제

SYS@ora19c> create table hr.emp tablespace users as select * from hr.employees;

Table created.

SYS@ora19c> delete from hr.emp where employee_id = 100;

1 row deleted.

SYS@ora19c> commit;

Commit complete.

SYS@ora19c> select * from hr.emp where employee_id = 100;

no rows selected

flashback query로 과거 값 조회

SYS@ora19c> select *
from hr.emp as of timestamp to_timestamp('2026-01-14 11:28:00', 'yyyy-mm-dd hh24:mi:ss')
where employee_id = 100;  2    3

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES      24000         

SYS@ora19c> select versions_xid, employee_id, salary
from hr.emp versions between timestamp to_timestamp('2026-01-14 11:28:00', 'yyyy-mm-dd hh24:mi:ss') and systimestamp
where employee_id = 100;  2    3

VERSIONS_XID     EMPLOYEE_ID     SALARY
---------------- ----------- ----------
0900060066050000         100      24000
                         100      24000

SYS@ora19c> select versions_xid, employee_id, salary
from hr.emp versions between scn minvalue and maxvalue
where employee_id = 100;  2    3

VERSIONS_XID     EMPLOYEE_ID     SALARY
---------------- ----------- ----------
020015006B050000         100      24000
                         100      24000
0900060066050000         100      24000
020021006B050000         100      24000

과거 값을 이용해서 insert

SYS@ora19c> insert into hr.emp
select *
from hr.emp as of timestamp to_timestamp('2026-01-14 11:28:00', 'yyyy-mm-dd hh24:mi:ss')
where employee_id = 100;  2    3    4

1 row created.

SYS@ora19c> select * from hr.emp where employee_id = 100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES      24000                                      90

SYS@ora19c> commit;

Commit complete.

Flashback Table

  • 백업으로 복원하지 않고 테이블을 특정 시점으로 recovery할 수 있음
  • 데이터베이스 온라인 상태 유지
  • flashback table 작업을 수행하기 위해 undo 데이터 사용
  • flashback table에 대한 행 이동이 활성화 되어야 함
  • flashback table 고려사항
    • 통계정보는 flashback되지 않음
    • 현재 인덱스와 종속 객체는 유지
    • 딕셔너리 테이블은 수행 불가
    • 테이블 구조 변경 또는 테이블 축소(shrink) 작업 이전으로 되돌릴 수 없음
    • flashback table 수행하는 동안 undo, redo 발생
  • Flashback Table을 수행하기 위한 권한
    • 테이블 소유자는 가능
    • 객체 권한 : grant flashback on hr.emp to insa;
    • 시스템 권한 : grant flashback any table to insa;
    • flashback에 대한 객체 권한 또는 시스템 권한 중에 하나를 부여 받아야 함

테이블 복구

장애 유발 : emp 테이블 전체 데이터 삭제

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107

SYS@ora19c> delete from hr.emp;

107 rows deleted.

SYS@ora19c> commit;

Commit complete.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
         0

과거 데이터로 insert

SYS@ora19c> select count(*)
from hr.emp as of timestamp to_timestamp('2026-01-14 11:58:00', 'yyyy-mm-dd hh24:mi:ss');  2

  COUNT(*)
----------
       107

SYS@ora19c> insert into hr.emp
select *
from hr.emp as of timestamp to_timestamp('2026-01-14 11:58:00', 'yyyy-mm-dd hh24:mi:ss');  2    3

107 rows created.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107

SYS@ora19c> rollback;

Rollback complete.

행 이동 활성화

SYS@ora19c> select row_movement from dba_tables where owner = 'HR' and table_name = 'EMP';

ROW_MOVE
--------
DISABLED

SYS@ora19c> alter table hr.emp enable row movement;

Table altered.

SYS@ora19c> select row_movement from dba_tables where owner = 'HR' and table_name = 'EMP';

ROW_MOVE
--------
ENABLED

flashback table 수행

SYS@ora19c> flashback table hr.emp to timestamp to_timestamp('2026-01-14 11:58:05', 'yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107

행 이동 비활성화

SYS@ora19c> alter table hr.emp disable row movement;

Table altered.

SYS@ora19c> select row_movement from dba_tables where owner = 'HR' and table_name = 'EMP';

ROW_MOVE
--------
DISABLED

Flashback Database

  • 데이터베이스에 대해 되감기 버튼처럼 작동
  • 이전 데이터로 되감기 수행하기 위해서 flashback database log, redo log 정보 이용
  • archivelog mode에서만 가능
SYS@ora19c> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
  • RVWR(Recovery Writer) 백그라운드 프로세스에서 수행
  • Flashback Database를 사용할 수 없는 경우
    • control file이 복원되었거나 재생성된 경우
    • 테이블스페이스 삭제된 경우
    • 데이터파일 크기가 감소된 경우

Flashback Database를 사용할 수 있는 시간을 분단위로 설정

SYS@ora19c> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SYS@ora19c> select issys_modifiable from v$parameter where name = 'db_flashback_retention_target';

ISSYS_MOD
---------
IMMEDIATE

SYS@ora19c> alter system set db_flashback_retention_target = 2880;

System altered.

SYS@ora19c> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2880

Flashback Database 활성화

SYS@ora19c> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SYS@ora19c> alter database flashback on;

Database altered.

SYS@ora19c> select flashback_on from v$database;

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

SYS@ora19c> ! ps -ef | grep rvwr
oracle   31032     1  0 13:58 ?        00:00:00 ora_rvwr_ora19c
oracle   31085 19649  0 13:59 pts/0    00:00:00 /bin/bash -c  ps -ef | grep rvwr
oracle   31087 31085  0 13:59 pts/0    00:00:00 grep rvwr

flashback buffer는 shared pool 메모리에 생성

SYS@ora19c> select * from v$sgastat where name like '%flashback%';

POOL           NAME                                                    BYTES     CON_ID
-------------- -------------------------------------------------- ---------- ----------
shared pool    flashback_marker_cache_si                                9200          0
shared pool    flashback generation buff                             3981120          0
SYS@ora19c> 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                          4.01                         0               3          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         1.21                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

복원 지점 설정

SYS@ora19c> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JAN-26 02.05.24.425803 PM +09:00

# 복원 지점 설정
SYS@ora19c> create restore point before_hr_trunc;

Restore point created.

SYS@ora19c> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                            RESTORE_POINT_TIME             PRE NAME                        PDB CLE PDB_INCARNATION# REP     CON_ID
---------- --------------------- --- ------------ ------------------------------- ------------------------------ --- ------------------------------ --- --- ---------------- --- ----------
   3153413                     5 NO             0 14-JAN-26 02.06.08.000000000 PM                                 NO  BEFORE_HR_TRUNC             NO  NO                 0 NO           0

truncate table

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107

SYS@ora19c> truncate table hr.emp;

Table truncated.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
         0

데이터베이스 정상 종료 후 mount로 시작

SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup mount
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             671088640 bytes
Database Buffers          142606336 bytes
Redo Buffers                7872512 bytes
Database mounted.

flashback database

SYS@ora19c> flashback database to restore point before_hr_trunc;

Flashback complete.

restore point 설정 안 한 경우

# 시간 지정
flashback database to timestamp to_timestamp('2026-01-14 14:05:26', 'yyyy-mm-dd hh24:mi:ss');

# 1시간 전
flashback database to timestamp(sysdate-1/24);

# scn 지정
flashback database to scn = 3153413;

read only모드로 데이터베이스 open 후 복원 확인

SYS@ora19c> alter database open read only;

Database altered.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107
  • 복원 확인 전에 데이터가 쓰이는 것을 막기 위해 read only모드로 open

데이터베이스 종료 후 resetlogs로 open

SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup mount
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             671088640 bytes
Database Buffers          142606336 bytes
Redo Buffers                7872512 bytes
Database mounted.

SYS@ora19c> alter database open resetlogs;

Database altered.

SYS@ora19c> select a.group#, a.sequence#, b.member, a.bytes/1024/1024 mb, a.archived, a.status, a.first_change#, to_char(first_time, 'yyyy-mm-dd hh24:mi:ss') first_time, a.next_change#, to_char(next_time, 'yyyy-mm-dd hh24:mi:ss') next_time
from v$log a, v$logfile b
where a.group# = b.group#;  2    3

    GROUP#  SEQUENCE# MEMBER                                                     MB ARC STATUS           FIRST_CHANGE# FIRST_TIME       NEXT_CHANGE# NEXT_TIME
---------- ---------- -------------------------------------------------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         3          0 /u01/app/oracle/oradata/ORA19C/redo03.log                  50 YES UNUSED                       0                                     0
         2          0 /u01/app/oracle/oradata/ORA19C/redo02.log                  50 YES UNUSED                       0                                     0
         1          1 /u01/app/oracle/oradata/ORA19C/redo01.log                  50 NO  CURRENT                3153415 2026-01-14 14:18:10   1.8447E+19
  • 데이터베이스가 과거 시점으로 돌아간 상태이기 때문에 redo 정보가 안 맞게 됨
    → resetlogs로 open 해야 함
  • db_flashback_retention_target 으로 설정된 기간까지만 복원 가능

restore point 삭제

SYS@ora19c> drop restore point before_hr_trunc;

Restore point dropped.

SYS@ora19c> select * from v$restore_point;

no rows selected

flashback database log 정보 조회

SYS@ora19c> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE     CON_ID
-------------------- --------- ---------------- -------------- ------------------------ ----------
             3152747 14-JAN-26             2880      104857600                        0          0

Flashback Database 비활성화

SYS@ora19c> alter database flashback off;

Database altered.

SYS@ora19c> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

# flashback database 로그 정보 사라짐
SYS@ora19c> select * from v$flashback_database_log;

no rows selected

Flashback Data Archive

  • 기록 데이터 저장소
  • fbda 백그라운드 프로세스를 사용하여 flashback data archive에 대한 활성화되어 있는 테이블의 데이터를 자동으로 추적하고 아카이브
  • flashback archive administer 시스템 권한 필요
  • flashback archive 객체 권한 필요

flashback data archive를 저장할 테이블스페이스 생성

SYS@ora19c> create tablespace fda_tbs datafile '/u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf' size 10m autoextend on;

Tablespace created.

SYS@ora19c> select a.file#, a.creation_change#, to_char(a.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time, b.name tbs_name, a.name file_name, a.checkpoint_change#, a.status
from v$datafile a, v$tablespace b
where a.ts# = b.ts#;  2    3

     FILE# CREATION_CHANGE# CREATION_TIME       TBS_NAME                       FILE_NAME                              CHECKPOINT_CHANGE# STATUS
---------- ---------------- ------------------- ------------------------------ -------------------------------------------------- ------------------ -------
         3             5480 2019-04-17 00:56:21 SYSAUX                         /u01/app/oracle/oradata/ORA19C/sysaux01.dbf                3153418 ONLINE
         1                9 2019-04-17 00:56:09 SYSTEM                         /u01/app/oracle/oradata/ORA19C/system01.dbf                3153418 SYSTEM
         4          1920446 2019-04-17 02:11:02 UNDOTBS1                       /u01/app/oracle/oradata/ORA19C/undotbs01.dbf               3153418 ONLINE
         7            32876 2019-04-17 00:57:08 USERS                          /u01/app/oracle/oradata/ORA19C/users01.dbf                 3153418 ONLINE
         5          3157250 2026-01-14 15:23:58 FDA_TBS                        /u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf               3157253 ONLINE

flashback data archive 생성

SYS@ora19c> create flashback archive fda1 tablespace fda_tbs quota 10m retention 1 year;

Flashback archive created.

SYS@ora19c> select * from dba_flashback_archive;

OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME                           STATUS
------------------------------ ------------------------------ ------------------ ----------------- ---------------------------------------- ---------------------------------------- -------
SYS                            FDA1                                            1               365 14-JAN-26 03.25.40.000000000 PM       14-JAN-26 03.25.40.000000000 P

SYS@ora19c> ! ps -ef | grep fbda
oracle    4511     1  0 15:25 ?        00:00:00 ora_fbda_ora19c
oracle    5007 19649  0 15:33 pts/0    00:00:00 /bin/bash -c  ps -ef | grep fbda
oracle    5009  5007  0 15:33 pts/0    00:00:00 grep fbda

flashback data archive 대상 테이블 활성화

SYS@ora19c> alter table hr.emp flashback archive fda1;

Table altered.

SYS@ora19c> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME        STATUS
------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------- -------------
EMP                            HR                             FDA1                           SYS_FBA_HIST_74271        ENABLED

데이터 수정

SYS@ora19c> select employee_id, salary from hr.emp where department_id = 20;

EMPLOYEE_ID     SALARY
----------- ----------
        201      13000
        202       6000

SYS@ora19c> update hr.emp set salary = 3000 where department_id = 20;

2 rows updated.

SYS@ora19c> commit;

Commit complete.

SYS@ora19c> select employee_id, salary from hr.emp where department_id = 20;

EMPLOYEE_ID     SALARY
----------- ----------
        201       3000
        202       3000

과거 시점 데이터 조회

SYS@ora19c> select employee_id, salary
from hr.emp as of timestamp(systimestamp - interval '3' minute)
where department_id = 20;  2    3

EMPLOYEE_ID     SALARY
----------- ----------
        201      13000
        202       6000
  • flashback archive 가 활성화 되어있기 때문에 undo가 아닌 flashback archive 정보로 보여줌
SYS@ora19c> delete from hr.emp where department_id = 30;

6 rows deleted.

SYS@ora19c> commit;

Commit complete.

SYS@ora19c> select * from hr.emp as of timestamp(systimestamp - interval '1' minute) where department_id = 30;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        114 Den                  Raphaely                  DRAPHEAL                  515.127.4561         07-DEC-02 PU_MAN       11000                       100            30
        115 Alexander            Khoo                      AKHOO                     515.127.4562         18-MAY-03 PU_CLERK      3100                       114            30
        116 Shelli               Baida                     SBAIDA                    515.127.4563         24-DEC-05 PU_CLERK      2900                       114            30
        117 Sigal                Tobias                    STOBIAS                   515.127.4564         24-JUL-05 PU_CLERK      2800                       114            30
        118 Guy                  Himuro                    GHIMURO                   515.127.4565         15-NOV-06 PU_CLERK      2600                       114            30
        119 Karen                Colmenares                KCOLMENA                  515.127.4566         10-AUG-07 PU_CLERK      2500                       114            30

6 rows selected.

복구

SYS@ora19c> update hr.emp e
set salary = (select salary
from hr.emp as of timestamp(systimestamp - interval '3' minute)
where department_id = 20
and e.employee_id = employee_id)
where department_id = 20;

2 rows updated.

SYS@ora19c> select employee_id, salary
from hr.emp as of timestamp(systimestamp - interval '3' minute)
where department_id = 20;  2    3

EMPLOYEE_ID     SALARY
----------- ----------
        201      13000
        202       6000

SYS@ora19c> insert into hr.emp
select *
from hr.emp as of timestamp(systimestamp - interval '3' minute)
where department_id = 30;  2    3    4

6 rows created.

SYS@ora19c> select count(*) from hr.emp;

  COUNT(*)
----------
       107

SYS@ora19c> select * from hr.emp where department_id = 30;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        114 Den                  Raphaely                  DRAPHEAL                  515.127.4561         07-DEC-02 PU_MAN       11000                       100            30
        115 Alexander            Khoo                      AKHOO                     515.127.4562         18-MAY-03 PU_CLERK      3100                       114            30
        116 Shelli               Baida                     SBAIDA                    515.127.4563         24-DEC-05 PU_CLERK      2900                       114            30
        117 Sigal                Tobias                    STOBIAS                   515.127.4564         24-JUL-05 PU_CLERK      2800                       114            30
        118 Guy                  Himuro                    GHIMURO                   515.127.4565         15-NOV-06 PU_CLERK      2600                       114            30
        119 Karen                Colmenares                KCOLMENA                  515.127.4566         10-AUG-07 PU_CLERK      2500                       114            30

6 rows selected.

SYS@ora19c> commit;

Commit complete.

flashback data archive 대상 테이블 비활성화

SYS@ora19c> alter table hr.emp no flashback archive;

Table altered.

SYS@ora19c> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME        STATUS
------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------- -------------
EMP                            HR                             FDA1                           SYS_FBA_HIST_74271        DISABLED
  • 비활성 후에는 undo에 있는 정보로 과거 시점 데이터를 보여줌

flashback data arhive 보존 기간 변경

SYS@ora19c> select * from dba_flashback_archive;

OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME                           STATUS
------------------------------ ------------------------------ ------------------ ----------------- ---------------------------------------- ---------------------------------------- -------
SYS                            FDA1                                            1               365 14-JAN-26 03.25.40.000000000 PM       14-JAN-26 03.25.40.000000000 PM

SYS@ora19c> alter flashback archive fda1 modify retention 2 year;

Flashback archive altered.

SYS@ora19c> select * from dba_flashback_archive;

OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME                           STATUS
------------------------------ ------------------------------ ------------------ ----------------- ---------------------------------------- ---------------------------------------- -------
SYS                            FDA1                                            1               730 14-JAN-26 03.25.40.000000000 PM       14-JAN-26 03.25.40.000000000 PM

flashback data archive 사이즈 변경

SYS@ora19c> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
------------------------------ ------------------ ------------------------------ ----------------------------------------
FDA1                                            1 FDA_TBS                        10

SYS@ora19c> alter flashback archive fda1 modify tablespace fda_tbs quota 100m;

Flashback archive altered.

SYS@ora19c> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
------------------------------ ------------------ ------------------------------ ----------------------------------------
FDA1                                            1 FDA_TBS                        100

flashback data archive 데이터 지우기

SYS@ora19c> alter flashback archive fda1 purge before timestamp(systimestamp - interval '1' day);

Flashback archive altered.

flashback data archive 삭제

SYS@ora19c> drop flashback archive fda1;

Flashback archive dropped.

flashback data archive tablespace 삭제

SYS@ora19c> drop tablespace fda_tbs including contents and datafiles;

Tablespace dropped.

flashback data archive 히스토리 테이블

flashback data archive 설정

SYS@ora19c> create tablespace fda_tbs datafile '/u01/app/oracle/oradata/ORA19C/fda_tbs01.dbf' size 10m autoextend on;

Tablespace created.

SYS@ora19c> create flashback archive fda1 tablespace fda_tbs quota 10m retention 1 year;

Flashback archive created.

SYS@ora19c> alter table hr.emp flashback archive fda1;

Table altered.

SYS@ora19c> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME        STATUS
------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------- -------------
EMP                            HR                             FDA1                           SYS_FBA_HIST_74301        ENABLED

hr.emp 테이블 수정

HR@ora19c> select employee_id, salary from hr.emp where department_id = 20;

EMPLOYEE_ID     SALARY
----------- ----------
        201      13000
        202       6000

HR@ora19c> update hr.emp set salary = 3000 where department_id = 20;

2 rows updated.

HR@ora19c> commit;

Commit complete.

HR@ora19c> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JAN-26 07.57.54.569446 PM +09:00

HR@ora19c> select employee_id, salary from hr.emp where department_id = 20;

EMPLOYEE_ID     SALARY
----------- ----------
        201       3000
        202       3000

HR@ora19c> select * from tab;

TNAME                          TABTYPE        CLUSTERID
------------------------------ ------------- ----------
REGIONS                        TABLE
COUNTRIES                      TABLE
LOCATIONS                      TABLE
DEPARTMENTS                    TABLE
JOBS                           TABLE
EMPLOYEES                      TABLE
JOB_HISTORY                    TABLE
EMP_DETAILS_VIEW               VIEW
BIN$RaVdXyhCZHTgY5Y4qMAw0A==$0 TABLE
FGA_EMP_LOG                    TABLE
INC_EMP                        TABLE
JAN                            TABLE
EMP                            TABLE

13 rows selected.

flashback data archive 관련 테이블 생성됨

HR@ora19c> select * from tab;

TNAME                          TABTYPE        CLUSTERID
------------------------------ ------------- ----------
REGIONS                        TABLE
COUNTRIES                      TABLE
LOCATIONS                      TABLE
DEPARTMENTS                    TABLE
JOBS                           TABLE
EMPLOYEES                      TABLE
JOB_HISTORY                    TABLE
EMP_DETAILS_VIEW               VIEW
BIN$RaVdXyhCZHTgY5Y4qMAw0A==$0 TABLE
FGA_EMP_LOG                    TABLE
INC_EMP                        TABLE
JAN                            TABLE
SYS_FBA_DDL_COLMAP_74318       TABLE
SYS_FBA_HIST_74318             TABLE
SYS_FBA_TCRV_74318             TABLE
EMP                            TABLE

16 rows selected.

HR@ora19c> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JAN-26 08.01.19.853275 PM +09:00

HR@ora19c> select employee_id, salary
from hr.emp as of timestamp(systimestamp - interval '5' minute)
where department_id = 20;  2    3

EMPLOYEE_ID     SALARY
----------- ----------
        201      13000
        202       6000
select * from tab
where tname like 'SYS_FBA%';

DDL 컬럼 매핑 관리 테이블

select * from SYS_FBA_DDL_COLMAP_74301;

히스토리 테이블(과거 데이터 저장)

select * from SYS_FBA_HIST_74301;

Transaction Consistent Read View

select rid, startscn, scn_to_timestamp(startscn), endscn, xid, op from SYS_FBA_TCRV_74301;

select employee_id, salary
from hr.emp
where department_id = 20;

원래 값으로 수정

update hr.emp e
set salary = (select salary
from hr.emp as of scn 3380658
where department_id = 20
and employee_id = e.employee_id)
where department_id = 20;

commit;

select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
26/01/14 20:06:10.779734000 +09:00

5분 후 flashback data archive 정보 추가됨

select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JAN-26 08.11.32.943486 PM +09:00

select * from SYS_FBA_HIST_74318;

select rid, startscn, scn_to_timestamp(startscn), endscn, xid, op from SYS_FBA_TCRV_74318;

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

[아이티윌 오라클 DBA 과정 91기] 260116 TIL  (0) 2026.01.20
[아이티윌 오라클 DBA 과정 91기] 260115 TIL  (0) 2026.01.15
[아이티윌 오라클 DBA 과정 91기] 260113 TIL  (0) 2026.01.13
[아이티윌 오라클 DBA 과정 91기] 260112 TIL  (0) 2026.01.13
[아이티윌 오라클 DBA 과정 91기] 260109 TIL  (0) 2026.01.11

관련글

티스토리툴바