你好,游客 登录 注册 搜索
背景:
阅读新闻

oracle误删除表数据后的恢复详解 - 小宝老豆的专栏

[日期:2013-04-17] 来源:  作者: [字体: ]

一、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 9999999999999999999SQL> col nscn for 9999999999999999999SQL> 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:00NAME 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:0714811 rows selected. 
当前的SCN为:
SQL> select dbms_flashback.get_system_change_number fscn from dual; FSCN-------------------- 12930142214 
使用应用用户尝试闪回
SQL> connect username/passwordConnected. 
现有数据:
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: 12929941968old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929941968 COUNT(*)---------- 861684SQL> /Enter value for scn: 12927633776old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12927633776select count(*) from hs_passport as of scn 12927633776 *ERROR at line 1:ORA-01466: unable to read data - table definition has changedSQL> /Enter value for scn: 12929928784old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12929928784 COUNT(*)---------- 825110SQL> /Enter value for scn: 12928000000old 1: select count(*) from hs_passport as of scn &scnnew 1: select count(*) from hs_passport as of scn 12928000000select 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; 



---------- 

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; 



---------- 

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; 



---------- 

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; 



---------- 

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; 



---------- 

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; 



---------- 

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、闪回查询无法恢复到表结构改变之前,因为闪回查询使用的当前的数据字典。





收藏 推荐 打印 | 录入:admin | 阅读:
相关新闻