物理删除oracle数据文件的恢复

归档模式下测试：

(普通文件系统)：open状态下物理删除数据文件，未关闭情况恢复：

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

SQL> create tablespace test datafile '/u01/oracle/oradata/CPP/test01.dbf' size 10M;

Tablespace created.SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect ,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T1 TEST

SQL> conn /as sysdba

Connected.

SQL> alter system checkpoint;

System altered.

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/oracle/oradata/CPP/example01.dbf

/u01/oracle/oradata/CPP/users01.dbf

/u01/oracle/oradata/CPP/undotbs01.dbf

/u01/oracle/oradata/CPP/sysaux01.dbf

/u01/oracle/oradata/CPP/system01.dbf

/u01/oracle/oradata/CPP/test01.dbf

6 rows selected.

[root@orcl1 CPP]# ls

control01.ctl redo01.log redo03.log system01.dbf test01.dbf users01.dbf

example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf

[root@orcl1 CPP]# rm -rf test01.dbf

SQL> create table t2 as select * from t1;

create table t2 as select * from t1 *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/oracle/oradata/CPP/test01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

[oracle@orcl1 ~]$ ps -ef | grep dbw0

oracle 2898 1 0 09:24 ? 00:00:00 ora_dbw0_CPP

oracle 5423 5382 3 09:50 pts/2 00:00:00 grep dbw0

[oracle@orcl1 ~]$ su - root

Password:

[root@orcl1 ~]# cd /proc/2898/

[root@orcl1 2898]# ls

attr clear_refs cwd fdinfo maps mountstats oom_score root smaps status

autogroup cmdline environ io mem net oom_score_adj sched stack syscall

auxv coredump_filter exe limits mountinfo numa_maps pagemap schedstat stat task

cgroup cpuset fd loginuid mounts oom_adj personality sessionid statm wchan

[root@orcl1 2898]# cd fd

[root@orcl1 fd]# ls

0 1 10 11 2 256 257 258 259 260 261 262 263 264 3 4 5 6 7 8 9

[root@orcl1 fd]# ll

total 0

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 0 -> /dev/null

l-wx------ 1 oracle oinstall 64 Jan 7 09:51 1 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 10 -> /u01/oracle/product/11.2.0/db_1/dbs/lkCPP

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 11 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracle oinstall 64 Jan 7 09:51 2 -> /dev/null

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 256 -> /u01/oracle/oradata/CPP/control01.ctl

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 257 -> /u01/oracle/fast_recovery_area/CPP/control02.ctl

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 258 -> /u01/oracle/oradata/CPP/system01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 259 -> /u01/oracle/oradata/CPP/sysaux01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 260 -> /u01/oracle/oradata/CPP/undotbs01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 261 -> /u01/oracle/oradata/CPP/users01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 262 -> /u01/oracle/oradata/CPP/example01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 263 -> /u01/oracle/oradata/CPP/temp01.dbf

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 264 -> /u01/oracle/oradata/CPP/test01.dbf (deleted)

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 3 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 4 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 5 -> /dev/null

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 6 -> /u01/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 7 -> /proc/2898/fd

lr-x------ 1 oracle oinstall 64 Jan 7 09:51 8 -> /dev/zero

lrwx------ 1 oracle oinstall 64 Jan 7 09:51 9 -> /u01/oracle/product/11.2.0/db_1/dbs/hc_CPP.dat

[oracle@orcl1 fd]# cp 264 /u01/oracle/oradata/CPP/test01.dbf

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

/u01/oracle/oradata/CPP/system01.dbf SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/u01/oracle/oradata/CPP/users01.dbf ONLINE

/u01/oracle/oradata/CPP/example01.dbf ONLINE

/u01/oracle/oradata/CPP/test01.dbf ONLINE

6 rows selected.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' offline;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

/u01/oracle/oradata/CPP/system01.dbf SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/u01/oracle/oradata/CPP/users01.dbf ONLINE

/u01/oracle/oradata/CPP/example01.dbf ONLINE

/u01/oracle/oradata/CPP/test01.dbf RECOVER

6 rows selected.

SQL> recover datafile '/u01/oracle/oradata/CPP/test01.dbf';

Media recovery complete.

SQL> alter database datafile '/u01/oracle/oradata/CPP/test01.dbf' online;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

/u01/oracle/oradata/CPP/system01.dbf SYSTEM

/u01/oracle/oradata/CPP/sysaux01.dbf ONLINE

/u01/oracle/oradata/CPP/undotbs01.dbf ONLINE

/u01/oracle/oradata/CPP/users01.dbf ONLINE

/u01/oracle/oradata/CPP/example01.dbf ONLINE

/u01/oracle/oradata/CPP/test01.dbf ONLINE

6 rows selected.

SQL> conn test/test;

Connected.

SQL> create table t2 as select * from t1;

Table created.

（ASM文件系统）open状态下物理删除数据文件，关闭情况恢复：

SQL> create tablespace test datafile '+DATA/mecbs/datafile/test01.dbf' size 10M;

Tablespace created.

SQL> conn /as sysdba

Connected.

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

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test;

Connected.

SQL> conn /as sysdba

Connected.

SQL> grant dba to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table t1 as select * from dba_objects where rownum<=1000;

Table created.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T1 TEST

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

+DATA/mecbs/datafile/users.259.862339391

+DATA/mecbs/datafile/undotbs1.258.862339391

+DATA/mecbs/datafile/sysaux.257.862339391

+DATA/mecbs/datafile/system.256.862339387

+DATA/mecbs/datafile/example.264.862339751

+DATA/mecbs/datafile/undotbs2.265.862341013

+DATA/mecbs/datafile/system01.dbf

+DATA/mecbs/datafile/crm01.dbf

+DATA/mecbs/datafile/test01.dbf

+DATA/mecbs/datafile/cross.dbf

+DATA/mecbs/datafile/aix_trans.dbf

11 rows selected.

ASMCMD [+data/mecbs/DATAFILE] > ls

AIX_TRANS.281.868377837

CRM.276.863565267

CROSSTBS.279.868372675

EXAMPLE.264.862339751

SYSAUX.257.862339391

SYSTEM.256.862339387

SYSTEM.275.863564943

TEST.278.868380831

UNDOTBS1.258.862339391

UNDOTBS2.265.862341013

USERS.259.862339391

aix_trans.dbf

crm01.dbf

cross.dbf

system01.dbf

test01.dbf

SQL> alter tablespace test offline;

Tablespace altered.

ASMCMD [+data/mecbs/DATAFILE] > rm -rf test01.dbf

ASMCMD [+data/mecbs/DATAFILE] >

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

+DATA/mecbs/datafile/system.256.862339387 SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+DATA/mecbs/datafile/users.259.862339391 ONLINE

+DATA/mecbs/datafile/example.264.862339751 ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/mecbs/datafile/system01.dbf SYSTEM

+DATA/mecbs/datafile/crm01.dbf ONLINE

+DATA/mecbs/datafile/test01.dbf OFFLINE

+DATA/mecbs/datafile/cross.dbf ONLINE

+DATA/mecbs/datafile/aix_trans.dbf ONLINE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 484356096 bytes

Fixed Size 2254464 bytes

Variable Size 264243584 bytes

Database Buffers 209715200 bytes

Redo Buffers 8142848 bytes

Database mounted.

SQL> alter database create datafile '+DATA/mecbs/datafile/test01.dbf';

Database altered.

SQL> recover datafile '+DATA/mecbs/datafile/test01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

+DATA/mecbs/datafile/system.256.862339387 SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+DATA/mecbs/datafile/users.259.862339391 ONLINE

+DATA/mecbs/datafile/example.264.862339751 ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/mecbs/datafile/system01.dbf SYSTEM

+DATA/mecbs/datafile/crm01.dbf ONLINE

+DATA/mecbs/datafile/test01.dbf OFFLINE

+DATA/mecbs/datafile/cross.dbf ONLINE

+DATA/mecbs/datafile/aix_trans.dbf ONLINE

11 rows selected.

SQL> alter tablespace test online;

Tablespace altered.

SQL> select name,status from v$datafile;

NAME STATUS

------------------------------------------------------------ -------

+DATA/mecbs/datafile/system.256.862339387 SYSTEM

+DATA/mecbs/datafile/sysaux.257.862339391 ONLINE

+DATA/mecbs/datafile/undotbs1.258.862339391 ONLINE

+DATA/mecbs/datafile/users.259.862339391 ONLINE

+DATA/mecbs/datafile/example.264.862339751 ONLINE

+DATA/mecbs/datafile/undotbs2.265.862341013 ONLINE

+DATA/mecbs/datafile/system01.dbf SYSTEM

+DATA/mecbs/datafile/crm01.dbf ONLINE

+DATA/mecbs/datafile/test01.dbf ONLINE

+DATA/mecbs/datafile/cross.dbf ONLINE

+DATA/mecbs/datafile/aix_trans.dbf ONLINE

11 rows selected.

SQL> conn test/test;

Connected.

SQL> select count(*) from t1;

COUNT(*)

----------

1000