oracle11g 数据文件误删恢复(…

OS:

Oracle Linux Server release 5.7

 

DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

首先创建yoon数据库

SQL> create tablespace yoon datafile '/u01/oracle/oradata/yoon/yoon01.dbf' size 50m;

Tablespace created.

 

SQL> create user yoon identified by yoon default tablespace yoon;

User created.

 

SQL> grant dba to yoon;

Grant succeeded.

 

SQL> create table yoon.yoon as select * from scott.emp;

Table created.

 

在操作中误删除数据文件yoon01.dbf

rm -rf yoon01.dbf

select ts#,file#,name,bytes,status from v$datafile;

ps -ef | grep dbw  | grep -v grep

cd /proc/..../fd

 

一、在线恢复：

[oracle@b28-122 yoon]$ rm -rf yoon01.dbf

 

[oracle@b28-122 yoon]$ ps -ef |grep dbw |grep -v grep
oracle   17417     1  0 00:51 ?        00:00:00 ora_dbw0_yoon

 

[oracle@b28-122 yoon]$ cd /proc/17417/fd

 

[oracle@b28-122 fd]$ cp 267 /u01/oracle/oradata/yoon/newyoon01.dbf

 

SQL> alter database datafile 6 offline;

Database altered.

 

SQL> alter database rename file '/u01/oracle/oradata/yoon/yoon01.dbf' to '/u01/oracle/oradata/yoon/newyoon01.dbf';

Database altered.

 

SQL> recover datafile 6;
Media recovery complete.

 

SQL> alter database datafile 6 online;

Database altered.

 

二、离线恢复

rm -rf yoon01.dbf

ps -ef | grep dbw | grep -v grep

cp /proc/.../fd/...   /u01/oracle/oradata/yoon/yoon01.dbf

shutdown immediate

startup

recover database;

alter database open;

 

[oracle@b28-122 yoon]$ rm -rf yoon01.dbf

 

[oracle@b28-122 yoon]$ ps -ef | grep dbw | grep -v grep
oracle   18743     1  0 01:03 ?        00:00:00 ora_dbw0_yoon

 

[oracle@b28-122 yoon]$ cd /proc/18743/fd

 

[oracle@b28-122 fd]$ cp 265 /u01/oracle/oradata/yoon/yoon01.dbf

 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2432699776 bytes
Database Buffers          889192448 bytes
Redo Buffers               16326656 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oracle/oradata/yoon/yoon01.dbf'

 

SQL> recover database;
Media recovery complete.

 

SQL> alter database open;

Database altered.

 

SQL> select * from yoon.yoon;

     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.