实验Oracle数据文件被误删除的场景恢复

环境：RHEL 5.4 + Oracle 11.2.0.3
背景：数据库没有备份，数据库文件被误操作rm，此时数据库尚未关闭，也就是对应句柄存在，如何快速恢复？

• 1.某个普通数据文件被删除
• 2.所有数据文件被删除

1.某个普通数据文件被删除

**1.1 模拟5号数据文件被rm误删除**

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD2     READ WRITE

SQL> col name for a55
SQL> select file#, name from v$datafile;

     FILE# NAME
---------- -------------------------------------------------------
         1 /u03/oradata/PROD2/system01.dbf
         2 /u03/oradata/PROD2/sysaux01.dbf
         3 /u03/oradata/PROD2/undotbs01.dbf
         4 /u03/oradata/PROD2/users01.dbf
         5 /u03/oradata/PROD2/example01.dbf

SQL> !ls -lrth /u03/oradata/PROD2/example01.dbf
-rw-r----- 1 oracle oinstall 346M May 20 10:58 /u03/oradata/PROD2/example01.dbf

SQL> !rm /u03/oradata/PROD2/example01.dbf

SQL> !ls -lrth /u03/oradata/PROD2/example01.dbf
ls: /u03/oradata/PROD2/example01.dbf: No such file or directory

SQL> exit

1.2 依据句柄号拷贝恢复5号数据文件

[oracle@edbjr2p2 PROD2]$ pwd
/u03/oradata/PROD2
[oracle@edbjr2p2 PROD2]$ ls -lrth
total 1.7G
-rw-r----- 1 oracle oinstall  21M May 20 02:50 temp01.dbf
-rw-r----- 1 oracle oinstall 5.1M May 20 10:58 users01.dbf
-rw-r----- 1 oracle oinstall  51M May 20 10:58 redo03.log
-rw-r----- 1 oracle oinstall  51M May 20 10:58 redo01.log
-rw-r----- 1 oracle oinstall  91M May 20 15:34 undotbs01.dbf
-rw-r----- 1 oracle oinstall 731M May 20 15:34 system01.dbf
-rw-r----- 1 oracle oinstall 751M May 20 15:34 sysaux01.dbf
-rw-r----- 1 oracle oinstall  51M May 20 15:38 redo02.log
-rw-r----- 1 oracle oinstall 9.3M May 20 15:38 control01.ctl
[oracle@edbjr2p2 PROD2]$ ps -ef|grep dbw0_PROD2|grep -v grep
oracle   28526     1  0 10:58 ?        00:00:01 ora_dbw0_PROD2
[oracle@edbjr2p2 PROD2]$ cd /proc/28526/fd/
[oracle@edbjr2p2 fd]$ ls -lrth|grep deleted
lrwx------ 1 oracle oinstall 64 May 20 15:39 261 -> /u03/oradata/PROD2/example01.dbf (deleted)
[oracle@edbjr2p2 fd]$ cp 261 /u03/oradata/PROD2/example01.dbf

恢复之后，数据库没有发现有异常报错，继续进行操作或重启库都没有问题。

2.所有数据文件被删除

如果是所有数据文件被删除，恢复的方法和上面一样，但测试这种情况一般还需要特殊处理后才可以开库。 比如我这里的实验遇到了2个错误： - ORA-600 [kcratr_scan_lastbwr] (文档 ID 1267231.1) - ORA-600 [3020] "Stuck Recovery" (文档 ID 30866.1)

具体现象如下：

SQL> select file#, checkpoint_change# from v$datafile
  2  ;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2150244
         2            2150244
         3            2150244
         4            2150244
         5            2150244

SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2150244
         2            2150244
         3            2150244
         4            2150244
         5            2150244

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],
[], [], [], [], [], [], [], []

SQL> recover database; 
ORA-00600: internal error code, arguments: [3020], [3], [2070], [12584982], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 2070, file
offset is 16957440 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/u03/oradata/PROD2/undotbs01.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'

RMAN> recover database until scn 2150244;

Starting recover at 20-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/PROD2/system01.dbf'

media recovery complete, elapsed time: 00:00:01

Finished recover at 20-MAY-19

RMAN> 
SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2150244
         2            2150244
         3            2150244
         4            2150244
         5            2150244

SQL> select file#, checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            2150244
         2            2150244
         3            2150244
         4            2150244
         5            2150244

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/PROD2/system01.dbf'

此时尝试设置隐藏参数：

_allow_resetlogs_corruption=true

再次尝试成功开库：

SQL> alter database open resetlogs;

Database altered.