闪回操作

ORACLE的闪回查询 delete操作后

 as of TIMESTAMP 和as of SCN

查询该表一分钟前的数据

select * from  abc as of TIMESTAMP SYSDATE-1/1440;

查询该表一分钟前的数据

select * from  abc as of SCN 16014177255;

通过这种方式,我们可以将数据回滚到以前某个状态,比如误删除并且commit后.可以通过闪回方式进行数据的恢复.

比如:

delete  from abc;

commit;

insert into abc select * from  abc as of TIMESTAMP SYSDATE-1/1440;

commit;

2.误删除数据的情况下

FLASHBACK TABLE b to timestamp to_timestamp('2007-08-28 11:14:00','yyyy-mm-dd hh24:mi:ss'); //我试过，可以

 FLASHBACK TABLE cc
   TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '01' minute);//我也史过可以，而且这种好一点，闪回一分钟以前的数据。
在这种误删除数据的情况下，应该事先激活表的行移动特征，如：
ALTER TABLE emp ENABLE ROW MOVEMENT;

闪回表：drop 操作后

SELECT object_name, original_name FROM user_recyclebin;

SHOW recyclebin

 desc "BIN$LS3MyjLQRcXgQAB/AQA8Ow==$0"

FLASHBACK TABLE tablename TO BEFORE DROP;

下面是一篇转载的文章:

Oracle Flashback

Oracle Flashback Database特性允许通过SQL语句Flashback Database语句，让数据库前滚到当前的前一个时间点或者SCN，而不需要做时间点的恢复。flashback可以迅速将数据库回到误操作或人为错误的前一个时间点，可减少解决问题所花费的时间。

OS:windows 2000
Oracle:10g

 

跟flashback有关的参数:

开起flashback语法

SQL>alter database flashback on;

查看放flash空间的地方和容量

SQL> show parameter recover; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string D:\oracle\product\10.2.0/flash_recovery_area db_recovery_file_dest_size big integer 2G

note:(修改大小值，容量大小跟flahsback的时间有关系)

SQL> alter system set db_recovery_file_dest_size=3G; System altered SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 3G

 

---

建立测试帐号

SQL> create user flashtest identified by flashtest; 已建立使用者. SQL> grant connect,resource to flashtest; 顺利授权. SQL> grant execute on dbms_flashback to flashtest; 顺利授权. SQL> grant dba to flashtest; 顺利授权. SQL> conn flashtest/flashtest; 已连线.

 

---

flashback drop

建立测试table

SQL> create table testdrop7(id number); Table created

建立几笔资料

SQL> insert into testdrop7 values (1); 1 row inserted SQL> insert into testdrop7 values (2); 1 row inserted

 

SQL> commit; Commit complete

 

SQL> drop table testdrop7; Table dropped

查寻回收区是否有刚被删除的资料

SQL> select object_name,original_name,operation,type,droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME ------------------------------ ---------------------- ------------------------- ------------------- --------------------- BIN$4ltRoED1RaCHZglxktu9kA==$0 TESTDROP DROP TABLE 2006-05-29:17:07:48 BIN$V9qmi5hUQDKcTBMbfJkk5A==$0 TESTDROP2 DROP TABLE 2006-05-29:17:43:37 BIN$0SEStB60QhC4+hULXLkNOA==$0 TESTDROP3 DROP TABLE 2006-05-29:17:51:58 BIN$rezTtmaDQ9qn2jJEy28jdg==$0 TESTDROP4 DROP TABLE 2006-05-29:17:59:05 BIN$WXp0b1+7Qfea9YaMLw8aHA==$0 TESTDROP6 DROP TABLE 2006-05-29:18:23:16 BIN$A2ADdSwnRX2Ib5CXFe1iuQ==$0 TESTDROP5 DROP TABLE 2006-05-29:18:23:18 BIN$7lPg3xr3RFyemQ5cmCq6KA==$0 TESTDROP7 DROP TABLE 2006-05-30:12:08:34 7 rows selected

救回刚才被误杀的table

SQL> flashback table testdrop7 to before drop ; Done

note:
如有相同的table名字的话，可改用下面语法
flashback table "BIN$A2ADdSwnRX2Ib5CXFe1iuQ==$0" to before drop;

查寻是否真的救回来了

SQL> select * from testdrop7; ID ---------- 1 2

 

---

Flashback Table

以时间回复:
先开起table的flashback的功能

SQL> alter table testdrop7 enable row movement; Table altered

note:如没有做这行的话，会出现错误讯习
ORA-08189: 未启用资料列移动, 因此无法倒溯表格

现在testdrop7里有5笔资料

SQL> select * from testdrop7; ID ---------- 1 2 3 4 5

增加二笔资料

SQL> insert into testdrop7 values (6); 1 row inserted SQL> insert into testdrop7 values (7); 1 row inserted SQL> commit; Commit complete

查寻一下现在时间

SQL> select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 ------------------------------ 2006-05-30 14:21:43

再增一笔资料准备测试还原

SQL> insert into testdrop7 values (8); 1 row inserted SQL> commit; Commit complete

现在的时间

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH2 ------------------------------ 2006-05-30 14:22:53

还原整个table到未加到数字8之前的状态

SQL> flashback table testdrop7 to timestamp to_timestamp('2006-05-30 14:21:43','yyyy-mm-dd hh24:mi:ss'); Done

确时还原了

SQL> select * from testdrop7; ID ---------- 1 2 3 4 5 6 7 7 rows selected

以scn值回复

查寻现在testdrop7里的资料

SQL> select * from testdrop7; ID ---------- 1 2 3 4 5 6 7 7 rows selected

假装误删一笔资料，并且commit了

SQL> delete from testdrop7 where id =7; 1 row deleted SQL> commit; Commit complete

现在testdrop7里确时只有6笔了

SQL> select * from testdrop7; ID ---------- 1 2 3 4 5 6 6 rows selected

有关scn值就是记录在这个table里

SQL> desc flashback_transaction_query; Name Type Nullable Default Comments ---------------- -------------- -------- ------- ----------------------------------------- XID RAW(8) Y Transaction identifier START_SCN NUMBER Y Transaction start SCN START_TIMESTAMP DATE Y Transaction start timestamp COMMIT_SCN NUMBER Y Transaction commit SCN COMMIT_TIMESTAMP DATE Y Transaction commit timestamp LOGON_USER VARCHAR2(30) Y Logon user for transaction UNDO_CHANGE# NUMBER Y 1-based undo change number OPERATION VARCHAR2(32) Y forward operation for this undo TABLE_NAME VARCHAR2(256) Y table name to which this undo applies TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies ROW_ID VARCHAR2(19) Y rowid to which this undo applies UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo

查寻最后的scn值为何

SQL> select start_scn, to_char(start_timestamp,'yyyy-mm-dd hh24:mi:ss') as s_time, commit_scn, to_char(commit_timestamp,'yyyy-mm-dd hh24:mi:ss') as c_time, table_name, undo_sql from flashback_transaction_query where table_name='TESTDROP7' order by commit_timestamp; START_SCN S_TIME COMMIT_SCN C_TIME TABLE_NAME UNDO_SQL --------------- ------------------------- ------------------ --------------------- ------------- ----------------------------------- 680553 2006-05-30 12:32:05 680731 2006-05-30 12:32:08 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAC'; 685412 2006-05-30 14:04:47 685415 2006-05-30 14:04:53 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAE'; 685412 2006-05-30 14:04:47 685415 2006-05-30 14:04:53 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAD'; 686429 2006-05-30 14:20:51 686432 2006-05-30 14:20:57 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAG'; 686429 2006-05-30 14:20:51 686432 2006-05-30 14:20:57 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAF'; 686506 2006-05-30 14:22:25 686507 2006-05-30 14:22:25 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAEAAAAHNAAH'; 686574 2006-05-30 14:24:00 686576 2006-05-30 14:24:00 TESTDROP7 delete from "FLASHTEST"."TESTDROP7" where ROWID = 'AAAM1tAAAAAAAAJGg+'; 686574 2006-05-30 14:24:00 686576 2006-05-30 14:24:00 TESTDROP7 insert into "FLASHTEST"."TESTDROP7"("ID") values ('8'); 686574 2006-05-30 14:24:00 686576 2006-05-30 14:24:00 TESTDROP7 insert into "FLASHTEST"."TESTDROP7"("ID") values ('7'); 686963 2006-05-30 14:31:58 686965 2006-05-30 14:32:01 TESTDROP7 insert into "FLASHTEST"."TESTDROP7"("ID") values ('7'); 10 rows selected

现在table还没有数值7

SQL> select * from testdrop7; ID ---------- 1 2 3 4 5 6 6 rows selected

输入最后的scn值，进行还原

SQL> flashback table testdrop7 to scn 686963; Done SQL> select * from testdrop7; ID ---------- 1 2 3 4 5 6 7 7 rows selected

 

---

Flash Version Query
Oracle Flashback Version Query特性，利用保存的回滚信息，可以看到特定的表在时间段内的任何修改，如电影的回放一样，可以了解表在该期间的任何变化

建立测试用table

SQL> create table testdrop10 (id number,text varchar(10)); Table created

新增测试资料

SQL> insert into testdrop10 values (1,'a'); 1 row inserted SQL> commit; Commit complete

查寻一下现在时间跟scn值，以利后面查寻方便

SQL> select current_scn,to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS') from v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAMP,'YYYY-MM- ----------- ------------------------------ 691931 2006-05-30 15:51:08

做一些dml的动作

SQL> update testdrop10 set text='b' where id=1; 1 row updated SQL> commit; Commit complete SQL> delete testdrop10 where id=1; 1 row deleted SQL> commit; Commit complete SQL> insert into testdrop10 values (1,'c'); 1 row inserted SQL> commit; Commit complete

 

SQL> SELECT versions_startscn,to_char(versions_starttime,'hh24:mi:ss') as s_time, 2 versions_endscn,to_char(versions_endtime,'hh24:mi:ss') as e_time, 3 versions_xid, versions_operation, 4 text 5 FROM testdrop10 6 VERSIONS BETWEEN TIMESTAMP 7 TO_TIMESTAMP('2006-05-30 15:51:08', 'YYYY-MM-DD HH24:MI:SS') 8 AND TO_TIMESTAMP('2006-05-30 15:53:30', 'YYYY-MM-DD HH24:MI:SS') 8 WHERE ID=1 9 ORDER BY versions_starttime; VERSIONS_STARTSCN S_TIME VERSIONS_ENDSCN E_TIME VERSIONS_XID VERSIONS_OPERATION TEXT ----------------- -------- --------------- -------- ---------------- ---------------------------- ---------- 691964 15:51:58 691975 15:52:13 060016007A010000 U b 691975 15:52:13 0A00250079010000 D b 691992 15:52:40 0400240078010000 I c 691964 15:51:58 a

note:如时间乱打的话会出现下面错误

SQL> SELECT versions_startscn,to_char(versions_starttime,'hh24:mi:ss') as s_time, 2 versions_endscn,to_char(versions_endtime,'hh24:mi:ss') as e_time, 3 versions_xid, versions_operation, 4 text 5 FROM testdrop10 6 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2006-05-30 15:51:08', 'YYYY-MM-DD HH24:MI:SS') 7 AND TO_TIMESTAMP('2006-05-30 15:54:00', 'YYYY-MM-DD HH24:MI:SS') 8 WHERE ID=1 9 ORDER BY versions_starttime; SELECT versions_startscn,to_char(versions_starttime,'hh24:mi:ss') as s_time, versions_endscn,to_char(versions_endtime,'hh24:mi:ss') as e_time, versions_xid, versions_operation, text FROM testdrop10 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2006-05-30 15:51:08', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2006-05-30 15:54:00', 'YYYY-MM-DD HH24:MI:SS') WHERE ID=1 ORDER BY versions_starttime ORA-08186: 指定的时戳无效

 

---

Flashback Transaction Query

在上线的环境中是不可能去追踪每个事务，想要获得已提交事务的XID，可由Flashback Version Query或直接查寻flashback_transaction_query这个table。

SQL> select * from testdrop10; ID TEXT ---------- ---------- 1 c SQL> delete from testdrop10 where id=1; 1 row deleted

在commit之前先查一下xid值

SQL> select xid from v$transaction; XID ---------------- 0500180092010000 SQL> commit; Commit complete

 

SQL> select undo_sql from flashback_transaction_query where xid='0500180092010000'; insert into "FLASHTEST"."TESTDROP10"("ID","TEXT") values ('1','c');

直接查寻flashback_transaction_query

SQL> desc flashback_transaction_query; Name Type Nullable Default Comments ---------------- -------------- -------- ------- ----------------------------------------- XID RAW(8) Y Transaction identifier START_SCN NUMBER Y Transaction start SCN START_TIMESTAMP DATE Y Transaction start timestamp COMMIT_SCN NUMBER Y Transaction commit SCN COMMIT_TIMESTAMP DATE Y Transaction commit timestamp LOGON_USER VARCHAR2(30) Y Logon user for transaction UNDO_CHANGE# NUMBER Y 1-based undo change number OPERATION VARCHAR2(32) Y forward operation for this undo TABLE_NAME VARCHAR2(256) Y table name to which this undo applies TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies ROW_ID VARCHAR2(19) Y rowid to which this undo applies UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo

 

SQL> select undo_sql from flashback_transaction_query where table_name='SYSDROP1';
delete from "SYS"."SYSDROP1" where ROWID = 'AAAM1sAABAAAO16AAB';
delete from "SYS"."SYSDROP1" where ROWID = 'AAAM1sAABAAAO16AAA';