alter database datafile'XXX'ofline dropを使って回復できますか？

                 alter database datafile 'XXX' offline drop          ，       ，             

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M autoextend on next 1M;

Tablespace created.

SQL> create table test(id number,name varchar2(12)) tablespace test;                                               

Table created.

SQL> insert into test values (1,'colin');

1 row created.

SQL> insert into test values (2,'janey');

1 row created.

SQL> insert into test values (3,'tom');

1 row created.

SQL> insert into test values (4,'adele');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- ------------
         1 colin
         2 janey
         3 tom
         4 adele

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' offline drop;

Database altered.

SQL> commit;

Commit complete.

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

SQL> select LOG_MODE,OPEN_MODE from v$database;

LOG_MODE     OPEN_MODE
------------ ----------
NOARCHIVELOG READ WRITE

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' online;
alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/test01.dbf'


SQL> recover datafile '/u01/app/oracle/oradata/orcl/test01.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' online;

Database altered.

SQL> select * from test;

        ID NAME
---------- ------------
         1 colin
         2 janey
         3 tom
         4 adele

    。           ，   online  。

       ：

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' offline drop;

Database altered.

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


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/

System altered.

SQL> 
System altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/test01.dbf';
ORA-00279: change 654004 generated at 07/09/2012 21:07:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_32_777559117.arc
ORA-00280: change 654004 for thread 1 is in sequence #32


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> /

System altered.

SQL> recover datafile '/u01/app/oracle/oradata/orcl/test01.dbf';
ORA-00279: change 654004 generated at 07/09/2012 21:07:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_32_777559117.arc
ORA-00280: change 654004 for thread 1 is in sequence #32


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/arch/1_32_777559117.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/app/oracle/arch/1_32_777559117.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

 
         以上のテストによって、非ファイリングモードでアルターdatabase datafile'XXX'ofline dropコマンドを使っても回復できないことが分かりました。第二の実験では、手でログを切り替えて、アーカイブログを開けられないように注意します。アーカイブが全然ないので、見つけられないと思います。メモしてください。非アーカイブモデルの下で勝手にいくつかのdropコマンドを使用しないでください。実際の生産環境の中で日誌の切り替えは頻繁で速いです。さもなければお金を取り出してoracleを探します。もちろん、業務が忙しくないシステムではオンラインのログを使ってすぐに復旧できます。この確率は比較的小さいです。