oracle闪回技术

一、闪回技术

闪回使错误恢复更容易；允许查看过去时间点的数据；减少恢复花费的时间；有基于回收站、undo(闪回查询、闪回版本查询、闪回表查询、闪回事务查询)、历史undo、flashback log

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

 

二、闪回查询(flashback query，利用undo，使用as of查询过去对象的数据)

1、闪回时间查询

conn scott/tiger;
select employee_id,last_name from emp; 
select sysdate from dual;  //记录删除数据之前的时间,是2022-12-18 16:10:46
delete from emp;
select employee_id,last_name from emp as of timestamp  to_timestamp('2022-12-18 16:10:46','yyyy-mm-dd hh24:mi:ss');  //利用闪回查看某个点表数据

2、闪回scn查询

rollback;  //取消删表操作
select current_scn from v$database;  //记录删除表前的scn号,是21778898
delete from emp;
select employee_id,last_name from emp as of scn 21778898;

 

三、闪回版本查询(flashback version query，利用undo查询一个时间段内，特定的行发生了几次变更，有多少个版本)

1、表中和闪回版本查询相关的伪列(外部表、临时表、固定表、视图不能使用versions子句，versions子句不能跨DDL命令使用)

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

2、闪回版本查询

select versions_xid,versions_operation,versions_starttime,versions_endtime,versions_startscn,versions_endscn,employee_id,salary
from emp  
versions between timestamp to_timestamp('2022-12-18 16:10:46','yyyy-mm-dd hh24:mi:ss') and
to_timestamp('2022-12-18 16:40:00','yyyy-mm-dd hh24:mi:ss')
where employee_id=1;

报invalid lower limit snapshot expression错误是因为时间区间超过undo_retention设置时间，可以适当调大。

select sysdate from dual;  //记录DML操作时间，2022-12-18 16:57:05
update emp set salary=1;select sysdate from dual;  //2022-12-18 16:59:37
select versions_xid,versions_operation,versions_starttime,versions_endtime,employee_id,salary
from emp  
versions between timestamp to_timestamp('2022-12-18 16:57:05','yyyy-mm-dd hh24:mi:ss') and
to_timestamp('2022-12-18 16:59:37','yyyy-mm-dd hh24:mi:ss')
where employee_id=100;

 

四、闪回表(flashback table，通过undo数据，将表闪回一个特定时间点)

前提：要有flashback any table系统权限或某个对象的flashback权限；有表select、insert、delete、alter权限；开启表的行移动功能

select sysdate from dual;  //记录删表时间,2022-12-18 16:39:50
delete from emp;
alter table emp enable row movement;  //打开行的移动,因为原来的表行可能被占用了
flashback table emp to timestamp to_timestamp('2022-12-18 16:39:50','yyyy-mm-dd hh24:mi:ss');
select * from emp;  //恢复成功，有107行
alter table emp disable row movement;

 

 

五、闪回事务处理查询(flashback transaction query，利用undo数据查询特定时间段内具体的事务)

前提：兼容性在10.0以上；启用补充日志功能(否则不能看到有些信息)。

alter database add supplemental log data;  //启用补充日志
conn scott/tiger;
create table trans_query(tname varchar(20),sal number);
insert into trans_query values('chen',1000);  //新增一条数据
update trans_query set sal=4000 where tname='chen';  //修改数据值
insert into trans_query values('mu',2500);  //新增第二条数据
delete from trans_query where tname='chen';  //删除第一条数据
commit; //上面从创建表开始开启一个事务，事务查询时要commit进行提交事务,这里就是第一个事务版本
insert into trans_query values('chen',7000);
commit;  //第二个事务版本
select versions_xid xid, versions_startscn start_scn,versions_endscn end_scn,versions_operation operation,tname,sal
from trans_query
versions between scn minvalue and maxvalue
where tname='chen';

 

 只有第二个事务版本插入的chen员工的数据，因为第一个事务插入了又删除员工chen的数据，相当于在第一个事务没有这条数据DML信息。

select xid, start_scn, commit_scn, operation, logon_user, undo_sql
from flashback_transaction_query
where xid ='020001004E110000';  //查看undo_sql要在前面开启补充日志

 

六、闪回删除的对象(flashback drop，利用回收站闪回drop掉的对象，前提是recyclebin要开启，删除的对象会留在回收站，关闭recyclebin或者drop table purge时，对象不会在回收站)

1、闪回表

conn scott/tiger;
show parameter recyclebin;  //查看参数是否开启
create table drop_test as select * from emp //创建测试表
select count(*) from drop_test;  //有107行
drop table drop_test;
show recyclebin;  //查看回收站，有drop_test 
flashback table drop_test to before drop;  //闪回删除的表
show recyclebin;  //再次查看回收站，此时已经没有那个表
select count(*) from drop_test;  //有107行
purge recyclebin;  //清空回收站
show recyclebin;  //没有表

2、闪回索引

create table index_test as select * from emp;
create index idx_empid on index_test(employee_id);
drop table index_test;
show recyclebin;
flashback table index_test to before drop;
select index_name from user_indexes where table_name = 'INDEX_TEST';  //虽然索引恢复了但是名称改变了

3、闪回同名表

show recyclebin;  //回收站存在两个同名的表trans_query，而且还有一个同名的表没有被删除

如果要闪回表必须重命名

flashback table TRANS_QUERY to before drop rename to TRANS_QUERY_drop;
show recyclebin;  //优先闪回后面删除的表

 

七、闪回数据归档(flashback data archive=FDA，不常用，将表的undo信息保存到特定表空间，保存时间更久；不能用于临时表、外部表、物化视图；可以闪回alter table、drop/truncate 分区、rename/truncate表)

create tablespace fda datafile '/u01/app/oracle/oradata/CHENMU/fda01.dbf' size 50m autoextend on;  //创建表空间
create user fda identified by fda123 default tablespace fda;  //创建用户并授权
grant connect,resource,flashback archive administer,unlimited tablespace to fda;
conn fda/fda123;
create flashback archive fda1  tablespace fda quota 50m retention 1 year;  //创建闪回归档区
select owner_name,flashback_archive_name,status from dba_flashback_archive;//查看已建立的闪回归档区
create table emp(empno varchar2(10) not null,ename varchar2(20)) flashback archive fda1;  //创建表并指定归档区
select table_name,owner_name from user_flashback_archive_tables;  //查看使用归档区的表
insert into emp values('1','chen');
commit;
select sysdate from dual;  //2022-12-19 22:24:23
truncate table emp;  //截断数据表，还能通过闪回查询查出
select * from emp as of timestamp to_timestamp('2022-12-19 22:24:23','yyyy-mm-dd hh24:mi:ss');  //可以查询truncate删除之前的数据
alter table emp no flashback archive;
drop flashback archive fda1;  //删除归档区

 

八、闪回数据库(flashback database，尽量少用，可以在备库使用；利用闪回日志进行数据回退；有点是不需要restore文件；缺点是只能闪回逻辑操作，不能做恢复介质，做过收缩后不能闪回)

普通还原点前提：开启归档模式；启用闪回区；开启闪回功能。保证还原点前提：开启归档模式和快速恢复区(fra)，保证还原点会默认打开归档日志。

1、普通还原点未启用闪回功能(已开启归档模式)

conn / as sysdba;
create restore point fd_test;  //还没开启数据库闪回功能
create table t(a varchar2(10));
insert into t values('chen');
commit;
shutdown immediate;
startup mount;
flashback database to restore point fd_test;  //会出现闪回日志未开启的错误，Flashback database logging is not on.

2、保证还原点未启用闪回功能

alter database open;
create restore point fd_test2 guarantee flashback database;
create table t2(a varchar2(10));
insert into t2 values('guarantee');
commit;
shutdown immediate;
startup mount;
flashback database to restore point fd_test2;
alter database open read only;
select * from t2;  //如果出现表不存在说明闪回成功

3、开启闪回功能并闪回整库

shutdown immediate;
startup mount;
alter database open resetlogs;
alter system set db_flashback_retention_target=4320;  //设置闪回日志保留期
alter database flashback on;  //开启闪回功能
select retention_target, flashback_size, estimated_flashback_size from v$flashback_database_log;  //查询闪回日志信息
conn scott/tiger;
create table t3(a varchar2(20));
insert into t3 values('chenmu');
commit;
select sysdate,dbms_flashback.get_system_change_number from dual;  //查看scn号,22327231
truncate table t3;  //模拟删除表
conn / as sysdba;
shutdown immediate;
startup mount;
flashback database to SCN 22327231; 
alter database open read only;  //验证数据
select * from scott.t3;