Oracle恢复delete、drop和truncate操作后的数据详解_四海名汀

http://blog.sina.com.cn/u/1558925640

首页博文目录关于我

个人资料

四海名汀

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：0

博客访问：0
关注人气：0
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Oracle恢复delete、drop和truncate操作后的数据详解

(2015-09-15 17:30:47)

标签：

恢复drop操作数据

恢复delete操作数据

oracle恢复删除数据

恢复表数据oracle

恢复truncate操作数据

分类： Oracle数据库

在Oracle日常数据处理的过程中，偶尔会遇到误操作删除表或表中数据的情况，那这时候该怎么办呢？能不能恢复数据呢？带着这些问题，我们将常用SQL操作delete和drop删除数据或数据表的数据恢复方法整理如下。

Oracle保存在回滚段里的数据时间是有一定时间限制的，由undo_retention(保持力)参数来决定。

在Command Window窗口中输入show parameter undo;查看该参数的值，该参数的单位为秒，如下：

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

可以看见，undo_retention参数的值为900秒，即15分钟。

修改默认的undo_retention参数设置为：

alter system set undo_retention=10800 scope=both;

一、创建测试表添加测试数据

--1、创建测试表

drop table t_mytest;

create table t_mytest(

tid number

)

--添加测试数据

insert into t_mytest

select 1 from dual

union all

select 2 from dual

union all

select 3 from dual

union all

select 4 from dual;

commit;

select * from t_mytest;

二、drop操作后的数据恢复

--删除表

drop table t_mytest;

--查看该表在数据库回收站中的情况

select object_name,original_name,partition_name,type,ts_name,createtime,droptime

from recyclebin

where original_name='T_MYTEST';

--查询该表在回收站的drop记录

OBJECT_NAME                    ORIGINAL_NAME                    PARTITION_NAME                   TYPE                      TS_NAME                        CREATETIME          DROPTIME
------------------------------ -------------------------------- -------------------------------- ------------------------- ------------------------------ ------------------- -------------------
BIN$eB3jYsPge6HgUwEAAH+N0w==$0 T_MYTEST                                                          TABLE                     TSP_APP                        2018-10-13:22:18:58 2018-10-13:22:19:12
BIN$eB3jYsPhe6HgUwEAAH+N0w==$0 T_MYTEST                                                          TABLE                     TSP_APP                        2018-10-13:22:21:07 2018-10-13:22:21:45

--如果上面查询结果有多条drop记录，选择你需要的时间点的表恢复，恢复可通过下列语句实现
flashback table "BIN$eB3jYsPhe6HgUwEAAH+N0w==$0" to before drop;

--如果上面查询只有一条drop记录，可以通过下列语句实现数据恢复
flashback table "BIN$eB3jYsPhe6HgUwEAAH+N0w==$0" to before drop;
--或者
flashback table t_mytest to before drop;

--如果想将drop操作的表恢复数据后，重命名为其他表名，可以通过下列语句来实现
flashback table "BIN$eB3jYsPge6HgUwEAAH+N0w==$0" to before drop rename to my_test01;

三、delete操作后的数据恢复

1、通过系统时间戳来恢复数据

--删除表中的某些数据

select *

--delete

from T_MYTEST

where tid=1;

commit;

--启动行移动功能

alter table T_MYTEST enable row movement;

--将数据恢复到删除之前的某个时间点

flashback table T_MYTEST to timestamp to_timestamp('2015-09-07 14:46:23','yyyy-mm-dd hh24:mi:ss');

--查看恢复 delete 操作后的数据情况

select * from T_MYTEST;

说明：此举可以恢复表结构，同时数据也恢复了，但必须是Oracle9i或10g以上版本支持，flashback无法恢复全文索引。

2、通过系统改变号SCN(system change number)来对数据进行恢复

--方法2

select *

--delete

from T_MYTEST

where tid in (1,3);

commit;

--查看系统当前的 SCN (当前查询到的 SCN=3150279)

select dbms_flashback.get_system_change_number from dual;

--找到数据删除前的系统 SCN (此处SCN 小于系统当前的 SCN )

如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试。

select * from T_MYTEST as of scn 3150276;

--恢复数据处理

将删除的两条数据恢复到原数据表中

insert into T_MYTEST select * from T_MYTEST as of scn 3150276

where tid not in (select tid from T_MYTEST);

commit;

--查看恢复 delete 操作后的数据

select * from T_MYTEST

3、通过基于系统时间戳的SCN来恢复数据

select *

--delete

from T_MYTEST

where tid in (1,2,3);

commit;

--查看系统基于时间戳的 SCN (此处的时间戳稍微小于删除的时间点即可，当前查询到的 SCN=3150728)

select timestamp_to_scn(to_timestamp('2015-09-10 15:50:00','yyyy-mm-dd hh24:mi:ss')) from dual;

--找到数据删除前的系统 SCN

如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试。

select * from T_MYTEST as of scn 3150728;

--恢复数据处理

将删除的两条数据恢复到原数据表中

insert into T_MYTEST select * from T_MYTEST as of scn 3150728

where tid not in (select tid from T_MYTEST);

commit;

--查看恢复 delete 操作后的数据

select * from T_MYTEST

注意：当表被drop之后，又重新创建了改变，此时如果回收站中查不到相关记录，则数据将不能恢复了，除非是在事务中commit之前的删除数据可以恢复。

四、truncate操作后的数据恢复
当我们对某个表采用truncate(截断)的方式来将数据清空后，如果想要恢复清空的表数据，这时就需要使用 PRM for Oracle 这个工具来恢复数据。ParnassusData Recovery Manager(以下简称PRM)是企业级ORACLE数据灾难恢复软件，可直接从Oracle9i,10g,11g,12c的数据库数据文件 (datafile)中抽取还原数据表上的数据，而不需要通过ORACLE数据库实例上执行SQL来拯救数据。 ParnassusDataRecovery Manager是一款基于JAVA开发的绿色软件，无需安装，下载解压后便可直接使用。
该工具的详细操作过程请参考：
http://www.askmaclean.com/archives/oracle-prm-dul-manual-cn.html

本文根据网络资料，通过实际的数据测试总结而来。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：利用sqluldr2导出数据和sqlldr导入数据的方法

后一篇：Oracle恢复Update操作前的数据详解

新浪BLOG意见反馈留言板　欢迎批评指正