oracle闪回技术

一、闪回设置

show parameter undo; 
undo_managment='AUTO';   //自动闪回管理
undo_retention=900;   //闪回数据保存秒数
undo_tablespace='UNDOTBS1';   //设置闪回表空间

二、闪回查询

1、闪回时间查询

select * from scott.dept;
delete from scott.dept where deptno=40; 
commit;
select * from scott.dept as of timestamp sysdate-1/1400;  //查看一分钟前的表信息，删除后一分钟内查询，否则要把时间范围扩大

2、闪回scn查询

select ora_rowscn,deptno,dname from scott.dept;  
update scott.dept set dname='DBA' where deptno=30;
commit;

select ora_rowscn,deptno,dname from scott.dept;  //获取修改数据后的scn号，得1343382
select deptno,dname from scott.dept as of scn 1343382-1;  //获取修改前的数据

三、闪回版本查询

1、伪列

versions_xid   //事务号
versions_operation  //执行的操作类型，U(update)、I(insert)、D(delete)
versions_starttime  //操作时间
versions_startscn  //scn号

//外部表、临时表、固定表、视图不能使用versions子句
//versions子句不能跨DDL命令使用

2、查询伪列

select versions_xid,versions_operation,versions_starttime,versions_startscn,deptno,dname from scott.dept versions between scn minvalue and maxvalue;

四、闪回表

update scott.emp set sal=sal+1;
commit;

select versions_starttime,versions_startscn from scott.emp versions between scn minvalue and maxvalue;

alter table scott.emp enable row movement;  //打开行的移动
flashback table scott.emp to scn 447150-1;
alter table scott.emp disable row movement;

五、闪回事务处理查询

alter database add supplemental log data;  //增加附加日志
alter database add supplemental log data (primary key) columns;
select supplemental _log_data_fk,supplemental _log_data_all,supplemental _log_data_min,supplemental _log_data_pk,supplemental _log_data_ui from v#database;


delete scott.emp;
commit;
select tname from tab where tname like '%TRAN%';
desc flashback_transaction_query;

select versions_xid from scott.emp versions between scn minvalue and maxvalue;
select undo_sql from flashback_transaction_query where xid='versions_xid ';  //反操作语句

spool /tmp/emp.sql rep;
set linesize 400;
select undo_sql from flashback_transaction_query where xid='versions_xid ';
spool off;
ho sed '/^insert into/p' -n /tmp/emp.sql >/tmp/emp1.sql
start /tmp/emp1.sql;

六、闪回技术归档

FDA=Flashback Data Archive，返回数据归档

create tablespace fda datafile '/u01/oracle/oradata/wyzc/fda01.dbf'  size 10m autoextend on;  //创建表空间

create flashback archive fda  tablespace fda quota 10m retention 1year;

select * from system_privilege_map where name like '%FLASH%';  //查询权限信息
grant flashback archive administer to scott;  //给scott用户赋予权限

conn scott/tiger;
alter table emp flashback archive fda;
select * from user_flashback_archive_tables where table_name 'EMP';//查看表是否打开了数据归档

update emp set sal=sal+1;
commit;
truncate table emp;  //截断数据表，还能通过闪回查询查出
select * from emp as of timestamp sysdate-1;  //闪回查询昨天的数据
insert into emp select * from emp as of timestamp sysdata-1;  //插入闪回查询的数据
select * from emp versions between scn minvalue and maxvalue;

drop table emp;  //不能删除，因为存在数据归档。删除、重命名、或修改某一列会出错
alter table emp no flashback archive;
drop table emp;
flashback table emp to before drop;

//关闭数据归档之后再从truncate执行，到闪回查询那一步会出错

七、闪回删除的对象和回收站

show parameter recycle;  //dba用户查看数据库回收站是否打开
conn scott/tiger;
drop table emp;
show recyclebin;  //查看回收站信息(非dba用户)
select * from tab;

desc "BIN...."; //和emp一样的结构信息
flashback table "BIN..." to before drop;
select * from emp;

//如果有两个重新的emp，用上一个语句恢复会出错，需要重命名
flashbacktable "BIN..."  to before drop rename to empold;

purge recyclebin;  //清空回收站
select count(*) from dba_recyclebin;
drop table emp;
purge table emp;  //清空回收站里的某一张表

八、闪回数据库(尽量少用，可以在备库使用)

select flashback_on from v$database;  //查看数据库闪回是否启动
shutdown immediate;  //关闭数据库，打开归档模式
startup mount;
alter database archivelog;
alter database flashback on;  //必须先归档之后再设置，否则会出现错误
alter database open;

show parameter flashback;  //得到最长的数据库返回时间，一般是1440min
show parameter db_re;//返回数据存放路径
drop table scott.emp purge;

//日志挖掘查找删除时间点
select group#,member,a.status from v$log a join v$logfile using(group#);
exec dbms_logmnr.add_logfile('/u01/oracle/oradata/cmh/redo02.log');
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catatlog);
select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents where table_name='EMP';
exec dbms_logmnr.end_logmnr;

shutdown immediate;
startup mount;
flashback database to scn 1342774;
alter database open read only;  //这时候修改数据会出错，read-only access
startup force; //强制启动也会出错
alter database open resetlogs;

参考连接：https://blog.csdn.net/javahelpyou/article/details/123946465