在 Oracle 11g RAC 環境中,閃回資料庫 可將整個資料庫(含所有實例)恢復到指定 SCN 或時間點,適用於邏輯錯誤(如誤 TRUNCATE 表)的秒級恢復。本文記錄完整操作流程。
SQL> SELECT flashback_on FROM v$database; -- NO SQL> ALTER DATABASE FLASHBACK ON; SQL> SELECT flashback_on FROM v$database; -- YES
SQL> CREATE TABLE t_zhongwc AS SELECT * FROM dba_objects; -- 75289 筆 SQL> SELECT current_scn FROM v$database; -- 1395475
SQL> TRUNCATE TABLE t_zhongwc; -- 資料歸零
$ srvctl stop database -d zhongwc $ sqlplus / as sysdba SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN 1395475; Flashback complete.
SQL> ALTER DATABASE OPEN READ ONLY; SQL> SELECT COUNT(*) FROM t_zhongwc; -- 75289 筆恢復
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE OPEN RESETLOGS; $ srvctl start instance -d zhongwc -i zhongwc2
SQL> SELECT instance_name, host_name, status FROM gv$instance; -- 兩節點皆 OPEN NORMAL
SQL> SELECT TO_CHAR(scn_to_timestamp(1395475), 'yyyy-mm-dd hh24:mi:ss') FROM dual;
-- 2013-01-23 17:14:05
SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2013-01-23 17:14:05','yyyy-mm-dd hh24:mi:ss');
※ 執行時間點閃回時資料庫必須處於 MOUNT 狀態(非 OPEN),否則報 ORA-38757。
RESETLOGS 開啟再啟動其他實例。V$FLASHBACK_DATABASE_LOG。