Oracle数据误删恢复机制

Oracle数据库在多版本数据管理的设计是非常优秀的，数据的误删恢复非常简单。除了truncate以外，drop table语句和所有DML语句都是可以轻松恢复。

恢复DROPED TABLE

Oracle和windows类似，也有个‘回收站’的概念，查看回收站的方法很多，例如user_recyclebin系统视图是比较常用的，该视图中的内容包括（恢复操作所需要的）：

被删除的表在回收站中叫什么名字（OBJECT_NAME）
被删除的表原来叫什么名字（ORIGINAL_NAME）
表是什么时候被删除的（DROPTIME）

desc user_recyclebin;

名称             空值?      类型           
-------------- -------- ------------ 
OBJECT_NAME    NOT NULL VARCHAR2(30) 
ORIGINAL_NAME           VARCHAR2(32) 
OPERATION               VARCHAR2(9)  
TYPE                    VARCHAR2(25) 
TS_NAME                 VARCHAR2(30) 
CREATETIME              VARCHAR2(19) 
DROPTIME                VARCHAR2(19) 
DROPSCN                 NUMBER       
PARTITION_NAME          VARCHAR2(32) 
CAN_UNDROP              VARCHAR2(3)  
CAN_PURGE               VARCHAR2(3)  
RELATED        NOT NULL NUMBER       
BASE_OBJECT    NOT NULL NUMBER       
PURGE_OBJECT   NOT NULL NUMBER       
SPACE                   NUMBER

示例：

先简单创建一张测试表，然后删除

SQL> conn test/test
Connected.
SQL> create table mytbl as select 1 as flag from dual;

Table created.

SQL> select * from mytbl;

      FLAG
----------
     1

SQL> drop table mytbl;

Table dropped.

然后可以在user_recyclebin中找到我们刚才删除的表

SQL> col object_name for a30
SQL> col original_name for a30
SQL> col droptime for a30
SQL> set linesize 1000
SQL> select object_name,original_name,droptime from user_recyclebin;

OBJECT_NAME            ORIGINAL_NAME              DROPTIME
------------------------------ ------------------------------ ------------------------------
BIN$ksxWUEIJRM3gUADAhgIE7Q==$0 MYTBL                  2019-09-17:23:21:47

可以使用OBJECT_NAME来查看表中的数据，OBJECT_NAME是由系统自动生成的名字，需要用双引号“”才能被识别为表名。

SQL> select * from "BIN$ksxWUEIJRM3gUADAhgIE7Q==$0";

      FLAG
----------
     1

接下来就可以用flashback table to before drop或flashback table to before drop rename to命令将表找回或重命名为新的表名

SQL> flashback table "BIN$ksxWUEIJRM3gUADAhgIE7Q==$0" to before drop;

Flashback complete.

SQL> select * from mytbl;

      FLAG
----------
     1

SQL> select * from user_recyclebin;

no rows selected

flashback table “XXX” to before drop rename to XXX命令可以将表命名为新的名字，而非ORIGINAL_NAME。恢复之后，回收站中该对象就看不到了。

由此我们可以看出，所谓的DROP TABLE表删除本质上仅仅是将表重命名为新的名字，而非物理清除。如果想物理清除需要使用PURGE关键字。

SQL> drop table mytbl purge;

Table dropped.

SQL> select * from user_recyclebin;

no rows selected

当然，被删除的表不可能永远保留，保留的时间和表空间的剩余空间有关，当剩余表空间不足时，则回收站里的对象会被清理掉，优先清理最早放进回收站的（DROPTIME最小的）。

恢复CHANGED ROWS

和被删除的表类似，被DML语句修改的行，也会在数据库中保留一段时间，保留时间长短和UNDO的保留策略以及UNDO的实际使用情况有关，稍后会更详细讲解。先来看看如何对已经提交的DML语句影响的行恢复到语句执行之前的状态（未提交的直接执行rollback即可）。

方法一：VERSIONS BETWEEN

示例：

模拟误删场景，先查看一下EMP表中的数据，然后删除表中的一行并提交，

SQL> conn scott/tiger
Connected.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from emp;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

SQL> delete from emp where empno = 7369;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from emp where empno = 7369;

no rows selected

利用VERSIONS子句查看被删除的行相关的信息

SQL> select * from emp versions between scn minvalue and maxvalue where empno = 7369;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7369 SMITH      CLERK       7902 17-DEC-80        800            20

我们明明只删了一行，为什么查询的结果中有两行呢？加上versions between scn minvalue and maxvalue关键字后，实际上是显示了表上数据行所有变化的“流水账”，准确的说，并非所有变化，而是UNDO保留时间窗口内记录的所有”流水账“加上表在UNDO保留时间窗口的起始时间点的静态版本。比如，undo_retention参数设置成了3600，表示这个保留时间窗口为1小时（3600s），那么versions子句所能看到的数据为：表在一小时前那个时间点的静态版本+这一小时内的所有的变化流水。

流水记录中的内容除了包括数据行内容本身以外，还包括这一行上发生的具体操作是什么（versions_operation辅助隐藏列，D表示delete，I表示insert，U表示update），操作发生的时间是什么（导致该行发生变化的操作何时开始versions_startscn,该行再次发生变化的时间即该行不再是最新行版本的时间versions_endscn，SCN是数据库随时间单调递增的系统变更号，可以理解为时间）

SQL> col versions_startscn for 99999999999999
SQL> col versions_endscn for 99999999999999
SQL> col versions_operation for a30

SQL> select versions_operation,versions_startscn,versions_endscn,t.empno 
   from emp versions between scn minvalue and maxvalue t;  

VERSIONS_OPERATION   VERSIONS_STARTSCN VERSIONS_ENDSCN        EMPNO
-------------------- ----------------- --------------- ------------
D                     145852760112                            7369
                                         145852760112         7369
                                                              7499
                                                              7521
                                                              7566
                                                              7654
                                                              7698
                                                              7782
                                                              7788
                                                              7839
                                                              7844
                                                              7876
                                                              7900
                                                              7902
                                                              7934

15 rows selected.

这样我们就能清楚地区分出EMPNO=7369的两行当中，versions_operation='D'的那一行是我们刚才删除的那一行，而另一行的versions_operation is null表示该行为初始版本（UNDO保留最早时间点的数据版本），由于被delete之前，改行没有其他操作，实际上两行的数据部分完全一致，恢复时只需要对查询结果去重即可，或者添加versions_operation = 'D'条件

现在我们开始恢复：

SQL> insert into emp select * from emp versions between scn minvalue and maxvalue where versions_operation = 'D' and empno = 7369;

1 rows inserted.

commit;

方法二：FLASHBACK

原理简单介绍：

Oracle FLASHBACK TABLE 功能同样是利用了UNDO“记流水账”的功能，但FLASHBACK TABLE TO BEFORE DROP不是，前面已经说了，这是使用了“回收站”的原理。回收站纯粹是为了防误删而设计的，而UNDO除了误删恢复以外，还有其他更重要的意义——UNDO和事务的一致性和隔离性息息相关、事务的回滚（rollback命令或事务的异常终止）和一致性读需要UNDO。这里给大家简单科普一下UNDO的大致作用，若当前会话上的事务对数据进行了修改，在执行提交之前，其他的会话不应该看到当前会话修改后的内容（事务隔离性），但数据又的确是修改了，只是没有提交，因此需要UNDO来实现其他会话看到当前会话修改之前的数据版本，UNDO具有这种记流水账的功能，因此具备提供时间窗口内任意一个时间点的数据版本的能力。

示例：

查看数据，然后删除一行，删除之前查看一下时间，主要是为了记录一个误删之前的时间点，后续恢复需要用到这个时间。这种方法相对于versions方法的局限性在于需要回忆起误删操作的大致时间范围。

SQL> conn scott/tiger
Connected.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from emp;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  as cur_date from dual;

CUR_DATE
-------------------
2019-09-19 02:07:15

SQL> delete from emp where empno = 7369;

1 row deleted.

SQL> commit;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as cur_date from dual;

CUR_DATE
-------------------
2019-09-19 02:08:29

SQL> select * from emp where empno = 7369;

no rows selected

使用as of timestamp子句查看指定时间点的数据版本

SQL> select * from emp as of timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss');

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

SQL> select * from emp as of timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss') where empno = 7369;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20

然后开始恢复，可以直接将as of timestamp语句(闪回查询)查到的误删行insert回去，也可以用flashback table to timestamp语句进行闪回操作。

SQL> flashback table emp to timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

两种方法的区别和优缺点总结：

versions方法受限于undo_retention参数,时间窗口会随着时间不段向后推进，若undo_retention设置过小，则很快就没法查到变化记录，不过可以临时调大参数来满足查询，优点是可以查到所有行的修改操作记录，缺点是比较复杂，不太直观。FLASHBACK方法则可以查到UNDO中实际存在的所有数据版本，和undo_rentention参数无关，UNDO保留的机制和回收站类似，取决于UNDO表空间的剩余大小，还受到相关参数的影响，flashback table方法的优点是便捷、直观，缺点是需要找到相对准确的时间点。

最后编辑于：2019.09.19 15:16:34

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,835评论 4赞 364
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,598评论 1赞 295
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,569评论 0赞 244
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,159评论 0赞 213
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,533评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,710评论 1赞 222
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,923评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,674评论 0赞 203
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,421评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,622评论 2赞 245
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,115评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,428评论 2赞 254
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,114评论 3赞 238
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,097评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,875评论 0赞 197
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,753评论 2赞 276
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,649评论 2赞 271