플래시백 데이터 아카이브(Flashback Data Archive, FDA)는 지정된 데이터베이스 객체에 대해 트랜잭션에 의한 데이터 변경 이력을 자동으로 추적하고 아카이브할 수 있는 기능입니다.
개요
플래시백 데이터 아카이브는 여러 개의 테이블스페이스로 구성되며, 추적 대상 테이블에 수행된 모든 트랜잭션의 과거 데이터를 내부 히스토리 테이블(internal history table)에 저장합니다.
이 기능은 UNDO 데이터를 장기간 보관할 수 있게 해주므로, UNDO 기반의 플래시백 작업을 훨씬 긴 기간 동안 수행할 수 있습니다. 짧은 기간과 긴 기간의 이력 데이터를 안전하게 보존하고 내부 히스토리 테이블을 엄격하게 보호하기 위해, FDA는 백업을 복원(restoration)하지 않고도 과거 데이터를 손쉽게 조회(recall)할 수 있는 강력한 옵션입니다.
실행에 필요한 권한
- FLASHBACK ARCHIVE ADMINISTER 시스템 권한을 가진 스키마는 PL/SQL 프로시저의 연결 해제(Disassociate) 및 재연결(Associate) 작업을 실행할 수 있습니다.
- 테이블이 한 번 연결 해제되면, 일반 사용자도 해당 테이블에 대한 적절한 권한이 있다면 DDL 및 DML 문을 수행할 수 있습니다.
- 플래시백 데이터 아카이브를 생성하려면 FLASHBACK ARCHIVE ADMINISTER 시스템 권한이 필요합니다.
- 플래시백 데이터 아카이브를 생성하려면 CREATE TABLESPACE 시스템 권한도 함께 필요합니다.
- 히스토리 정보가 저장될 테이블스페이스에 충분한 쿼터(quota)가 할당되어 있는지 반드시 확인해야 합니다.
트랜잭션 데이터와 함께 컨텍스트(context) 정보를 저장하려면 DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL 프로시저를 사용하고, 다음 매개변수 값 중 하나를 전달해야 합니다.
- TYPICAL: USERENV 컨텍스트의 기본 감사(auditing) 속성만 저장합니다.
- ALL: SYS_CONTEXT 함수를 통해 사용자가 접근 가능한 모든 컨텍스트를 저장합니다.
- NONE: 컨텍스트 정보를 저장하지 않습니다.
여기서는 USERENV와 사용자 정의 컨텍스트 값을 모두 캡처하기 위해 ALL을 사용합니다.
CONN sys@surya AS SYSDBA
EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');테스트 및 구현
다음 예제에서는 테이블스페이스 수준에서 FDA를 활성화하고, 여러 테이블스페이스에 대해 특정 보존 기간(retention)을 설정합니다. 또한 보존 기간 내에 삭제된 데이터를 플래시백 데이터 아카이브에서 조회하는 방법을 다룹니다.
이 기능의 목적은 FDA를 통해 과거 데이터를 손쉽게 조회하는 것입니다. 이 기능을 활성화하지 않으면 과거 데이터를 얻기 위해 데이터베이스 전체를 복원해야 하며, 대규모 데이터베이스 시스템에서는 그 복잡성이 상당히 커집니다.
예제
1) 테이블스페이스 생성
SQL> CREATE TABLESPACE FBA DATAFILE SIZE 500M AUTOEXTEND ON NEXT 100M;Tablespace created.
2) 디폴트 플래시백 데이터 아카이브(FDA) 생성
SQL> CREATE FLASHBACK ARCHIVE DEFAULT FLA1 TABLESPACE FBA QUOTA 500M RETENTION 1 YEAR;Flashback archive created.
3) 논디폴트(Non-Default) FDA 생성
SQL> CREATE FLASHBACK ARCHIVE FLA2 TABLESPACE users QUOTA 400M RETENTION 6 MONTH;Flashback archive created.
4) 생성된 FDA 목록 조회
SELECT owner_name,
flashback_archive_name,
flashback_archive#,
retention_in_days,
TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
status
FROM dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
---------- ---------------------- ------------------ ----------------- -------------------- -------------------- -------
SYS FLA1 1 365 16-DEC-2021 19:28:53 16-DEC-2021 19:28:53 DEFAULT
SYS FLA2 2 180 16-DEC-2021 19:29:14 16-DEC-2021 19:29:145) 디폴트 FDA 설정 및 상세 정보 확인
SQL> ALTER FLASHBACK ARCHIVE FLA1 SET DEFAULT;Flashback archive altered.
SELECT flashback_archive_name,
flashback_archive#,
tablespace_name,
quota_in_mb
FROM dba_flashback_archive_ts
ORDER BY flashback_archive_name;FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------- ------------------ --------------- -----------
FLA1 1 FBA 500
FLA2 2 USERS 400SQL> SELECT *
FROM DBA_FLASHBACK_ARCHIVE_TABLES
WHERE TABLE_NAME = 'EMPLOYEES'
AND OWNER_NAME = 'HR';TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
----------- ---------- ---------------------- ------------------- -------
EMPLOYEES HR FLA1 SYS_FBA_HIST_92593 ENABLEDFDA 동작 방식 테스트
아래 테스트를 통해 FDA가 실제로 어떻게 동작하는지 확인할 수 있습니다.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------
2021/12/16 19:39:31
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1964623EMPLOYEES 테이블에서 레코드 삭제 및 갱신 테스트
SQL> DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 192;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> UPDATE HR.EMPLOYEES SET SALARY = 12000 WHERE EMPLOYEE_ID = 168;
SQL> COMMIT;
SQL> UPDATE HR.EMPLOYEES SET SALARY = 12500 WHERE EMPLOYEE_ID = 168;
SQL> COMMIT;
SQL> UPDATE HR.EMPLOYEES SET SALARY = 12550 WHERE EMPLOYEE_ID = 168;
SQL> COMMIT;FDA를 활용한 데이터 비교
다음 단계를 따라 FDA로 변경 전후의 데이터를 비교할 수 있습니다.
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM HR.EMPLOYEES
AS OF TIMESTAMP TO_TIMESTAMP('2021/12/16 19:39:31','YYYY/MM/DD HH24:MI:SS')
MINUS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM HR.EMPLOYEES;EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
192 Sarah Bell위 결과에서 FDA를 통해 삭제된 행을 확인할 수 있습니다. 또한 VERSIONS_STARTSCN 의사컬럼(pseudocolumn)을 사용해서도 해당 데이터를 조회할 수 있습니다.
특정 SCN 기준 데이터 조회
SQL> COL VERSIONS_STARTTIME FORMAT A40
SELECT VERSIONS_STARTTIME,
VERSIONS_STARTSCN,
FIRST_NAME,
LAST_NAME,
SALARY
FROM HR.EMPLOYEES VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2021/12/16 19:39:31','YYYY/MM/DD HH24:MI:SS') AND SYSTIMESTAMP
WHERE EMPLOYEE_ID = 168;VERSIONS_STARTTIME VERSIONS_STARTSCN FIRST_NAME LAST_NAME SALARY
----------------------------- ----------------- ---------- ---------- ----------
16-DEC-21 07.40.08.000000000 PM 1964648 Lisa Ozer 12500
16-DEC-21 07.40.08.000000000 PM 1964646 Lisa Ozer 12000
Lisa Ozer 11500
16-DEC-21 07.40.08.000000000 PM 1964650 Lisa Ozer 12550SALARY 컬럼에 대해 500씩 차이 나는 갱신(UPDATE) 문을 여러 번 수행했기 때문에, 동일한 행에 대해 서로 다른 버전들이 조회되는 것을 확인할 수 있습니다.
DDL 제약과 우회 방법 (데이터 변경 이력 보존)
Disassociate / Associate
업그레이드, 테이블 분할(split) 등 더 복잡한 DDL 작업이 필요할 때는 Disassociate와 Associate PL/SQL 프로시저를 사용하여 특정 테이블의 플래시백 데이터 아카이브를 일시적으로 비활성화할 수 있습니다. Associate 프로시저는 재연결 후 스키마 무결성을 검증합니다. 즉, 베이스 테이블과 히스토리 테이블의 스키마가 동일해야 합니다. 이 두 프로시저를 실행하려면 FLASHBACK ARCHIVE ADMINISTER 권한이 필요합니다.
FDA가 적용된 테이블에서 제한되는 주요 DDL 작업은 다음과 같습니다.
- 컬럼 추가, 삭제, 이름 변경 또는 수정
- 파티션 삭제 또는 잘라내기(truncate)
- 테이블 이름 변경 또는 truncate (FBA가 적용된 테이블 삭제 시 ORA-55610 오류 발생)
- 일부 변경 작업(MOVE / SPLIT / 파티션 변경 등)은 DBMS_FLASHBACK_ARCHIVE 패키지를 사용해야 합니다.
실습: FDA 테이블에서 DDL 작업 수행하기
다음 예제에서는 FDA가 적용된 테이블에서 제약 조건 관련 DDL 작업을 어떻게 처리할 수 있는지 살펴봅니다. 먼저 데모 테이블 EMPLOYEES_FBA를 생성하고 제약 조건을 추가합니다.
SQL> CREATE TABLE HR.EMPLOYEES_FBA AS SELECT * FROM HR.EMPLOYEES;
Table created.
SQL> ALTER TABLE HR.EMPLOYEES_FBA ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);
Table altered.데모 테이블에 FDA 활성화 및 레코드 갱신
SQL> ALTER TABLE HR.EMPLOYEES_FBA FLASHBACK ARCHIVE;
Table altered.
SQL> UPDATE HR.EMPLOYEES_FBA SET SALARY = 10000 WHERE EMPLOYEE_ID = 203;
1 row updated.
SQL> COMMIT;
Commit complete.제약 조건 비활성화/활성화 시 발생하는 ORA-55610 오류
SQL> ALTER TABLE HR.EMPLOYEES_FBA DISABLE CONSTRAINT EMPLOYEE_PK;
Table altered.
SQL> ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;
ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table제약 조건 오류 발생 시 처리 방법
참고: 테이블에 제약 조건(Primary Key, Unique Key, Foreign Key 또는 Check Constraint)을 추가하면, 내부 SYS_FBA_ 아카이브 테이블에 직접 접근하지 않는 한 과거 데이터를 자동으로 읽을 수 없게 됩니다. 따라서 제약 조건 관리와 테이블의 이력 추적 작업 시에는 각별히 주의해야 합니다.
SQL> SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME = 'EMPLOYEES_FBA';
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------- ---------- ---------------------- ------------------- -------
EMPLOYEES_FBA HR FLA1 SYS_FBA_HIST_93946 ENABLEDDBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA로 연결 해제
SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('HR','EMPLOYEES_FBA');
PL/SQL procedure successfully completed.연결 해제 후 제약 조건 다시 활성화
SQL> ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;
Table altered.DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA로 FDA 재활성화
SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('HR','EMPLOYEES_FBA');
PL/SQL procedure successfully completed.특정 시점 이전의 이력 데이터 삭제(Purge)
SQL> ALTER FLASHBACK ARCHIVE FLA1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Flashback archive altered.FDA 비활성화
SQL> ALTER TABLE HR.EMPLOYEES NO FLASHBACK ARCHIVE;
SQL> ALTER TABLE HR.EMPLOYEES_FBA NO FLASHBACK ARCHIVE;FDA 삭제
SQL> DROP FLASHBACK ARCHIVE FLA1;결론
플래시백 데이터 아카이브(FDA) 기능은 데이터 변경 이력을 관리하고 보존할 수 있는 중앙화되고 통합된 인터페이스를 제공합니다. 정책 기반의 자동화된 관리를 통해 새로운 규제 준수나 변화하는 비즈니스 요구에 대응하기 위해 과거 데이터를 추적해야 하는 데이터베이스 운영 환경에 획기적인 해결책을 제공합니다.
데이터베이스 여정에서 전문가의 도움이 필요하다면 언제든 문의하세요. 피드백 탭을 통해 의견을 남기거나 질문을 보낼 수 있으며, 언제든지 대화를 시작할 수 있습니다.