使用alter database datafile 'XXX' offline drop 是否能够恢复(非归档模式下)_alter database datafile offline drop数据找回-CSDN博客

本文链接：https://blog.csdn.net/colin_liu2009/article/details/7736965

本文通过实验揭示在非归档模式下，使用`alter database datafile 'XXX' offline drop`命令无法恢复数据。在日志未切换时可能误以为成功，但一旦日志切换，由于缺乏归档，数据无法恢复。强调在非归档模式下避免使用drop命令，以免造成不可逆的数据丢失，特别是在高日志切换频率的生产环境中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在群里面听到一位网友在说使用了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: {<RET>=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: {<RET>=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

通过以上测试表明，在非归档模式下使用了alter database datafile 'XXX' offline drop命令是不能被恢复的，虽然我上面的第一次实验成功了，那是因为online日志还没有切换，在日志中还能够找到相应的反操作。在第二个实验中，手工切换日志，会提示不能打开归档日志，因为我根本就没有归档，肯定找不到啊，所以不能够恢复的。切记，在非归档模型下不要随意使用一些drop命令，在实际生产环境中日志的切换是很频繁且快的，否则只有掏钱找oracle了。当然，如果在业务不繁忙的系统可以及时的通过online日志进行恢复，这种几率比较小。