使用Oracle10g Flashback database功能恢復使用者錯誤

聽海★藍心夢發表於2011-12-01
Flashback Database是Oracle10g的新增功能,在啟動Flashback Database之後,它定期將已發生變化的塊寫入閃回日誌的日誌檔案中。這些日誌不是由傳統的Log Writer (LGWR) 過程寫入,而是由一種稱作Recovery Writer (RVWR)的新過程寫入。這是Oracle10g的新增程式。

$ ps -ef|grep rvwr|grep -v grep oracle 27231 1 0 17:10:30 ? 0:00 ora_rvwr_eygle

與常規的重做日誌(redo logs)不同,回閃日誌既不需要由DBA建立,也不需要由他們維護;它們由Oracle Managed Files(OMF)自動在閃回恢復區域所指定的目錄中建立。這些檔案不會歸檔,所以,如果在該目錄發生介質故障後就不可能再進行恢復。
1.啟用閃回資料庫特性

SYS AS SYSDBA on 2005-03-29 16:42:19 >startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1301704 bytes Variable Size 261890872 bytes Database Buffers 50331648 bytes Redo Buffers 1048576 bytes Database mounted. SYS AS SYSDBA on 29-MAR-05 >alter database archivelog; Database altered. SYS AS SYSDBA on 29-MAR-05 >alter database flashback on; Database altered. SYS AS SYSDBA on 29-MAR-05 >alter database open; Database altered. SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SYS AS SYSDBA on 2005-03-29 17:01:42 >SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 2 FROM V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI -------------------- ------------------- 10642627 2005-03-29 17:01:02

2.閃回日誌
Oracle會在閃回區記錄日誌.

$ pwd /data5/flash_recovery_area/EYGLE/flashback $ ls -l total 32064 -rw-r----- 1 oracle dba 8200192 Mar 29 16:49 o1_mf_14l5bclp_.flb -rw-r----- 1 oracle dba 8200192 Mar 29 17:31 o1_mf_14l6w5h4_.flb

3.模擬使用者錯誤

SYS AS SYSDBA on 2005-03-29 17:01:44 >archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 0 Next log sequence to archive 1 Current log sequence 1 SYS AS SYSDBA on 2005-03-29 17:01:55 >select sysdate from dual; SYSDATE ------------------- 2005-03-29 17:02:09 SYS AS SYSDBA on 2005-03-29 17:02:16 >drop table t3; Table dropped. SYS AS SYSDBA on 2005-03-29 17:02:22 >select sysdate from dual; SYSDATE ------------------- 2005-03-29 17:02:28 SYS AS SYSDBA on 2005-03-29 17:02:28 >create table t1 as select * from dba_users; Table created. SYS AS SYSDBA on 2005-03-29 17:02:42 >select sysdate from dual; SYSDATE ------------------- 2005-03-29 17:02:48 SYS AS SYSDBA on 2005-03-29 17:02:48 >create table t2 as select * from dba_tablespaces; Table created. SYS AS SYSDBA on 2005-03-29 17:03:01 >select sysdate from dual; SYSDATE ------------------- 2005-03-29 17:03:06 SYS AS SYSDBA on 2005-03-29 17:03:06 >select sysdate from dual; SYSDATE ------------------- 2005-03-29 17:03:17

4.進行閃回操作

SYS AS SYSDBA on 2005-03-29 17:03:43 >shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS AS SYSDBA on 2005-03-29 17:04:04 >startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1301704 bytes Variable Size 261890872 bytes Database Buffers 50331648 bytes Redo Buffers 1048576 bytes Database mounted. SYS AS SYSDBA on 29-MAR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SYS AS SYSDBA on 2005-03-29 17:05:31 >select * from V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- ------------------- ---------------- -------------- ------------------------ 10642627 2005-03-29 17:01:02 1440 8192000 0 SYS AS SYSDBA on 2005-03-29 17:06:38 >flashback database to timestamp 2 to_timestamp ('2005-03-29 17:02:28','yyyy-mm-dd hh24:mi:ss'); Flashback complete.

5.只讀開啟,驗證資料

SYS AS SYSDBA on 2005-03-29 17:06:58 >alter database open read only; Database altered. SYS AS SYSDBA on 2005-03-29 17:07:07 >desc t3 ERROR: ORA-04043: object t3 does not exist SYS AS SYSDBA on 2005-03-29 17:07:39 >desc t1 ERROR: ORA-04043: object t1 does not exist

6.繼續修正恢復

SYS AS SYSDBA on 2005-03-29 17:07:42 >alter database close; Database altered. SYS AS SYSDBA on 2005-03-29 17:07:49 >flashback database to timestamp 2 to_timestamp ('2005-03-29 17:02:48','yyyy-mm-dd hh24:mi:ss'); Flashback complete. SYS AS SYSDBA on 2005-03-29 17:08:17 >alter database open; alter database open * ERROR at line 1: ORA-16196: database has been previously opened and closed SYS AS SYSDBA on 2005-03-29 17:08:24 >alter database dismount; Database altered. SYS AS SYSDBA on 2005-03-29 17:08:35 >shutdown ORA-01507: database not mounted ORACLE instance shut down. SYS AS SYSDBA on 2005-03-29 17:08:48 >startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1301704 bytes Variable Size 261890872 bytes Database Buffers 50331648 bytes Redo Buffers 1048576 bytes Database mounted. SYS AS SYSDBA on 29-MAR-05 >alter database open read only; Database altered. SYS AS SYSDBA on 29-MAR-05 >select count(*) from t1; COUNT(*) ---------- 12

7.resetlogs開啟資料庫

SYS AS SYSDBA on 29-MAR-05 >shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS AS SYSDBA on 29-MAR-05 >startup mount ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1301704 bytes Variable Size 261890872 bytes Database Buffers 50331648 bytes Redo Buffers 1048576 bytes Database mounted. SYS AS SYSDBA on 29-MAR-05 >alter database open resetlogs; Database altered. SYS AS SYSDBA on 29-MAR-05 >

注意,一旦resetlogs之後,將不能再flashback至resetlogs之前的時間點。
【轉帖】

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

相關文章