Oracle 10g 六種Flashback

Oracle 10g 支援六種閃回功能：Flashback Database、Flashback Table、Flashback Drop、Flashback Query、Flashback version Query、Flashback Transaction Query

一、Flashback Database：（基於閃回日誌）

Oracle Flashback Database lets you quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

注：①Flashback Database不能撤銷物理損壞，而只能恢復使用者錯誤造成的邏輯錯誤。

②Flashback Database在功能上等價於不完整資料庫恢復。但是Flashback Database通常情況下比不完整恢復要快，原因在於：不完整恢復部分過程是完全還原，其時間在很大程度上與資料庫的大小成正比；而Flashback Database所需時間主要取決於需要撤銷的更改的數量。任何常規環境下，與資料庫總資料量相比，更改的資料量微不足道。

1、配置Flashback Database

（1）、database 必須處於 Archivelog

SYS@ tsid > select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

（2）、必須建立flash_recovery_area

兩個例項引數：db_recovery_file_dest、db_recovery_file_dest_size

SYS@tsid>alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';

System altered.

SYS@ tsid > alter system set db_recovery_file_dest_size=2G;

System altered.

（3）、設定flashback retention target

例項引數：DB_FLASHBACK_RETENTION_TARGET，單位分鐘，預設一天

SYS@ tsid > alter system set db_flashback_retention_target=240;

System altered.

（4）、shutdown immediate;

（5）、startup mount;

SYS@ tsid > alter database flashback on;

Database altered.

此處注意：若資料庫允許多個instance同時訪問，可能會出現錯誤提示：

ORA-38759: Database must be mounted by only one instance and not open.

此時，必須在startup mount exclusive狀態。關於startup mount exclusive，參考：http://space.itpub.net/25744374/viewspace-732248

2、使用Flashback Database

可以使用SQL*PLUB、RMAN、Database Control

方法：shutdown -> mount -> flashback database -> resetlogs

（1）、SQL*PLUS（timestamp、SCN）

SYS@ tsid > shutdown abort; /*使用任何型別的關閉無意義，都會丟失正在進行的工作*/

ORACLE instance shut down.

SYS@ tsid > startup mount;

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 180355588 bytes

Database Buffers 20971520 bytes

Redo Buffers 7098368 bytes

Database mounted.

SYS@ tsid > flashback database to timestamp to_timestamp('2012-04-17 11:00:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SYS@ tsid > alter database open resetlogs;

Database altered.

=====================================================================

SYS@ tsid > shutdown abort;

ORACLE instance shut down.

SYS@ tsid > startup mount;

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

Database mounted.

SYS@ tsid > flashback database to SCN 3030912;

Flashback complete.

注意：因為時間點很難確定，可以將資料開啟至read only模式，進行查詢，根據情況，重複執行閃回和恢復命令，直到找到希望的時間。

alter database open read only;

recover database until time ‘2012-04-17:11:02:00’;

（2）、RMAN（time、SCN、sequence）

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

RMAN> flashback database to scn=3035221;

Starting flashback at 2012-04-17 11:56:20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:15

Finished flashback at 2012-04-17 11:56:36

RMAN> alter database open resetlogs;

database opened

===============================================================

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 209715200 bytes

Fixed Size 1289724 bytes

Variable Size 176161284 bytes

Database Buffers 25165824 bytes

Redo Buffers 7098368 bytes

RMAN> flashback database to sequence=25;

Starting flashback at 2012-04-17 12:12:42

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery

....

media recovery complete, elapsed time: 00:00:28

Finished flashback at 2012-04-17 12:13:14

（3）、Database Control

Database Control 的基於時間的閃回粒度只能是分鐘，RMAN可以閃回到秒，SQL*PLUS可以閃回到時間戳——它可以是1秒的百萬分之一。

3、監視Flashback Database

（1）、閃回緩衝區不受DBA限制，但可以檢視當前閃回緩衝區的大小。

SYS@ tsid > select * from v$sgastat where name='flashback generation buff';

POOL NAME BYTES

------------ -------------------------- ----------

shared pool flashback generation buff 3981204

（2）、關閉不必要表空間的Flashback 功能，從而限制閃回資料量。

SYS@ tsid > alter tablespace test flashback off;

Tablespace altered.

要重新開啟，在mount狀態下，alter tablespace test flashback on;

通過檢視v$tablespace可以檢視錶空間閃回狀態：

SYS@ tsid > select name,flashback_on from v$tablespace;

NAME FLA

------------------------------ ---

SYSTEM YES

UNDOTBS1 YES

SYSAUX YES

USERS YES

TEST NO

TEMP YES

UNDOTBS2 YES

7 rows selected.

二、Flashback drop：（基於RECYCLEBIN）

Oracle Flashback Drop provides a way to restore accidentally dropped tables.

注：①Flashback Drop僅適用於表，但也會恢復所有相關的物件：索引、觸發器、許可權、唯一的主鍵和非空約束。但不包括外來鍵約束。

②只能閃回drop的表，不能閃回truncate的表。

③若刪除的表所佔用的空間被重用，將不能閃回。

④SYSTEM表空間的表直接刪除，而不是rename，不進回收站，不能Flashback drop。

⑤如果刪除了使用者，如：drop user scott cascade;則無法恢復任何SCOTT的表。

⑥閃回其間可以對錶重新命名，以防止以前的表名已被佔用，但是和表一同閃回的索引、觸發器、約束都保持回收站中的名稱，只能手動重新命名。

1、管理RECYCLEBIN

（1）回收站受引數recyclebin引數的影響，預設情況下，recyclebin引數為ON，說明啟用回收站，則刪除的表可以進回收站，支援Flashback drop。若recyclebin引數為OFF，則刪除的表不進回收站。

（2）查詢回收站：show recyclebin;

Select owner,owner,original_name,type,droptime,can_undrop,space from dba_recyclebin; (can_undrop列顯示是否可Flashback Drop)

（3）對回收站空間進行回收

注意：①有關空間使用情況的正常診斷程式會忽略回收站佔用的空間，這就造成在活動物件佔用的空間達到警告和臨界空間的使用級別之前，不會觸發“表空間比例已滿”的警報。因此，刪除表，DBA_FREE_SPACE顯示空閒空間，而實際上DBA_SEGMENTS將會顯示段空間仍被佔用。

②若表空間自動擴充套件，Oracle優先重寫回收站而不是擴充套件資料檔案。

SYS@ tsid > create tablespace small datafile 'E:\ORADATA\TEST\small.DBF' size 1M;

Tablespace created.

SYS@ tsid > create table t1(id number,name varchar(20),address varchar2(40)) tablespace small;

Table created.

插入資料，直到提示：ORA-01653: unable to extend table SYS.T1 by 8 in tablespace SMALL

檢視DBA_FREE_SPACE，顯示沒有可用空間。刪除表t1，再次查詢：

SYS@ tsid > select * from dba_free_space where tablespace_name='SMALL';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

SMALL 8 9 65536 8 8

SMALL 8 17 65536 8 8

SMALL 8 25 65536 8 8

SMALL 8 33 65536 8 8

SMALL 8 41 65536 8 8

SMALL 8 49 65536 8 8

SMALL 8 57 65536 8 8

SMALL 8 65 65536 8 8

SMALL 8 73 65536 8 8

SMALL 8 81 65536 8 8

SMALL 8 89 65536 8 8

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- ---------- ---------- ---------- ------------

SMALL 8 97 65536 8 8

SMALL 8 105 65536 8 8

SMALL 8 113 65536 8 8

SMALL 8 121 65536 8 8

15 rows selected.

而檢視DBA_SEGMENTS：

SYS@ tsid > select segment_name,bytes from dba_segments where tablespace_name='SMALL';

SEGMENT_NAME BYTES

--------------------------------------------------------------------------------- ----------

BIN$6qV8lQb4RHSLKm7RP376ng==$0 983040

======================================================================

清除回收站物件 purge:

Purge table

Purge index

Purge tablespace

Purge tablespace user

Purge user_recyclebin;

Purge dba_recyclebin;

三、Flashback Query（基於UNDO）

注：SYS使用者不支援！

（1）、單一查詢方式

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > delete from t where id=6;

1 row deleted.

TEST@ tsid > delete from t where id=5;

1 row deleted.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select current_scn from v$database;

CURRENT_SCN

-----------

3085265

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

TEST@ tsid > select * from t as of scn 3085250;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > select * from t as of scn 3085250 minus select * from t;

ID NAME

---------- ----------

5 e

6 f

TEST@ tsid > insert into t select * from t as of scn 3085250 minus select * from t;

2 rows created.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

==========================================================================

（2）、使用DBMS_FLASHBACK程式包，將整個會話回退到過去某個時間點。

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > select systimestamp from dual;

SYSTIMESTAMP

--------------------------------------------

17-APR-12 04.31.46.907000 PM +08:00

TEST@ tsid > delete from t where id=6;

1 row deleted.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > execute dbms_flashback.enable_at_time(to_timestamp('17-04-12 16:31:46','dd-mm-yy hh24:mi:ss'));

PL/SQL procedure successfully completed.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

6 f

6 rows selected.

TEST@ tsid > execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

四、Flashback Table

注：sys使用者不支援該閃回！

==========================================================================

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 b

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

2082114

SQL> update t set name='a' where id=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 a

SQL> select * from t as of scn 2082114;

ID NAME

---------- ----------

1 a

2 b

SQL> flashback table t to scn 2082114;

flashback table t to scn 2082114

*

第 1 行出現錯誤:

ORA-08189: 因為未啟用行移動功能, 不能閃回表

SQL> alter table t enable row movement;

表已更改。

SQL> flashback table t to scn 2082114;

閃回完成。

SQL> select * from t;

ID NAME

---------- ----------

1 a

2 b

當要閃回的表存在外來鍵約束時，可能出現問題。有兩種方法可以解決錯誤：

1、先閃回主鍵表，再閃回外來鍵表。

2、如果涉及多個表和許多DML語句，邏輯上很難找到一個生效的順序。解決方法是一起閃回這兩個表。如：

Flashback table emp,dept to timestamp to_timestamp(’17-04-12 16:30:00’,’dd-mm-yy hh24:mi:ss’);

Oracle在一個事務中同時閃回兩個表，並且僅在該事務的結尾檢查約束，此時資料庫在邏輯上是一致的。所以這樣閃回會成功。

五、Flashback version Query

注：Flashback version Query不能用於外部表、臨時表、V$檢視，這些物件不生成undo

select versions_xid,versions_operation,versions_startscn,

versions_starttime,versions_endscn,versions_endtime,id,name

from t versions between scn minvalue and maxvalue;

六、Flashback Transaction Query

注：①查詢檢視FLASHBACK_TRANSACTION_QUERY必須具有SELECT ANY TRANSACTION許可權。

②使用Flashback Transaction Query，需要開啟supplemental logging：

SYS@ tsid > alter database add supplemental log data;

Database altered.

③檢視FLASHBACK_TRANSACTION_QUERY可以返回commit和active事務。官網對其中兩個欄位這樣描述：

COMMIT_SCN	NUMBER		Transaction commit system change number; NULL for active transactions
COMMIT_TIMESTAMP	DATE		Transaction commit timestamp; NULL for active transactions

TEST@ tsid > select * from t

2 ;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

TEST@ tsid > update t set name='f' where id<>1;

4 rows updated.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 f

3 f

4 f

5 f

TEST@ tsid > select id,name,versions_xid from t versions between timestamp (systimestamp - 3/1440) and systimestamp where id<

>1;

ID NAME VERSIONS_XID

---------- ---------- ----------------

4 f 0900280048030000

3 f 0900280048030000

2 f 0900280048030000

2 b

3 c

4 d

5 f 0900280048030000

5 e

8 rows selected.

TEST@ tsid > col undo_sql format a90

TEST@ tsid > select operation,undo_sql from flashback_transaction_query where xid=hextoraw('0900280048030000');

OPERATION UNDO_SQL

-------------------------------- --------------------------------------------------------------------------------

UPDATE update "TEST"."T" set "NAME" = 'e' where ROWID = 'AAADB0AAEAAAAAmAAA';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'd' where ROWID = 'AAADB0AAEAAAAAkAAD';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'c' where ROWID = 'AAADB0AAEAAAAAkAAC';

UNKNOWN

UPDATE update "TEST"."T" set "NAME" = 'b' where ROWID = 'AAADB0AAEAAAAAkAAB';

UNKNOWN

BEGIN

9 rows selected.

TEST@ tsid > update "TEST"."T" set "NAME" = 'e' where ROWID = 'AAADB0AAEAAAAAmAAA';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'd' where ROWID = 'AAADB0AAEAAAAAkAAD';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'c' where ROWID = 'AAADB0AAEAAAAAkAAC';

1 row updated.

TEST@ tsid > update "TEST"."T" set "NAME" = 'b' where ROWID = 'AAADB0AAEAAAAAkAAB';

1 row updated.

TEST@ tsid > commit;

Commit complete.

TEST@ tsid > select * from t;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

5 e

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25744374/viewspace-721460/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 10g 六種Flashback

相關文章