Oracle 10g Flashback 技術小結(zt)

概述：Flashback 在Oracle 10g中提供了更強大的功能。它包括了table level的Flashback Query, Flashback Version Query, Flashback Transaction Quey, Flashback Table and Flashback Drop 和database level的Flashback database。

其中，Flashback Query, Flashback Version Query, Flashback Transaction Quey 和Flashback Table 是基於undo data的，而Flashback Drop 是基於recycle bin的。

Flashback database 需要Flashback logs 和 Archivelog mode 還有Flash recovery area。

[@more@]

下面就利用幾個簡單的例子來學習每一種功能的用法和適用情況：

//=============================================================
// Flashback Query
//=============================================================

//先刪除一條資料，後用Flashback Query查詢到刪除的紀錄，重新插入。

SQL> show user
USER is "JOHN"
SQL> select * from namelist;

NAME
--------------------
test
test
AfterCreation
John
Jack
Jeff
Guarantee

7 rows selected.

SQL> select sysdate from dual;

SYSDATE
------------
22-APR-08

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2008-04-22 13:36:07

SQL> delete from namelist where name='Jeff';

1 row deleted.

SQL> select * from namelist;

NAME
--------------------
test
test
AfterCreation
John
Jack
Guarantee

6 rows selected.

SQL> commit;

Commit complete.

SQL> select * from namelist;

NAME
--------------------
test
test
AfterCreation
John
Jack
Guarantee

6 rows selected.

SQL>
SQL>
SQL> select * from namelist as of timestamp to_timestamp('2008-4-22 13:36:00','YYYY-MM-DD HH24:MI:SS');

NAME
--------------------
test
test
AfterCreation
John
Jack
Jeff
Guarantee

7 rows selected.

SQL> select * from namelist as of timestamp to_timestamp('2008-4-22 13:36:00','YYYY-MM-DD HH24:MI:SS') where name='Jeff';

NAME
--------------------
Jeff

SQL> insert into namelist (select * from namelist as of timestamp to_timestamp('2008-4-22 13:36:00','YYYY-MM-DD HH24:MI:SS') where name='Jeff');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
AfterCreation
John
Jack
Guarantee

7 rows selected.

SQL>

//=============================================================
//Flashback Version Query
//=============================================================

//利用versions_startscn等偽列(pseudo column)來查詢歷史版本的資料。

SQL> SET LINESIZE 999
SQL> SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 name
5 FROM namelist
6 VERSIONS BETWEEN SCN
7 513833
8 AND 514453;

//查詢結果略去...

SQL> SELECT versions_operation,name from namelist VERSIONS BETWEEN SCN 513833 AND 514453;

V NAME
- --------------------
U IFULEU
test
test
Jeff
John
Jack
Damnit

7 rows selected.

SQL> select scn_to_timestamp(513833) from dual;

SCN_TO_TIMESTAMP(513833)
---------------------------------------------------------------------------
23-APR-08 08.03.20.000000000 PM

SQL> select scn_to_timestamp(514453) from dual;

SCN_TO_TIMESTAMP(514453)
---------------------------------------------------------------------------
23-APR-08 08.11.47.000000000 PM

//利用SCN轉換成Timestamp，用Timestamp也查詢了一次

SQL>
SQL> SELECT versions_operation,name from namelist VERSIONS BETWEEN TIMESTAMP
2 TO_TIMESTAMP('2008-04-23 20:03:20','YYYY-MM-DD HH24:MI:SS')
3 AND TO_TIMESTAMP('2008-04-23 20:11:47','YYYY-MM-DD HH24:MI:SS');

V NAME
- --------------------
U IFULEU
test
test
Jeff
John
Jack
Guarantee
Damnit

8 rows selected.

SQL> SELECT versions_operation,name from namelist VERSIONS BETWEEN TIMESTAMP
2 TO_TIMESTAMP('2008-04-23 17:00:00','YYYY-MM-DD HH24:MI:SS')
3 AND TO_TIMESTAMP('2008-04-23 20:11:47','YYYY-MM-DD HH24:MI:SS');
SELECT versions_operation,name from namelist VERSIONS BETWEEN TIMESTAMP
*
ERROR at line 1:
ORA-30052: invalid lower limit snapshot expression
//這個是我在實驗過程中遇到的一個錯誤，搞了半天老是報這個錯誤，後來官方文件讀了很久才意識到可能是undo_retention出了問題。察看了一下，只有
900，立馬改成10800...後先用scn試了一下，可以後把scn轉換成timestamp又試了一下，終於可以了...這個東西可真難用啊!!!

SQL>alter system set undo_retention=10800 scope=both;

SQL> show parameter undo_retention;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800

//=============================================================
//Flashback Transaction Query//=============================================================

//具體就是查詢flashback_transaction_query這張表，由於輸出資訊太多，格式化麻煩，就引用文件上的例子了... 詳細資訊參考OracleDatabase Application Developer's Guide - Fundamentals 10g Release 2 (10.2)的Chapter 10 --Developing Flashback Applications

SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
FROM flashback_transaction_query
WHERE table_owner = 'HR' AND
start_timestamp >=
TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS');

XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER
---------------- --------- ---------- --------- ---------- -----------
0004000700000058 195245 195246 UPDATE EMP HR
0004000700000058 195245 195246 UPDATE EMP HR
0004000700000058 195245 195246 INSERT EMP HR
000200030000002D 195243 195244 DELETE EMP HR
000200030000002D 195243 195244 INSERT DEPT HR
000200030000002D 195243 195244 UPDATE EMP HR

SQL> column table_name format a80
SQL> set linesize 200
SQL> select TABLE_NAME,OPERATION from flashback_transaction_query where TABLE_OWNER='JOHN';

TABLE_NAME OPERATION
-------------------------------------------------------------------------------- --------------------------------
TESTBIN INSERT
BIN$ARR9XqE5RFS528GS3J1g4g==$0 INSERT
BIN$ARR9XqE5RFS528GS3J1g4g==$0 INSERT
BIN$ARR9XqE5RFS528GS3J1g4g==$0 INSERT
TESTBIN INSERT

SQL>

//Initialization Parameter related to Flashback: DB_FLASHBACK_RETENTION_TARGET -- Default value 1440 (minutes),specifies the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the flash recovery area.

//Database dictionary tables related to Flashback: FLASHBACK_TRANSACTION_QUERY,V$FLASHBACK_DATABASE_LOG,V$FLASHBACK_DATABASE_STAT(but only delete,insert, update,B,unknow are kept in V$FLASHBACK_DATABASE_STAT.OPERATION. Drop and truncate are not.)

//=============================================================
// Flashback Table-- Returning Individual Tables to Past States
//=============================================================

//By default, the database disables triggers on the affected table before performing a FLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled). If you wish for triggers on a table to apply during FLASHBACK TABLE, then add an ENABLE TRIGGERS clause to the FLASHBACK TABLE statement:
FLASHBACK TABLE table_name TO TIMESTAMP timestamp ENABLE TRIGGERS;

//===================Basing on SCN==============================

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
AfterCreation
John
Jack
Guarantee

7 rows selected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
451948

SQL>
SQL> delete from namelist where name='Jack';

1 row deleted.
SQL> commit;

Commit complete.

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
AfterCreation
John
Guarantee

6 rows selected.

SQL> flashback table namelist to scn 451948;
flashback table namelist to scn 451948
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
//Should enable row movement first!!!

SQL> alter table namelist enable row movement;

Table altered.

SQL> flashback table namelist to scn 451948;

Flashback complete.

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
John
Jack
Guarantee
AfterCreation

7 rows selected.

//=============Basing on timestamp===============================
//Note: The mapping of timestamps to SCNs is not always exact. When using timestamps with the FLASHBACK TABLE statement, the actual point in time to which the table is flashed back can vary by up to approximately three seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, use an SCN rather than a time expression.

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-04-22 14:03:44

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
John
Jack
Guarantee
AfterCreation

7 rows selected.

SQL> delete from namelist where name='John';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
Jack
Guarantee
AfterCreation

6 rows selected.

SQL> flashback table namelist to timestamp to_timestamp('2008-04-22 14:03:44','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select * from namelist;

NAME
--------------------
test
test
Jeff
John
Jack
Guarantee
AfterCreation

7 rows selected.

SQL>

//=============================================================
//Flashback Drop-- Undo a DROP TABLE Operation
//=============================================================

SQL> select * from testbin;

NAME
--------------------
haha

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
453094

SQL>
SQL> drop table testbin;

Table dropped.

SQL>
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTBIN BIN$vR97iYk8TkmzFF4IZtOiNA==$0 TABLE 2008-04-22:14:27:49
SQL>
SQL> select * from 'BIN$vR97iYk8TkmzFF4IZtOiNA==$0';
select * from 'BIN$vR97iYk8TkmzFF4IZtOiNA==$0'
*
ERROR at line 1:
ORA-00903: invalid table name
//Note the use of quotes, due to the possibility of special characters appearing in therecycle bin object names. So you have to use double quotation mark here!!

SQL> select * from "BIN$vR97iYk8TkmzFF4IZtOiNA==$0";

NAME
--------------------
haha

SQL> set linesize 100
SQL> desc "BIN$vR97iYk8TkmzFF4IZtOiNA==$0";

//You can manipulate the table as it is not dopped~~~

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NAME VARCHAR2(20)

SQL>
SQL> flashback table testbin to scn 453094; //Try to use Flashback Table to flashback the dropped table but failed.
flashback table testbin to scn 453094
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table "BIN$vR97iYk8TkmzFF4IZtOiNA==$0" to before drop;

Flashback complete.

SQL>
SQL> select * from testbin;

NAME
--------------------
haha

SQL>

//You can assign a new name to the restored table by specifying the RENAME TO clause.
For example:FLASHBACK TABLE john.testbin TO BEFORE DROP RENAME TO john.testbin2;