oracle–闪回操作
闪回概念:
当发生逻辑错误时,从闪回恢复区中取出在发生逻辑错误之前的数据,并进行恢复。
闪回作用:
1 撤销已经提交的事务;
2 恢复被删除的表格;
3 获取表上的历史记录信息;
闪回分类:
1 闪回表中的数据;
2 闪回删除的表;
3 闪回事务查询;
4 闪回数据库;
5 闪回归档日志;
下面阐述前三类闪回技术:
一 闪回表中的数据
实际上是将表中的数据快速恢复到过去的某一个时间点或者SCN(系统改变号)。
举例:
create table stu (ID number,ename varchar2(10));--创建表格
--插入数据并提交
insert into stu values(1,'李磊');
insert into stu values(2,'韩梅梅');
insert into stu values(3,'王华');
commit;
select timestamp_to_scn(sysdate) from dual;---查询当前SCN:2277892
delete from stu where ID=3;---删除数据;
commit;
flashback table stu to scn 2277892 ;
--错误提醒:ORA-08189: 因为未启用行移动功能, 不能闪回表。
alter table stu enable row movement;--开启行移动功能
flashback table stu to scn 2277892 ;--再次执行闪回;闪回成功。
此刻,用于实现delete误删的数据恢复。
要点说明:
1 必须开启行移动功能;
2 必须知道SCN或者需要将数据恢复的时间点;
3 用户在闪回的时候权限不足,需要给用户授权:
grant flashback any table to scott;--必须在管理员用户下才可以授权。
知识点引申:
1 如何将具体时间转化成SCN?
select timestamp_to_scn(to_date('2020-04-09,13:25:59','yyyy-mm-dd,hh24:mi:ss')) scn from dual;
2 如何将SCN转化为具体时间?
select scn_to_timestamp(2163746) from dual;
二 闪回删除的表格
将已经被删除的表格进行恢复。
举例:
create table student(id number,ename varchar2(10))--创建表
drop table student;--删除表格;
show recyclebin;--查看回收站;
--结果如下:
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
STUDENT BIN$c3w1vZNnTo65BXfdITKzcw==$0 TABLE 2020-04-10:16:22:22
create table student(id number,ename varchar2(10));--再次创建表
drop table student;--再次删除表格;
show recyclebin;--再次查看回收站;
--结果如下:
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
STUDENT BIN$ohSu47LKQqSgXgXRXYr8yg==$0 TABLE 2020-04-10:16:25:47
STUDENT BIN$c3w1vZNnTo65BXfdITKzcw==$0 TABLE 2020-04-10:16:22:22
现将表格进行恢复;
flashback table student to before drop;--闪回成功
show recyclebin;--再次查看回收站;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
STUDENT BIN$c3w1vZNnTo65BXfdITKzcw==$0 TABLE 2020-04-10:16:22:22
说明:可以发现,如果回收站里面的两个表格名字相同,通过flashback闪回的表是最近删除的表;
当然,可以进行选择性闪回;
--因为之前有名字为“student”表格,所以需要恢复并重命名;
flashback table "BIN$c3w1vZNnTo65BXfdITKzcw==$0" to before drop rename to stu;
注意:默认情况下,闪回的是最近删除的同名表;
清空回收站:purge recyclebin;
删除表格并清空:drop table stu purge;
三 闪回事务查询
举例:
create table tb_version(id number,name varchar2(10));
insert into tb_version values(12,'a1');
insert into tb_version values(13,'a2');
insert into tb_version values(14,'a3');
commit;
insert into tb_version values(25,'a4');
insert into tb_version values(26,'a5');
insert into tb_version values(27,'a6');
commit;
insert into tb_version values(38,'a1');
insert into tb_version values(39,'a1');
insert into tb_version values(30,'a1');
commit;
--bug:如果插入数据且commit数据速度太快,可能会导致事务信息丢失。
select id,name,versions_xid,versions_operation,versions_starttime,versions_endtime from tb_version
versions between timestamp minvalue and maxvalue;
ID NAME VERSIONS_XID V VERSIONS_STARTTIME VERSIONS_ENDTIME
-- ---------- ---------------- - --------------------------------------------------------------------------- -------------------
----------------------------------
30 a1 0900150031050000 I 10-4月 -20 08.02.15 下午
39 a1 0900150031050000 I 10-4月 -20 08.02.15 下午
38 a1 0900150031050000 I 10-4月 -20 08.02.15 下午
27 a6 0700130092040000 I 10-4月 -20 08.02.06 下午
26 a5 0700130092040000 I 10-4月 -20 08.02.06 下午
25 a4 0700130092040000 I 10-4月 -20 08.02.06 下午
12 a1
13 a2
14 a3
OPERATION
--------------------------------
UNDO_SQL
---------------------------------------------------------------------------
---------------------------------------------------------------------------
INSERT
delete from "SCOTT"."TB_VERSION" where ROWID = 'AAASYUAAEAAAAIlAAF';
INSERT
delete from "SCOTT"."TB_VERSION" where ROWID = 'AAASYUAAEAAAAIlAAE';
INSERT
delete from "SCOTT"."TB_VERSION" where ROWID = 'AAASYUAAEAAAAIlAAD';
BEGIN
--需要授权:
grant select any transaction to scottt;
--开启权限第一步:alter database add supplemental log data。
--执行undo_sql之后,即可恢复状态。