探索ORACLE之RMAN_07 磁碟損壞資料丟失恢復

作者：吳偉龍 Name：Prodence Woo

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

有的時候在企業裡面難免會出現由於磁碟損壞而導致資料庫的故障乃至資料的丟失，那麼這個時候，那麼這個時候資料的備份就顯得尤為的重要。在這一節我們重點討論下由於裝載資料檔案，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 6 16:03:33 2012

Errors in file /DBSoft/admin/WWL/bdump/wwl_pmon_3502.trc:

ORA-00471: DBWR process terminated with error

Fri Jul 6 16: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, check alert log for more info

資料庫無法啟動到mount狀態，要執行恢復必須啟動到mount狀態下才能執行，不過我們在alert日誌裡面看到是因為確實控制檔案2資料庫無法啟動到mount狀態，見如下：

Fri Jul 6 16: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 6 16: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

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 recovery catalog

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

6 Full 540.81M DISK 00:01:13 06-JUL-12

BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20120706T154942

Piece Name: /DBBak/bak_WWL_07_06_06nfdv8n_1_1

List of Datafiles in backup set 6

File LV Type Ckp 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 LV Size Device Type Elapsed Time Completion Time

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

7 Full 7.11M DISK 00:00:01 06-JUL-12

BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20120706T155059

Piece Name: /DBBak/bakctl_c-5520179-20120706-01

Control File Included: Ckp SCN: 1263606 Ckp time: 06-JUL-12

SPFILE Included: Modification time: 06-JUL-12

透過備份資訊執行如下恢復到新的磁碟上：

RMAN> run {

2> set newname for datafile '/DBData/WWL/system01.dbf' to '/DBBak2/oradata/WWL/system01.dbf';

3> set newname for datafile '/DBData/WWL/undotbs01.dbf' to '/DBBak2/oradata/WWL/undotbs01.dbf';

4> set newname for datafile '/DBData/WWL/sysaux01.dbf' to '/DBBak2/oradata/WWL/sysaux01.dbf';

5> set newname for datafile '/DBData/WWL/users01.dbf' to '/DBBak2/oradata/WWL/users01.dbf';

6> set newname for datafile '/DBData/WWL/wwl001.dbf' to '/DBBak2/oradata/WWL/wwl01.dbf';

7> set newname for datafile '/DBData/WWL/wwl002.dbf' to '/DBBak2/oradata/WWL/wwl02.dbf';

8> set newname for datafile '/DBData/WWL/wwl003.dbf' to '/DBBak2/oradata/WWL/wwl03.dbf';

9> restore database;

10> switch datafile all;

11> recover database;

12> }

executing command: SET NEWNAME

Starting restore at 06-JUL-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=46 devtype=DISK

channel ORA_DISK_1: starting datafile 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: reading from backup piece /DBBak/bak_WWL_07_06_06nfdv8n_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/DBBak/bak_WWL_07_06_06nfdv8n_1_1 tag=TAG20120706T154942

channel ORA_DISK_1: restore complete, elapsed time: 00:01:46

Finished restore at 06-JUL-12

datafile 1 switched to datafile copy

input datafile copy recid=8 stamp=787945637 filename=/DBBak2/oradata/WWL/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=9 stamp=787945637 filename=/DBBak2/oradata/WWL/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=10 stamp=787945637 filename=/DBBak2/oradata/WWL/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=11 stamp=787945637 filename=/DBBak2/oradata/WWL/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=12 stamp=787945637 filename=/DBBak2/oradata/WWL/wwl01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=13 stamp=787945637 filename=/DBBak2/oradata/WWL/wwl02.dbf

datafile 7 switched to datafile copy

input datafile copy recid=14 stamp=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/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 backup controlfile 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 CONTROLFILE REUSE 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 open resetlogs;

6.9 刪除原redo日誌檔案，重建redo新日誌檔案組到新的磁碟上：

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL>

SQL> alter database add logfile group 4 ('/DBBak2/oradata/WWL/redo4a.log','/DBBak2/oradata/WWL/redo4b.log') size 128M;

Database altered.

SQL> alter database add logfile group 5 ('/DBBak2/oradata/WWL/redo5a.log','/DBBak2/oradata/WWL/redo5b.log') size 128M;

Database altered.

SQL> alter database add logfile group 6 ('/DBBak2/oradata/WWL/redo6a.log','/DBBak2/oradata/WWL/redo6b.log') size 128M;

Database altered.

SQL> alter database add logfile 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 YES UNUSED 0

6 1 0 134217728 2 YES UNUSED 0

7 1 1 134217728 2 NO CURRENT 1263590 06-JUL-12

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

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>

至此恢復完成。

探索ORACLE之RMAN_07 磁碟損壞資料丟失恢復

相關文章