实验Oracle数据文件被误删除的场景恢复
实验Oracle数据文件被误删除的场景恢复
Alfred Zhao
发布于 2019-05-22 14:53:19
发布于 2019-05-22 14:53:19
环境：RHEL 5.4 + Oracle 11.2.0.3 背景：数据库没有备份，数据库文件被误操作rm，此时数据库尚未关闭，也就是对应句柄存在，如何快速恢复？
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> exit1.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.本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019-05-20 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读