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方法的优点是便捷、直观，缺点是需要找到相对准确的时间点。
网友评论