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.