循序漸進oracle第8章：Oracle的閃回特性之恢復刪除表的資料四種方法

/* 2008/06/09
*環境：Windows XP +Oracle10.2.0.1
*非歸檔模式
*循序漸進oracle——資料庫管理、最佳化與備份恢復
*循序漸進oracle第8章：Oracle的閃回特性之(flashback query)和(flashback table)恢復刪除表的資料
*恢復刪除表的四種方法
*/

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 9 08:26:42 2008

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

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

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

非歸檔模式:
SQL> archive log list;
資料庫日誌模式             非存檔模式
自動存檔             禁用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列     1
當前日誌序列           3
SQL>
SQL> show parameter flashback;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> select dbid,name,flashback_on,current_scn from v$database;

      DBID NAME      FLASHBACK_ON       CURRENT_SCN
---------- --------- ------------------ -----------
1184709774 ORCL      NO                      578449

SQL> alter user scott account unlock;

使用者已更改。

SQL> connect scott/tiger
ERROR:
ORA-28001: the password has expired

更改 scott 的口令
新口令:
重新鍵入新口令:
口令已更改
已連線。

SQL> create table empcopy
  2  as
  3  select * from emp;

表已建立。

SQL> select * from emp;

SQL> delete emp where deptno<50;

已刪除14行。

SQL> commit;

提交完成。

已經提交，怎麼恢復:(flashback不支援sys使用者進行DML操作)
方法一：
用flashback的時間閃回。需要flashback一個資料表，需要具有flashback any table的系統許可權或者是

該表的flashback物件許可權，同時具有該表的select,insert,delete,alter許可權，由於flashback table技

術使用DML操作去恢復資料，不能保證rowid不變，所以在閃回之前還需要啟用表的row movement特性。

SQL> alter table emp enable row movement;

表已更改。

SQL> flashback table emp to timestamp to_timestamp('2008-06-09 8:40:00','yyyy-mm
-dd hh24:mi:ss');

閃回完成。

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

 

方法二：
用flashback的SCN閃回.
SQL> conn sys/mzl as sysdba
已連線。
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                  580643

SQL> alter table scott.emp enable row movement;

表已更改。

SQL> flashback table scott.emp to scn 580000;

閃回完成。

***********************************************************************************

怎麼確定沒刪資料前的scn呢？

可以查詢v$archived_log檢視來找時間對應的scn.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'

SQL> select name,first_change#,next_change#,first_time from v$archived_log;

或者：

SQL> select thread#,stamp,first_change#,next_change#,first_time from v$log_history;

**************************************************************************************

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

 

方法三：用閃回查詢(flashback query)閃回,從oracle9i開始就可以用該功能,用閃回時間查詢功能。
SQL> create table emp_recover
  2  as
  3  select * from emp as of timestamp to_timestamp('2008-06-09 9:00:00','yyyy-m
m-dd hh24:mi:ss');

表已建立。

SQL> select count(*) from emp_recover;

  COUNT(*)
----------
        14

SQL> delete from emp;

 

SQL> insert into emp
  2  select * from emp_recover;

已建立14行。

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

方法四：用閃回查詢(flashback query)閃回,從oracle9i開始就可以用該功能,用閃回SCN查詢功能。
SQL> connect sys/mzl as sysdba
已連線。
SQL> select dbms_flashback.get_system_change_number "SCN" from dual;

       SCN
----------
    582257

SQL> drop table scott.emp_recover;

表已刪除。

建立恢復表：
SQL> create table scott.emp_recover
  2  as
  3  select * from scott.emp where 1=0;

表已建立。

用變數多次輸入最接近的scn:
SQL> select count(*) from scott.emp as of scn &scn;
輸入 scn 的值:  582250
原值    1: select count(*) from scott.emp as of scn &scn
新值    1: select count(*) from scott.emp as of scn 582250

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

SQL> select count(*) from scott.emp as of scn &scn;
輸入 scn 的值:  582000
原值    1: select count(*) from scott.emp as of scn &scn
新值    1: select count(*) from scott.emp as of scn 582000

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

SQL> select count(*) from scott.emp as of scn &scn;
輸入 scn 的值:  581000
原值    1: select count(*) from scott.emp as of scn &scn
新值    1: select count(*) from scott.emp as of scn 581000

  COUNT(*)
----------
        14

SQL> insert into scott.emp_recover
  2  select * from scott.emp as of scn 581000;

已建立14行。

SQL> commit;

提交完成。

SQL> delete from scott.emp;

已刪除0行。

SQL> insert into scott.emp
  2  select * from scott.emp_recover;

已建立14行。

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

 

註釋：flashback不支援sys使用者進行DML操作，所以最好不要用sys運算元據。