探索ORACLE之RMAN_07恢复

探索ORACLE之RMAN_07恢复

作者：吴伟龙 Name：Prodence Woo

QQ：286507175 msn:[e-mail]

备份的终极目的是为了更好的将数据恢复和还原过来，在前面的章节中我们已经重点谈完了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;

TNAMETABTYPE 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_NAMESTATUS

---------------- ------------

WWLOPEN

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 TYPEVALUE

----------------------------------------------- ------------------------------

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 Full80.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 Jul5 18:25 bak

-rw-rw---- 1 oracle oinstall 1544 May 16 16:44 hc_WWL.dat

-rw-r--r-- 1 oracle oinstall 12 Jul5 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 Jul5 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、再次启动数据库：

SQL> startup

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.datinit.ora initWWL.ora lkDUMMYlkWWL orapwWWL pfilebaksnapcf_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 132 31457280 1 YES INACTIVE 1063037 04-JUL-12

3 1 3431457280 1 NO CURRENT 1118555 05-JUL-12

2 1 3331457280 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 Area100663296 bytes

Fixed Size 1217884 bytes

Variable Size88083108 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

---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------

11 35 31457280 1 NOCURRENT 113991505-JUL-12

3 1 3431457280 1 YES INACTIVE 1118555 05-JUL-12

2 1 3331457280 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 Area100663296 bytes

Fixed Size 1217884 bytes

Variable Size88083108 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_NAMESTATUS

---------------- ------------

WWLOPEN

SQL> select * from v$log;

GROUP# THREAD#SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- ------------------- ------------- ---------

1 1 131457280 1 NO CURRENT 1200799 06-JUL-12

2 1 031457280 1 YESUNUSED 0

3 1 031457280 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 1731457280 1 YES INACTIVE 1241271 06-JUL-12

2 1 1831457280 1 YES INACTIVE 1241273 06-JUL-12

3 1 1931457280 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://blog.csdn.net/wuweilong/article/details/7596056

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/: 35083510 3512 35143516 3518 35293531 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

root2965 2943 0 14:39 ? 00:00:00 hald-addon-storage: polling/dev/hdc

root3944 3050 0 16:07 pts/2 00:00:00 su - oracle

oracle3945 3944 0 16:07 pts/2 00:00:00 -bash

oracle3977 3945 0 16:07 pts/2 00:00:00 rlwrap sqlplus / as sysdba

oracle3978 3977 0 16:07 pts/3 00:00:00 sqlplus as sysdba

oracle3979 3978 0 16:07 ? 00:00:00 oracleWWL(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

root4022 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 添加新的硬盘，并将其格式化创建文件系统，用于数据库数据文件存放的新路径，详细步骤参考：

Fdisk分区方法：http://blog.csdn.net/wuweilong/article/details/7538634

Parte分区方法：http://blog.csdn.net/wuweilong/article/details/7553200

卷管理分方法：http://blog.csdn.net/wuweilong/article/details/7565530

我刚才创建的分区名称是/DBBak2，见如下：

[oracle@wwldb /]$ df -h

FilesystemSize Used Avail Use% Mounted on

/dev/mapper/VolGroup00-LogVol00

7.7G 3.0G 4.3G42% /

/dev/sda199M 12M 82M13% /boot

tmpfs506M 0 506M 0%/dev/shm

/dev/mapper/DBSoft-dbsoft

20G 1.7G 18G9% /DBSoft

/dev/mapper/DBBack-DBBack001

20G 720M 18G4% /DBBak

/dev/mapper/DBBak2-DBBak2

20G 173M19G 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

------- ---- -- ---------- ----------- ---------------------------

6Full 540.81M DISK00: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

------- ---- -- ---------- ----------- ---------------------------

7Full 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';

setnewname for datafile '/DBData/WWL/sysaux01.dbf' to'/DBBak2/oradata/WWL/sysaux01.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" RESETLOGSARCHIVELOG

MAXDATAFILES 100

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXINSTANCES 8

MAXDATAFILES 100

5MAXINSTANCES 8

6MAXLOGHISTORY 292

7LOGFILE

8GROUP 1 '/DBBak2/oradata/WWL/redo01.log'SIZE 30M,

GROUP 3 '/DBBak2/oradata/WWL/redo03.log' SIZE 30M,

10GROUP 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# BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

---------- -------------------- ---------- ---------- --- ---------------- ------------- ---------

4 1 0134217728 2 YES UNUSED 0

5 1 0134217728 2 YESUNUSED 0

6 1 0134217728 2 YES UNUSED 0

7 1 1134217728 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# BYTESMEMBERS ARC STATUSFIRST_CHANGE# FIRST_TIM

---------- -------------------- ---------- ---------- --- ---------------- ------------- ---------

4 1 2134217728 2 YES INACTIVE 1263859 06-JUL-12

5 1 3134217728 2 YES INACTIVE 1263861 06-JUL-12

6 1 4134217728 2 YES INACTIVE 1263863 06-JUL-12

7 1 5134217728 2 NO CURRENT 1263866 06-JUL-12

SQL>

至此恢复完成。