Home >Database >Mysql Tutorial >探索Oracle之RMAN_07单个数据文件丢失恢复
探索Oracle之RMAN_07单个数据文件丢失恢复
- WBOYOriginal
- 2016-06-07 17:15:221137browse
备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了RMAN的备份，实际上也穿插的谈了些复杂的完
备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了RMAN的备份，实际上也穿插的谈了些复杂的完整恢复。当然在这节当中我们将会由浅入深的详细谈谈在几种不同情况下的数据库恢复。
相关阅读：
探索Oracle之RMAN_01概念
探索Oracle之RMAN_02基本使用
探索Oracle之RMAN_03非一致性备份
探索Oracle之RMAN_04非一致性备份
探索Oracle之RMAN_05增量备份
探索Oracle之RMAN_06备份策略
探索Oracle之RMAN_07单个数据文件丢失恢复
探索Oracle之RMAN_07整个业务表空间丢失恢复
探索Oracle之RMAN_07 磁盘损坏数据丢失恢复
探索Oracle之RMAN_07 数据库所有文件全部丢失恢复
探索Oracle之RMAN_07 重做日志redu文件丢失恢复
探索Oracle之RMAN_07 参数文件丢失恢复
探索Oracle之RMAN_07控制文件丢失恢复
探索Oracle之RMAN_07 system表空间丢失恢复
1、 数据文件的丢失恢复
1.1 在wwl表空间上创建5张表，并添加数据。
SQL> create table wwl01 (id number(3),namevarchar2(10));
Table created.
SQL> insert into wwl01 values(1,'wwl');
1 row created.
SQL> insert into wwl01 values(2,'wm');
1 row created.
SQL> insert into wwl01 values(3,'zq');
1 row created.
SQL> insert into wwl01 values(4,'wbq');
1 row created.
SQL> insert into wwl01 values(5,'wq');
1 row created.
SQL> create table wwl02 as select * from wwl01;
Table created.
SQL> create table wwl03 as select * from wwl01;
Table created.
SQL> create table wwl04 as select * from wwl01;
Table created.
SQL> create table wwl05 as select * from wwl01;
Table created.
查看表中的数据：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- ------- ----------
WWL01 TABLE
WWL02 TABLE
WWL03 TABLE
WWL04 TABLE
WWL05 TABLE
1.2 执行全库备份
[oracle@wwldb ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jun 2200:59:59 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: WWL (DBID=5520179)
RMAN> backup database;
1.3 模拟数据丢失，手动删除数据文件wwl001.dbf
[oracle@wwldb WWL]$ rm -rfwwl001.dbf
1.4 再次启动数据库，无法启动并报错不能锁定数据文件5，查看dbwr的跟踪文件。
SQL>startup force;
ORACLEinstance started.
TotalSystem Global Area 285212672 bytes
FixedSize 1218968 bytes
VariableSize 92276328 bytes
DatabaseBuffers 184549376 bytes
RedoBuffers 7168000 bytes
Databasemounted.
ORA-01157:cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:'/DBData/WWL/wwl001.dbf'
1.5 检查跟踪文件，报如下错误，非常的清楚的告诉了找不到的文件：
Errors in file /DBSoft/admin/WWL/bdump/wwl_dbw0_29185.trc:
ORA-01157: Message 1157 not found; No messagefile for product=RDBMS, facility=ORA; arguments: [5]
ORA-01110: Message 1110 not found; No messagefile for product=RDBMS, facility=ORA; arguments: [5] [/DBData/WWL/wwl001.dbf]
ORA-27037: Message 27037 not found; No messagefile for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASEOPEN...
由以上信息可以得出数据库故障是由于数据文件wwl001.dbf数据文件异常丢失或者损坏导致数据库的故障，那么数据文件丢失就必然存在数据库数据的丢失，但是万幸的是，在丢失之前我们已经做过备份了，现在我们就来通过之前的备份将wwl001.dbf文件恢复回来，数据文件的恢复分为两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下：
1.6 恢复方法一，零停机，在线恢复
开始执行恢复操作，分为如下七个步骤：
RMAN>startup force mount;
Oracle instancestarted
databasemounted
Total System GlobalArea 285212672 bytes
Fixed Size 1218968 bytes
VariableSize 92276328 bytes
DatabaseBuffers 184549376 bytes
RedoBuffers 7168000 bytes
SQL> alter database datafile '/DBData/WWL/wwl001.dbf' offline;
Database altered.
3、开启数据库
SQL> alter database open;
Database altered.
RMAN> restore datafile'/DBData/WWL/wwl001.dbf';
Starting restore at 22-JUN-12
using target database control fileinstead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141devtype=DISK
channel ORA_DISK_1: startingdatafile backupset restore
channel ORA_DISK_1: specifyingdatafile(s) to restore from backup set
restoring datafile 00005 to /DBData/WWL/wwl001.dbf
channel ORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1
channel ORA_DISK_1: restored backuppiece 1
piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021
channel ORA_DISK_1: restorecomplete, elapsed time: 00:00:07
Finished restore at 22-JUN-12
[oracle@wwldb WWL]$ ll wwl*
-rw-r----- 1 oracle oinstall52436992 Jun 22 01:21 wwl001.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 22 01:20 wwl002.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 22 01:20 wwl003.dbf
RMAN>recover datafile '/DBData/WWL/wwl001.dbf';
Startingrecover at 22-JUN-12
using targetdatabase control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=144 devtype=DISK
starting mediarecovery
media recoverycomplete, elapsed time: 00:00:01
Finishedrecover at 22-JUN-12
RMAN>
6、将数据文件在线
SQL> conn / as sysdba
Connected.
SQL> alter database datafile'/DBData/WWL/wwl001.dbf' online;
Database altered.
7、验证数据是否恢复，可以看到数据全部恢复回来了。
SQL>conn wwl/wwl
Connected.
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
WWL01 TABLE
WWL02 TABLE
WWL03 TABLE
WWL04 TABLE
WWL05 TABLE
SQL>select count(*) from tab;
COUNT(*)
----------
5
SQL>
1.7 恢复方法二，离线恢复
开始执行恢复操作，分为五个步骤：
SQL> startupforce mount;
ORACLE instancestarted.
Total SystemGlobal Area 285212672 bytes
Fixed Size 1218968 bytes
VariableSize 96470632 bytes
DatabaseBuffers 180355072 bytes
RedoBuffers 7168000 bytes
Databasemounted.
RMAN> restoredatafile '/DBData/WWL/wwl002.dbf'
2> ;
Starting restoreat 22-JUN-12
using targetdatabase control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=156 devtype=DISK
channelORA_DISK_1: starting datafile backupset restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00006 to /DBData/WWL/wwl002.dbf
channelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1
channelORA_DISK_1: restored backup piece 1
piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021
channelORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restoreat 22-JUN-12
RMAN> recover datafile '/DBData/WWL/wwl002.dbf';
Starting recover at 22-JUN-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-JUN-12
RMAN>
4、开启数据库
SQL> conn /as sysdba
Connected.
SQL> alterdatabase open;
Databasealtered.
5、验证数据是否恢复
SQL> select *from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
WWL01 TABLE
WWL02 TABLE
WWL03 TABLE
WWL04 TABLE
WWL05 TABLE
SQL> selectcount(*) from wwl01;
COUNT(*)
----------
5
SQL>
