閃回技術通常用於快速簡單恢複資料庫中出現的認為誤操作等邏輯錯誤,從閃回的方式可以分為基於資料庫閃回、表閃回、事務閃回,根據閃回對資料的影響程度又可以分為閃回恢複,閃回查詢。閃回恢複將修改資料,閃回點之後的資料將全部丟失。而閃回查詢則可以查詢資料被DML的不同版本,也可以在此基礎之上確定是否進行恢複。
1.查看閃回是否啟用
SQL> select log_mode,open_mode,flashback_on from v$database;
LOG_MODE OPEN_MODE FLASHBACK_ON
------------ -------------------- ------------------
NOARCHIVELOG READ WRITE NO --NO表示閃回沒有開啟
SQL>
2.查看閃回目錄和閃回的大小
SQL> show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/Oracle/app/oracle/flash_
recovery_area
db_recovery_file_dest_size big integer 3852M
SQL>
3.查看閃回的生存周期
SQL> show parameter db_flashback; --預設是分鐘
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
4.開啟閃回
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1: --出現錯誤,是因為資料庫是OPEN狀態,必須修改為掛載狀態
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL> select status from v$instance; --查看是OPEN狀態
STATUS
------------
OPEN
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 661209088 bytes
Fixed Size 1338560 bytes
Variable Size 478151488 bytes
Database Buffers 176160768 bytes
Redo Buffers 5558272 bytes
Database mounted.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database archivelog; --必須啟動歸檔才可以使用閃回
Database altered.
SQL> alter database flashback on; --啟動閃回成功
Database altered.
SQL> select * from v$flashback_database_log; --查看閃回日誌
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
1361192 14-FEB-14 1440 8192000 589824
SQL> select * from v$flashback_database_stat; --查看閃回狀態
BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
14-FEB-14 14-FEB-14 16384 0 0 0
SQL> ho ls -hlt $ORACLE_BASE/flash_recovery_area/ORCL/flashback --查看閃回的日誌
total 7.9M
-rw-r----- 1 oracle oinstall 7.9M Feb 14 11:56 o1_mf_9hv4zfp5_.flb
SQL>
5.基於scn來閃回資料
SQL> select * from t1;
no rows selected
SQL> insert into t1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
2
SQL> select sysdate,timestamp_to_scn(sysdate) from dual;
SYSDATE TIMESTAMP_TO_SCN(SYSDATE)
--------- -------------------------
14-FEB-14 1385810
SQL> delete from t1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t1;
no rows selected
SQL> flashback table t1 to scn 1385810; --閃回到scn為1385810
Flashback complete.
SQL> select * from t1; --資料找回來了
A
----------
2
SQL>
6.基於快閃記憶體點來閃回資料
SQL> select * from t1;
A
----------
2
SQL> create restore point abc; --建立閃回儲存點
Restore point created.
SQL> insert into t1 values(3);
1 row created.
SQL> select * from t1;
A
----------
2
3
SQL> flashback table t1 to restore point abc; --還原到abc儲存點
Flashback complete.
SQL> select * from t1;
A
----------
2
SQL>
7.閃回表刪除(只能針對普通使用者)
SQL> drop table t1; --刪除表
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
Q BIN$8la0lsFgu07gQAICcgITjQ==$0 TABLE 2014-02-14:13:03:00
T1 BIN$8lkqPmU58tXgQAICcgIYKw==$0 TABLE 2014-02-14:14:46:26
TONG BIN$8laD8n7xgCzgQAICcgIRvg==$0 TABLE 2014-02-14:12:48:21
TONG BIN$8NsWmNwAEmLgQAICcgIUNA==$0 TABLE 2014-01-26:14:56:19
SQL> select * from t1; --表不存在
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table t1 to before drop; --恢複t1表和表中的資料(flashback table t1 to before drop rename to t2 --恢複重新命名表)
Flashback complete.
SQL> select * from t1;
A
----------
2
SQL>