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

此時只能通過之前的備份恢複，跟第四種情形恢複方法一樣，不再贅述。

Oracle資料庫常見的誤操作恢複方法（上）