误删数据的紧急修复

最新推荐文章于 2024-10-30 22:57:10 发布

cuiwangxie1183

最新推荐文章于 2024-10-30 22:57:10 发布

阅读量123

点赞数

文章标签：数据库

在日常维护数据库过程或开发应用过程，可能由于某种原因不慎将表删除了，这时有哪些补救措施呢？下面我们来说说其中的一种方法：

flashback drop用于恢复用户误删除的对象（包括表，索引等）这个技术依赖于table recycle bin(表空间回收站,前提是drop表未使用purge），flashback 不支持sys用户，system表空间下的对象，也不能从回收站里拿到。故使用sys或者system用户登陆时show recyclebin为空。

flashback drop是基于tablespace recyclebin来实现的，它只支持闪回与table相关连的对象，比如表，索引，约束，触发器等，如果是函数或者存储过程需要flashback query来实现

从oracle 10g开始，每个表空间都会有一个叫作回收站的逻辑区域，当用户执行drop（未加purge）命令时，被删除的表和表的相关联的对象（包括索引，约束，触发器，LOB段，LOB index段）不会被物理删除，这些对象先转移到回收站中，这就给用户提供了一个恢复的可能。

当发生空间不够时，oracle会按照先入先出的顺序覆盖recycle Bin中的对象，也可以手动的删除recycle Bin占用的空间。

当然要使用这个功能首先要确保，该功能已经开启，我们可以使用show parameter recyclebin 来查看该功能是否开启，如查没有可以使用以下命令进行开启或关闭：

alter system set recyclebin=on;

alter system set recyclebin=off;

alter session recyclebin=on;

alter session recyclebin=off;

下面来看看基本的命令：

1.purge tablespace tablespace_name 用于清空表空间的recycle bin

2.purge tablespace tablespace_name user user_name 清空指定表空间的recyclebin中指定用户的对象

3.purge recyclebin 删除当前用户的recyclebin中的对象

4.purge dba_recyclebin;删除所有用户的recyclebin中的对象，该命令要sysdba权限

5.drop table table_name purge 删除对象不放入recyclebin中永久删除

6.purge index recycle_bin_object_name当想释放recyclebin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。因为索引是可以重建的。

7.show recyclebin 查看回收站中有哪些对象，也可以使用select original_name,object_name from recyclebin;查询

下面我们来测试一下：

首先生成一张表，生成一张测试数据，新建一个索引。

然后drop表（未加purge）

使用show recyclebin；查看回收站有t2表，但不能显示索引信息

使用select original_name,object_name from recyclebin;可以看到索引信息

使用命令恢复删除的表flashback table t1 to before drop;可以看到表已经恢复便索引的名字未恢复

恢复索引alter index "BIN$djskdjks" rename to inx_t1;

如果已经有了要恢复的表名则使用命令：flashback table t1 to before drop rename to t2;

flashback table "BIN$9isdksjdksla" to before drop; recyclebin中有二张一样的表

flashback drop 的限制条件

1.只能用于非系统表空间和本地管理的表空间

2.对象的参考约束不会被恢复，指向该对象的外键约束需要重建

3.对象能否恢复成功，取决于对象空间是否被覆盖重用

4.当删除表时，依赖于该表的物化视图也会同时删除，但是由于物化视图不会放入到recyclebin中，所以需要重建

当我们误更新或删除数据时这时又没有备份时，我们可以使用闪回查询来紧急处理，下面介绍下闪回查询：

flashback query分三种：

flashback query

flashback version query

flashback transaction query

flashback query是利用多版本一致性的特性从undo表空间读取操作前的记录数据，flashback query对v$table,x$tables等动态性能视图无效。对于dba_*,all_*,user_* 等数据字典是有效的。

下面来测试一下：生成测试数据

下面更新几条数据：

基于时间的查询

select * from t2 as of timestamp to_timestamp('2015-12-02 02:50:31','yyyy-mm-ddhh24:mi:ss');

下面是删除数据的测试：

这时我们就可以使用该语句进行恢复：insert into t1 select * from t2 as of timestamp to_timestamp('2015-12-02 02:10:45','yyyy-mm-dd hh24:mi:ss');

commit;

如上述示例中所表示的，as of timestamp 的确非常易用，但是在某些情况下，建议使用as of scn的方式执行flashback query,比如需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp的方式，可能会由于时间点不綂一的缘故造成数据选择或插入失败，通过scn方式则能够确保记录的约束一致.

利用scn查询

更新数据:

利用select imestamp_to_scn(to_timestamp('13-JAN-12 04.05.57','dd-mm-yy hh24.mi.ss')) from dual;根据时间找对应的scn号，利用select scn_to_timestamp(3814379) from dual; 查找scn号对应的时间

闪回查询：

flashback version query查询某段时间内数据变化

相对于flashback query只能看到某一点的对象状态，oracle 10g引入的flashback version query可以看到过去某个时间段内，记录是如何发生的。根据这个历史，DBA就可以快速的判断数据是在什么时间的状态，flashback version query 技术其实有很多伪列，但是ora_rowscn是最重要。它记录的是最后一次被修改的scn.注意是被提交的修改。如果没有提交，这个伪列不会发生变化

ora_rowscn 伪列

select ora_rowscn,id from t2;

Select versions_xid,versions_startscn,versions_endscn, DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original') "Operation", id from t2

versions between scn minvalue and maxvalue;

select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid in(select versions_xid from t1 versions between scn 12345 and 2345);