oracle10g flashback 分如下：Flashback Database， Flashback Drop，Flashback Query和Flashback Table （注flashback：不支持sys 用戶system表空間下的對象）
實驗一：(Flashback Database)
Flashback Database（功能相當於rman的不完全恢復）
1）配置兩個參數： 一個是大小（DB_RECOVERY_FILE_DEST_SIZ），一個是位置
（DB_RECOVERY_FILE_DEST）
安裝10g 此兩個參數默認如下：
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest ='/u01/app/fbdb' scope=both;
System altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/fbdb
db_recovery_file_dest_size big integer 2G
2）啟用flashback 功能;
數據庫mount狀態
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1219016 bytes
Variable Size 104859192 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted
3)檢查flashback_on 參數是關閉還是開啟的 ，默認是關閉的。
SQL> select name,flashback_on from v$database ;
NAME FLASHBACK_ON
------------------------- ------------------------------------
RMANDB NO
啟動flashback_on 到on
SQL> alter database flashback on ;
Database altered.
SQL> select name,flashback_on from v$database ;
NAME FLASHBACK_ON
------------------------- ------------------------------------
RMANDB YES
4)設置參數db_flashback_retention_target （目的：控制flashback log數據保留的時間）默認是：1440minute(24h)
SQL> show parameter db_flashback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
alter system set t db_flashback_retention_target=1440 scope=both;
設置完成可以啟動數據庫
SQL> alter database open ;
Database altered.
到此設置完成。（數據庫必須在歸檔模式下 ）
設置歸檔很簡單 把數據庫置mount 狀態 用alter database archivelog；
5）操作實例如下
1.刪除表18:10:03 SQL> select count(*) from scott.tt;
COUNT(*)
----------
197
18:13:30SQL> drop table scott.tt;
Table dropped
18:14:35 SQL> commit;
Commit complete
2.Flashback Database 需要數據庫在mount狀態
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
18:17:03 startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1219016 bytes
Variable Size 109053496 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
3.執行恢復
18:19:18 SQL> Flashback database to timestamp to_timestamp('2012-02-08 18:00:10','yyyy-mm-dd
18:19:57 2 hh24:mi:ss');
Flashback complete.
4.打開數據庫
18:20:17 SQL> alter database open resetlogs;
Database altered.
5.查看結果
18:21:05 SQL> select count(*)
18:21:22 2 from scott.tt;
COUNT(*)
----------
197
18:21:23 SQL>
實驗二：(Flashback Query)閃回查詢
說明：Flashback Query適合9i以上的版本（恢復insert update）
Flashback Query只和undo_retention（單位是s）undo_management 和 flashback on/off 以及recyclebin on/off沒有關係
18:36:51 SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
18:37:23 SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
18:37:33 SQL> select count(*) from scott.tt;
COUNT(*)
----------
197
18:44:23 SQL> delete from scott.tt;
197 rows deleted.
18:44:35 SQL> commit;
Commit complete.
18:44:37 SQL> select count(*) from scott.tt;
COUNT(*)
----------
0
區當前數據庫的SCN號
select dbms_flashback.get_system_change_number from dual
或用select * from v$archived_log
建立恢復數據表8:55:05 SQL> create table scott.te as select * from scott.tt where 1=0;
Table created.
恢復數據庫
18:57:17 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
660207
18:57:22 SQL> select count(*)
18:57:36 2 from scott.tt as of scn 660207 ;
COUNT(*)
----------
0
18:57:37 SQL> select first_change#,next_change# from v$archived_log;
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
502070 502376
502376 512408
512408 534997
534997 555397
555397 559789
559726 561664
562446 562516
562516 562545
0 0
562705 2.8147E+14
0 0
FIRST_CHANGE# NEXT_CHANGE#
------------- ------------
561478 564744
564744 595699
595699 629408
629408 651618
651618 658945
629408 651618
595699 629408
18 rows selected.
18:57:43 SQL> select count(*)
18:57:51 2 from scott.tt as of scn 651618 ;
COUNT(*)
----------
197
18:59:22 SQL> Insert into scott.te select *
18:59:45 2 from scott.tt as of scn 651618
18:59:45 3 ;
197 rows created.
Commit;
Insert into scott.tt select *
from scott.te
Insert into scott.tt select *
from scott.te
commit
再把te的數據insert到tt中就ok了！
實驗三：(Flashback drop)
說明：Flashback drop適合10G以上的版本
用於恢復用戶誤刪除的對象（表，索引 約束 觸發器 等于recyclebin有關）
把刪除的對象放到回收站（相當於windows的回收站），但不支持sys 用戶system表空間下的對象放在回收站。
show parameter recyc
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer
0
recyclebin string
on
1. 刪除表
2. 10:42:15 SQL> conn scott/tiger
3. Connected.
4. 10:47:04 SQL> select count(*) from te;
5.
6. COUNT(*)
7. ----------
8. 197
9.
10.10:47:13 SQL> drop table te;
11.
12.Table dropped.
13.
14.10:47:21 SQL> commit
15.10:47:24 2 ;
16.
17.Commit complete
2.查看recyclebin信息
10:47:24 SQL> col origninal_name format a15;
10:47:36 SQL> col type format a15;
10:47:39 SQL> select object_name,original_name,type from user_recyclebin;
OBJECT_NAME
------------------------------------------------------------
ORIGINAL_NAME TYPE
---------------------------------------------------------------- ---------------
BIN$uIAH9hZd1C7gQAB/AQAQhA==$0
TE TABLE
10:50:54 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TE BIN$uIAH9hZd1C7gQAB/AQAQhA==$0 TABLE 2012-02-09:10:47:21
3.將刪除的表恢復（閃回）
conn scott/tiger
Connected.
10:50:34 SQL> show recycel
SP2-0158: unknown SHOW option "recycel"
10:50:54 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TE BIN$uIAH9hZd1C7gQAB/AQAQhA==$0 TABLE 2012-02-09:10:47:21
10:51:04 SQL> select * from te;
select * from te
*
ERROR at line 1:
ORA-00942: table or view does not exist
10:51:45 SQL> flashback table te to before drop ;
Flashback complete.
10:52:02 SQL> select count(*) from te;
COUNT(*)
----------
197
10:52:09 SQL> show recyclebin
10:52:22 SQL>
10:53:57 SQL> select object_name,original_name,type from user_recyclebin;
no rows selected
說明：
如果出現表te表刪除后，一個同名的對象te（表後者索引）被創建，閃回的時候需要重新命名
flashback table te to before drop rename to te1
如果同名的表被多次drop到recyclebi中，這個時候要遵循先進先出的原則。
如果同名的表被多次drop到recyclebi中，這個時候要遵循先進先出的原則。
在recyclebin 參數設置為off時，不支持 flashbacktable drop
10:58:57 SQL> conn /as sysdba
Connected.
10:59:03 SQL> alter system set recyclebin=off;
System altered.
10:59:22 SQL> conn /as sysdba
Connected.
10:59:30 SQL> show parameter recycle
NAME TYPE
------------------------------------ ---------------
VALUE
------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer
0
recyclebin string
OFF
10:59:38 SQL> conn scott/tiger
Connected.
10:59:45 SQL> drop table te;
Table dropped.
10:59:59 SQL> select count(*) from te;
select count(*) from te
*
ERROR at line 1:
ORA-00942: table or view does not exist
11:00:09 SQL> select object_name,original_name,type from user_recyclebin;
no rows selected
11:00:21 SQL>
實驗四：(Flashback table)
這個實驗很簡單 ，與它相關的參數是表需要row movement
11:10:49 SQL> select row_movement from user_tables where table_name='TT';
ROW_MOVEMENT
----------------
DISABLED
11:10:54 SQL>
不支持移動。
設置row movement
11:16:05 SQL> conn scott/tiger
Connected.
11:20:25 SQL> select row_movement from user_tables where table_name='T2';
ROW_MOVEMENT
----------------
DISABLED
11:20:27 SQL> select count(*) from t2;
COUNT(*)
----------
197
11:20:45 SQL> conn /as sysdba
Connected.
11:21:07 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
659440
11:21:08 SQL> conn scott/tiger
Connected.
11:21:16 SQL> delete from t2;
197 rows deleted.
11:21:22 SQL> commit;
Commit complete.
11:21:47 SQL> flashback table t2 to scn 659440;
flashback table t2 to scn 659440
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
11:22:32 SQL> alter table t2 enable row movement;
Table altered.
11:22:53 SQL> select count(*) from t2;
COUNT(*)
----------
0
11:23:36 SQL> flashback table t2 to scn 659440;
Flashback complete.
11:23:41 SQL> select count(*) from t2;
COUNT(*)
----------
197
Flashback table 命令支持同時操作的多個表，表名中間一逗號分開，如果有一個失敗替，這個語句就執行不下去：flashback table a,b ,c to scn 1103864;
