Oracle 10g Flashback 技术小结 (1)-CSDN博客

概述：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。

下面就利用几个简单的例子来学习每一种功能的用法和适用情况：

//=============================================================
// 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;

[To be continued...]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9765498/viewspace-257129/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9765498/viewspace-257129/