欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-04 09:27:55
oracle的闪回查询、闪回表、闪回 /* 一、 要使用闪回查询,数据库必须开启automatic undo management,必须有undo表空间,必须设置好回滚段的保留时间 */...

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;