归档模式下测试：

一.open状态下物理删除数据文件，关闭情况恢复：

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

Tablespace created.

SQL> conn scott/scott;

Connected.

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

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