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

最新推荐文章于 2024-05-21 15:07:56 发布
最新推荐文章于 2024-05-21 15:07:56 发布
阅读量532 收藏
点赞数

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

 

稀饭土豆的番茄
关注
oracle 不小心删除表数据补救--闪回大法
Daily博客
08-27 499
就刚刚，一顿操作猛如虎，自以为删了好多生产环境的无用垃圾数据，已提交不能回滚。本来还在沾沾自喜，少导出点东西，没想到一个小时后，同事说，那是有用的。心跳了一下，立刻百度，及时补救误删数据，要点如下： 该表的Flashback权限 拥有该表的SELECT\INSERT\DELETE\ALTER权限 必须保证表示ROW MOVEMENT 采用PURGE参数删除表中数据时不能使用Flashbac...
oracle中误删除表后恢复语句(FLASHBACK)
12-15
您可能感兴趣的文章:Oracle误删除表数据后的数据恢复详解Oracle7.X 回滚表空间数据文件误删除处理方法Oracle7.X 回滚表空间数据文件误删除处理方法Oracle7.X 回滚表空间数据文件误删除处理方法误删除$ORACLE_HOME/...
Oracle中删除表,删除数据以及恢复数据
认真做梦 她是做事 始于坚持 成于开放
12-24 312
1.删除表中数据两种方法         a. delete * from My_Table;         b. truncate table My_Table;2.删除整个表         drop table My_Table;如何恢复不小心 Drop 掉的表呢,其实 Oracle 中也有类似的 "回收站"比如不小心删除了表 My_Table         drop table My_...
oracle误删表数据（delete）
kai402458953的博客
01-14 432
转：http://www.cnblogs.com/hqbhonker/p/3977200.html 1.打开Flash存储的权限ALTER TABLE tableName ENABLE row movement ; 2.找到这个表在这个时间点的数据 select * from table as of timestamp to_timestamp('2019-01-14 11:00:00','...
oracle误删表
LanFengXuXue的博客
04-12 377
在操作数据的时候，一不小心把系统里的表给删了，不慌，还能救回来。哈哈哈。 查询到这个My_Table表还在Oracle回收站中 SELECT * FROM user_recyclebin WHERE original_name='My_Table'; 恢复： FLASHBACK TABLE My_Table TO BEFORE DROP; ...
oracle误删除表数据后的恢复详解.doc
05-04
本文将详细讲解 Oracle 误删除表数据后的恢复策略，主要涉及 Undo Retention 参数的查询与修改，以及两种快速恢复数据的方法。 1. **Undo Retention 参数** - **查询 Undo Retention**：在 Oracle 中，`undo_...
oracle 误删除表恢复
01-25
### Oracle误删除表恢复 在Oracle数据库管理过程中，误删表是常见的操作失误之一，而如何有效、快速地恢复这些被误删的表对于DBA（数据库管理员）来说至关重要。本文将详细介绍如何在Oracle环境下利用回收站功能...
oracle数据库表数据误删恢复
shuxiansheng1的博客
04-26 407
今天因为操作失误误删了一个表中的部分数据，在没有数据备份且不会挖掘日志的情况下，感觉有点头大。幸好oracle提供了一种快照的方式，可以找回被删除的数据，在这里记录下 我找回数据的过程。 1.首先确定下删除数据的时间点，如果不确定准确的时间，也可用你知道的数据删除之前的时间点，不过这样在你使用的时间和你删除数据的时间点之间如果数据不一致，可能导致你找回的数据不全。 我删除的时间是2018-09...
Oracle数据库误删除数据的不同恢复方法整理（超级实用）
02-02
Oracle数据库误删除数据的不同恢复方法整理（超级实用）
Oracle 恢复表中误删的数据
csdnlyu的博客
03-21 260
Oracle 恢复表中误删的数据 不小心把系统中菜单表的数据删除了，特此记录一下恢复方法： 1、查询删除数据时间点之前的数据 select * from 表名 as of timestamp to_timestamp(‘2018-03-21 08:12:11’,’yyyy-mm-dd hh24:mi:ss’)； 2、将row movement设置为enable alter table...
ORACLE 误删表、误删表数据后恢复
kayGod
01-11 2521
最近在工作中使用ORACLE，一个失误删错了表，当时心急如焚，因为接触ORACLE时间不长，也不清楚ORACLE的功能， 同事就去找了下恢复的解决办法，发现ORACLE删除表后，是换了个名字，躺到了ORALCE的回收站中。         记录一下使用的语句，以便以后遇到。                  关于ORALCE误删表后恢复         查看回收站的表：      
oracle恢复误删除表
huyunfei的专栏
03-25 416
oracle恢复误删除表   今天用PLSQL不知道什么原因，用户下的所有表都没了，感觉可能是有人误删了。 部分转于：www.2cto.com  （在使用中遇到一些情况，加少许补充） 最终使用以下命令恢复： if(您要使用下面这个命令，请先看后面的准备工作) { flashback table username.T_tableName to
误删除 Oracle 数据库数据的恢复方法
qq_40907977的博客
09-27 5041
学习数据库时，我们只是以学习的态度，考虑如何使用数据库命令语句，并未想过工作中，如果误操作一下，都可能导致无可挽回的损失。 今天主要以oracle数据库为例，介绍关于表中数据删除的解决办法。（不考虑全库备份和利用归档日志） 删除表中数据有三种方法： delete（删除一条记录） drop或truncate删除表格中数据 1.delete误删除的解决方法 原理： 利用oracle提供的闪回方法，如果在删除数据后还没做大量的操作（只要保证被删除数据的块没被覆写），就可以利用闪回方式直接找回删除的数据。 具体
Oracle中误删表或表数据处理方法
m0_71581614的博客
02-15 384
Oracle数据库中误删表数据或者表的处理方法
Oracle误删表的记录怎么办？
Xeon_CC的博客
02-21 340
在删除数据的时候不小心，把delete语句执行错了，把别的表给delete，而且还执行了commit！那么如何进行恢复呢，下面介绍几种恢复方法。 登陆Oracle： sqlplus username/password@192.168.0.xxx/服务名 1.打开Flash存储的权限 ALTER TABLE tableName ENABLE row movement ; 2.把表还原到指定时间点 flashback table tableName to timestamp to_timestamp(’‘201
oracle数据库误删的表以及表中记录的恢复
凌枫ozo的博客
04-19 914
oracle数据库误删的表以及表中记录的恢复 本文转载自：https://blog.csdn.net/fei7837226/article/details/80446552 一、表的恢复（闪恢复） 对误删的表，只要没有使用PURGE永久删除选项，那么从flash back区恢复回来希望是挺大的。一般步骤有： 1、从flash back里查询被删除的表 ...
Oracle误删表后，如何找回
最新发布
weixin_42421495的博客
05-21 925
Oracle误删表后，迅速找回
Oracle数据库误删表数据恢复步骤详解
"Oracle数据库中误删除表数据后的恢复方法主要涉及了undo_retention参数的查询与修改，以及Oracle的快速恢复功能，包括回闪功能的使用。在Oracle 10gR2环境下，通过调整undo_retention参数可以延长撤销段的保留时间...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值