Oracle数据库常见的误操作恢复方法（上）
实验环境：Linux6.4 + Oracle 11g
面向读者：Oracle开发维护人员
本文以Oracle自带的scott用户进行演示：
首先逻辑备份导出scott的对象数据
$ exp scott/tiger file=‘/u01/app/backup/scott.dmp‘ log=‘/u01/app/backup/scott.log‘ owner=scott;
1.误操作drop了emp表
利用表级闪回恢复，只要回收站中有就可以恢复。
SQL> drop table emp;
Table dropped.
闪回恢复
SQL> flashback table emp to before drop;
Flashback complete.
闪回回来的表如果之前有主键和索引，对应的名字会变成一串字符，虽然不影响使用，但为了规范管理，建议将它们重命名为之前的名字
重命名索引名称：
SQL> alter index "BIN$ESbzggEjPKfgU58JqMDOTQ==$1" rename to PK_EMP;
Index altered
重命名主键约束名称：
SQL> alter table emp rename constraint "BIN$ESbzggEiPKfgU58JqMDOTQ==$1" to PK_EMP;
Table altered
若闪回表时，数据库已存在同名表，可以在闪回时指定新的表名：
SQL> flashback table emp to before drop rename to emp1;
Flashback complete.
2.误操作delete了emp表
利用闪回查询结果恢复，只要undo表空间中还有误操作时间前的数据。
SQL> delete from emp;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from emp;
no rows selected
SQL> select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘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.
确定查询结果是要恢复的数据后，可以直接插入到emp表。
SQL> insert into emp select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘);
14 rows created.
SQL> commit;
Commit complete.
3.误操作delete了emp表的部分记录
利用闪回查询结果恢复，只要undo表空间中还有误操作时间前的数据，跟第二种场景一样，只是多了条件限定。
SQL> delete from emp where sal <2000;
8 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
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
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) where sal <2000;
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
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
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
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8 rows selected.
确定查询结果是要恢复的数据后，可以直接插入到emp表。
SQL> insert into emp select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) where sal <2000;
8 rows created.
SQL> commit;
Commit complete.
4.误操作truncate了emp表
SQL> truncate table emp;
Table truncated.
这时由于表定义已经改变，无法查到表的历史数据了
SQL> select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘);
select * from emp as of timestamp to_timestamp(‘2015-03-13 15:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘)
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
只能通过之前的备份恢复了，当然，只能恢复到exp导出时的状态。
$ imp scott/tiger file=‘/u01/app/backup/scott.dmp‘ log=‘/u01/app/backup/restore_emp.log‘ tables=emp ignore=y
Import: Release 11.2.0.4.0 - Production on Fri Mar 13 15:29:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT‘s objects into SCOTT
. importing SCOTT‘s objects into SCOTT
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
验证数据已经成功恢复
SQL> select count(1) from emp;
COUNT(1)
----------
14
5.误操作带有purge选项drop了表
SQL> drop table emp purge;
Table dropped.
由于purge选项的drop不进入回收站，闪回表不再可用。
SQL> flashback table emp to before drop;
flashback table emp to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
此时只能通过之前的备份恢复，跟第四种情形恢复方法一样，不再赘述。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
