探索ORACLE之RMAN_07單個資料檔案丢失恢複

探索ORACLE之RMAN_07單個資料檔案丢失恢複

作者：吳偉龍   Name：Prodence Woo

QQ：286507175  msn:[email protected]

備份的終極目的是為了更好的将資料恢複和還原過來，在前面的章節中我們已經重點談完了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');

SQL> insert into wwl01 values(3,'zq');

SQL> insert into wwl01 values(4,'wbq');

SQL> insert into wwl01 values(5,'wq');

SQL> create table wwl02 as select * from wwl01;

SQL> create table wwl03 as select * from wwl01;

SQL> create table wwl04 as select * from wwl01;

SQL> create table wwl05 as select * from wwl01;

檢視表中的資料：

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 messagefile for product=RDBMS, facility=ORA; arguments: [5]

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

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

Linux Error: 2: No such file or directory

Additional information: 3

ORA-1157 signalled during: ALTER DATABASEOPEN...

由以上資訊可以得出資料庫故障是由于資料檔案wwl001.dbf資料檔案異常丢失或者損壞導緻資料庫的故障，那麼資料檔案丢失就必然存在資料庫資料的丢失，但是萬幸的是，在丢失之前我們已經做過備份了，現在我們就來通過之前的備份将wwl001.dbf檔案恢複回來，資料檔案的恢複分為兩種，一直是在不影響資料庫其它業務情況下的線上聯機恢複，還有一種是停機停業務的恢複，詳細見如下：

1.6    恢複方法一，零停機，線上恢複

開始執行恢複操作，分為如下七個步驟：

1、強制将資料庫啟動到mount狀态

RMAN>startup force mount;

Oracle instancestarted

databasemounted

Total System GlobalArea     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;

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

channelORA_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;

7、驗證資料是否恢複，可以看到資料全部恢複回來了。

SQL>conn wwl/wwl

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    恢複方法二，離線恢複

開始執行恢複操作，分為五個步驟：

SQL> startupforce mount;

ORACLE instancestarted.

Total SystemGlobal Area  285212672 bytes

Fixed Size                  1218968 bytes

VariableSize              96470632 bytes

DatabaseBuffers          180355072 bytes

2、将資料檔案從備份中restore出來

RMAN> restoredatafile '/DBData/WWL/wwl002.dbf'

2> ;

Starting restoreat 22-JUN-12

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

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

4、開啟資料庫

SQL> conn /as sysdba

SQL> alterdatabase open;

Databasealtered.

5、驗證資料是否恢複

SQL> select *from tab;

WWL03                          TABLE

WWL05                          TABLE

SQL> selectcount(*) from wwl01;