探索ORACLE之RMAN_07恢复
作者：吴伟龙 Name：Prodence Woo
备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了RMAN的备份，实际上也穿插的谈了些复杂的完整恢复。当然在这节我们将会由浅入深的详细谈谈在几种不同情况下的数据库恢复。
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 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
Linux Error: 2: No such file ordirectory
Additional information: 3
ORA-1157 signalled during: ALTERDATABASE OPEN...
由以上信息可以得出数据库故障是由于数据文件wwl001.dbf数据文件异常丢失或者损坏导致数据库的故障，那么数据文件丢失就必然存在数据库数据的丢失，但是万幸的是，在丢失之前我们已经做过备份了，现在我们就来通过之前的备份将wwl001.dbf文件恢复回来，数据文件的恢复分为两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下：
1.6
开始执行恢复操作，分为如下七个步骤：
1、强制将数据库启动到mount状态
RMAN>startup force mount;
Oracle instancestarted
databasemounted
Total SystemGlobal Area 285212672 bytes
Fixed Size 1218968 bytes
VariableSize 92276328 bytes
DatabaseBuffers 184549376 bytes
RedoBuffers 7168000 bytes
2、将对应的数据文件offline
SQL> alter database datafile '/DBData/WWL/wwl001.dbf' offline;
Database altered.
3、开启数据库
SQL> alter database open;
Database altered.
4、将数据文件从备份中restore出来
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
查看到数据文件已经restore出来了。
[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
5、执行数据恢复，保证scn一致
RMAN>recover datafile '/DBData/WWL/wwl001.dbf';
Startingrecover at 22-JUN-12
using targetdatabase control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channel ORA_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
开始执行恢复操作，分为五个步骤：
1、强制将数据库启动到mount状态
SQL> startupforce mount;
ORACLE instancestarted.
Total SystemGlobal Area 285212672 bytes
Fixed Size 1218968 bytes
VariableSize 96470632 bytes
DatabaseBuffers 180355072 bytes
RedoBuffers 7168000 bytes
Databasemounted.
2、将数据文件从备份中restore出来
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
3、执行数据恢复，保证scn一致
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>
2、
注意：以下的所有实验，都是基于上面的全库备份来做的恢复。
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/lockdata 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 forproduct=RDBMS, facility=ORA; arguments: [5]
ORA-01110: Message 1110 not found; No message file forproduct=RDBMS, facility=ORA; arguments: [5] [/DBData/WWL/wwl001.dbf]
ORA-27037: Message 27037 not found; No message file forproduct=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 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 forproduct=RDBMS, facility=ORA; arguments: [6] [/DBData/WWL/wwl002.dbf]
ORA-27037: Message 27037 not found; No message file forproduct=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01157: Message 1157 not found; No message file forproduct=RDBMS, facility=ORA; arguments: [7]
ORA-01110: Message 1110 not found; No message file forproduct=RDBMS, facility=ORA; arguments: [7] [/DBData/WWL/wwl003.dbf]
ORA-27037: Message 27037 not found; No message file forproduct=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
Additional information: 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 thread 1 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 thread 1 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 thread 1 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 recovery complete, 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
3、 SYSTEM表空间数据文件丢失恢复
注意：以下的所有实验，都是基于上面的全库备份来做的恢复。
3.1 删除system表空间的所有数据文件。
[oracle@wwldb WWL]$ rm -rf syste*
[oracle@wwldb WWL]$ exit
3.2 再次启动数据库报错。
SQL> startup force
ORACLE instance started.
Total System GlobalArea 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannotidentify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:'/DBData/WWL/system01.dbf'
3.3 检查跟踪文件，分析错误。
Errors in file /DBSoft/admin/WWL/bdump/wwl_dbw0_4600.trc:
ORA-01157: Message 1157 not found; No message file for product=RDBMS,facility=ORA; arguments: [1]
ORA-01110: Message 1110 not found; No message file for product=RDBMS,facility=ORA; arguments: [1] [/DBData/WWL/system01.dbf]
ORA-27037: Message 27037 not found; No message file for product=RDBMS,facility=ORA
Linux Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
由如上日志文件我们得出是由于/DBData/WWL/system01.dbf文件丢失，而且这个文件同时又是system表空间的的数据文件，因为system表空间存放了数据字典信息，所以该数据文件是不可以采用脱机的方式实现在线的的恢复。
3.4 恢复system表空间数据文件（开始执行恢复分为五个步骤）
1、强制启动数据库到mount状态
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL>
2、Restore出来system表空间的数据文件
RMAN> restore datafile 1;
Starting restore at 22-JUN-12
using target database control file instead of recoverycatalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupsetrestore
channel ORA_DISK_1: specifying datafile(s) to restorefrom backup set
restoring datafile 00001 to /DBData/WWL/system01.dbf
channel ORA_DISK_1: reading from backup piece/DBBak/bak_WWL_06_22_0vne4ph6_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1tag=TAG20120622T010021
channel ORA_DISK_1: restore complete, elapsed time:00:01:06
Finished restore at 22-JUN-12
3、执行system表空间数据恢复。
RMAN> recover datafile 1;
Starting recover at 22-JUN-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 20 is already on diskas file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arc
archive log thread 1 sequence 21 is already on diskas file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arc
archive log thread 1 sequence 22 is already on diskas file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_22_7y8y7l70_.arc
archive log thread 1 sequence 23 is already on diskas file /DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_23_7y903v17_.arc
archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arcthread=1 sequence=20
archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arcthread=1 sequence=21
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-JUN-12
4、Open数据库
SQL> alter database open;
Database altered.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
WWL OPEN
SQL>
4、 控制文件(controlfile)丢失恢复
基于控制文件的复合多路径性，它的丢失分为两种，一种是其中某个控制文件的损坏或丢失，另外一种是所有控制文件均丢失。基于第一种情况，只需把好的控制文件复制一份在损坏或丢失的那个控制文件路径下即可。第二种情况下则需要通过备份信息来对控制文件进行恢复或手工重建控制文件。
丢失单一控制文件的判断及恢复。
数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号
SQL>shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/DBData/oradata/WWL/control02.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
必须强制关闭数据库
SQL>shutdown abort;
ORACLEinstance shut down.
启动数据库报控制文件验证失败，检查告警日志文件
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 285212672 bytes
FixedSize 1218968 bytes
VariableSize 104859240 bytes
DatabaseBuffers 171966464 bytes
RedoBuffers 7168000 bytes
ORA-00205: error in identifying control file, check alert logfor more info
查看告警日志，报提示找不到controlfile2
Fri Jun 2211:54:26 2012
Errors infile /DBSoft/admin/WWL/udump/wwl_ora_4816.trc:
ORA-00210:cannot open the specified control file
ORA-00202:control file: '/DBData/oradata/WWL/control02.ctl'
ORA-27041:unable to open file
Linux Error:2: No such file or directory
从上面的信息我们可以得出是由于控制文件丢失导致了数据库无法正常的启动和关闭，下面我们要做的就是对控制文件进行做恢复，因为我们知道控制文件具有重复多路径属性，默认会有三个控制文件。现在日志中看到的是控制文件2丢失，找不到，我们可以通过控制文件1和3来恢复2。
4.1
1、查看控制文件存在路径
SQL>show parameter control_files
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_files string /DBSoft/oradata/WWL/control01.ctl, /DBData/oradata/WWL/control02.ctl,/DBData/oradata/WWL/control03.ctl
SQL>
我们可以从如上看到，该套数据库存在三个控制文件其中一个控制文件存放在/DBSoft目中中的oradata/wwl/目录下，另外两个控制文件存在/DBData目录中的/oradata/wwl/的目录下，从上面刚才的信息中我们可以得之是control02.ctl控制文件丢失导致数据库故障。
2、检查下控制文件是不存在还是损坏了
[oracle@wwldb WWL]$cd /DBData/oradata/WWL/
[oracle@wwldb WWL]$ll
total 0
[oracle@wwldb WWL]$
怪了，这个目录怎么一个控制文件都没有了呀，看看控制文件一是否存在。
[oracle@wwldb WWL]$ cd/DBSoft/oradata/WWL/
[oracle@wwldb WWL]$ ls
control01.ctl
非常万幸，controlfile1还是存在的，这样我们就可以通过controlfile1来恢复controlfile2和3了。
3、关闭数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL>
4、恢复损坏丢失的控制文件
[oracle@wwldbWWL]$ ls
control01.ctl
[oracle@wwldbWWL]$ pwd
/DBSoft/oradata/WWL
[oracle@wwldbWWL]$ ls
control01.ctl
[oracle@wwldbWWL]$ cp control01.ctl /DBData/oradata/WWL/control02.ctl
[oracle@wwldbWWL]$ cp control01.ctl /DBData/oradata/WWL/control03.ctl
[oracle@wwldbWWL]$ ll /DBData/oradata/WWL/
total13792
-rw-r-----1 oracle oinstall 7061504 Jun 22 12:51 control02.ctl
-rw-r-----1 oracle oinstall 7061504 Jun 22 12:51 control03.ctl
[oracle@wwldb WWL]$
5、启动数据库
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 285212672 bytes
FixedSize 1218968 bytes
VariableSize 104859240 bytes
DatabaseBuffers 171966464 bytes
RedoBuffers 7168000 bytes
Databasemounted.
Databaseopened.
SQL>
4.2 所有控制文件全部丢失。
丢失单一控制文件的判断及恢复。
数据库无法正常关闭，因为在关闭的时候必须向控制文件中更新scn号
SQL>shutdown immediate
ORA-00210:cannot open the specified control file
ORA-00202:control file: ' /DBSoft/oradata/WWL/control01.ctl'
ORA-27041:unable to open file
LinuxError: 2: No such file or directory
Additionalinformation: 3
必须强制关闭数据库
SQL>shutdown abort;
ORACLEinstance shut down.
启动数据库报控制文件验证失败，检查告警日志文件
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 285212672 bytes
FixedSize 1218968 bytes
VariableSize 104859240 bytes
DatabaseBuffers 171966464 bytes
RedoBuffers 7168000 bytes
ORA-00205:error in identifying control file, check alert log for more info
查看告警日志，报提示找不到controlfile1
Fri Jun 22 13:16:07 2012
Errors in file /DBSoft/admin/WWL/udump/wwl_ora_5104.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/DBSoft/oradata/WWL/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
4.3 所有控制文件均丢失通过RMAN来进行控制文件的恢复：
1、强制启动数据库到nomount状态
SQL> startupforce nomount;
ORACLE instancestarted.
Total SystemGlobal Area 285212672 bytes
Fixed Size 1218968 bytes
VariableSize 109053544 bytes
DatabaseBuffers 167772160 bytes
RedoBuffers 7168000 bytes
SQL> exit
2、执行restore控制文件恢复
RMAN>restore controlfile;
3、打开数据库
SQL>alter database mount;
Database altered.
SQL>alter database open;
Database altered.
6、
Oracle数据库的参数文件有两种一种是pfile(初始化参数文件)，还有一种是spfile(服务器初始化参数文件)；实际上spfile是pfile衍生过来的一新参数文件，应用9i以后的版本，在9i之前的版本都不支持，只支持pfile；而且pfile是不能通过oracle命令来进行备份的，只有spfile才支持备份。
通过RMAN的备份来实现参数文件的恢复，仅适用于9i以后
1、通过rman备份参数文件：
RMAN> backupspfile;
Starting backupat 05-JUL-12
using channelORA_DISK_1
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
including currentSPFILE in backupset
channelORA_DISK_1: starting piece 1 at 05-JUL-12
channelORA_DISK_1: finished piece 1 at 05-JUL-12
piecehandle=/DBBak/bak_WWL_07_05_03nfbi5c_1_1 tag=TAG20120705T175348 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backupat 05-JUL-12
Starting ControlFile and SPFILE Autobackup at 05-JUL-12
piecehandle=/DBSoft/product/10.2.0/db_1/dbs/c-5520179-20120705-01 comment=NONE
Finished ControlFile and SPFILE Autobackup at 05-JUL-12
RMAN>
2、备份完之后，我们可以看到如下备份信息：
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- ------------ ----------- ------------ ---------------
3 Full 80.00K DISK 00:00:01 05-JUL-12
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120705T175348
Piece Name: /DBBak/bak_WWL_07_05_03nfbi5c_1_1 参数文件备份路径
SPFILE Included: Modification time: 05-JUL-12
3、模拟参数文件丢失：
SQL> showparameter spfile;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string /DBSoft/product/10.2.0/db_1/db
s/spfileWWL.ora
SQL>
删除参数文件：
[oracle@wwldb~]$ rm /DBSoft/product/10.2.0/db_1/dbs/spfileWWL.ora
[oracle@wwldb~]$ ls /DBSoft/product/10.2.0/db_1/dbs/spfileWWL.ora
ls: /DBSoft/product/10.2.0/db_1/dbs/spfileWWL.ora: No such file ordirectory
[oracle@wwldbdbs]$ ll
total7240
drwxr-xr-x2 oracle oinstall 4096 Jul 5 18:25 bak
-rw-rw----1 oracle oinstall 1544 May 16 16:44hc_WWL.dat
-rw-rw----1 oracle oinstall 24 May 23 15:52lkDUMMY
-rw-rw----1 oracle oinstall 24 May 16 16:47lkWWL
-rw-r-----1 oracle oinstall 1536 May 21 15:05orapwWWL
drwxr-xr-x2 oracle oinstall 4096 Jul 5 18:25 pfilebak
-rw-r-----1 oracle oinstall 7389184 Jul 5 17:53snapcf_WWL.f
[oracle@wwldbdbs]$
我们可以看到这里面pfile和spfile都没有了，那么数据库肯定是起不来的，这个时候需要数据库起来肯定需要恢复spfile或手工写一个pfile来供数据库的启动。
4、重启数据库
SQL> startup
ORA-01078:failure in processing system parameters
LRM-00109: couldnot open parameter file '/DBSoft/product/10.2.0/db_1/dbs/initWWL.ora'
报错：没有找到参数文件，数据库无法启动，甚至无法到达nomount状态，这个时候我们是不能使用rman的，那么我们必须手工写一个非常简单的pfile，将数据库启动到nomount状态下，这个时候就可以通过rman来进行对数据库的spfile进行恢复了。
5、开始恢复参数文件：
编辑一个pfile文件在/DBSoft/product/10.2.0/db_1/dbs/目录下,执行添加如下一行内容即可,文件名称为initWWL.ora：
db_name=wwl
6、再次启动数据库，现在数据库已经能启动到nomount状态了：
SQL> conn / assysdba
Connected to anidle instance.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 117440512 bytes
Fixed Size 1218004 bytes
VariableSize 58722860 bytes
DatabaseBuffers 50331648 bytes
Redo Buffers 7168000 bytes
ORA-00205: errorin identifying control file, check alert log for more info
SQL> selectinstance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------------------
WWL STARTED
SQL>
7、这个时候我们就能通过rman来对spfile服务器参数文件进行恢复了。
RMAN> restore spfile;
Starting restore at 05-JUL-12
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002: failure of restore command at07/05/2012 18:44:06
RMAN-06563: control file or SPFILE must berestored using FROM AUTOBACKUP
恢复的时候报错，说控制文件和spfile文件的恢复必须是来自于自动备份，但是我们没有做自动备份呀，怎么办，我们刚才是手工备份的参数文件。那么我们将用如下的指定备份路径来进行参数文件的恢复。
RMAN> restore spfile from '/DBBak/bak_WWL_07_05_03nfbi5c_1_1'; 也就是我们刚才在备份信息中看到的路径
Starting restore at 05-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /DBBak/bak_WWL_07_05_03nfbi5c_1_1
channel ORA_DISK_1: SPFILE restore fromautobackup complete
Finished restore at 05-JUL-12
RMAN>
OK了，恢复成功。
8、查看spfile是否恢复成功;
[oracle@wwldb dbs]$ ll
total 7248
drwxr-xr-x 2 oracle oinstall 4096 Jul 5 18:25 bak
-rw-rw---- 1 oracle oinstall 1544 May 16 16:44 hc_WWL.dat
-rw-r--r-- 1 oracle oinstall 12 Jul 5 18:36 initWWL.ora
-rw-rw---- 1 oracle oinstall 24 May 23 15:52 lkDUMMY
-rw-rw---- 1 oracle oinstall 24 May 16 16:47 lkWWL
-rw-r----- 1 oracle oinstall 1536May 21 15:05 orapwWWL
drwxr-xr-x 2 oracle oinstall 4096 Jul 5 18:25 pfilebak
-rw-r----- 1 oracle oinstall 7389184Jul 5 17:53 snapcf_WWL.f
-rw-r----- 1 oracleoinstall 2560 Jul 5 18:47 spfileWWL.ora
我们可以看到，现在spfile已经恢复成功了。
9、再次启动数据库：
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 79693416 bytes
Database Buffers 197132288 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL>
我们可以看到，数据库现在是已经起来了。
通过手工重建来实现参数文件的恢复，适用于所有版本
1、我们可以参考建库后生成的init.ora文件来进行修改，只需保留以下即可
[oracle@wwldb dbs]$ cat init.ora |grep -v ^# |grep -v ^$ >initWWL.ora
[oracle@wwldb dbs]$ ls
bak hc_WWL.dat init.ora initWWL.ora lkDUMMY lkWWL orapwWWL pfilebak snapcf_WWL.f
2、编辑initWWL.ora文件，修改db_name和control_files的值为实际值即可。
db_name=WWL
db_files = 80 # SMALL
db_file_multiblock_read_count = 8 # SMALL
db_block_buffers = 100 # SMALL
shared_pool_size = 62198988 #SMALL
log_checkpoint_interval = 10000
processes = 50 #SMALL
parallel_max_servers = 5 #SMALL
log_buffer = 32768 # SMALL
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
global_names = TRUE
control_files ='/DBSoft/oradata/WWL/control01.ctl','/DBData/oradata/WWL/control02.ctl','/DBData/oradata/WWL/control03.ctl'
3、启动数据库：
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
至此参数文件已经恢复成功。
4、 重做日志(Redo)文件丢失恢复
重做日志文件记录了数据库的变更数据。一般重做日志文件的失败不会使数据库已提交的数据丢失，但是会影响数据库的恢复。重做日志分为两种状态当前联机重做日志和非当前的联机重做日志
4.1 非当前redo(联机重做日志)文件丢失恢复
数据库运行的时候，日志中报如下错误：
ORA-00313:open failed for members of log group 1 of thread 1
ORA-00312:online log 1 thread 1: '/DBData/WWL/redo01.log'
查看日志组，判断损坏的日志组是否为当前日志组
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------
1 1 32 31457280 1 YES INACTIVE 1063037 04-JUL-12
3 1 34 31457280 1 NO CURRENT 1118555 05-JUL-12
2 1 33 31457280 1 YES INACTIVE 1086278 05-JUL-12
我们可以看到损坏的那组日志不是当前的日志，这个时候我么可以通过使用clear命令来重建该日志文件组。
通过重建来恢复非当前日志组，实现数据库的打开。
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: openfailed for members of log group 1 of thread 1
ORA-00312: onlinelog 1 thread 1: '/DBData/WWL/redo01.log'
SQL>alter database clear logfile group 1;
Databasealtered.
重建完之后数据库可以打开了，至此恢复完成
SQL> alter database open;
Database altered.
4.2当前redo(联机重做日志)文件丢失恢复
数据库启动的时候报如下错误
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313:open failed for members of log group 1 of thread 1
ORA-00312:online log 1 thread 1: '/DBData/WWL/redo01.log'
ORA-27037:unable to obtain file status
LinuxError: 2: No such file or directory
Additionalinformation: 3
查看日志组，判断损坏的日志组是否为当前日志组
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------
3 1 34 31457280 1 YES INACTIVE 1118555 05-JUL-12
2 1 33 31457280 1 YES INACTIVE 1086278 05-JUL-12
在这里可以看到损坏的为当前日志组，那么意味着会有在线数据丢失，因为重做日志里面当前状态里面存放的是是没有归档及写入到数据文件的活动数据，那么这种恢复必然是会导致数据的不同步，从而使数据丢失。
我们可以首先尝试清空日志组信息并重建的方式来进行恢复：
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR atline 1:
ORA-01624:log 1 needed for crash recovery of instance WWL (thread 1)
ORA-00312:online log 1 thread 1: '/DBData/WWL/redo01.log'
如上方法不行，可以尝试采取基于SCN，控制文件信息或取消的方法来尝试恢复数据库。
使用基于控制文件的redo恢复：
SQL>RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 1139916 generated at 07/05/201221:49:48 needed for thread 1
ORA-00289: suggestion :/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf
ORA-00280: change 1139916 for thread 1 is in sequence #35
Specify log: {<RET>=suggested | filename | AUTO |CANCEL}
auto
ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log'/DBSoft/product/10.2.0/db_1/dbs/arch1_35_783449272.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL>alter system set "_allow_resetlogs_corruption" = true scope = spfile;
System altered.
SQL>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>startup mount;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>alter system reset "_allow_resetlogs_corruption" scope = spfile sid ='*';
System altered.
SQL>alter database open resetlogs;
Database altered.
SQL>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
WWL OPEN
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------
1 1 1 31457280 1 NO CURRENT 1200799 06-JUL-12
2 1 0 31457280 1 YESUNUSED 0
3 1 0 31457280 1 YES UNUSED 0
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------
1 1 17 31457280 1 YES INACTIVE 1241271 06-JUL-12
2 1 18 31457280 1 YES INACTIVE 1241273 06-JUL-12
3 1 19 31457280 1 NO CURRENT 1241275 06-JUL-
5、
介于Rman恢复的自动性，我们刚才对数据库的全库，当然包括了所有表空间，文件还有控制文件及参数文件都做了备份，这些备份的内容实际上数据库在运行中不可缺少的必要元素，通常情况下我们在生产库中只需要备份这些内容即可。有了这些备份，即使数据库的的任何信息丢失，我们都可以通过这些备份信息得以恢复使得数据库正常运行，当然如果我们将archivelog 也备份，当然就会更好了，一般情况下这个可以不用备份，备份archivelog占用空间非常大。如果有海量的储存，当然备份也无妨咯，那样就可以将数据库实现相对更加完整的恢复。
Egg:
删除数据库的所有文件，仅保留redo日志和archivelog文件
删除控制文件：
[oracle@wwldb WWL]$ pwd
/DBData/oradata/WWL
[oracle@wwldb WWL]$ ls
control02.ctl control03.ctl
[oracle@wwldb WWL]$ rm -rf *.ctl
[oracle@wwldbWWL]$
[oracle@wwldb WWL]$ pwd
/DBSoft/oradata/WWL
[oracle@wwldb WWL]$ ls
control01.ctl
[oracle@wwldb WWL]$ rm -rfcontrol01.ctl
[oracle@wwldbWWL]$
删除数据文件：
[oracle@wwldb WWL]$ ls
redo01.log redo02.log redo03.logsysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@wwldb WWL]$ rm -rf *.dbf
[oracle@wwldb WWL]$ ls
redo01.log redo02.log redo03.log
[oracle@wwldbWWL]$
删除参数文件：
[oracle@wwldb dbs]$ cd$ORACLE_HOME/dbs
[oracle@wwldb dbs]$ rm -rfspfileWWL.ora
[oracle@wwldbdbs]$
执行大恢复:
1、现在数据库是关闭状态
[oracle@wwldb dbs]$ ps -ef|grep ora
root 2910 2888 0 10:02 ? 00:00:15hald-addon-storage: polling/dev/hdc
root 28954 3478 0 11:38 pts/100:00:00 su - oracle
oracle 28955 28954 0 11:38 pts/100:00:00 -bash
root 29361 3438 0 13:41 pts/200:00:00 su - oracle
oracle 29362 29361 0 13:41 pts/200:00:01 -bash
oracle 29988 29362 0 16:06 pts/200:00:00sqlplus as sysdba
oracle 30102 28955 0 17:03 pts/100:00:00 ps -ef
oracle 30103 28955 0 17:03 pts/100:00:00 grep ora
[oracle@wwldbdbs]$
2、要恢复首先要将数据库启动到mount状态才能恢复
[oracle@wwldb dbs]$ sqlplus / assysdba
SQL*Plus:Release 10.2.0.1.0 -Production on Wed May 23 17:03:42 2012
Copyright (c)1982, 2005, Oracle. Allrights reserved.
Connected to anidle instance.
SQL> startup
ORA-01078:failure inprocessing system parameters
LRM-00109: couldnot openparameter file '/DBSoft/product/10.2.0/db_1/dbs/initWWL.ora'
SQL>
3、因为数据库没有参数文件，起不来，只能能启动到nomount状态
[oracle@wwldb dbs]$ rman target /
RecoveryManager: Release 10.2.0.1.0- Production on Wed May 23 17:06:32 2012
Copyright (c)1982, 2005, Oracle. Allrights reserved.
connected totarget database (notstarted)
RMAN> startup force nomount;
startup failed:ORA-01078: failure inprocessing system parameters
LRM-00109: couldnot open parameterfile '/DBSoft/product/10.2.0/db_1/dbs/initWWL.ora'
starting Oracleinstance withoutparameter file for retrival of spfile
Oracle instancestarted
Total System GlobalArea 159383552bytes
Fixed Size 1218244 bytes
VariableSize 58722620 bytes
DatabaseBuffers 92274688 bytes
RedoBuffers 7168000 bytes
RMAN>
4、开始恢复参数文件，只有恢复了参数文件和控制文件数据库才能到mount状态来恢复数据文件：
RMAN> restorespfile fromautobackup;
Starting restoreat 23-MAY-12
using targetdatabase control fileinstead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=36devtype=DISK
RMAN-00571:===========================================================
RMAN-00569:=============== ERRORMESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002:failure of restorecommand at 05/23/2012 17:08:42
RMAN-06495: mustexplicitly specifyDBID with SET DBID command
RMAN>
注意：这里有问题了，spfile无法恢复，必须指定DBID。我记得在每次登录到rman的时候都会显示一个数据库的DBID，但是为什么这里要我们指定DBID呢？？？原因很简单，因为我们登录RMAN的时候，数据库已经宕机了，而且参数文件，控制文件，数据文件都没有了，它到哪里去找DBID；所以需要我们手动来指定，问题是现在既然登录的时候不显示，我们也不知道在哪里呀,如下:
[oracle@wwldb dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0- Production on WedMay 23 17:06:32 2012
Copyright (c) 1982, 2005, Oracle. All rightsreserved.
connected to targetdatabase (not started)
没关系的因为我们的控制文件做了自动备份，在自动备份的控制文件里面已经存在了DBID，我们现在去找找吧，默认就备份在如下位置：
[oracle@wwldb dbs]$ pwd
/DBSoft/product/10.2.0/db_1/dbs
[oracle@wwldb dbs]$ ls
c-5520179-20120518-01c-5520179-20120523-01 hc_WWL.dat lkWWL
其中5520179就是DBID了，我们只需要通过指定这个DBID，就可以将spfile恢复并将数据库启动到mount状态。
RMAN> set DBID=5520179
executingcommand: SET DBID
RMAN> restore spfile fromautobackup;
Starting restoreat 23-MAY-12
using targetdatabase control fileinstead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=39devtype=DISK
channelORA_DISK_1: looking forautobackup on day: 20120523
channelORA_DISK_1: autobackup found:c-5520179-20120523-04
channelORA_DISK_1: SPFILE restorefrom autobackup complete
Finished restoreat 23-MAY-12
RMAN>
5、恢复控制文件
RMAN> restore controlfile fromautobackup;
Starting restoreat 23-MAY-12
using channelORA_DISK_1
channelORA_DISK_1: looking forautobackup on day: 20120523
channelORA_DISK_1: autobackup found:c-5520179-20120523-04
channelORA_DISK_1: control filerestore from autobackup complete
outputfilename=/DBSoft/product/10.2.0/db_1/dbs/cntrlWWL.dbf
Finished restoreat 23-MAY-12
RMAN>
重启数据库到mount状态---很重要
SQL> startup mount;
ORACLE instancestarted.
Total SystemGlobal Area 285212672bytes
Fixed Size 1218968 bytes
VariableSize 79693416 bytes
DatabaseBuffers 197132288 bytes
RedoBuffers 7168000 bytes
Databasemounted.
SQL> exit
Disconnectedfrom Oracle Database 10gEnterprise Edition Release 10.2.0.1.0 - Production
With thePartitioning, OLAP and DataMining options
[oracle@wwldb dbs]$ rman target /
RecoveryManager: Release 10.2.0.1.0- Production on Wed May 23 19:04:15 2012
Copyright (c)1982, 2005, Oracle. Allrights reserved.
connected totarget database: WWL(DBID=5520179, not open)
注意：详细参考如下：
http://space.itpub.net/?uid-20674423-action-viewspace-itemid-730717
6、恢复数据库其它的数据文件：
RMAN> restore database;
Starting restoreat 23-MAY-12
using targetdatabase control fileinstead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: sid=156devtype=DISK
channelORA_DISK_1: starting datafilebackupset restore
channelORA_DISK_1: specifyingdatafile(s) to restore from backup set
restoringdatafile 00004 to/DBData/WWL/users01.dbf
channelORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_05_23_0cnbn2a3_1_1
channelORA_DISK_1: restored backuppiece 1
piecehandle=/DBBak/bak_WWL_05_23_0cnbn2a3_1_1tag=TAG20120523T133147
channelORA_DISK_1: restore complete,elapsed time: 00:00:02
channelORA_DISK_1: starting datafilebackupset restore
channelORA_DISK_1: specifyingdatafile(s) to restore from backup set
restoringdatafile 00001 to/DBData/WWL/system01.dbf
restoringdatafile 00002 to/DBData/WWL/undotbs01.dbf
restoringdatafile 00003 to/DBData/WWL/sysaux01.dbf
channelORA_DISK_1: reading frombackup piece /DBBak/bak_WWL_05_23_0enbn2bk_1_1
channelORA_DISK_1: restored backuppiece 1
piecehandle=/DBBak/bak_WWL_05_23_0enbn2bk_1_1tag=TAG20120523T133235
channelORA_DISK_1: restore complete,elapsed time: 00:01:05
Finished restoreat 23-MAY-12
RMAN>
RMAN> recover database;
Starting recoverat 23-MAY-12
using channel ORA_DISK_1
starting mediarecovery
media recovery complete,elapsedtime: 00:00:05
Finished recoverat 23-MAY-12
RMAN> alter database open; ---------我们看到数据库已经打开了处于open状态
database opened
SQL> select instance_name,statusfrom v$instance;
INSTANCE_NAME STATUS
----------------------------
WWL OPEN
SQL>
6、
有的时候在企业里面难免会出现由于磁盘损坏而导致数据库的故障乃至数据的丢失，那么这个时候数据的备份就显得尤为的重要。在这一节我们重点讨论下由于装载数据文件，redo日志文件，controlfile控制文件的磁盘损坏的数据恢复。
6.1 通过强制卸载磁盘模拟数据磁盘损坏：
[root@wwldb ~]# umount -f /DBData/
umount2: 资源或设备忙
umount: /DBData: device is busy
umount2: 资源或设备忙
umount: /DBData: device is busy
[root@wwldb ~]# fuser -m -k /DBData/
/DBData/: 3508 3510 3512 3514 3516 3518 3529 3531 3535 3541 3610c
[root@wwldb ~]# fuser -m -k -i -k /DBData/ 强制kill /DBData相关进程
[root@wwldb ~]# umount -f /DBData/ 卸载/DBData
[root@wwldb ~]#
6.2 umount 后，通过alert看到实例也随之宕机了。
Fri Jul 616:03:33 2012
Errors in file/DBSoft/admin/WWL/bdump/wwl_pmon_3502.trc:
ORA-00471: DBWR process terminated with error
Fri Jul 616:03:33 2012
PMON: terminating instance due to error 471
Instance terminated by PMON, pid = 3502
[root@wwldb bdump]# ps -ef|grep ora
root 2965 2943 0 14:39 ? 00:00:00 hald-addon-storage: polling/dev/hdc
root 3944 3050 0 16:07 pts/2 00:00:00 su - oracle
oracle 3945 3944 0 16:07 pts/2 00:00:00 -bash
oracle 3977 3945 0 16:07 pts/2 00:00:00 rlwrap sqlplus / as sysdba
oracle 3978 3977 0 16:07 pts/3 00:00:00 sqlplus as sysdba
oracle 3979 3978 0 16:07 ? 00:00:00 oracleWWL(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 4022 3980 0 16:10 pts/4 00:00:00 grep ora
[root@wwldb bdump]#
6.3 要恢复首先要将数据库启动到mount状态才能恢复
SQL> startup
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, checkalert log for more info
数据库无法启动到mount状态，要执行恢复必须启动到mount状态下才能执行，不过我们在alert日志里面看到是因为缺失控制文件2数据库无法启动到mount状态，见如下：
Fri Jul 616:13:24 2012
ORA-00202: control file:'/DBData/oradata/WWL/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 616:13:24 2012
ORA-205 signalled during: ALTER DATABASE MOUNT...
这个时候我们可以尝试查找其它控制文件是否都存在，存放在哪里，只要存在任何一个控制文件我们只需要修改参数文件来达到将数据库启动到mount状态。
SQL> show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /DBSoft/oradata/WWL/control01.ctl, /DBData/oradata/WWL/control02.ctl,/DBData/oradata/WWL/control03.ctl
我们通过spfile参数可以看到控制文件是存放在两块磁盘上，损坏的磁盘为/DBData，那么也就以为着control02.ctl和control03.ctl两个控制文件损坏，这个时候我们可以通过/DBSoft磁盘上的control01.ctl来启动数据库，或者将control02.ctl和control03通过control01.ctl转储到其它磁盘上来启动数据库。
我现在通过修改参数文件仅保留control01.ctl来启动数据库。
SQL> alter system set control_files ='/DBSoft/oradata/WWL/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
我们可以看到通过修改参数文件，现在数据库已经启动到mount状态。
6.4 添加新的硬盘，并将其格式化创建文件系统，用于数据库数据文件存放的新路径，详细步骤参考：
我刚才创建的分区名称是/DBBak2，见如下：
[oracle@wwldb /]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
7.7G 3.0G 4.3G 42% /
/dev/sda1 99M 12M 82M 13% /boot
tmpfs 506M 0 506M 0%/dev/shm
/dev/mapper/DBSoft-dbsoft
20G 1.7G 18G 9% /DBSoft
/dev/mapper/DBBack-DBBack001
20G 720M 18G 4% /DBBak
/dev/mapper/DBBak2-DBBak2
20G 173M 19G 1% /DBBak2
6.5 创建对应的目录
[oracle@wwldb ~]$ mkdir /DBBak2/oradata/WWL
[oracle@wwldb WWL]$ pwd
/DBBak2/oradata/WWL
[oracle@wwldb WWL]$ ls -a
. ..
6.6 将数据文件恢复到/DBBak2/oradata/WWL目录中
查看备份信息：
RMAN> list backup;
using target database control file instead of recoverycatalog
List of Backup Sets
===================
BS Key Type LVSize Device Type Elapsed TimeCompletion Time
------- ---- -- ---------- ----------- ---------------------------
6 Full 540.81M DISK 00:01:13 06-JUL-12
BP Key:6 Status: AVAILABLE Compressed: NO Tag: TAG20120706T154942
PieceName: /DBBak/bak_WWL_07_06_06nfdv8n_1_1
List ofDatafiles in backup set 6
File LV TypeCkp SCN Ckp Time Name
---- -- -------------- --------- ----
1 Full 1263589 06-JUL-12 /DBData/WWL/system01.dbf
2 Full 1263589 06-JUL-12 /DBData/WWL/undotbs01.dbf
3 Full 1263589 06-JUL-12 /DBData/WWL/sysaux01.dbf
4 Full 1263589 06-JUL-12 /DBData/WWL/users01.dbf
5 Full 1263589 06-JUL-12 /DBData/WWL/wwl001.dbf
6 Full 1263589 06-JUL-12 /DBData/WWL/wwl002.dbf
7 Full 1263589 06-JUL-12 /DBData/WWL/wwl003.dbf
BS Key Type LVSize Device Type Elapsed TimeCompletion Time
------- ---- -- ---------- ----------- ---------------------------
7 Full 7.11M DISK 00:00:01 06-JUL-12
BP Key:7 Status: AVAILABLE Compressed: NO Tag: TAG20120706T155059
PieceName: /DBBak/bakctl_c-5520179-20120706-01
Control FileIncluded: Ckp SCN: 1263606 Ckp time:06-JUL-12
SPFILEIncluded: Modification time: 06-JUL-12
通过备份信息执行如下恢复到新的磁盘上：
RMAN> run {
2> set newname fordatafile '/DBData/WWL/system01.dbf' to '/DBBak2/oradata/WWL/system01.dbf';
3> set newname fordatafile '/DBData/WWL/undotbs01.dbf' to '/DBBak2/oradata/WWL/undotbs01.dbf';
4> set newname fordatafile '/DBData/WWL/sysaux01.dbf' to '/DBBak2/oradata/WWL/sysaux01.dbf';
5> set newname fordatafile '/DBData/WWL/users01.dbf' to '/DBBak2/oradata/WWL/users01.dbf';
6> set newname fordatafile '/DBData/WWL/wwl001.dbf' to '/DBBak2/oradata/WWL/wwl01.dbf';
7> set newname fordatafile '/DBData/WWL/wwl002.dbf' to '/DBBak2/oradata/WWL/wwl02.dbf';
8> set newname fordatafile '/DBData/WWL/wwl003.dbf' to '/DBBak2/oradata/WWL/wwl03.dbf';
9> restore database;
10> switch datafile all;
11> recover database;
12> }
executing command: SETNEWNAME
executing command: SETNEWNAME
executing command: SETNEWNAME
executing command: SETNEWNAME
executing command: SETNEWNAME
executing command: SETNEWNAME
executing command: SETNEWNAME
Starting restore at 06-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=46devtype=DISK
channel ORA_DISK_1: startingdatafile backupset restore
channel ORA_DISK_1:specifying datafile(s) to restore from backup set
restoring datafile 00001 to/DBBak2/oradata/WWL/system01.dbf
restoring datafile 00002 to/DBBak2/oradata/WWL/undotbs01.dbf
restoring datafile 00003 to/DBBak2/oradata/WWL/sysaux01.dbf
restoring datafile 00004 to/DBBak2/oradata/WWL/users01.dbf
restoring datafile 00005 to/DBBak2/oradata/WWL/wwl01.dbf
restoring datafile 00006 to/DBBak2/oradata/WWL/wwl02.dbf
restoring datafile 00007 to/DBBak2/oradata/WWL/wwl03.dbf
channel ORA_DISK_1: readingfrom backup piece /DBBak/bak_WWL_07_06_06nfdv8n_1_1
channel ORA_DISK_1: restoredbackup piece 1
piecehandle=/DBBak/bak_WWL_07_06_06nfdv8n_1_1 tag=TAG20120706T154942
channel ORA_DISK_1: restorecomplete, elapsed time: 00:01:46
Finished restore at 06-JUL-12
datafile 1 switched todatafile copy
input datafile copy recid=8stamp=787945637 filename=/DBBak2/oradata/WWL/system01.dbf
datafile 2 switched todatafile copy
input datafile copy recid=9stamp=787945637 filename=/DBBak2/oradata/WWL/undotbs01.dbf
datafile 3 switched todatafile copy
input datafile copy recid=10stamp=787945637 filename=/DBBak2/oradata/WWL/sysaux01.dbf
datafile 4 switched todatafile copy
input datafile copy recid=11stamp=787945637 filename=/DBBak2/oradata/WWL/users01.dbf
datafile 5 switched todatafile copy
input datafile copy recid=12stamp=787945637 filename=/DBBak2/oradata/WWL/wwl01.dbf
datafile 6 switched todatafile copy
input datafile copy recid=13stamp=787945637 filename=/DBBak2/oradata/WWL/wwl02.dbf
datafile 7 switched todatafile copy
input datafile copy recid=14stamp=787945637 filename=/DBBak2/oradata/WWL/wwl03.dbf
Finsh
恢复脚本如下：
run {
set newname for datafile'/DBData/WWL/system01.dbf' to '/DBBak2/oradata/WWL/system01.dbf';
set newname for datafile'/DBData/WWL/undotbs01.dbf' to '/DBBak2/oradata/WWL/undotbs01.dbf';
set newname for datafile'/DBData/WWL/users01.dbf' to '/DBBak2/oradata/WWL/users01.dbf';
set newname for datafile'/DBData/WWL/wwl001.dbf' to '/DBBak2/oradata/WWL/wwl01.dbf';
set newname for datafile'/DBData/WWL/wwl002.dbf' to '/DBBak2/oradata/WWL/wwl02.dbf';
set newname for datafile'/DBData/WWL/wwl003.dbf' to '/DBBak2/oradata/WWL/wwl03.dbf';
restore database;
switch datafile all;
6.7 生成控制文件trace文件，用来重建控制文件：
SQL> alter database backupcontrolfile to trace as '/tmp/ctl.txt';
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 100663296 bytes
Fixed Size 1217884 bytes
Variable Size 88083108 bytes
Database Buffers 8388608 bytes
Redo Buffers 2973696 bytes
SQL> CREATE CONTROLFILEREUSE DATABASE "WWL" RESETLOGS ARCHIVELOG
MAXDATAFILES 100
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXINSTANCES 8
MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/DBBak2/oradata/WWL/redo01.log' SIZE 30M,
GROUP 3 '/DBBak2/oradata/WWL/redo03.log' SIZE 30M,
10 GROUP 4 (
) SIZE 128M,
GROUP 5 (
'/DBBak2/oradata/WWL/redo5a.log',
'/DBBak2/oradata/WWL/redo4a.log',
'/DBBak2/oradata/WWL/redo4b.log'
) SIZE 128M,
GROUP 5 (
'/DBBak2/oradata/WWL/redo5a.log',
'/DBBak2/oradata/WWL/redo5b.log'
) SIZE 128M,
GROUP 6 (
'/DBBak2/oradata/WWL/redo6a.log',
'/DBBak2/oradata/WWL/redo6b.log'
) SIZE 128M,
GROUP 7 (
'/DBBak2/oradata/WWL/redo7a.log',
'/DBBak2/oradata/WWL/redo7b.log'
) SIZE 128M
DATAFILE
'/DBBak2/oradata/WWL/system01.dbf',
'/DBBak2/oradata/WWL/undotbs01.dbf',
'/DBBak2/oradata/WWL/sysaux01.dbf',
'/DBBak2/oradata/WWL/users01.dbf',
'/DBBak2/oradata/WWL/wwl01.dbf',
'/DBBak2/oradata/WWL/wwl02.dbf',
'/DBBak2/oradata/WWL/wwl03.dbf'
CHARACTER SET ZHS16CGB231280
35 ;
Control file created.
6.8 以resetlog模式启动数据库：
SQL> alter database openresetlogs;
6.9 删除原redo日志文件，重建redo新日志文件组到新的磁盘上：
SQL> alterdatabase drop logfile group 1;
Databasealtered.
SQL> alterdatabase drop logfile group 2;
Databasealtered.
SQL> alterdatabase drop logfile group 3;
Database altered.
SQL>
SQL> alter database addlogfile group 4 ('/DBBak2/oradata/WWL/redo4a.log','/DBBak2/oradata/WWL/redo4b.log')size 128M;
Database altered.
SQL> alter database addlogfile group 5('/DBBak2/oradata/WWL/redo5a.log','/DBBak2/oradata/WWL/redo5b.log') size 128M;
Database altered.
SQL> alter database addlogfile group 6('/DBBak2/oradata/WWL/redo6a.log','/DBBak2/oradata/WWL/redo6b.log') size 128M;
Database altered.
SQL> alter database addlogfile group 7 ('/DBBak2/oradata/WWL/redo7a.log','/DBBak2/oradata/WWL/redo7b.log')size 128M;
Database altered.
SQL>
6.10 查看日志组信息及状态：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- -------------------- ---------- ---------- --- ---------------- ------------- ---------
4 1 0 134217728 2 YES UNUSED 0
5 1 0 134217728 2 YESUNUSED 0
6 1 0 134217728 2 YES UNUSED 0
7 1 1 134217728 2 NO CURRENT 1263590 06-JUL-12
SQL> alter system switchlogfile;
System altered.
SQL> alter system switchlogfile;
System altered.
SQL> alter system switchlogfile;
System altered.
SQL> alter system switchlogfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- -------------------- ---------- ---------- --- ---------------- ------------- ---------
4 1 2 134217728 2 YES INACTIVE 1263859 06-JUL-12
5 1 3 134217728 2 YES INACTIVE 1263861 06-JUL-12
6 1 4 134217728 2 YES INACTIVE 1263863 06-JUL-12
7 1 5 134217728 2 NO CURRENT 1263866 06-JUL-12
SQL>
至此恢复完成。
