flashback技術之---flashback database

lsq_008發表於2012-07-29
Database
flashback database可以實現基於時間點的不完全恢復，將資料庫閃回到某個時間點，而不需要漫長的restore操作

1. flashback database的一些限制：
--只能用來undo changes to a datafile made by an oracle database，不能用來修復media failures或者是資料檔案被刪除的情況
--如果控制檔案是restore的或重建過的，那麼所有的flashback資訊將丟失。
--對於nologging操作，flashback無法恢復，比如direct-path insert操作，恢復後相關的資料塊狀態是corruption的。

2.開啟flashback database的需求
--資料庫執行在歸檔模式
--配置flash recovery area
--對於rac，flash recovery area必須配置在 a clustered file system or in ASM.

3.啟用flashback database

--確認資料庫目前未開啟flashback
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

--配置閃回區
SQL> alter system set db_recovery_file_dest_size=1g;

System altered.

SQL> alter system set db_recovery_file_dest='/home/db/oracle/recovery/';

System altered.

--啟動資料庫到mount狀態

SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              92276980 bytes
Database Buffers           71303168 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

System altered.

SQL>  ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

4.使用flashback database恢復被誤刪除的表

SQL> select count(*) from test; 

  COUNT(*)
----------
     44936

SQL>  select sysdate from dual;

SYSDATE
-------------------
2012-07-18 03:26:59


SQL> drop table test;

Table dropped.

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed
database dismounted
Oracle instance shut down

[oracle@rhel bin]$ ./rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 18 03:34:16 2012

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

connected to target database: MBS (DBID=1440741932)
RMAN> STARTUP MOUNT;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     167772160 bytes

Fixed Size                     1218316 bytes
Variable Size                 92276980 bytes
Database Buffers              71303168 bytes
Redo Buffers                   2973696 bytes

RMAN> FLASHBACK DATABASE TO TIME    "TO_DATE('2012-07-18 03:26:59','yyyy-mm-dd hh24:mi:ss')";

Starting flashback at 18-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 18-JUL-12

RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';  --啟動資料庫到只讀狀態

sql statement: ALTER DATABASE OPEN READ ONLY

--登陸到資料庫，檢視test表是否存在及其記錄數
[oracle@rhel bin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 18 03:37:48 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from test;

  COUNT(*)
----------
     44936

--確認閃回沒有問題後，資料庫需要以OPEN RESETLOGS 的方式開啟：

SQL> startup mount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              92276980 bytes
Database Buffers           71303168 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

--如果需要將資料庫恢復到最近的狀態，同時恢復test表，則先匯出test表
[oracle@rhel ~]$ exp \'/ as sysdba\' tables=test file=test.dmp statistics=none

Export: Release 10.2.0.1.0 - Production on Wed Jul 18 03:44:04 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST      44936 rows exported
Export terminated successfully without warnings.

--匯出表後，可以將資料庫重新恢復到最近的狀態， 然後將表test匯入，這樣就完成了表test的資料恢復。

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

相關文章