快活林资源网

Oracle误删除表数据后的数据恢复详解
数据库 2024/12/27 佚名
3 1
快活林资源网 Design By www.csstdc.com

Oracle误删除表数据后的恢复详解
 
测试环境：
SYSTEM:IBM AIX 5L                         Oracle Version:10gR2
 

1. undo_retention参数的查询与修改

使用show parameter undo命令查看当前的数据库参数undo_retention设置。
显示如下：
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
undo_retention(保持力)，900单位是秒，即15分钟。
修改默认的undo_retention参数设置：
SQL> ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS2
undo_retention 10800，单位秒，即3小时。

2. oracle误删除表数据后的的快速恢复功能方法

2.1 方法一
通过oracle提供的回闪功能

exec dbms_flashback.enable_at_time(to_date('2011-04-15 08: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包的权限。

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

查看FIRST_CHANGE#,NEXT_CHANGE#,FIRST_TIME
SQL> set pagesize 9999
SQL> col fscn for 999999999
SQL> col nscn for 999999999
SQL> select name,FIRST_CHANGE# fscn,NEXT_CHANGE# nscn,FIRST_TIME from v$archived_log;

当前的SCN为:
SQL> select dbms_flashback.get_system_change_number fscn from dual;
      FSCN
----------
   3435958

使用应用用户尝试闪回
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干预。

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

示例
3.1 使用闪回查询前必须确定下面两个参数：
UNDO_MANAGEMENT = AUTO
undo_retention = 10800;
这个时间可以随便设，它表示在系统中保留提交了的UNDO信息的时间，10800就是保留3小时，即180分钟。
3.2 使用闪回查询

SQL> conn /as sysdba
Connected.
SQL> drop user lsf cascade;

User dropped.

SQL> create user lsf identified by lsf;

User created.

SQL> grant connect,resource to lsf;

Grant succeeded.

SQL> grant execute on dbms_flashback to lsf;

Grant succeeded.

SQL> conn lsf/lsf
Connected.
SQL> create table T(id int, name varchar2(20));

Table created.

SQL> insert into T values(1,'lsf');

1 row created.

SQL> insert into T values(2,'lsf');

1 row created.

SQL> insert into T values(3,'lsf');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     1 lsf
     2 lsf
     3 lsf

SQL> set time on
10:12:50 SQL> delete from T where id=1;

1 row deleted.

10:13:02 SQL> commit;

Commit complete.

10:13:10 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     2 lsf
     3 lsf

10:13:18 SQL> execute DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-15 10:12:50','YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

10:13:50 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     1 lsf
     2 lsf
     3 lsf

10:13:57 SQL> execute DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

10:15:48 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     2 lsf
     3 lsf

3.3 使用闪回查询恢复数据

10:16:59 SQL> truncate table T;

Table truncated.

10:18:15 SQL> select * from T;

no rows selected

10:18:22 SQL> insert into T values(1,'lsf');

1 row created.

10:19:42 SQL> insert into T values(2,'lsf');

1 row created.

10:19:48 SQL> insert into T values(3,'lsf');

1 row created.

10:19:55 SQL> insert into T values(4,'lsf');

1 row created.

10:20:07 SQL> insert into T values(5,'lsf');

1 row created.

10:20:15 SQL> insert into T values(6,'lsf');

1 row created.

10:20:21 SQL> commit;

Commit complete.

10:20:26 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     1 lsf
     2 lsf
     3 lsf
     4 lsf
     5 lsf
     6 lsf

6 rows selected.

10:20:56 SQL> delete T;

6 rows deleted.

10:21:27 SQL> commit;

Commit complete.

10:21:40 SQL> declare
10:22:29  2 cursor flash_recover is
10:22:43  3 select * from T;
10:22:50  4 t_recode T%rowtype;
10:23:11  5 begin
10:23:14  6 DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-15 10:20:56','YYYY-MM-DD HH24:MI:SS'));
10:24:22  7 open flash_recover;
10:24:39  8 DBMS_FLASHBACK.DISABLE;
10:24:59  9 loop
10:25:05 10 FETCH flash_recover into t_recode;
10:25:24 11 EXIT WHEN flash_recover%NOTFOUND;
10:25:45 12 insert into T values(t_recode.id,t_recode.name);
10:26:35 13 end loop;
10:26:39 14 CLOSE FLASH_RECOVER;
10:26:50 15 commit;
10:26:56 16 end;
10:26:58 17 /

PL/SQL procedure successfully completed.

10:27:00 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     1 lsf
     2 lsf
     3 lsf
     4 lsf
     5 lsf
     6 lsf

6 rows selected.

我们可以已经恢复了所有的6条纪录，但是由于闪回查询的局限性，有可能不能恢复所有的6条记录，原因就在下面。

4. 局限性
4.1 闪回查询是基于SCN的，虽然我们执行的是：
DBMS_FLASHBACK.ENABLE_AT_TIME(to_date('2011-04-15 10:20:56','YYYY-MM-DD HH24:MI:SS'));
但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); 来定位你的恢复时间点，下面是使用方法：

10:27:27 SQL> VARIABLE SCN_SAVE NUMBER;
10:32:47 SQL> EXECUTE :SCN_SAVE := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

PL/SQL procedure successfully completed.

10:33:24 SQL> print SCN_SAVE;

 SCN_SAVE
----------
  3438420

10:33:41 SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:SCN_SAVE);

PL/SQL procedure successfully completed.

10:34:31 SQL> select * from T;

    ID NAME
---------- ------------------------------------------------------------
     1 lsf
     2 lsf
     3 lsf
     4 lsf
     5 lsf
     6 lsf

6 rows selected.

另外，在使用DBMS_FLASHBACK.ENABLE_AT_TIME前，你必须设定你的NLS_DATE_FORMAT的精确程度，Oracle默认的是精确到天，如果你不设定，像上面的例子你不会得到预期结果。
4.2 如果你使用sysdate和DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER来获取时间点或者SCN值，你必须注意它们取得都是当前的时间点和SCN值。
4.3 你只能在事务开始时进入闪回查询模式，如果之前有DML操作，则必须COMMIT。
4.4 闪回查询无法恢复到表结构改变之前，因为闪回查询使用的当前的数据字典。

快活林资源网 Design By www.csstdc.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
快活林资源网 Design By www.csstdc.com
www.csstdc.com 快活林资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
快活林资源网 Design By www.csstdc.com