探索ORACLE之RMAN_07整个业务表空间丢失恢复
作者：吴伟龙 Name：Prodence Woo
QQ：286507175 msn:hapy-wuweilong@hotmail.com
1、 整个业务表空间丢失恢复
注意：以下的所有实验，都是基于上面的全库备份来做的恢复。
2.1 删除wwl表空间的所有数据文件
[root@wwldb ~]# cd /DBData/WWL/
[root@wwldb WWL]# rm -rf wwl*
[root@wwldb WWL]# ll
总计 881068
-rw-r----- 1 oracle oinstall 31457792 06-22 01:34 redo01.log
-rw-r----- 1 oracle oinstall 31457792 06-22 01:34 redo02.log
-rw-r----- 1 oracle oinstall 31457792 06-22 02:14 redo03.log
-rw-r----- 1 oracle oinstall 27263795206-22 02:08 sysaux01.dbf
-rw-r----- 1 oracle oinstall 50332467206-22 02:09 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 05-29 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 06-22 02:08 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 06-22 01:34 users01.dbf
2.2 启动数据库，报如下错误。
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 100664936 bytes
Database Buffers 176160768 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5- see DBWR trace file
ORA-01110: data file 5:'/DBData/WWL/wwl001.dbf'
2.3 检查跟踪文件，非常清晰的告诉丢失了有文件找不到，丢失了。
***SERVICE NAME:() 2012-06-22 09:17:38.573
***SESSION ID:(167.1) 2012-06-22 09:17:38.573
ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [5]
ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [5] [/DBData/WWL/wwl001.dbf]
ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA
LinuxError: 2: No such file or directory
Additionalinformation: 3
ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [6]
ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [6] [/DBData/WWL/wwl002.dbf]
ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA
LinuxError: 2: No such file or directory
Additionalinformation: 3
ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [7]
ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [7] [/DBData/WWL/wwl003.dbf]
ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA
LinuxError: 2: No such file or directory
Additionalinformation: 3
由如上的跟踪信息我们得出是由于/DBData/WWL/wwl001.dbf；/DBData/WWL/wwl002.dbf；/DBData/WWL/wwl001.dbf这三个文件丢失导致数据库无法起来，并且这三个文件同时构成了一个WWL表空间。在这个表空间中存储了各种各样重要的数据。同样我们可以按照之前的方法通过恢复数据文件的方式来进行数据恢复，介于这次丢失的是所有数据文件，数量比较多，而且如果对所有数据文件做恢复，不仅大量的增加的工作量，同时也增加的恢复的风险。所以在这里我们通过使用RMAN执行表空间恢复的方式来进行恢复。当然基于表空间的恢复也分两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下：
2.4 恢复方法一：零停机，在线恢复
开始执行恢复操作，分为如下七个步骤：
1、查看数据库状态是open的，我们刚才的删除数据文件没有对库照成太大的影响。
SQL>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------------------
WWL OPEN
2、将wwl表空间离线
SQL>alter tablespace wwl offline for recover;
Tablespacealtered.
3、将wwl表空间的所有数据文件从备份中restore出来
RMAN> restoretablespace wwl;
Starting restoreat 22-JUN-12
using targetdatabase control file instead of recovery catalog
allocated channel: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
restoring datafile00005 to /DBData/WWL/wwl001.dbf
restoring datafile00006 to /DBData/WWL/wwl002.dbf
restoring datafile00007 to /DBData/WWL/wwl003.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:08
Finished restoreat 22-JUN-12
查看到WWL表空间的所有数据文件已经restore出来了。
[root@wwldb WWL]#ll wwl*
-rw-r----- 1oracle oinstall 52436992 06-22 10:11 wwl001.dbf
-rw-r----- 1oracle oinstall 5251072 06-22 10:11wwl002.dbf
-rw-r----- 1oracle oinstall 5251072 06-22 10:11wwl003.dbf
[root@wwldb WWL]#
4、执行表空间的所有数据恢复，保证scn一致
RMAN> recover tablespacewwl;
Starting recover at 22-JUN-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence20 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arc
archive log thread 1 sequence21 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arc
archive log thread 1 sequence22 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_22_7y8y7l70_.arc
archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arcthread=1 sequence=20
media recovery complete,elapsed time: 00:00:02
Finished recover at 22-JUN-12
5、将表空间在线
SQL>conn / as sysdba
Connected.
SQL>alter tablespace wwl online;
Tablespacealtered.
6、验证表空间数据是否都恢复回来了。
SQL>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------- ----------
WWL01 TABLE
WWL02 TABLE
WWL03 TABLE
WWL04 TABLE
WWL05 TABLE
SQL>select count(*) from wwl01;
COUNT(*)
----------
5
SQL>
2.5恢复方法二，离线恢复
开始执行恢复操作，分为五个步骤：
1、强制将数据库启动到mount状态
SQL> startupforce mount;
ORACLE instancestarted.
Total SystemGlobal Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
DatabaseBuffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
2、将wwl表空间的所有数据文件从备份中restore出来
RMAN> restoretablespace wwl;
Starting restoreat 22-JUN-12
using targetdatabase control file instead of recovery catalog
allocated channel: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
restoring datafile00005 to /DBData/WWL/wwl001.dbf
restoring datafile00006 to /DBData/WWL/wwl002.dbf
restoring datafile00007 to /DBData/WWL/wwl003.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:07
Finished restore at 22-JUN-12
3、执行表空间的所有数据恢复，保证scn一致
RMAN> recovertablespace wwl;
Starting recoverat 22-JUN-12
using channelORA_DISK_1
starting mediarecovery
archive log thread1 sequence 20 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arc
archive log thread1 sequence 21 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arc
archive log thread1 sequence 22 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_22_7y8y7l70_.arc
archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arcthread=1 sequence=20
media recoverycomplete, elapsed time: 00:00:02
Finished recover at 22-JUN-12
4、打开数据库
SQL> alter database open;
Database altered.
5、验证表空间数据是否都恢复回来了。
SQL>conn wwl/wwl
Connected.
SQL>select count(*) from tab;
COUNT(*)
----------
5
SQL>select * from wwl05;
ID NAME
--------------------
1 wwl
2 wm
3 zq
4 wbq
5 wq