DM7閃回與閃回查詢
閃回
當使用者操作不慎導致錯誤的刪改資料時，非常希望有一種簡單快捷的方式可以恢復資料。閃回技術，就是為了使用者可以迅速處理這種資料邏輯損壞的情況而產生的。
閃回技術主要是透過回滾段儲存的UNDO記錄來完成歷史記錄的還原。設定ENABLE_FLASHBACK為1後，開啟閃回功能。DM會保留回滾段一段時間，回滾段保留的時間代表著可以閃回的時間長度。由UNDO_RETENTION引數指定。
SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK'); LINEID SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK') ---------- --------------------------------------- 1 0 used time: 204.313(ms). Execute id is 62. SQL> select sf_get_para_value(1,'UNDO_RETENTION'); LINEID SF_GET_PARA_VALUE(1,'UNDO_RETENTION') ---------- ------------------------------------- 1 90 used time: 6.236(ms). Execute id is 63.
下面修改動態引數ENABLE_FLASHBACK,scope=1同時修改記憶體和dm.ini檔案
SQL> call sp_set_para_value(1,'ENABLE_FLASHBACK',1); DMSQL executed successfully used time: 13.216(ms). Execute id is 64. SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK'); LINEID SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK') ---------- --------------------------------------- 1 1 used time: 5.703(ms). Execute id is 65.
回滾段保留的時間預設值為90秒，我們要修改它為1天
SQL> select sf_get_para_value(1,'UNDO_RETENTION'); LINEID SF_GET_PARA_VALUE(1,'UNDO_RETENTION') ---------- ------------------------------------- 1 90 used time: 6.236(ms). Execute id is 63. SQL> call sp_set_para_value(1,'UNDO_RETENTION',86400); DMSQL executed successfully used time: 7.155(ms). Execute id is 74. SQL> select sf_get_para_value(1,'UNDO_RETENTION'); LINEID SF_GET_PARA_VALUE(1,'UNDO_RETENTION') ---------- ------------------------------------- 1 86400 used time: 5.877(ms). Execute id is 75.
開啟閃回功能後，DM會在記憶體中記錄下每個事務的起始時間和提交時間。透過使用者指定的時刻，查詢到該時刻的事務號，結合當前記錄和回滾段中的UNDO記錄，就可以還原出特定事務號的記錄。即指定時刻的記錄狀態。從而完成閃回查詢。閃回查詢功能完全依賴於回滾段管理，對於DROP等誤操作不能恢復。閃回特性可應用在以下方面：
1. 自我維護過程中的修復：當一些重要的記錄被意外刪除，使用者可以向後移動到一個時間點，檢視丟失的行並把它們重新插入現在的表內恢復；
2. 用於分析資料變化：可以對同一張表的不同閃回時刻進行連結查詢，以此檢視變化的資料。
閃回查詢
當系統INI引數ENABLE_FLASHBACK置為1時，閃回功能開啟，可以進行閃回查詢。MPP環境不支援閃回查詢。
SQL> select sf_get_para_value(1,'ENABLE_FLASHBACK'); LINEID SF_GET_PARA_VALUE(1,'ENABLE_FLASHBACK') ---------- --------------------------------------- 1 1 used time: 5.703(ms). Execute id is 65. [root@shard1 jydm]# strings dm.ini | grep flash ENABLE_FLASHBACK = 1 #Whether to enable flashback function
閃回查詢子句
閃回查詢子句的語法，是在資料查詢語句（參考第4章）的基礎上，為FROM子句增加了閃回查詢子句。
語法格式
< 閃回查詢子句>::=WHEN |
引數
1．time_exp 一個日期表示式，一般用字串方式表示
2．trxid 指定事務ID號
語句功能
使用者透過閃回查詢子句，可以得到指定表過去某時刻的結果集。指定條件可以為時刻，或事務號
使用說明
1．閃回查詢只支援普通表（包括加密表與壓縮表）、臨時表和堆表，不支援水平分割槽表、垂直分割槽表、列儲存表、外部表與檢視；
2．閃回查詢中trxid的值，一般需要由閃回版本查詢（見下節）的偽列來確定。實際使用中多采用指定時刻的方式。
例1閃回查詢特定時刻的PERSON_TYPE表。
查詢PERSON_TYPE表。
SQL> SELECT * FROM PERSON.PERSON_TYPE; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 used time: 15.321(ms). Execute id is 78.
在插入資料之前記錄時間，在閃回查詢時使用
SQL> select sysdate;
LINEID SYSDATE
---------- ---------------------------
1 2019-12-01 23:34:12.761683
used time: 0.480(ms). Execute id is 79.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('防損員');
affect rows 1
used time: 0.615(ms). Execute id is 80.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('保潔員');
affect rows 1
used time: 0.562(ms). Execute id is 81.
SQL> commit;
executed successfully
used time: 16.237(ms). Execute id is 82.
SQL> SELECT * FROM PERSON.PERSON_TYPE;
LINEID PERSON_TYPEID NAME
---------- ------------- --------
1 1 採購經理
2 2 採購代表
3 3 銷售經理
4 4 銷售代表
5 7 防損員
6 8 保潔員
6 rows got
used time: 0.626(ms). Execute id is 83.
使用閃回查詢取得2019-12-01 23:34:12時刻的資料。此時刻在插入資料的操作之前，可見此時的結果集不應該有2019-12-01 23:34:12時刻以後插入的資料。
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23:34:12'; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 used time: 1.416(ms). Execute id is 84.
在2019-12-01 23:39:26時刻刪除資料，並提交。
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2019-12-01 23:39:26.865328 used time: 0.580(ms). Execute id is 85. SQL> DELETE FROM PERSON.PERSON_TYPE WHERE PERSON_TYPEID > 5; affect rows 2 used time: 1.797(ms). Execute id is 86. SQL> commit; executed successfully used time: 19.834(ms). Execute id is 87. SQL> SELECT * FROM PERSON.PERSON_TYPE; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 5 防損員 used time: 0.691(ms). Execute id is 88.
使用閃回查詢得到刪除前的資料。
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23:39:26'; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 5 防損員 6 6 保潔員 6 rows got used time: 1.262(ms). Execute id is 89.
閃回查詢指定TRXID的PERSON_TYPE表。
要獲得TRXID資訊，需要透過閃回版本查詢的偽列VERSIONS_ENDTRXID。
在2019-12-01 23:45:27 時刻修改資料，並提交。
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2019-12-01 23:48:27.983996 used time: 0.509(ms). Execute id is 90. SQL> SELECT * FROM PERSON.PERSON_TYPE; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 防損員 6 10 保潔員 6 rows got used time: 0.515(ms). Execute id is 97. SQL> UPDATE PERSON.PERSON_TYPE SET NAME='保安員' WHERE PERSON_TYPEID=9; affect rows 1 used time: 1.460(ms). Execute id is 99. SQL> commit; executed successfully used time: 16.640(ms). Execute id is 100. SQL> UPDATE PERSON.PERSON_TYPE SET NAME='收銀員' WHERE PERSON_TYPEID=9; affect rows 1 used time: 1.851(ms). Execute id is 101. SQL> commit; executed successfully used time: 16.781(ms). Execute id is 102. SQL> SELECT * FROM PERSON.PERSON_TYPE; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 收銀員 6 10 保潔員 6 rows got used time: 0.516(ms). Execute id is 103.
進行閃回版本查詢，確定TRXID。
SQL> SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-01 23:48:27.983996' AND SYSDATE; LINEID VERSIONS_ENDTRXID NAME ---------- -------------------- -------- 1 NULL 採購經理 2 NULL 採購代表 3 NULL 銷售經理 4 NULL 銷售代表 5 NULL 收銀員 6 NULL 保潔員 7 749195 保安員 7 rows got used time: 1.384(ms). Execute id is 104.
根據TRXID確定版本。
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749195; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 保安員 6 10 保潔員 6 rows got used time: 1.261(ms). Execute id is 105.
第二次更新的事務ID為749195,那麼第一次更新的事務ID為749194
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749194; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 防損員 6 10 保潔員 6 rows got
閃回版本查詢
語法格式
< 閃回版本查詢子句>::=VERSIONS BETWEEN |
引數
1．time_exp 日期表示式，一般用字串方式表示。time_exp1表示起始時間，time_exp2表示結束時間
2. trxid 指定事務ID號，整數表示。trxid1表示起始trxid，trxid2表示結束trxid
使用說明
1．閃回版本查詢支援普通表（包括加密表與壓縮表）、臨時表和堆表，不支援水平分割槽表、垂直分割槽表、列儲存表、外部表與檢視；
2．支援偽列，作為閃回版本查詢的輔助資訊。
偽列 說明
VERSIONS_START{TRXID|TIME} 起始TRXID或時間戳
VERSIONS_END{TRXID|TIME} 提交TRXID或時間戳。如果該值為NULL，表示行版本仍然是當前版本
VERSIONS_OPERATION 在行上的操作（I=Insert,D=Delete,U=Update）
語句功能
使用者透過閃回版本查詢子句，可以得到指定表過去某個時間段內，事務導致記錄變化的全部記錄。指定條件可以為時刻，或事務號。
例1 閃回版本查詢指定時間段內，PERSON_TYPE表的記錄變化
SQL> select sysdate; LINEID SYSDATE ---------- --------------------------- 1 2019-12-02 00:00:18.221877 used time: 0.662(ms). Execute id is 107.
在2019-12-02 00:00:18時刻修改資料，並提交。
SQL> UPDATE PERSON.PERSON_TYPE SET NAME='打字員' WHERE PERSON_TYPEID=9; affect rows 1 used time: 1.758(ms). Execute id is 110. SQL> commit; executed successfully used time: 16.964(ms). Execute id is 111. SQL> SQL> UPDATE PERSON.PERSON_TYPE SET NAME='衛生員' WHERE PERSON_TYPEID=9; affect rows 1 used time: 1.268(ms). Execute id is 112. SQL> commit; executed successfully used time: 15.983(ms). Execute id is 113. SQL> SELECT * FROM PERSON.PERSON_TYPE; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 衛生員 6 10 保潔員 6 rows got used time: 0.669(ms). Execute id is 114.
進行閃回版本查詢，獲得指定時間段內變化的記錄。
SQL> SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-02 00:00:17' AND SYSDATE; LINEID VERSIONS_ENDTRXID NAME ---------- -------------------- -------- 1 NULL 採購經理 2 NULL 採購代表 3 NULL 銷售經理 4 NULL 銷售代表 5 NULL 衛生員 6 NULL 保潔員 7 749197 打字員 7 rows got used time: 1.412(ms). Execute id is 115.
第二次更新的事務ID為749197
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749197; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 打字員 6 10 保潔員 6 rows got used time: 1.371(ms). Execute id is 120.
第一次更新的事務ID為749196
SQL> SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749196; LINEID PERSON_TYPEID NAME ---------- ------------- -------- 1 1 採購經理 2 2 採購代表 3 3 銷售經理 4 4 銷售代表 5 9 收銀員 6 10 保潔員 6 rows got used time: 0.585(ms). Execute id is 121.
閃回事務查詢
閃回事務查詢提供系統檢視V$FLASHBACK_TRX_INFO供使用者檢視在事務級對資料庫所做的更改。根據檢視資訊，可以確定如何還原指定事務或指定時間段內的修改。
使用說明
系統檢視名為V$FLASHBACK_TRX_INFO，定義如下所示。
SQL> desc V$FLASHBACK_TRX_INFO LINEID NAME TYPE$ NULLABLE ---------- ---------------- ------------- -------- 1 START_TRXID BIGINT Y 事務中第一個DML的TRXID 2 START_TIMESTAMP DATETIME(6) Y 事務中第一個DML的時間戳 3 COMMIT_TRXID BIGINT Y 提交事務的TRXID 4 COMMIT_TIMESTAMP DATETIME(6) Y 提交事務時的時間戳 5 LOGIN_USER VARCHAR(256) Y 擁有事務的使用者 6 UNDO_CHANGE# INTEGER Y 記錄修改順序序號 7 OPERATION CHAR(1) Y DML操作型別 D：刪除；U：修改；I：插入；N：更新插入（專門針對CLUSTER PRIMARY KEY的插入）；C：事務提交；P：預提交記錄；O：default 8 TABLE_NAME VARCHAR(256) Y DML 修改的表 9 TABLE_OWNER VARCHAR(256) Y DML修改表的擁有者 10 ROW_ID BIGINT Y DML修改行的ROWID 11 UNDO_SQL VARCHAR(3900) Y 撤銷DML操作的SQL語句 11 rows got used time: 88.801(ms). Execute id is 122.
查詢指定時間之後的事務資訊，可為閃回查詢操作提供參考
SQL> SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > '2019-12-01 23:13:28'; LINEID START_TRXID START_TIMESTAMP COMMIT_TRXID COMMIT_TIMESTAMP LOGIN_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL ---------- -------------------- --------------------------- -------------------- --------------------------- ---------- ------------ --------- ---------- ----------- -------------------- -------- 1 749189 2019-12-01 23:13:28.000000 749190 2019-12-01 23:33:57.588000 SYSDBA 16 C NULL NULL NULL NULL used time: 1.437(ms). Execute id is 126.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2666400/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- DM8 閃回查詢
- Oracle 11G 閃回技術 使用Oracle閃回事務查詢Oracle
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- 2.6.3 指定閃回區
- [20180423]表空間閃回與snapshot standby
- Oracle資料庫閃回Oracle資料庫
- flashback query閃回資料
- my2sql資料閃回SQL
- 詳解oracle資料庫閃回Oracle資料庫
- 【趙強老師】MySQL的閃回MySql
- 【PDB】pdb閃回，Oracle還原點Oracle
- Oracle 閃回資料庫測試Oracle資料庫
- mysql閃回工具binlog2sqlMySql
- Oracle閃回技術--Flashback Version QueryOracle
- 工具分享丨資料閃回工具MyFlash
- 【RECO_ORACLE】Oracle閃回PDB的方法Oracle
- Flashback Drop閃回刪除功能實踐
- dg_閃回資料庫實驗資料庫
- Orcale利用閃回功能恢復資料
- [20180423]關於閃回表與主外來鍵約束.txt
- Oracle資料庫閃回區空間不足Oracle資料庫
- Oracle回收站表閃回機制研究Oracle
- MySQL工具之binlog2sql閃回操作MySql
- 【Flashback】Flashback Drop閃回刪除功能實驗
- 一個非常老但是很有用的功能-閃回
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- 利用binlog2sql閃回丟失資料SQL
- rac使用預設閃回區歸檔空間滿
- [20180419]關於閃回的一些問題.txt
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- Oracle閃回技術 概覽 應用程式開發功能Oracle
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- MySQL使用binlog2sql閃回誤刪除資料MySql
- MySQL 覆蓋索引、回表查詢MySql索引
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫