oracle闪回 versions between 实践

oracle闪回技术，在一定范围内运用很方便，他可以直接使用sql语句对数据库数据进行挖掘，而不用去做复杂的日志挖掘或是物化视图等技术。

行级闪回需要配制以下两个参数
 
undo_management = auto
只有设置成auto才能查询到表更新记录undo_retention =900 设置表更新记录时间,单位为秒,只有在这个时间内的操作才能被闪回,10G第二版默认为900秒,9i为3600秒.
1.2行级闪回查询 
行级闪回查询有以下三种
 
--行级闪回查询
 
select a, b, c, versions_xid, versions_starttime, versions_endtime,
versions_startscn,versions_endscn,versions_operation 
from test versions between timestamp minvalue and maxvalue
where c=12; 
--行级闪回查询,查询一段时间内的变更,注意开始时间和结束时间之差小于undo_retention设置的值,否则会提示
"ORA-30052: 下限快照表达式无效" 
select a, b, c, versions_xid, versions_starttime, versions_endtime,
versions_startscn,versions_endscn,versions_operation
from test versions between timestamp 
to_date('2008-09-23 16:09:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2008-09-23 16:45:00','yyyy-mm-dd hh24:mi:ss')
where c=12; 
--行级闪回查询,根据scn值查询
 
select a, b, c, versions_xid, versions_starttime, versions_endtime,
versions_startscn,versions_endscn,versions_operation
from test versions between scn 339493 and 339635
where c=12; 
/*
其中
test
的表结构如下
 
create table TEST
( 
A V
ARCHAR2(20),
B V
ARCHAR2(40),
C NUMBER
)
*/ 
--versions_xid 更改该行的事务标识符 
--versions_startscn和versions_endscn 该时刻系统更改号
 --versions_starttime和versions_endtime 该数据的起如时间和结束时间
 
--versions_operation
表示执行了什么样操作
 
I表示insert,U表示update,D表示delete 以下功能还可以根据当前系统scn查打以前scn的数据
 
select dbms_flashback.get_system_change_number from dual;
--查询当前系统更改号
 
然后根据当前系统scn推算到以前scn,根据以前scn查询当时表里的数据
 
select * from test as of scn 404030;
--查询系统更必号404030表test时候的数据 

SQL> create table test(id number,name varchar2(10),gender varchar2(5));

表已创建。

SQL> insert into test values(1,'宋春风','男');  已创建 1 行。

SQL> insert into test values(2,'叶民','男');     已创建 1 行。

SQL> insert into test values(3,'白冰','男');     已创建 1 行。

SQL> insert into test values(4,'方巍森','男');  已创建 1 行。

SQL> insert into test values(5,'孙书祯','男');  已创建 1 行。

SQL> insert into test values(6,'史波','男');     已创建 1 行。

SQL> commit;                                            提交完成。

利用下面的SQL就可以查处最近更改的数据。

SQL> SELECT ID,NAME,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_OPERATION 

FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE 

VERSIONS_STARTTIME IS NOT NULL ORDER BY VERSIONS_STARTTIME DESC;

        ID NAME   GENDE VERSIONS_STARTTIME        VERSIONS_ENDTIME          V

---------- ------ ----- ------------------------- ------------------------- -

         6 史波   男    30-11月-11 04.02.28 下午                            I

         5 孙书祯 男    30-11月-11 04.02.28 下午                            I

         1 宋春风 男    30-11月-11 04.02.28 下午                            I

         3 白冰   男    30-11月-11 04.02.28 下午                            I

         2 叶民   男    30-11月-11 04.02.28 下午                            I

         4 方巍森 男    30-11月-11 04.02.28 下午                            I

已选择6行。

修改几条数据和接下来的查询做对比。

 

SQL> UPDATE TEST SET GENDER='女' WHERE NAME='孙书祯';     已更新 1 行。

SQL> COMMIT;      提交完成。

SQL> UPDATE TEST SET GENDER='女' WHERE NAME='史波';        已更新 1 行。

SQL> COMMIT;                                                                         提交完成。

再次查询，被修改的数据就可以看到被修改和修改前的数据。

SQL> SELECT ID,NAME,GENDER,VERSIONS_STARTTIME,VERSIONS_ENDTIME,

VERSIONS_OPERATION FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND 

MAXVALUE WHERE VERSIONS_STARTTIME IS NOT NULL ORDER BY 

VERSIONS_STARTTIME DESC;

        ID NAME   GENDE VERSIONS_STARTTIME        VERSIONS_ENDTIME          V

---------- ------ ----- ------------------------- ------------------------- -

         6 史波   女    30-11月-11 04.15.07 下午                            U

         5 孙书祯 女    30-11月-11 04.14.31 下午                            U

         4 方巍森 男    30-11月-11 04.02.28 下午                            I

         3 白冰   男    30-11月-11 04.02.28 下午                            I

         2 叶民   男    30-11月-11 04.02.28 下午                            I

         1 宋春风 男    30-11月-11 04.02.28 下午                            I

         6 史波   男    30-11月-11 04.02.28 下午  30-11月-11 04.15.07 下午  I

         5 孙书祯 男    30-11月-11 04.02.28 下午  30-11月-11 04.14.31 下午  I

已选择8行。   

再修改几条数据后后查询。

SQL> delete from test WHERE NAME='史波';            已删除 1 行。

SQL> delete from test WHERE NAME='孙书祯';         已删除 1 行。

SQL> commit;                                                      提交完成。

SQL> SELECT ID,NAME,GENDER,VERSIONS_STARTTIME,VERSIONS_ENDTIME,

VERSIONS_OPERATION FROM TEST VERSIONS BETWEEN TIMESTAMP MINVALUE AND 

MAXVALUE WHERE VERSIONS_STARTTIME IS NOT NULL ORDER BY 

VERSIONS_STARTTIME DESC;

        ID NAME   GENDE VERSIONS_STARTTIME        VERSIONS_ENDTIME          V

---------- ------ ----- ------------------------- ------------------------- -

         5 孙书祯 男    30-11月-11 04.26.02 下午                            D

         6 史波   男    30-11月-11 04.26.02 下午                            D

         6 史波   女    30-11月-11 04.15.07 下午  30-11月-11 04.19.08 下午  U

         5 孙书祯 女    30-11月-11 04.14.31 下午  30-11月-11 04.19.08 下午  U

         2 叶民   男    30-11月-11 04.02.28 下午                            I

         3 白冰   男    30-11月-11 04.02.28 下午                            I

         4 方巍森 男    30-11月-11 04.02.28 下午                            I

         5 孙书祯 男    30-11月-11 04.02.28 下午  30-11月-11 04.14.31 下午  I

         1 宋春风 男    30-11月-11 04.02.28 下午                            I

         6 史波   男    30-11月-11 04.02.28 下午  30-11月-11 04.15.07 下午  I

已选择10行。

通过以上小实验可以看出，VERSIONS_STARTTIME是数据被修改的起始时间，VERSIONS_ENDTIME是数据被修改后新数据的有效时间，也就是VERSIONS_STARTTIME和VERSIONS_ENDTIME时间段内，这条数据再没被修改过，如果VERSIONS_ENDTIME为空，就说明这天记录从VERSIONS_STARTTIME时间起再没被修改过。VERSIONS_OPERATION是修改状态，I代表INSERT，U代表UPDATE，D代表DELETE。此时如果想回滚INSERT的数据，只需要DELETE反向操作即可，如果想回滚UPDATE操作，将数据反向UPDATE回去即可，比如本实验已经可以看到进行UPDATE操作的是NAME为史波和孙书祯的两条记录，而且也可以看到进行UPDATE之前的数据他们的性别是男，所以只需要在做个反向UPDATE，将性别该为男即可实现回退，如果要回滚DELETE操作，同样做个INSERT操作，将删除的数据在插回去即可。

注：此SQL只能查询到回滚段内的信息，超出回滚段范围这个SQL就无能为力了，需要借助LOGMGR工具挖掘日志了（详见http://www.dbdream.org/?p=149）

朋友遇到了非常经典的ORACLE事故——误删除，开发人员告诉他，昨天下午五点-六点不小心误删了几条数据，问是否可以恢复，朋友的环境是ORACLE 10gR2，没有备份，但有开归档和闪回，这个是可以找回数据的。
以下为找回误删除数据的实验。

 
SQL> create table t1(id number,name varchar2(20));
Table created
SQL> insert into t1 values(1,'zhangsan');
1 row inserted
SQL> insert into t1 values(2,'zhangsi');
1 row inserted
SQL> insert into t1 values(3,'zhangwu'); 
1 row inserted
SQL>  commit;
Commit complete

删除部分数据，并记录SCN。

 
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    4354137
SQL> delete from t1 where id=3;
1 row deleted
SQL> commit;
Commit complete

创建一张大表，用于测试。

 
SQL> create table t2 as select * from dba_objects;
Table created
SQL> INSERT INTO T2 SELECT * FROM T2;
75068 rows inserted
SQL> /
150136 rows inserted 
SQL> / 
300272 rows inserted
SQL> /
600544 rows inserted

对T2表做大量的update操作，模拟回滚段被回收。

UPDATE T2 SET OWNER=OWNER,OBJECT_NAME=OBJECT_NAME,SUBOBJECT_NAME=SUBOBJECT_NAME,
OBJECT_ID=OBJECT_ID,DATA_OBJECT_ID=DATA_OBJECT_ID,OBJECT_TYPE=OBJECT_TYPE,
CREATED=CREATED,LAST_DDL_TIME=LAST_DDL_TIME,TIMESTAMP=TIMESTAMP,STATUS=STATUS,
TEMPORARY=TEMPORARY,GENERATED=GENERATED,SECONDARY=SECONDARY,
NAMESPACE=NAMESPACE,EDITION_NAME=EDITION_NAME;
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。
SQL> /
已更新1201104行。

如果回滚段足够大，此时可以查询到SCN4354137之前的信息。

SQL> select * from t1 as of scn 4354137;
ID         NAME
---------- --------------------
         1 zhangsan
         2 zhangsi
         3 zhangwu

此时可以使用闪回表技术找回数据。

SQL> flashback table t1 to scn 4354137;
闪回完成。
SQL> select * from t1;
        ID NAME
---------- --------------------
         1 zhangsan
         2 zhangsi
         3 zhangwu

如果回滚段不够大，回滚段SCN4354137之前的空间将被回收，此时将无法查询SCN4354137之前的信息。

SQL> select * from t1 as of scn 4354137; 
select * from t1 as of scn 4354137
ORA-01555: 快照过旧: 回退段号 8 (名称为 "_SYSSMU8_2456689326$") 过小

此时如果数据库开闪回，并且误删除的时间在db_flashback_retention_target参数范围内，可以利用闪回数据库技术，将整个数据库回退到之前的状态。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area  313860096 bytes
Fixed Size                  1374304 bytes
Variable Size             201328544 bytes
Database Buffers          104857600 bytes
Redo Buffers                6299648 bytes
数据库装载完毕。
SQL> flashback database to scn 4354137;
闪回完成。
SQL> alter database open resetlogs;
数据库已更改。
SQL> select * from t1;
        ID NAME
---------- --------------------
         1 zhangsan
         2 zhangsi
         3 zhangwu 

如果误删除的时间超出了db_flashback_retention_target参数的范围，可能数据库无法闪回到scn 4354137状态，即使可以闪回到误删除之前的状态，无论是闪回表还是闪回数据库，必然对scn 4354137之后的操作有影响，闪回表到scn 4354137，scn 4354137之后对这个表所做的所有操作都将回退，如果是闪回数据库，整个数据库scn 4354137之后的操作都将被回退。误删除的数据重要，误删除之后的数据也重要，这时候如果选择闪回技术就要权衡哪个更重要的问题啦，还好ORACLE自8i开始，推出了LOGMNR日志分析工具，借用 LOGMNR工具，可以在不影响其他数据的同时找回误删除的数据。
初次使用，需要安装，很简单，只需要执行以下2个脚本即可。

SQL> conn / as sysdba
已连接。
SQL> @?/rdbms/admin/dbmslm
程序包已创建。
授权成功。
同义词已创建。
SQL> @?/rdbms/admin/dbmslmd
程序包已创建。
同义词已创建。 

查看utl_file_dir设置

 
SQL> show parameter utl_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
utl_file_dir                         string      D:\oracle\arch

可以通过命令行修改此参数，也可以通过修改pfile文件设置此参数。

 
SQL> alter system set utl_file_dir='D:\oracle\arch' scope=spfile;
系统已更改。

该参数为静态参数，需重启数据库后生效。
创建LOGMNR数据字典。

 
SQL> exec dbms_logmnr_d.build(
dictionary_filename => 'dict.ora',
dictionary_location => 'd:\oracle\arch');
PL/SQL 过程已成功完成。

添加需要分析的归档日志。

 
SQL> exec dbms_logmnr.add_logfile(
LogFileName =>'D:\oracle\arch\O1_MF_1_117_79SR4KVR_.ARC',
Options => dbms_logmnr.new);
PL/SQL 过程已成功完成。

开始日志挖掘，分析日志。

 
SQL> execute dbms_logmnr.start_logmnr (
DictFileName => ’G:＼oracle＼logs＼dict.ora’);
PL/SQL procedure successfully completed

查看日志信息

 
SQL> select SCN,OPERATION,SEG_OWNER, TABLE_NAME,SEG_TYPE_NAME, 
SQL_REDO,SQL_UNDO from v$logmnr_contents where scn>5533530 
and scn<5533541 and sql_redo like'delete%'; 
SCN：4354137
OPERATION：DELETE
SEG_OWNER：STREAM
TABLE_NAME：T1
SEG_TYPE_NAME：TABLE
SQL_REDO：delete from "STREAM"."TEST01" where "ID" = '3' and "NAME" = 'zhangwu'
SQL_UNDO：insert into "STREAM"."TEST01"("ID","NAME") values ('3','zhangwu');

SQL_REDO即之前做的模拟误删除的操作，SQL_UNDO就是还原应该做的操作，ORACLE LOGMNR工具真的是很黄很暴力。

http://blog.chinaunix.net/uid-179343-id-3327370.html