1. 闪回表

表闪回前提需要开启表的行迁移，如下：

alter table t1 enable row movement;

闪回查询确认数据：

select * from t1 as of scn 1078236;

闪回表到指定 scn：

flashback table t1 to scn 1078236;

闪回表到指定时间：

flashback table t1 to timestamp to_timestamp('2024-06-21 10:10:10', 'yyyy-mm-dd hh24:mi:ss');

关闭表迁移

alter table t1 disable row movement;

2. 闪回事务

# 开启 supplemental log
alter database add supplemental log data;

# 设置主键的附加日志数据

alter database add supplemental log data (primary key) columns;


# 找到事务号，比如：14000A0072010000

select distinct xid,commit_scn from 
  flashback_transaction_query where table_owner='HR'
  and table_name='EMPLOYEES'
  and commit_timestamp > systimestamp - interval '10' minute
  order by commit_scn;

# 闪回事务

declare
xids sys.xid_array;
begin
    xids := sys.xid_array('14000A0072010000');
    dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
end;

3. 闪回数据库

闪回数据库需要设置闪回区，开启归档模式。

# 设置闪回区
conn / as sysdba
show parameter recover
alter system set db_recovery_file_dest_size=4G;
alter system set db_recovery_file_dest='/dest/recovery_area';

# 查看归档模式
archive log list;

# 开启归档
conn / as sysdba
shutdown immediate
startup mount
alter database archivelog;
alter database open;

# 验证是否开启了闪回
conn / as sysdba
select flashback_on from v$database;

#开启闪回
alter database flashback on;

# 闪回日志保留2天数据
alter system set db_flashback_retention_target=2880;
show parameter retention_target;

此时业务误操作，删除了表数据。

闪回数据库：

# 关闭数据库
shutdown immediate
startup mount
flashback database to scn 1065864;

# 或者按时间闪回

flashback database to timestamp to_timestamp('2024-06-21 10:10:10', 'yyyy-mm-dd hh24:mi:ss');

文章评论