Oracle已經提供了非常完善的資料備份恢復措施，從9i開始，針對一些常見場景下的小規模資料恢復需求，Oracle推出了一系列的Flashback技術。

Flashback中文稱為“閃回”，Oracle的閃回技術並不是一個單獨技術，而是根據不同的恢復粒度而推出的一系列資料快速恢復技術。更重要的是，各個Flashback技術雖然名稱相同或者相似，但底層依賴的技術還是存在很大的差異。

在筆者之前的系列中，針對flashback query、flashback table和flashback archive等進行過比較詳細的介紹。Flashback Database是Flashback家族中恢復粒度最大的專案，本篇介紹如何利用Flashback Database實現資料表級別資料恢復。

1、環境和前提條件介紹

筆者使用Oracle 11gR2進行實驗，具體版本為11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

資料庫執行在歸檔模式下，配置Fast Recovery Area實行自動管理。

SQL> show parameter recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/fast_recovery_area

db_recovery_file_dest_size big integer 10000M

recovery_parallelism integer 0

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 21

Current log sequence 21

此時，系統保持預設的配置專案，不開啟flashback database特性。

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO

SQL> show parameter flashback

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

注意：引數db_flashback_retention_target是控制flashback database的重要引數專案。該引數在11g中預設為1440，單位是分鐘，也就是摺合24小時。Flashback Database是基於在recovery area中單獨建立的Flashback Database Log。如果對一些業務處理量比較大且頻繁，變化比較大的資料庫開啟較長的Retention Target時間，需要確保Fast Recovery Area的大小足夠大。

2、啟動Flashback Database

開啟Flashback Database，需要正常關閉資料庫，之後啟動到mount狀態。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 306187524 bytes

Database Buffers 58720256 bytes

Redo Buffers 6176768 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

啟動資料庫到open狀態。

SQL> alter database open;

Database altered.

Flashback資訊和Flashback能恢復到的最早時間，可以透過檢視檢視到。

SQL> select current_scn, flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON

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

1350667 YES

--最早時間和SCN號

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE

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

1350306 19-六月-15 13:33:30 1440 131072000 220200960

Recovery Area中的Flashback Database Log，如下：

SQL> select * from v$flashback_database_logfile;

NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME TYPE

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

/u01/app/fast_recovery_area/ORA11G/flashback/o1_mf_br7bf8xq_.flb 1 1 1 65536000 1350353 19-六月-15 NORMAL

/u01/app/fast_recovery_area/ORA11G/flashback/o1_mf_br7bfdjj_.flb 2 1 1 65536000 0 RESERVED

3、故障發生模擬

下面模擬故障場景，使用者test下面有若干段物件。

SQL> select owner, tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;

OWNER TABLESPACE_NAME COUNT(*)

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

TEST TESTTBL 5

誤刪除幾張資料表。

SQL> drop table test.emp;

Table dropped

SQL> drop table test.dept;

Table dropped

此時資料庫SCN和時間資訊如下：

SQL> select current_scn, flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON

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

1351835 YES

SQL> select sysdate a from dual;

A

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

19-六月-15 13:54:20

4、恢復操作

下面進行過去test.emp和test.dept資料的尋找。首先，關閉資料庫，進行Flashback閃回資料庫到未發生故障時間點。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 289410308 bytes

Database Buffers 75497472 bytes

Redo Buffers 6176768 bytes

Database mounted.

直接在mount狀態閃回到過去時間點，之後以read only（注意：這個很重要，關係能否回到原點狀態）開啟資料庫。

SQL> flashback database to timestamp to_date('2015-6-19 13:35:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> alter database open read only;

Database altered.

登入閃回版本資料庫，檢視被刪除資料表是否存在。

SQL> conn sys/xxx@ora11g as sysdba

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Connected as SYS

SQL> select owner, tablespace_name, count(*) from dba_segments where owner='TEST' group by owner, tablespace_name;

OWNER TABLESPACE_NAME COUNT(*)

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

TEST TESTTBL 5

下面就清晰很多，可以以資料表（Schema也可以）為目標，匯出資料。

[oracle@SimpleLinux ~]$ expdp \"/ as sysdba\" dumpfile=test_part.dmp tables=test.emp,test.dept

Export: Release 11.2.0.4.0 - Production on Fri Jun 19 14:07:38 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1038

ORA-16000: database open for read-only access

資料泵（Datapump）顯然是不行，因為expdp/impdp是工作在後臺的資料工具，啟動作業起碼需要建立一張作業主表。這個對於只讀狀態資料庫顯然不行。

退而求其次，使用exp工具，小巧簡單。

[oracle@SimpleLinux ~]$ exp \"/ as sysdba\" file=test_part.dmp tables=test.emp,test.dept

Export: Release 11.2.0.4.0 - Production on Fri Jun 19 14:22:33 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Current user changed to TEST

. . exporting table EMP 14 rows exported

. . exporting table DEPT 4 rows exported

Export terminated successfully without warnings.

注意：現在已經成功分離出被誤刪除的資料，下面可以將資料庫恢復到故障恢復點狀態。此時，資料庫依然是Read Only狀態。

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

注意：雖然現在Flashback到一箇舊的版本，但是由於並沒有發生讀寫操作（Read Only）。所以原有資料庫的歸檔日誌依然是可用的，當前資料庫依然是在一個原有的執行路徑上。所以，才存在透過“應用”archive redo log，將資料恢復到原有狀態的機會。

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 372449280 bytes

Fixed Size 1364732 bytes

Variable Size 289410308 bytes

Database Buffers 75497472 bytes

Redo Buffers 6176768 bytes

Database mounted.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

將exp匯出的檔案資料表，匯入到資料庫中。

[oracle@SimpleLinux ~]$export NLS_LANG=american_america.al32utf8

[oracle@SimpleLinux ~]$ imp \"/ as sysdba\" file=scott_part.dmp fromuser=test touser=test

Import: Release 11.2.0.4.0 - Production on Fri Jun 19 14:27:49 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing TEST's objects into TEST

. . importing table "EMP" 14 rows imported

. . importing table "DEPT" 4 rows imported

About to enable constraints...

Import terminated successfully without warnings.

[oracle@SimpleLinux ~]$

確認資料情況。

SQL> select owner, tablespace_name, segment_name from dba_segments where owner='TEST';

OWNER TABLESPACE_NAME SEGMENT_NAME

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

TEST TESTTBL PK_DEPT

TEST TESTTBL PK_EMP

TEST TESTTBL BIN$GNiF1FvbCgngVQAAAAAAAQ==$0

TEST TESTTBL BIN$GNiF1FveCgngVQAAAAAAAQ==$0

TEST TESTTBL DEPT

TEST TESTTBL EMP

TEST TESTTBL BIN$GNiF1FvcCgngVQAAAAAAAQ==$0

TEST TESTTBL BIN$GNiF1FvfCgngVQAAAAAAAQ==$0

TEST TESTTBL SALGRADE

9 rows selected

SQL> purge dba_recyclebin;

Done

SQL> select owner, tablespace_name, segment_name from dba_segments where owner='TEST';

OWNER TABLESPACE_NAME SEGMENT_NAME

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

TEST TESTTBL PK_DEPT

TEST TESTTBL PK_EMP

TEST TESTTBL DEPT

TEST TESTTBL EMP

TEST TESTTBL SALGRADE

恢復成功。

5、結論

Oracle Flashback技術本身就是針對原有介質恢復技術時間長、業務影響大等缺點進行的有益補充。上面實驗中，透過Flashback Database技術，實現了最小粒度物件的恢復功能。

使用Flashback Database進行資料表級別的定點恢復

相關文章