Oracle误删除数据和表的恢复办法

浅浅的花香味﹌ 2023-01-03 01:46 206阅读 0赞

# 前言 #

在工作中我们操作数据库的时候经常会发生一个不该发生的问题：用户意外的删除一个非常重要的表或者是表中的数据而且没有备份，需要尽快的恢复，以下就是解决的办法：

*  *主要是利用Oracle回收站的闪回特性oracle 回收站recyclebin是10g才有的新特性，当我们drop table cube\_scope【purge】时，如果不指定purge时，系统只是将这个表重命名为BIN$开头的名称，并在数据字典中修改了相关数据，表所占用的物理空间并没有真正的回收，此时所占用的空间还是原来的表空间，当表空间不够用时，ORACLE会跟据DROPSCN\#自动进行逐个清理回收站中对像所占用的空间，10g默认是打开回收站功能的。*

DML和DDL含义和区别一定要搞明白

> DML与DDL的含义：  
> 1、DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge  
> 2、DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：  
> create、alter、drop、truncate、comment、grant、revoke  
> 二、DML与DDL的区别：  
> 1.DML操作是可以手动控制事务的开启、提交和回滚的。  
> 2.DDL操作是隐性提交的，不能rollback！

## 一、delete 数据 误删除 ##

**1.删除数据之后表结构没有变化**

直接使用表闪回，表闪回要求用户必须要有flash any table权限  
先确定删除数据的时间(在删除数据之前的时间就行,不过最好要具体到删除数据的时间点)

alter  table 表名 enable row  movement    //开启行移动功能
    flashback table 表名 to timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss') 
    //恢复数据到删除时间点之前的状态
     Alter table 表名 disable row movement      //关闭行移动功能(一定不能忘记)

**2、 删除数据之后表结构发生了变化**

//找出被删除的数据
    select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss')
     //把删除的数据重新插回原表，但注意主键不要重复
    insert into 表名 (select * from 表名
     as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss'));

## 二 、drop命令 删除表 ##

由于oracle在删除表时，没有直接清空表所占的块,oracle把这些已删除的表的信息放到了一个虚拟容器`“回收站”`中，而只是对该表的数据块做了可以被覆写的标志，所以在块未被重新使用前还可以恢复。

--查看回收站中的表
    select object_name,original_name,partition_name,type,ts_name,createtime,droptime
    from recyclebin;

可以看到被删除的表在回收站被命名为`BIN$0z+2cCjdSjqkx1nsP/re7w==$0`

flashback table 原表名 to before drop (rename to 新表名)   //闪回原表(重命名新表名)

或者

//用户可能会经常多次创建和删除同一个表就需要找到回收站的表名闪回相应的表版本
    flashback table "回收站中的表名(如：BIN$0z+2cCjdSjqkx1nsP/re7w==$0)" 
    to before drop (rename to 新表名)

但是用这种方法只是把我们的表给找回来了，我们的索引和约束都没有回来。所以我们一定要重新建立索引和约束。如果oracle 闪回功能还可以闪回整个数据库，使数据库回到过去某一状态。

alter database flashback on
    
    flashback database to scn SCNNO;     //使用SCN号闪回或者使用时间戳闪回
    
    flashback database to timestamp to_timestamp('2007-10-25 12:00:00','yyyy-mm-dd hh24:mi:ss');

如果想要不经过回收站直接删除并释放所占空间

drop table 表名 purge  //此命令相当于truncate+drop 操作，一般不建议这么做。

手工清除回收站中的对象。

prug table 表空间.表名     //清除具体的对象
    
    prug tablespace  表空间    //清除指定的表空间对象
    
    purge tablespace 表空间 user 用户名  //删除表空间指定用户下的所有对像
    
    purge recyclebin    //删除回收站
    
    purge dba_recyclebin    //删除全体用户在回收站的数据

**ORACLE空间利用原则**

1.  使用现有的表空间的未使用空间
2.  如果没有了空闲空间，则检查回收站，对于回收站的对象按照先进先出的原则，对于最先删除的对象，oracle在空间不足之时会最先从回收站删除以满足新分配空间的需求
3.  如果回收站也没有对象可以清理，则检查表空间是否自扩展，如果自扩展则扩展表空间，然后分配新空间
4.  .如果表空间非自扩展，或者已经不能自扩展(到达最大限制)，则直接报表空间不足错误，程序终止

*  `drop tablespace` :会将recyclebin中所有属于该tablespace的对像清除
 *  `drop user` ：会将recyclebin中所有属于该用户的对像清除
 *  `drop cluster` : 会将recyclebin中所有属于该cluster的成员对像清除
 *  `drop type` : 会将recyclebin中所有依赖该type对像清除

*另外还需要注意一种情况，对像所在的表空间要有足够的空间，不然就算drop掉经过recyclebin由于空间不足oracle会自动删除的哦（切记）！*

## 三、truncate删除数据 ##

**1、使用闪回特性**

因为truncate不是DML语句，是DDL语句，不能使用闪回查询的方式恢复表数据，这里介绍一种通过flashback database(闪回整个数据库)的方式恢复数据的方法。

在11g下，可以在mount和open模式来执行该命令

select status from v$instance;   查看数据库模式
    alter database open;  //或者是mount
    alter database flashback on
    flashback database to timestamp to_timestamp('2007-10-25 12:00:00','yyyy-mm-dd hh24:mi:ss');

在执行完flashback database 命令之后，可以使用多种方式修复数据库：

1). 直接alter database open resetlogs 打开数据库，指定scn 或者timestamp 时间点之后产生的数据统统丢失。

2). 先执行alter database open read only 命令以read-only 模式打开数据库,查看恢复后的数据是否满足要求，如果满足要求，则通过resetlogs打开数据库，否则，重新执行flashback 操作。

3). 先执行alter database open read only 命令以read-only 模式打开数据库，然后立刻通过逻辑导出的方式将误操作涉及表的数据导出，再执行recover database 命令以重新应用数据库产生的redo，将数据库修复到flashback database 操作前的状态，然后再通过逻辑导入的方式，将之前误操作的表重新导入，这样的话对现有数据的影响最小，不会有数据丢失。

这里演示第二种方法

alter database open read only;

查看数据是否符合要求不符合继续`flashback database to timestamp to_timestamp`

如果符合

startup mount force；
    
    alter database open resetlog;

第三种方法  
闪回之后

alter database open read only
    
    exp  用户名/用户密码 file=t.dmp tables=表名;  //导出被truncate表的数据 如果不熟悉exp命令的话，可以去看exp/expdp 与 imp/impdp命令导入导出数据库详解
    
    shutdown immediate  //重启数据库恢复到闪回之前的时间点
    
    startup mount;
    
    recover database;
    
    alter database open;
    
    然后导入刚才导出的数据
    
    imp 用户名 /密码  file=t.dmp tables=表名 ignore=y;

转载自：[Oracle误删除数据和表的恢复办法][Oracle]  
本文只对其进行了补充和格式调整

[Oracle]: https://www.cnblogs.com/lk-fxtx/p/7739805.html