ORACLE闪回功能基本用法

1. 闪回参数查询

进行闪回查询必须设置自动回滚段管理，在init.ora设置参数UNDO_MANAGEMENT=AUTO，参数UNDO_RETENTION=n，n决定了能往前闪回的最大时间（单位是秒），值越大就需要越多Undo空间。

命令窗口输入 show parameter undo;

2. 测试表和数据准备

DROP TABLE apps.TAB_TEST;
CREATE TABLE apps.TAB_TEST(
  id number(10) primary key,
  name nvarchar2(20),
  phone varchar2(20),
  sex number(1) default 0 not null,
  address nvarchar2(200)
);

DROP SEQUENCE apps.TAB_TEST_SEQ;
CREATE SEQUENCE apps.TAB_TEST_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

CREATE OR REPLACE TRIGGER apps.TAB_TEST_TRG BEFORE INSERT OR UPDATE ON apps.TAB_TEST
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.id IS NULL THEN
    SELECT  apps.TAB_TEST_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(id),0) INTO v_newVal FROM apps.TAB_TEST;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT apps.TAB_TEST_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   sqlserver_utilities.identity := v_newVal; 
   -- assign the value from the sequence to emulate the identity column
   :new.id := v_newVal;
  END IF;
END;

insert into apps.TAB_TEST(name,phone,sex) values('张三','18888888888',0);
insert into apps.TAB_TEST(name,phone,sex) values('李四','18888888889',1);
insert into apps.TAB_TEST(name,phone,sex) values('王五','18888888889',0);
insert into apps.TAB_TEST(name,phone,sex) values('杨六','18888888889',1);
commit;

3. 测试结果验证

场景1: 不小心删除了重要数据，如何进行闪回。
--获取当前SCN（和当前时间）
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, dbms_flashback.get_system_change_number scn from dual;

查询表apps.TAB_TEST不同时间点的数据（可按时间戳或者SCN）

select * from apps.TAB_TEST as of timestamp to_date('2023-02-09 11:44:20','yyyy-mm-dd hh24:mi:ss');
select * from apps.TAB_TEST as of scn 9187625565;

将表闪回到北京时间 2023-02-09 11:44:20

1. 启用对应表的行移动功能

使用表所在用户进行执行，否则在执行闪回时，会报错:ORA-08189: cannot flashback the table because row movement is not enabled

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') time, to_char(dbms_flashback.get_system_change_number) scn from dual;
TIME                SCN
------------------- ----------------------------------------
2023-02-09 11:44:20 9187625565

确保该表row movement为enable。

alter table apps.TAB_TEST enable row movement;

2. 执行闪回到具体的时间

FLASHBACK TABLE apps.TAB_TEST TO TIMESTAMP TO_TIMESTAMP('2023-02-09 11:44:20', 'YYYY-MM-DD HH24:MI:SS');
--或者 FLASHBACK TABLE apps.TAB_TEST TO SCN 9187615633（该时间点对应的SCN）;

注意：当不小心在简单视图上删除了数据，需要分析创建视图的sql，并且找到基表进行数据恢复。

另外，如果需要闪回一个表，需要以下条件：

  a、需要有flashback any table的系统权限或者是该表的flashback对象权限；

  b、需要有该表的select,insert,delete,alter权限；

  c、必须保证该表row movement。

场景2. 闪回删除表（从回收站恢复对象）

Oracle Flashback Drop特性提供一个类似回收站的功能，用来恢复不小心被删除的表。

当删除表时，Oracle 并不立刻释放被删除的表所占用的空间，而是将这个被删除的表进行自动重命名(为了避免同类对象名称的重复)并放进回收站中。

所谓的回收站类似于Windows系统中的回收站，是一个虚拟的容器，用于存放所有被删除的对象，在回收站中被删除的对象将占用创建时的同样的空间。

如果这个被删除的表需要进行恢复，就可利用Flashback Drop功能。

测试删除表，然后恢复

DROP TABLE APPS.TAB_TEST;
--闪回删除表APPS.TAB_TEST
flashback table APPS.TAB_TEST to before drop;
--或者flashback table "BIN$9DyqD9pO3+PgU76FEqyz+w==$0" to before drop;

其他查询版本信息语句： 

select versions_starttime, versions_endtime, versions_xid, versions_operation, versions_startscn, versions_endscn, id, name, phone, sex, address
from apps.TAB_TEST versions between timestamp minvalue and maxvalue order by versions_startscn;

--下面语句因表(视图)flashback_transaction_query数据量可能过千万，执行时间可能会很长：
select * from flashback_transaction_query a,
(select versions_starttime, versions_endtime, versions_xid, versions_operation, id, name, phone, sex, address
from apps.TAB_TEST versions between timestamp minvalue and maxvalue) b
where a.xid = versions_xid;