探索ORACLE之RMAN_07整个业务表空间丢失恢复

作者：吴伟龙   Name：Prodence Woo

QQ：286507175  msn:hapy-wuweilong@hotmail.com

 

1、     整个业务表空间丢失恢复

注意：以下的所有实验，都是基于上面的全库备份来做的恢复。

2.1 删除wwl表空间的所有数据文件

[root@wwldb ~]# cd /DBData/WWL/

[root@wwldb WWL]# rm -rf wwl*

[root@wwldb WWL]# ll

总计 881068

-rw-r----- 1 oracle oinstall  31457792 06-22 01:34 redo01.log

-rw-r----- 1 oracle oinstall  31457792 06-22 01:34 redo02.log

-rw-r----- 1 oracle oinstall  31457792 06-22 02:14 redo03.log

-rw-r----- 1 oracle oinstall 27263795206-22 02:08 sysaux01.dbf

-rw-r----- 1 oracle oinstall 50332467206-22 02:09 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 05-29 22:00 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 06-22 02:08 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 06-22 01:34 users01.dbf

 

 2.2 启动数据库，报如下错误。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

 

Total System Global Area  285212672 bytes

Fixed Size                  1218968 bytes

Variable Size             100664936 bytes

Database Buffers          176160768 bytes

Redo Buffers                7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5- see DBWR trace file

ORA-01110: data file 5:'/DBData/WWL/wwl001.dbf'

 

  2.3 检查跟踪文件，非常清晰的告诉丢失了有文件找不到，丢失了。

***SERVICE NAME:() 2012-06-22 09:17:38.573

***SESSION ID:(167.1) 2012-06-22 09:17:38.573

ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [5]

ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [5] [/DBData/WWL/wwl001.dbf]

ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA

LinuxError: 2: No such file or directory

Additionalinformation: 3

ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [6]

ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [6] [/DBData/WWL/wwl002.dbf]

ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA

LinuxError: 2: No such file or directory

Additionalinformation: 3

ORA-01157:Message 1157 not found; No message file for product=RDBMS, facility=ORA;arguments: [7]

ORA-01110:Message 1110 not found; No message file for product=RDBMS, facility=ORA;arguments: [7] [/DBData/WWL/wwl003.dbf]

ORA-27037:Message 27037 not found; No message file for product=RDBMS, facility=ORA

LinuxError: 2: No such file or directory

Additionalinformation: 3

 

由如上的跟踪信息我们得出是由于/DBData/WWL/wwl001.dbf；/DBData/WWL/wwl002.dbf；/DBData/WWL/wwl001.dbf这三个文件丢失导致数据库无法起来，并且这三个文件同时构成了一个WWL表空间。在这个表空间中存储了各种各样重要的数据。同样我们可以按照之前的方法通过恢复数据文件的方式来进行数据恢复，介于这次丢失的是所有数据文件，数量比较多，而且如果对所有数据文件做恢复，不仅大量的增加的工作量，同时也增加的恢复的风险。所以在这里我们通过使用RMAN执行表空间恢复的方式来进行恢复。当然基于表空间的恢复也分两种，一直是在不影响数据库其它业务情况下的在线联机恢复，还有一种是停机停业务的恢复，详细见如下：

2.4 恢复方法一：零停机，在线恢复

开始执行恢复操作，分为如下七个步骤：

1、查看数据库状态是open的，我们刚才的删除数据文件没有对库照成太大的影响。

SQL>select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

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

WWL              OPEN

 

2、将wwl表空间离线

SQL>alter tablespace wwl offline for recover;

Tablespacealtered.

 

3、将wwl表空间的所有数据文件从备份中restore出来

RMAN> restoretablespace wwl;

 

Starting restoreat 22-JUN-12

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=156 devtype=DISK

 

channelORA_DISK_1: starting datafile backupset restore

channelORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile00005 to /DBData/WWL/wwl001.dbf

restoring datafile00006 to /DBData/WWL/wwl002.dbf

restoring datafile00007 to /DBData/WWL/wwl003.dbf

channelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1

channelORA_DISK_1: restored backup piece 1

piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021

channelORA_DISK_1: restore complete, elapsed time: 00:00:08

Finished restoreat 22-JUN-12

查看到WWL表空间的所有数据文件已经restore出来了。

[root@wwldb WWL]#ll wwl*

-rw-r----- 1oracle oinstall 52436992 06-22 10:11 wwl001.dbf

-rw-r----- 1oracle oinstall  5251072 06-22 10:11wwl002.dbf

-rw-r----- 1oracle oinstall  5251072 06-22 10:11wwl003.dbf

[root@wwldb WWL]#

4、执行表空间的所有数据恢复，保证scn一致

RMAN> recover tablespacewwl;

Starting recover at 22-JUN-12

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence20 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arc

archive log thread 1 sequence21 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arc

archive log thread 1 sequence22 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_22_7y8y7l70_.arc

archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arcthread=1 sequence=20

media recovery complete,elapsed time: 00:00:02

Finished recover at 22-JUN-12

5、将表空间在线

SQL>conn / as sysdba

Connected.

 

SQL>alter tablespace wwl online;

Tablespacealtered.

6、验证表空间数据是否都恢复回来了。

 

SQL>select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

WWL01                          TABLE

WWL02                          TABLE

WWL03                          TABLE

WWL04                          TABLE

WWL05                          TABLE

 

SQL>select count(*) from wwl01;

  COUNT(*)

----------

         5

SQL>

 

2.5恢复方法二，离线恢复

开始执行恢复操作，分为五个步骤：

1、强制将数据库启动到mount状态

SQL> startupforce mount;

ORACLE instancestarted.

 

Total SystemGlobal Area  285212672 bytes

Fixed Size                  1218968 bytes

Variable Size             104859240 bytes

DatabaseBuffers          171966464 bytes

Redo Buffers                7168000 bytes

Database mounted.

2、将wwl表空间的所有数据文件从备份中restore出来

RMAN> restoretablespace wwl;

 

Starting restoreat 22-JUN-12

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=156 devtype=DISK

 

channelORA_DISK_1: starting datafile backupset restore

channelORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile00005 to /DBData/WWL/wwl001.dbf

restoring datafile00006 to /DBData/WWL/wwl002.dbf

restoring datafile00007 to /DBData/WWL/wwl003.dbf

channelORA_DISK_1: reading from backup piece /DBBak/bak_WWL_06_22_0vne4ph6_1_1

channelORA_DISK_1: restored backup piece 1

piecehandle=/DBBak/bak_WWL_06_22_0vne4ph6_1_1 tag=TAG20120622T010021

channelORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 22-JUN-12

3、执行表空间的所有数据恢复，保证scn一致

RMAN> recovertablespace wwl;

Starting recoverat 22-JUN-12

using channelORA_DISK_1

starting mediarecovery

 

archive log thread1 sequence 20 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arc

archive log thread1 sequence 21 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_21_7y80zjqx_.arc

archive log thread1 sequence 22 is already on disk as file/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_22_7y8y7l70_.arc

archive logfilename=/DBData/flash_recovery_area/WWL/archivelog/2012_06_22/o1_mf_1_20_7y804kwr_.arcthread=1 sequence=20

media recoverycomplete, elapsed time: 00:00:02

Finished recover at 22-JUN-12

4、打开数据库

SQL> alter database open;

Database altered.

 

5、验证表空间数据是否都恢复回来了。

SQL>conn wwl/wwl

Connected.

SQL>select count(*) from tab;

 

  COUNT(*)

----------

         5

SQL>select * from wwl05;

 

        ID NAME

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

         1 wwl

         2 wm

         3 zq

         4 wbq

         5 wq