oracle误删除表数据后的恢复详解

 

一、undo_retention参数的查询、修改：

用show parameter undo;命令查看当时的数据库参数undo_retention设置。

显示如下：

undo_management                       string       AUTO
undo_retention                              integer     10800
undo_suppress_errors                  boolean      FALSE
undo_tablespace                           string       UNDOTBS1

undo_retention(保持力)，10800单位是秒。即3个小时。

修改默认的undo_retention参数设置：

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

二、oracle误删除表数据后的的快速恢复功能方法：

【方法一】：
通过oracle提供的回闪功能：
exec dbms_flashback.enable_at_time(to_date('2007-07-23 10:21:00','yyyy-mm-dd hh24:mi:ss'));

set serveroutput on
DECLARE r_temp hr.job_history%ROWTYPE;
CURSOR c_temp IS SELECT * FROM hr.job_history;
BEGIN
OPEN c_temp;
dbms_flashback.disable;
LOOP
FETCH c_temp INTO r_temp;
EXIT WHEN c_temp%NOTFOUND;
insert into hr.job_history(EMPLOYEE_ID,JOB_ID,START_DATE,END_DATE) values (r_temp.EMPLOYEE_ID,r_temp.JOB_ID,r_temp.START_DATE,r_temp.END_DATE);
commit;
END LOOP; 
CLOSE c_temp; 
END;
这种办法可以将删除的数据恢复到对应的表中，首先要保证该用户有执行dbms_flashback包的权限

【方法二】：
insert into hr.job_history
select * from hr.job_history as of timestamp to_timestamp('2007-07-23 10:20:00', 'yyyy-mm-dd hh24:mi:ss');
这种方法简单，容易掌握，功能和上面的一样时间为你误操作之前的时间，最好是离误操作比较近的，因为oracle保存在回滚保持段里的数据时间有一定的时间限制由undo_retention 这个参数值决定。

 

SQL> col fscn for 9999999999999999999
SQL> col nscn for 9999999999999999999
SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;

...................

NAME                                           FSCN                 NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/mwarch/oracle/1_52413.dbf              12929941968          12929942881 2005-06-22 14:38:28
/mwarch/oracle/1_52414.dbf              12929942881          12929943706 2005-06-22 14:38:32
/mwarch/oracle/1_52415.dbf              12929943706          12929944623 2005-06-22 14:38:35
/mwarch/oracle/1_52416.dbf              12929944623          12929945392 2005-06-22 14:38:38
/mwarch/oracle/1_52417.dbf              12929945392          12929945888 2005-06-22 14:38:41
/mwarch/oracle/1_52418.dbf              12929945888          12929945965 2005-06-22 14:38:44
/mwarch/oracle/1_52419.dbf              12929945965          12929948945 2005-06-22 14:38:45
/mwarch/oracle/1_52420.dbf              12929948945          12929949904 2005-06-22 14:46:05
/mwarch/oracle/1_52421.dbf              12929949904          12929950854 2005-06-22 14:46:08
/mwarch/oracle/1_52422.dbf              12929950854          12929951751 2005-06-22 14:46:11
/mwarch/oracle/1_52423.dbf              12929951751          12929952587 2005-06-22 14:46:14
...................

/mwarch/oracle/1_52498.dbf              12930138975          12930139212 2005-06-22 15:55:57
/mwarch/oracle/1_52499.dbf              12930139212          12930139446 2005-06-22 15:55:59
/mwarch/oracle/1_52500.dbf              12930139446          12930139682 2005-06-22 15:56:00

NAME                                           FSCN                 NSCN FIRST_TIME
------------------------------ -------------------- -------------------- -------------------
/mwarch/oracle/1_52501.dbf              12930139682          12930139915 2005-06-22 15:56:02
/mwarch/oracle/1_52502.dbf              12930139915          12930140149 2005-06-22 15:56:03
/mwarch/oracle/1_52503.dbf              12930140149          12930140379 2005-06-22 15:56:05
/mwarch/oracle/1_52504.dbf              12930140379          12930140610 2005-06-22 15:56:05
/mwarch/oracle/1_52505.dbf              12930140610          12930140845 2005-06-22 15:56:07

14811 rows selected.

当前的SCN为:

SQL> select dbms_flashback.get_system_change_number fscn from dual;

                FSCN
--------------------
         12930142214

使用应用用户尝试闪回

SQL> connect username/password
Connected.

现有数据:

SQL> select count(*) from hs_passport;

  COUNT(*)
----------
    851998

创建恢复表

SQL> create table hs_passport_recov as select * from hs_passport where 1=0;

Table created.

选择SCN向前恢复

SQL> select count(*) from hs_passport as of scn 12929970422;

  COUNT(*)
----------
    861686

尝试多个SCN，获取最佳值（如果能得知具体时间，那么可以获得准确的数据闪回）

SQL> select count(*) from hs_passport as of scn &scn;
Enter value for scn: 12929941968
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929941968

  COUNT(*)
----------
    861684

SQL> /
Enter value for scn: 12927633776
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12927633776
select count(*) from hs_passport as of scn 12927633776
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> /
Enter value for scn: 12929928784
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12929928784

  COUNT(*)
----------
    825110

SQL> /
Enter value for scn: 12928000000
old   1: select count(*) from hs_passport as of scn &scn
new   1: select count(*) from hs_passport as of scn 12928000000
select count(*) from hs_passport as of scn 12928000000
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

最后选择恢复到SCN为12929941968的时间点

SQL> insert into hs_passport_recov select * from hs_passport as of scn 12929941968;

861684 rows created.

SQL> commit;

Commit complete.

 

 

数据恢复简单例子

 

简介 

在过去，如果用户误删/更新了数据后，作为用户并没有什么直接的方法来进行恢复，他们必须求助DBA来对数据库进行恢复，到了Oracle9i，这一个难堪局面有所改善。Oracle 9i中提供了一项新的技术手段--闪回查询，用户使用闪回查询可以及时取得误操作前的数据，并可以针对错误进行相应的恢复措施，而这一切都无需DBA干预。 

下面我们通过一个例子来具体说明闪回查询的用法。 

二、示例 

1、使用闪回查询前必须确定下面两个参数： 

UNDO_MANAGEMENT = AUTO 

undo_retention = 1200; #这个时间可以随便设，他表示在系统中保留提交了的UNDO信息的时间，1200就是保留20分钟。 

2、使用闪回查询 

SQL> conn sys/sys as sysdba 

Connected. 

SQL> create user flash identified by flash; 

User created. 

SQL> grant connect, resource to flash; 

Grant succeeded. 

SQL> grant execute on dbms_flashback to flash; 

Grant succeeded. 

SQL> conn flash/flash 

Connected. 

SQL> set echo on 

SQL> create table t (a varchar2(10)); 

Table created. 

SQL> insert into t values('gototop'); 

1 row created. 

SQL> insert into t values('www.ncn.cn'); 

1 row created. 

SQL> / 

SQL> select * from t; 

A 

---------- 

gototop 

www.ncn.cn 

www.ncn.cn 

SQL> set time on 

15:00:22 SQL> 

15:00:22 SQL> 

15:00:23 SQL> delete t where a='gototop'; 

1 row deleted. 

15:00:35 SQL> commit; 

Commit complete. 

15:00:38 SQL> select * from t; 

A 

---------- 

www.ncn.cn 

www.ncn.cn 

15:08:22 SQL> execute DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:00:00')); 

PL/SQL procedure successfully completed. 

15:09:02 SQL> select * from t; 

A 

---------- 

gototop 

www.ncn.cn 

www.ncn.cn 

15:16:43 SQL> execute DBMS_FLASHBACK.DISABLE; 

PL/SQL procedure successfully completed. 

15:17:05 SQL> select * from t; 

A 

---------- 

www.ncn.cn 

www.ncn.cn 

3、使用闪回查询恢复数据： 

15:36:55 SQL> select * from t; 

no rows selected 

15:36:57 SQL> insert into t values('www.ncn.cn'); 

1 row created. 

15:37:15 SQL> / 

1 row created. 

15:37:16 SQL> / 

1 row created. 

15:37:17 SQL> / 

1 row created. 

15:37:18 SQL> / 

1 row created. 

15:37:19 SQL> commit; 

Commit complete. 

15:37:21 SQL> / 

Commit complete. 

15:37:22 SQL> / 

Commit complete. 

15:37:23 SQL> select * from t; 

A 

---------- 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

6 rows selected. 

15:37:28 SQL> 

15:38:44 SQL> delete t; 

6 rows deleted. 

15:38:50 SQL> commit; 

Commit complete. 

15:38:53 SQL> commit; 

Commit complete. 

15:38:54 SQL> / 

Commit complete. 

15:38:57 SQL> declare 

15:39:28 2 cursor flash_recover is 

15:39:28 3 select * from t; 

15:39:28 4 t_recode t%rowtype; 

15:39:28 5 begin 

15:39:28 6 DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:37:23')); 

15:39:28 7 open FLASH_RECOVER; 

15:39:28 8 DBMS_FLASHBACK.DISABLE; 

15:39:28 9 loop 

15:39:28 10 FETCH FLASH_RECOVER INTO t_recode; 

15:39:28 11 EXIT WHEN FLASH_RECOVER%NOTFOUND; 

15:39:28 12 insert into t values (t_recode.a); 

15:39:28 13 end loop; 

15:39:28 14 CLOSE FLASH_RECOVER; 

15:39:28 15 commit; 

15:39:28 16 end; 

15:39:28 17 / 

PL/SQL procedure successfully completed. 

15:39:28 SQL> 15:39:28 SQL> 

15:39:31 SQL> select * from t; 

A 

---------- 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

www.ncn.cn 

15:39:35 SQL> 

我们可以已经恢复了5条纪录，但我们要恢复的6条纪录，为什么会少一条呢？原因就在下面。 

三、局限性 

1、闪回查询是基于SCN的，虽然我执行的是： 

DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2003-05-16 15:37:23')); 

但Oracle并不会精确的这个时间点，而是ROUND DOWN到最近的一次SCN，然后从这个SCN开始进行恢复。而Oracle 9i是每五分钟记录一次SCN的，并将SCN和对应时间的映射做个纪录。 

这正是上面我们进行恢复时少了一条的原因。因此如果使用DBMS_FLASHBACK.ENABLE_AT_TIME来进行恢复，为了避免恢复失败，我可以先等5分钟，然后再进行恢复。 

使用DBMS_FLASHBACK.ENABLE_AT_TIME进行恢复还有一个缺点，那就是在Oracle 9i中SCN和对应时间的映射信息只会保留5天，因此我们无法通过DBMS_FLASHBACK.ENABLE_AT_TIME来恢复5天前的数据。如果你想使用闪回查询来恢复5天前的数据，你必须自己来确定需要恢复的SCN，然后使用 

DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(SCN_NUMBER); 来定位你的恢复时间点，下面是使用方法： 

15:58:58 SQL> VARIABLE SCN_SAVE NUMBER; 

16:13:43 SQL> EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; 

PL/SQL procedure successfully completed. 

16:13:50 SQL> print SCN_SAVE; 

SCN_SAVE 

---------- 

2.1202E+11 

16:28:34 SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE); 

PL/SQL procedure successfully completed. 

另外，在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天，如果你不设定，像上面的例子你不会得到预期结果。 

2、如果你使用sysdate和DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER来获取时间点或者SCN值，你必须注意它们取得都是当前的时间点和SCN值。 

3、你只能在事务开始时进入闪回查询模式，如果之前有DML操作，则必须COMMIT。 

4、闪回查询无法恢复到表结构改变之前，因为闪回查询使用的当前的数据字典。  

 

附老陈的例子

 

Using Flashback Queries: Example The following statements show a current value from the sample table hr.employees and then change the value. The intervals used in these examples are very short for demonstration purposes. Time intervals in your own environment are likely to be larger.

 

SELECT salary FROM employees

   WHERE last_name = 'Chung';

 

    SALARY

----------

      3800

 

UPDATE employees SET salary = 4000

   WHERE last_name = 'Chung';

 

1 row updated.

 

SELECT salary FROM employees

   WHERE last_name = 'Chung';

 

    SALARY

----------

      4000

 

To learn what the value was before the update, you can use the following Flashback Query:

 

SELECT salary FROM employees

   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)

   WHERE last_name = 'Chung';

 

    SALARY

----------

      3800

 

To learn what the values were during a particular time period, you can use a version Flashback Query:

 

SELECT salary FROM employees

  VERSIONS BETWEEN TIMESTAMP

    SYSTIMESTAMP - INTERVAL '10' MINUTE AND

    SYSTIMESTAMP - INTERVAL '1' MINUTE

  WHERE last_name = 'Chung';

 

To revert to the earlier value, use the Flashback Query as the subquery of another UPDATE statement:

 

UPDATE employees SET salary =      

   (SELECT salary FROM employees

   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)

   WHERE last_name = 'Chung')

   WHERE last_name = 'Chung';

1 row updated.

 

SELECT salary FROM employees

   WHERE last_name = 'Chung';

 

    SALARY

----------

      3800