探索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>

 

至此恢复完成。