來源:http://blog.roodo.com/mywork/archives/1684525.html
Oracle Flashback Database特性允許通過SQL語句Flashback
Database語句，讓數據庫前滾到當前的前一個時間點或者SCN，而不需要做時間點的恢復。flashback可以迅速將數據庫回到誤操作或人為錯誤
的前一個時間點，可減少解決問題所花費的時間。
OS:windows 2000
Oracle:10g
跟flashback有關的參數:OS:windows 2000
Oracle:10g
開起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 |
| 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 |
如有相同的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 |
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'; |
