Flashback Database閃回資料庫為資料庫的恢復提供了一種途徑，上篇複習閃回刪除實驗，它解決了誤刪除個別表，其實是在表空間開闢一個空間做回收站，用來存放誤刪除的表空間，當然刪除的表空間命名發生了變化，這個可以從select命令查出與原來表的對應關係，從這裡可以看出，他的侷限性，就是刪除的表會被清空。本次閃回資料恰巧解決了上面的問題。

Flashback Database閃回資料庫決定其閃回的時間,資料量與三個設定有關；

1、db_recovery_file_dest 閃回存在的具體目錄位置；

2、db_recovery_file_dest_size 閃回存放目錄存放檔案大小；（先設定大小，再設定目錄位置，否則會報錯）

3、db_flashback_retention_target 閃回保留時間資料庫預設是1440分鐘=24小時

當然要想使用閃回資料庫功能，必須開始歸檔日誌哦

SYS@orcl>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /orcldg_archivelog

Oldest online log sequence 22

Next log sequence to archive 23

Current log sequence 23

開啟歸檔

資料庫處於mount狀態 alter database archivelog

設定閃回資料庫

資料庫處於mount狀態

資料庫設定為歸檔模式

alter system set db_recovery_file_dest_size=4g scope=both;

alter system set db_recovery_file_dest='/orcldg_flash_recovery' scope=both;

設定資料庫閃回時間（一般使用預設時間： 1天）

SYS@orcl>show parameter flashback;

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

上面看了閃回資料庫設定方法，下面我們透過實驗看到閃回庫效果；

首先：在資料庫上建立pos/pos 使用者及密碼賦予建立表、連線許可權，建立it1、it2、it3

POS@orcl>select table_name,tablespace_name from user_tables where table_name like 'IT%';

TABLE_NAME TABLESPACE_NAME

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

IT1 DATA

IT2 DATA

IT3 DATA

POS@orcl>select count(*) from it1;

COUNT(*)

----------

86996

POS@orcl>select count(*)from it2;

COUNT(*)

----------

86997

POS@orcl>select count(*)from it3;

COUNT(*)

----------

86998

其次，檢視現在庫時間、資料庫閃回最大能恢復的時間區間

POS@orcl>desc v$flashback_database_log;

Name Null? Type

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

OLDEST_FLASHBACK_SCN NUMBER

OLDEST_FLASHBACK_TIME DATE

RETENTION_TARGET NUMBER

FLASHBACK_SIZE NUMBER

ESTIMATED_FLASHBACK_SIZE NUMBER

POS@orcl>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

POS@orcl>select OLDEST_FLASHBACK_SCN ,OLDEST_FLASHBACK_TIME from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM

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

1408110 2022-01-17 15:13:32

POS@orcl>select sysdate from dual;

SYSDATE

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

2022-01-18 09:18:04

再次，刪除上面it1 ,it2表空間，保留it3表空間

POS@orcl>drop table it1;

Table dropped.

POS@orcl>drop table it2 purge;

Table dropped.

POS@orcl>commit;

Commit complete.

最後，閃回資料庫，恢復it1,it2表，保留it3表，開始操作

關閉資料庫，啟動資料庫到exclusive模式（獨佔模式）

SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@orcl>startup mount exclusive;

ORACLE instance started.

Total System Global Area 1503199232 bytes

Fixed Size 2253424 bytes

Variable Size 939527568 bytes

Database Buffers 553648128 bytes

Redo Buffers 7770112 bytes

Database mounted.

閃回庫有兩個方式：一是到指定時間點，二是指定到SCN；

本次使用指定恢復到指定時間點

SYS@orcl>flashback database to timestamp(to_date('2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss'));

flashback database to timestamp(to_date('2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss'))

*

ERROR at line 1:

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

ORA-38762: redo logs needed for SCN 1425892 to SCN End-of-Redo

ORA-38761: redo log sequence 20 in thread 1, incarnation 4 could not be

accessed

使用sqlplus下無法實現資料庫閃回，顯示sequence 20 in thread 1無法訪問。

透過使用rman方式實現庫閃回

RMAN> flashback database to time="2022-01-18 09:18:04','yyyy-mm-dd hh24:mi:ss";

Starting flashback at 18-JAN-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

starting media recovery

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

Finished flashback at 18-JAN-22

開啟庫到只讀狀態

[oracle@db1 flashback]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 18 09:34:32 2022

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl>alter database open read only;

Database altered.

SYS@orcl>

使用exp imp工具將原來刪除的表匯出，匯入（無法使用expdp impdp因為資料庫只讀）

[oracle@db1 ~]$ exp pos/pos@orcl file=it.dmp log=it.log tables=IT1,IT2

Export: Release 11.2.0.4.0 - Production on Tue Jan 18 09:37:16 2022

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table IT1 87002 rows exported

. . exporting table IT2 87001 rows exported

Export terminated successfully without warnings.

關閉庫，恢復到正常狀態後，匯入表it1 It2

SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@orcl>startup mount;

ORACLE instance started.

Total System Global Area 1503199232 bytes

Fixed Size 2253424 bytes

Variable Size 939527568 bytes

Database Buffers 553648128 bytes

Redo Buffers 7770112 bytes

Database mounted.

SYS@orcl>recover database;（恢復庫正常狀態，it1 it2已刪除，it3保留）

Media recovery complete.

SYS@orcl>alter database open;

POS@orcl>select count(*) from it1;

select count(*) from it1

*

ERROR at line 1:

ORA-00942: table or view does not exist

POS@orcl>select count(*) from it2;

select count(*) from it2

*

ERROR at line 1:

ORA-00942: table or view does not exist

POS@orcl>select count(*)from it3;

COUNT(*)

----------

87002

匯入表it1 it2

[oracle@db1 ~]$ imp pos/pos@orcl file=it.dmp log=it11.log tables=IT1,IT2

Import: Release 11.2.0.4.0 - Production on Tue Jan 18 09:40:47 2022

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing POS's objects into POS

. . importing table "IT1" 87002 rows imported

. . importing table "IT2" 87001 rows imported

Import terminated successfully without warnings.

POS@orcl>select table_name,tablespace_name from user_tables where table_name like 'IT%';

TABLE_NAME TABLESPACE_NAME

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

IT1 DATA

IT2 DATA

IT3 DATA

POS@orcl>select count(*)from it1;

COUNT(*)

----------

87002

POS@orcl>select count(*)from it2;

COUNT(*)

----------

87001

POS@orcl>select count(*)from it3;

COUNT(*)

----------

87002

表恢復正常。

如果在恢復完成後使用，不匯入匯出資料表，使用alter database open resetlogs方式開啟庫，則會造成資料庫資料的丟失

（資料庫所有檔案恢復到了一個以前時間的狀態，注意不要使用）

總結：閃回資料庫給我們提供了閃回刪除以外的方式恢復資料表，是以犧牲空間為代價換回資料庫的還原時間，可以在sqlplus、

rman下進行恢復操作，閃回資料庫的恢復速度快於使用者下的備份恢復，主要他是基於閃回日誌。資料庫能夠閃回恢復的程度

取決於閃回空間大小閃回空間不斷迴圈的使用，而閃回保留策略決定了閃回日誌的保留長度。

總之，合理的平衡恢復速度和可使用空間之間的關係。

【Flashback】Flashback Database閃回資料庫功能實驗

相關文章