(轉)oracle10g新特性之 flashback

<wbr> <wbr> <wbr> 1、flashback drop

<wbr> <wbr> <wbr> 目前，Oracle10g為了加快使用者錯誤操作的恢復，提供了flashback drop新特性。

<wbr> <wbr> <wbr> flashback drop功能可以允許你從當前資料庫中恢復一個被drop了的物件，在執行drop操作時，現在Oracle不是真正刪除它，而是將該物件自動將放入回收 站。“回收站”我們可以理解為一個虛擬的容器，它用來存放所有被刪除的物件。在回收站中，被刪除的物件將佔用建立時的同樣的空間，你甚至還可以對已經刪除 的表查詢，也可以利用flashback功能來恢復它， 這個就是flashback drop功能。

<wbr> <wbr> <wbr> 回收站內的相關資訊我們可以從recyclebin /user_recyclebin/dba_recyclebin等檢視中獲取，或者透過SQL*Plus的show recyclebin 命令檢視。

例：

SQL> create table myemp as select * from emp

表建立.

<wbr>

SQL> <wbr> drop table myemp

表刪除.

<wbr>

SQL> show recyclebin

ORIGINAL NAME <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>RECYCLEBIN NAME <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> OBJECT TYPE <wbr> DROP TIME

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

MYEMP <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> BIN$3oMpfWHfR6q1ccyEgXwBvg==$0 TABLE <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2008-05-17:09:42:54

<wbr>

-----------------------閃回還原

SQL> flashback table myemp to before drop

<wbr> 2 <wbr> /

<wbr>

閃回完成。

<wbr>

SQL> select empno ,ename ,job,sal deptno from myemp where deptno=20

2 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> /

3 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>

4 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>EMPNO ENAME <wbr> <wbr> <wbr> <wbr> <wbr> JOB <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> DEPTNO

5 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> ---------- ---------- --------- ----------

6 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>7369 SMITH <wbr> <wbr> <wbr> <wbr> <wbr> CLERK <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 800

7 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>7566 JONES <wbr> <wbr> <wbr> <wbr> <wbr> MANAGER <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 2975

8 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>7788 SCOTT <wbr> <wbr> <wbr> <wbr> <wbr> ANALYST <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3000

9 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>7876 ADAMS <wbr> <wbr> <wbr> <wbr> <wbr> CLERK <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1100

10 <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 7902 FORD <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> ANALYST <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 3000

<wbr>

<wbr> <wbr> <wbr> 注意：使用"purge recyclebin"可以清除回收站中的所有物件。

<wbr> <wbr> <wbr> 同時，您也可以透過purge user_recyclebin或purge dba_recyclebin來清除不同的回收站物件。透過PURGE TABLESPACE TSNAME，PURGE TABLESPACE TSNAME USER USERNAME命令來選擇清除回收站。

<wbr> <wbr> <wbr> 假如需要徹底刪除一個表，不想放到回收站中，可以在drop語句中增加purge選項，例如：drop table tablename purge

<wbr> <wbr> <wbr> 註釋：您需要注意的是sysdba的Drop操作不會被記錄，Oracle從不推薦使用者用sysdba身份來建立使用者物件。

SQL> drop table keep_scn;
Table dropped
SQL> flashback table keep_scn to before drop;

Done
SQL> drop table keep_scn;

Table dropped
改變恢復後的表名 rename to
SQL> flashback table keep_scn to before drop rename to keep_scnback;

Done

<wbr>

<wbr> <wbr> 2、flashback database

<wbr> <wbr> <wbr> 如果想啟動FLASHBACK DATABASE的功能，您必須在MOUNT模式下，執行alter database <wbr> flashback on命令。或者是alter tablespace tsname flashback on，資料庫將採集falshback log，如果需要關閉該功能，則修改On為OFF。
<wbr> <wbr> <wbr> 如果想執行flashback database命令，你可以在兩種方式下執行：RMAN與SQLPLUS。

SQL >flashback database to time to_date(xxx);
SQL >flashback database to time TO_TIMESTAMP (xxx);
SQL >flashback database to scn xxx
SQL >flashback database to sequence xxx thread 1
SQL>flashback database to timestamp(sysdate-1/24)

<wbr> <wbr> <wbr> 我們下面看看具體的例子說明

SQL>shutdown immediate;--立即關閉資料庫和例項

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount; --啟動例項並載入資料庫
ORACLE instance started.
Total System Global Area <wbr> <wbr>113246208 bytes
Fixed Size <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 787708 bytes
Variable Size <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>87030532 bytes
Database Buffers <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>25165824 bytes
Redo Buffers <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>262144 bytes
Database mounted.

SQL> alter database flashback on;--開啟閃回功能
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
可以看到，flashback還必須要歸檔的保證

SQL> alter database archivelog;--設為歸檔模式
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;--開啟資料庫
Database altered.

SQL> archive log list; --檢視當前模式
Database log mode <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>Archive Mode
Automatic archival <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> Enabled
Archive destination <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence <wbr> <wbr> <wbr> <wbr>13
Next log sequence to archive <wbr> <wbr>15
Current log sequence <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr>15
<wbr> <wbr> <wbr> 經過以上步驟，我們確保了flashback database的功能，我們還可以發現，10g的自動歸檔是不需要手工干預的，只要開啟了歸檔，就是自動歸檔了，歸檔區也在DB_RECOVERY_FILE_DEST引數指定的地點。

透過如下的查詢
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
<wbr> <wbr> <wbr> Session altered.
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME <wbr>FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1135440 2008-05-17 15:20:54

<wbr> <wbr> <wbr> 我們可以知道，可以前滾恢復到的最早的SCN與時間點是多少，如果沒有確保flashback database，該檢視將沒有查詢結果。

<wbr> <wbr> <wbr> 我們建立三個一樣的表
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from t1;
Table created.
SQL> create table t3 as select * from t1;
Table created.
SQL> set time on;
SQL> select sysdate from dual;
SYSDATE
-------------------
2008-05-17 15:29:33

<wbr> <wbr> <wbr> 我們確定了一個時間點
<wbr> <wbr> <wbr> 現在，我們分別truncate一個表與drop一個表，模擬誤操作。

SQL> set time on;
15:30:10 SQL> truncate table t2;
Table truncated.
15:30:43 SQL> drop table t3;
Table dropped.
<wbr> <wbr> <wbr> 沒有備份，我們利用flashback來恢復資料庫到2008-05-17 15:29:33時間點。

15:39:02 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

15:39:31 SQL> startup mount exclusive
ORACLE instance started.

15:41:19 SQL> FLASHBACK DATABASE TO timestamp(to_date(2008-05-17 15:29:33','yyyy-mm-dd hh24:mi:ss'));
Flashback complete.

<wbr> <wbr> <wbr> 之後，我們可以用 ALTER DATABASE OPEN READ ONLY 來檢查結果是否正確，如果滿足結果，則可以用resetlog來正式啟動資料庫，注意，一旦resetlogs之後，將不能再flashback的resetlogs之前的時間點。

15:41:32 SQL> alter database open resetlogs;
Database altered.
我們現在查詢那三個表
15:42:10 SQL> select count(*) from t1;
<wbr> <wbr>COUNT(*)
----------
<wbr> <wbr> <wbr> <wbr>47708
15:42:47 SQL> select count(*) from t2;
<wbr> <wbr>COUNT(*)
----------
<wbr> <wbr> <wbr> <wbr>47708
15:42:50 SQL> select count(*) from t3;
<wbr> <wbr>COUNT(*)
----------
<wbr> <wbr> <wbr> <wbr>47708

<wbr> <wbr> <wbr> 發現truncate的記錄或者是drop的表都存在，現在資料庫已經前滾到前一個時間點了。

15:44:56 SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM $FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
<wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> <wbr> 1136169 2008-05-17 15:41:50
<wbr> <wbr> <wbr> 同時發現資料庫的最早的flashback的時間與scn都回到resetlog的時間點了。

測試如下：

Microsoft Windows [版本 5.2.3790]
(C) 版權所有 1985-2003 Microsoft Corp.

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 4月 2 16:53:38 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys as sysdba;
輸入口令:
已連線到空閒例程。
SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 92275368 bytes
Database Buffers 192937984 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
SQL>
SQL>
SQL>
SQL> alter database flashback on;

資料庫已更改。

SQL> alter database open
2 ;

資料庫已更改。

SQL> set timing on;
SQL>
SQL>
SQL>
SQL> select 'ff' from dual;

'F
--
ff

已用時間: 00: 00: 00.00
SQL>
SQL>
SQL>
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 88081064 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
SQL>
SQL> FLASHBACK DATABASE TO timestamp(to_date(2009-04-02 16:58:49','yyyy-mm-dd hh
24:mi:ss'));
ERROR:
ORA-01756: 引號內的字串沒有正確結束


已用時間: 00: 00: 00.00
SQL> FLASHBACK DATABASE TO timestamp(to_date('2009-04-02 16:58:49','yyyy-mm-dd
hh24:mi:ss'));

閃回完成。

已用時間: 00: 00: 08.14
SQL> alter database open resetlog;
alter database open resetlog
*
第 1 行出現錯誤:
ORA-02288: 無效的 OPEN 模式


已用時間: 00: 00: 00.01
SQL> alter database open resetlogs;

資料庫已更改。

已用時間: 00: 00: 48.68
SQL>
SQL>
SQL>
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL>
SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 92275368 bytes
Database Buffers 192937984 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
SQL>
SQL>
SQL> FLASHBACK DATABASE TO timestamp(to_date('2009-04-02 16:59:37','yyyy-mm-dd
hh24:mi:ss'));

閃回完成。

已用時間: 00: 00: 09.56
SQL> alter database open resetlogs;

資料庫已更改。

已用時間: 00: 00: 40.96
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL>
SQL>
SQL>
SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 96469672 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
資料庫裝載完畢。
SQL> FLASHBACK DATABASE TO timestamp(to_date('2009-04-02 16:59:29','yyyy-mm-dd
hh24:mi:ss'));

閃回完成。

已用時間: 00: 00: 11.10
SQL> alter database open resetlogs;

資料庫已更改。

已用時間: 00: 00: 37.63
SQL>