oracle的闪回查询、闪回表、闪回数据库

oracle的闪回查询、闪回表、闪回

/* 一、 要使用闪回查询，数据库必须开启automatic undo management,必须有undo表空间，必须设置好回滚段的保留时间 */

-- 在sqlplus中查看undo_management参数值是否为auto，如果是“manual”手动，需要修改为“auto”;
-- 查看回滚段的保留时间undo_retention,默认900秒（15分钟）
show parameter undo
-- 查看所有的回滚段
select segment_id ,segment_name  from dba_rollback_segs;
-- 指定事务使用某个回滚段，如果不人为的指定回滚段，则数据库会根据回滚段中事务来权衡，以使得所有回滚段中事务压力尽可能平均。
set transaction use rollback segment rbs6;
-- 修改undo_management参数值为auto
/*
oracle有个spfile动态参数文件，里面设置了oracle的各种参数。所谓的动态，就是说你可以在不关闭数据库的情况下，更改数据库参数，记录在spfile里面。
更改参数的时候，有4种scope选项,scope就是范围。
scope=spfile 仅仅更改spfile里面的记载，不更改内存，也就是不立即生效，而是等下次数据库启动生效，有一些参数只允许用这种方法更改；
scope=memory 仅仅更改内存，不改spfile，也就是下次启动就失效了；
scope=both 内存和spfile都更改；
不指定scope参数，等同于scope=both。
*/
alter system set undo_management='auto' scope=spfile;
-- 修改undo_retention为1小时
alter system set undo_retention=3600 scope=both;
-- 查看修改是否立即生效
show parameter undo
select name,value from v$spparameter where name='undo_management';
-- 重启数据库，使修改生效
shutdown immediate
startup
show parameter undo
/* 测试闪回查询 */
select * from t1 where id<10;
delete from t1 where id<10;
commit;
-- 查询15分钟之前的表数据
select * from t1 as of timestamp(sysdate - 15/1440) where id<10;
-- 将数据恢复
insert into t1  select * from t1 as of timestamp(sysdate - 15/1440) where id<10;
commit;
/* 根据时间的闪回本质上是基于scn的闪回 */
-- 将dbms_flashback的执行权限授权给scott用户
grant execute on dbms_flashback to scott;
-- 查询当前的系统改变号scn，并记录下来，2363533
select dbms_flashback.get_system_change_number from dual; 
-- 删除数据
delete from t1 where id<10;
commit;
-- 根据删除数据时间点前的scn来查询删除前的数据
select * from t1 as of scn(2363533) where id<10;
-- 将数据恢复
insert into t1  select * from t1 as of scn(2363533) where id<10;
commit;
-- 使用ora_rowscn伪列来查看与每一行关联的scn
select ora_rowscn,t1.* from t1
-- 查看scn映射的事务提交时间
select scn_to_timestamp(2363533) from dual;
-- 查看每行数据的最新事务提交时间
select scn_to_timestamp(ora_rowscn), t1.* from t1;

/* 二、闪回表 */
drop table t1;
select * from t1;
-- 删除t1表后，该表的block还在表空间中，查询回收站可以看到被删除的对象
select * from recyclebin;
-- 闪回表到删除之前
flashback table t1 to before drop;
/* 闪回表到某个时间点 */
update t1 set contents='abc';
commit;
-- 必须启用表的行移动功能
alter table t1 enable row movement;
flashback table t1 to timestamp(systimestamp - 5/1440);

/* 三、闪回数据库 */
-- 需要有sysdba权限,才能使用flashback database命令，必须以独占模式装载数据库，但不打开数据库。
-- 同时将数据库置于闪回模式，执行闪回。
startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;
-- 查看闪回模式是否开启
select current_scn, flashback_on from v$database;
shutdown;
startup mount exclusive;
-- 闪回数据库到1小时之前
flashback database to timestamp sysdate-1/24;
-- 闪回成功后，打开数据库，同时resetlogs开启对数据库的写访问权限
alter database open resetlogs;
startup mount exclusive;
alter database flashback off;
alter database open;