两种方式恢复误删除的oracle文件

使用create datafile以及拷贝进程句柄两种方式恢复误删除的oracle文件。推荐使用alter database create datafile 方式。

测试环境:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /archive
Oldest online log sequence     90
Next log sequence to archive   92
Current log sequence           92
SQL>

测试数据准备：
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
TESTTBS               //测试用表空间
6 rows selected.
SQL>

SQL> select * from tt;
        ID NAME
---------- ----------
         0 kk
SQL>
SQL> insert into tt values (1,'tom');  //插入测试数据
1 row created.
SQL> commit;
Commit complete.
SQL>

SQL>
SQL> select * from tt;  //恢复后可以查询到这两条记录即可

        ID NAME
---------- ----------
         0 kk
         1 tom

SQL>

SQL> select file_id,file_name,tablespace_name from dba_data_files;

SQL> set line 300
SQL>
SQL> /

   FILE_ID FILE_NAME                                                    TABLESPACE_NAME
---------- ------------------------------------------------------------ ------------------------------
         4 /u01/app/oracle/oradata/orcl/users01.dbf                     USERS
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf                   UNDOTBS1
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf                    SYSAUX
         1 /u01/app/oracle/oradata/orcl/system01.dbf                    SYSTEM
         5 /u01/app/oracle/oradata/orcl/testtbs.dbf                     TESTTBS

SQL>

方法一：alter database create datafile ' '，alter database recover datafile ' ',alter tablespace ' 'online(此方式可以关闭数据库，利用archive和redo文件恢复数据。)

SQL> host rm /u01/app/oracle/oradata/orcl/testtbs.dbf
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from tt;
select * from tt
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/testtbs.dbf'  //系统已经报错

SQL>
SQL> alter database create datafile '/u01/app/oracle/oradata/orcl/testtbs.dbf';  //step1，create datafile

Database altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/testtbs.dbf';  //step2，recover datafile
ORA-00279: change 1474613 generated at 01/20/2016 09:49:10 needed for thread 1
ORA-00289: suggestion : /archive/1_82_898310472.dbf
ORA-00280: change 1474613 for thread 1 is in sequence #82


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1474972 generated at 01/20/2016 09:51:50 needed for thread 1
ORA-00289: suggestion : /archive/1_83_898310472.dbf
ORA-00280: change 1474972 for thread 1 is in sequence #83


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1474975 generated at 01/20/2016 09:51:52 needed for thread 1
ORA-00289: suggestion : /archive/1_84_898310472.dbf
ORA-00280: change 1474975 for thread 1 is in sequence #84


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1474979 generated at 01/20/2016 09:51:54 needed for thread 1
ORA-00289: suggestion : /archive/1_85_898310472.dbf
ORA-00280: change 1474979 for thread 1 is in sequence #85


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1474983 generated at 01/20/2016 09:51:54 needed for thread 1
ORA-00289: suggestion : /archive/1_86_898310472.dbf
ORA-00280: change 1474983 for thread 1 is in sequence #86


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1474990 generated at 01/20/2016 09:51:57 needed for thread 1
ORA-00289: suggestion : /archive/1_87_898310472.dbf
ORA-00280: change 1474990 for thread 1 is in sequence #87


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL>
SQL> alter tablespace testtbs online;  //step3 alter tablespace online

Tablespace altered.

SQL>
SQL> select * from tt;   //完成

        ID NAME
---------- ----------
         0 kk
         1 tom

SQL>

方法二：cp /proc/进程id/fd/*******  /****/testtbs.dbf 拷贝linux句柄（句柄不明白啥意思，姑且先这样写），recover datafile恢复数据（此方式数据库不能关闭）。

SQL> host rm /u01/app/oracle/oradata/orcl/testtbs.dbf

SQL> select * from tt;

        ID NAME
---------- ----------
         0 kk
         1 tom

SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL> select * from tt;
select * from tt
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/testtbs.dbf'
SQL>

[oracle@training proc]$             //切换oracle系统账号
[oracle@training proc]$ ps -ef |grep dbw         //查询dbwr进程id
oracle    3601     1  0 01:31 ?        00:00:00 ora_dbw0_orcl
oracle    3789  3758  0 01:32 pts/2    00:00:00 grep dbw
[oracle@training proc]$ cd /proc/3601/fd

[oracle@training fd]$ ll
total 0
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 21 01:32 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 21 01:32 10 -> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3559.trc
l-wx------ 1 oracle oinstall 64 Jan 21 01:32 11 -> /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3559.trm
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 12 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 14 -> /proc/3601/fd
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 15 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 16 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 17 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkORCL
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 18 -> /u01/app/oracle/oradata/orcl/control01.ctl
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 19 -> /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
l-wx------ 1 oracle oinstall 64 Jan 21 01:32 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 20 -> /u01/app/oracle/oradata/orcl/system01.dbf
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 21 -> /u01/app/oracle/oradata/orcl/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 22 -> /u01/app/oracle/oradata/orcl/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 23 -> /u01/app/oracle/oradata/orcl/users01.dbf
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 24 -> /u01/app/oracle/oradata/orcl/testtbs.dbf (deleted)    //数据文件已经标记为删除
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 25 -> /u01/app/oracle/oradata/orcl/temp01.dbf
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 26 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Jan 21 01:32 3 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/orcl_ora_3559.trc
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 6 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 7 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Jan 21 01:32 8 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkinstorcl (deleted)
lr-x------ 1 oracle oinstall 64 Jan 21 01:32 9 -> /proc/3601/fd
[oracle@training fd]$

[oracle@training fd]$ cp 24  /u01/app/oracle/oradata/orcl/tbs1.dbf  //此处数据文件有变化，试验中上面句柄文件不知道为什么会消失，后重新创建其他名字的表空间，继续实验。

SQL>

SQL> recover datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 5 - file is in use or recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/tbs1.dbf'

SQL> alter tablespace tbs1 offline;

Tablespace altered.

SQL> recover datafile ' /u01/app/oracle/oradata/orcl/tbs1.dbf'
ORA-01179: file  /u01/app/oracle/oradata/orcl/tbs1.dbf does not exist

SQL> host ls -l  /u01/app/oracle/oradata/orcl/tbs1.dbf
-rw-r----- 1 oracle oinstall 10493952 Jan 21 01:47 /u01/app/oracle/oradata/orcl/tbs1.dbf

SQL>
SQL> recover datafile 5;
Media recovery complete.
SQL> alter tablespace tbs1 online;

Tablespace altered.

SQL>
SQL> select * from tt;

        ID NAME
---------- --------
         0 kk
         1 tom

SQL>

 --完成