oracle19_闪回数据存档（FDA）

show CON_NAME
CREATE TABLESPACE fda_ts DATAFILE SIZE
1M AUTOEXTEND ON NEXT 1M;
ALTER USER lihao QUOTA UNLIMITED ON fda_ts;

创建闪回存档
show CON_NAME
CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 10G RETENTION 1 YEAR;
GRANT FLASHBACK ARCHIVE ON fda_1year TO lihao;
GRANT FLASHBACK ARCHIVE ADMINISTER TO lihao;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO lihao;
GRANT CREATE ANY CONTEXT TO lihao;

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;
说明
DBA_FLASHBACK_ARCHIVE 描述数据库中所有可用的闪回存档。
OWNER_NAME：闪回存档的创建者名称
FLASHBACK_ARCHIVE_NAME：闪回存档的名称
FLASHBACK_ARCHIVE#：闪回存档的编号
RETENTION_IN_DAYS：在闪回存档中保留数据的最大持续时间（天）
CREATE_TIME：闪回存档的创建时间
LAST_PURGE_TIME：系统上次清除闪回存档中的数据的时间
STATUS：表示闪回存档是否是系统的默认闪回存档（DEFAULT）（NULL）

SELECT flashback_archive_name,
flashback_archive#,
tablespace_name,
quota_in_mb
FROM dba_flashback_archive_ts
ORDER BY flashback_archive_name;
说明
DBA_FLASHBACK_ARCHIVE_TS 描述数据库中可用的闪回归档文件中的所有表空间。
FLASHBACK_ARCHIVE_NAME：闪回存档的名称
FLASHBACK_ARCHIVE#：闪回存档的编号
TABLESPACE_NAME：闪回存档中表空间的名称
QUOTA_IN_MB：表空间中可用于闪回存档的最大空间（以MB为单位）；NULL表示没有配额限制

创建一个表，并将其与FDA相关联
CREATE TABLE tab1 (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT tab_1_pk PRIMARY KEY (id)
) FLASHBACK ARCHIVE fda_1year;

SELECT owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM dba_flashback_archive_tables
ORDER BY owner_name, table_name;
说明
DBA_FLASHBACK_ARCHIVE_TABLES 显示有关数据库中已启用闪回存档的所有表的信息。
TABLE_NAME：为闪回存档启用的表的名称
OWNER_NAME：为闪回存档启用的表的所有者名称
FLASHBACK_ARCHIVE_NAME：闪回存档的名称
ARCHIVE_TABLE_NAME：包含用户表历史数据的存档表的名称
STATUS：是否启用了闪回存档的状态

DBMS_FLASHBACK_ARCHIVE软件包包含执行各种闪回任务的过程
根据需要保存多少用户上下文 ALL - the entire SYS_CONTEXT
EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level(‘ALL’);

CREATE OR REPLACE CONTEXT test_context USING test_ctx_api;
CREATE OR REPLACE PACKAGE test_ctx_api AS
PROCEDURE set_value (p_name IN VARCHAR2,
p_value IN VARCHAR2);
END test_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY test_ctx_api AS

PROCEDURE set_value (p_name IN VARCHAR2,
p_value IN VARCHAR2) AS
BEGIN
DBMS_SESSION.set_context(‘test_context’, LOWER(p_name), p_value);
END;

END test_ctx_api;
/

EXEC DBMS_SESSION.set_identifier(‘Peter Pan’);
EXEC lihao.test_ctx_api.set_value(‘my_attribute’,‘First Action’);
INSERT INTO tab1 VALUES (1, ‘ONE’);
COMMIT;

EXEC DBMS_SESSION.set_identifier(‘Peter Parker’);
EXEC test_ctx_api.set_value(‘my_attribute’,‘Second Action’);

UPDATE tab1 SET description = ‘TWO’
WHERE id = 1;
COMMIT;

EXEC DBMS_SESSION.set_identifier(‘Peter Rabbit’);
EXEC test_ctx_api.set_value(‘my_attribute’,‘Third Action’);
UPDATE tab1 SET description = ‘THREE’
WHERE id = 1;
COMMIT;

SELECT versions_startscn,–versions_starttime,
versions_endscn,–versions_endtime,
versions_xid,
versions_operation,
description,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, ‘USERENV’,‘SESSION_USER’) AS session_user,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, ‘USERENV’,‘CLIENT_IDENTIFIER’) AS client_identifier,
DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, ‘test_context’,‘my_attribute’) AS my_attribute
FROM tab1
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(3/17/21) AND SYSTIMESTAMP
WHERE id = 1
ORDER BY versions_startscn;

ALTER TABLE lihao.tab1 NO FLASHBACK ARCHIVE;
DROP TABLE lihao.tab1 PURGE;

CREATE TABLE app_tab1 AS
SELECT level AS id,
'Description for ’ || level AS description
FROM dual
CONNECT BY level <= 5;


ALTER TABLE app_tab1 ADD CONSTRAINT app_tab1_pk PRIMARY KEY (id);

CREATE TABLE app_tab2 AS
SELECT level AS id,
'Description for ’ || level AS description
FROM dual
CONNECT BY level <= 5;
ALTER TABLE app_tab2 ADD CONSTRAINT app_tab2_pk PRIMARY KEY (id);


创建一个新的应用程序
DBMS_FLASHBACK_ARCHIVE软件包包含执行各种任务的过程 闪回数据归档（FDA）启用的表从其基础FDA与其解除关联或重新关联的表
register_application：此过程使用应用程序名称和（可选）闪回数据存档，并注册用于数据库加固的应用程序。
flashback_archive_name：闪回数据存档的名称，其中存储了给定应用程序的安全表的历史数据。如果未指定闪回数据存档，则使用默认的闪回数据存档
BEGIN
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name => ‘MY_APP’,
flashback_archive_name => ‘FDA_1YEAR’);
END;
/

SELECT a.appname,
b.faname
FROM sys_fba_app a
JOIN sys_fba_fa b ON a.fa# = b.fa#;

ADD_TABLE_TO_APPLICATION将表添加到应用程序
add_table_to_application：将一个表作为安全性表添加到该应用程序
application_name：应用程序的名称
table_name：表的名称
BEGIN
DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => ‘MY_APP’,
table_name => ‘APP_TAB1’,
schema_name => ‘LIHAO’);

DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => ‘MY_APP’,
table_name => ‘APP_TAB2’,
schema_name => ‘LIHAO’);
END;
/

SELECT a.appname,
c.owner AS table_owner,
c.object_name As table_name
FROM sys_fba_app a
JOIN sys_fba_app_tables b ON a.app# = b.app#
JOIN dba_objects c ON b.obj# = c.object_id
ORDER BY 1,2,3;

未启用该应用程序，因此这些表当前未与FDA关联
SELECT owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM dba_flashback_archive_tables
WHERE table_name LIKE ‘APP_TAB%’
ORDER BY owner_name, table_name;

BEGIN
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => ‘MY_APP’);
END;
/

SELECT owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM dba_flashback_archive_tables
WHERE table_name LIKE ‘APP_TAB%’
ORDER BY owner_name, table_name;

DISABLE_APPLICATION禁用该应用程序中所有表的FDA
BEGIN
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => ‘MY_APP’);
END;
/

SELECT owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM dba_flashback_archive_tables
WHERE table_name LIKE ‘APP_TAB%’
ORDER BY owner_name, table_name;

DROP_APPLICATION删除应用程序
BEGIN
DBMS_FLASHBACK_ARCHIVE.remove_table_from_application(
application_name => ‘MY_APP’,
table_name => ‘APP_TAB1’,
schema_name => ‘LIHAO’);

DBMS_FLASHBACK_ARCHIVE.remove_table_from_application(
application_name => ‘MY_APP’,
table_name => ‘APP_TAB2’,
schema_name => ‘LIHAO’);

DBMS_FLASHBACK_ARCHIVE.drop_application(
application_name => ‘MY_APP’);
END;
/