?

【Flashback】Flashback Database閃回數(shù)據(jù)庫功能實踐

上一篇?/?下一篇??2012-04-07 21:16:40 / 個人分類：備份與恢復

查看( 278 )?/?評論( 2 )?/?評分(?5?/?0?) ??Flashback?Database閃回數(shù)據(jù)庫功能極大的降低了由于用戶錯誤導致的數(shù)據(jù)丟失的恢復成本。這是一種以空間換取縮短恢復時間的解決方案，這是值得的。
? 這里給出閃回數(shù)據(jù)庫的使用方法，體驗一下這種恢復操作的便利性。

1.使用Flashback Database的前提條件
1）啟用了flashback database
2）必須打開flash recovery area，若為RAC，flash recovery area必須位于共享存儲中。
3）必須處于archivelog模式，開啟FORCE LOGGIN

2.一一確認上面的前提條件是否滿足
1）驗證是否啟用了flashback database并確認FORCE LOGGIN是否開啟
SYS@ora11g> select flashback_on,force_logging from v$database;

FLASHBACK_ON?????? FOR
------------------ ---
YES??????????????? NO

若flashback_on為“NO”，修改方法見《【Flashback】啟用Flashback Database閃回數(shù)據(jù)庫功能》（http://space.itpub.net/519536/viewspace-590636）
若force_logging為“NO”，請使如下SQL語句開啟。

SYS@ora11g> alter database force logging;

Database altered.

SYS@ora11g> select flashback_on,force_logging from v$database;

FLASHBACK_ON?????? FOR
------------------ ---
YES??????????????? YES

2）驗證是否開啟flash recovery area
此步驟在啟用閃回數(shù)據(jù)庫功能時已經(jīng)確認過。
SYS@ora11g> show parameter db_recovery_file

NAME??????????????????????? TYPE??????? VALUE
--------------------------- ----------- ------------------------------
db_recovery_file_dest?????? string????? /u01/app/oracle/flash_recovery
??????????????????????????????????????? _area
db_recovery_file_dest_size? big integer 3852M

3）數(shù)據(jù)庫是否處于archivelog模式
SYS@ora11g> archive log list;
Database log mode????????????? Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence???? 11
Next log sequence to archive?? 13
Current log sequence?????????? 13

3.確認數(shù)據(jù)庫可以前滾到的SCN和Time的方法
如果需要恢復的數(shù)據(jù)點比這個時間還要早的話，很不幸，閃回數(shù)據(jù)庫功能將無能為力。
SYS@ora11g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SYS@ora11g> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
???????????? 1033529 2012-04-02 03:36:40

4.閃回數(shù)據(jù)庫功能閃亮登場
1）創(chuàng)建測試表fd_1、fd_2和fd_3
SYS@ora11g> create?table?fd_1 as select * from dba_objects;

Table created.

SYS@ora11g> create table fd_2 as select * from fd_1;

Table created.

SYS@ora11g> create table fd_3 as select * from fd_1;

Table created.

SYS@ora11g> select count(*) from fd_1;

? COUNT(*)
----------
???? 72465

SYS@ora11g> select count(*) from fd_2;

? COUNT(*)
----------
???? 72465

SYS@ora11g> select count(*) from fd_3;

? COUNT(*)
----------
???? 72465

SYS@ora11g> set time on
21:59:40 SYS@ora11g> select sysdate from dual;

SYSDATE
-------------------
2012-04-07 21:59:44

2）truncate表fd_2、drop掉表fd_3
21:59:44 SYS@ora11g> truncate table fd_2;

Table truncated.

22:00:06 SYS@ora11g> drop table fd_3;

Table dropped.

3）使用Flashback Database功能進行恢復到刪除前的時間點2012-04-07 21:59:44
22:00:17 SYS@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE?instance shut down.
22:02:04 SYS@ora11g> startup mount exclusive;
ORACLE instance started.

Total System Global Area? 313860096 bytes
Fixed Size????????????????? 1336232 bytes
Variable Size???????????? 247467096 bytes
Database Buffers?????????? 58720256 bytes
Redo Buffers??????????????? 6336512 bytes
Database mounted.
22:02:52 SYS@ora11g> Flashback Database to timestamp(to_date('2012-04-07 21:59:44','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.

4）閃回后修復數(shù)據(jù)庫兩種方式之一：open read only
推薦使用這樣的方法進行恢復，因為在read only方式打開之后，將需要恢復的表EXP導出，然后通過recover database將數(shù)據(jù)庫恢復到原狀態(tài)，再將缺失的數(shù)據(jù)IMP到數(shù)據(jù)庫中。這樣操作對數(shù)據(jù)庫的影響可以降低到最小，可以保證其他表沒有數(shù)據(jù)的丟失。
read only打開后查看三張表的狀態(tài)：
22:03:57 SYS@ora11g> alter database open read only;

Database altered.

22:04:33 SYS@ora11g> select count(*) from fd_1;

? COUNT(*)
----------
???? 72465

22:04:37 SYS@ora11g> select count(*) from fd_2;

? COUNT(*)
----------
???? 72465

22:04:40 SYS@ora11g> select count(*) from fd_3;

? COUNT(*)
----------
???? 72465

取消閃回結(jié)果，恢復到閃回前狀態(tài)的方法：
22:04:43 SYS@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:05:09 SYS@ora11g> startup mount;
ORACLE instance started.

Total System Global Area? 313860096 bytes
Fixed Size????????????????? 1336232 bytes
Variable Size???????????? 247467096 bytes
Database Buffers?????????? 58720256 bytes
Redo Buffers??????????????? 6336512 bytes
Database mounted.
22:06:08 SYS@ora11g> recover database;
Media recovery complete.
22:06:18 SYS@ora11g> alter database open;

Database altered.

22:06:41 SYS@ora11g> select count(*) from fd_1;

? COUNT(*)
----------
???? 72465

22:06:55 SYS@ora11g> select count(*) from fd_2;

? COUNT(*)
----------
???????? 0

22:06:59 SYS@ora11g> select count(*) from fd_3;
select count(*) from fd_3
???????????????????? *
ERROR at line 1:
ORA-00942: table or view does not exist

可見，通過上面的recover后，數(shù)據(jù)庫恢復到了閃回前的狀態(tài)。

5）閃回后修復數(shù)據(jù)庫兩種方式之二：open resetlogs
通過open resetlogs方式打開數(shù)據(jù)庫后，很顯然，閃回到時間點之后的數(shù)據(jù)將全部丟失，慎用！

SYS@ora11g> alter database open resetlogs;
SYS@ora11g> select count(*) from fd_1;

? COUNT(*)
----------
???? 72465

SYS@ora11g> select count(*) from fd_2;

? COUNT(*)
----------
???? 72465

SYS@ora11g> select count(*) from fd_3;

? COUNT(*)
----------
???? 72465

5.小結(jié)
? 這里對Flashback Database閃回數(shù)據(jù)庫的語法進行總結(jié)。閃回數(shù)據(jù)庫可以在SQL*Plus環(huán)境和RMAN環(huán)境下使用。
? 基于時間戳進行閃回數(shù)據(jù)庫操作方法：
Flashback Database to timestamp(to_date('2012-04-07 21:59:44','yyyy-mm-dd hh24:mi:ss'));
Flashback Database to timestamp(sysdate-1/24);
? 基于SCN進行閃回數(shù)據(jù)庫操作方法：
Flashback Database to 1321427;

Good luck.

secooler
12.04.07

-- The End --

轉(zhuǎn)載于:https://blog.51cto.com/aixuexi/864873

超強干貨來襲 云風專訪：近40年碼齡，通宵達旦的技術人生

總結(jié)

以上是生活随笔為你收集整理的【Flashback】Flashback Database闪回数据库功能实践的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。