Oracle数据库常见的误操作恢复方法（上）
2015-03-13 22:16 AlfredZhao 阅读(2029) 评论(1) 编辑 收藏 举报实验环境：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
此时只能通过之前的备份恢复，跟第四种情形恢复方法一样，不再赘述。
