oracle修復(fù)探索ORACLE之RMAN_07 磁盤(pán)損壞數(shù)據(jù)丟失恢復(fù)
瀏覽量： 次 發(fā)布日期：2023-08-11 21:27:30
探索ORACLE之RMAN_07 磁盤(pán)損壞數(shù)據(jù)丟失恢復(fù)
redo日志文件，controlfile控制文件的磁盤(pán)損壞的數(shù)據(jù)恢復(fù)。
6.1 通過(guò)強(qiáng)制卸載磁盤(pán)模擬數(shù)據(jù)磁盤(pán)損壞：
[root@wwldb ~]# umount -f /DBData/
umount2: 資源或設(shè)備忙
umount: /DBData: device is busy
umount2: 資源或設(shè)備忙
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/ 強(qiáng)制kill /DBData相關(guān)進(jìn)程
[root@wwldb ~]# umount -f /DBData/ 卸載/DBData
[root@wwldb ~]#
6.2 umount 后，通過(guò)alert看到實(shí)例也隨之宕機(jī)了。
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 要恢復(fù)首先要將數(shù)據(jù)庫(kù)啟動(dòng)到mount狀態(tài)才能恢復(fù)
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
數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)到mount狀態(tài)，要執(zhí)行恢復(fù)必須啟動(dòng)到mount狀態(tài)下才能執(zhí)行，不過(guò)我們?cè)赼lert日志里面看到是因?yàn)榇_實(shí)控制文件2數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)到mount狀態(tài)，見(jiàn)如下：
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...
這個(gè)時(shí)候我們可以嘗試查找其它控制文件是否都存在，存放在哪里，只要存在任何一個(gè)控制文件我們只需要修改參數(shù)文件來(lái)達(dá)到將數(shù)據(jù)庫(kù)啟動(dòng)到mount狀態(tài)。
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
我們通過(guò)spfile參數(shù)可以看到控制文件是存放在兩塊磁盤(pán)上，損壞的磁盤(pán)為/DBData，那么也就以為著control02.ctl和control03.ctl兩個(gè)控制文件損壞，這個(gè)時(shí)候我們可以通過(guò)/DBSoft磁盤(pán)上的control01.ctl來(lái)啟動(dòng)數(shù)據(jù)庫(kù)，或者將control02.ctl和control03通過(guò)control01.ctl轉(zhuǎn)儲(chǔ)到其它磁盤(pán)上來(lái)啟動(dòng)數(shù)據(jù)庫(kù)。
我現(xiàn)在通過(guò)修改參數(shù)文件僅保留control01.ctl來(lái)啟動(dòng)數(shù)據(jù)庫(kù)。
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>
我們可以看到通過(guò)修改參數(shù)文件，現(xiàn)在數(shù)據(jù)庫(kù)已經(jīng)啟動(dòng)到mount狀態(tài)。
6.4 添加新的硬盤(pán)，并將其格式化創(chuàng)建文件系統(tǒng)，用于數(shù)據(jù)庫(kù)數(shù)據(jù)文件存放的新路徑，詳細(xì)步驟參考：
Fdisk分區(qū)方法：javascript:void(0)
Parte分區(qū)方法：javascript:void(0)
卷管理分方法：javascript:void(0)
我剛才創(chuàng)建的分區(qū)名稱(chēng)是/DBBak2，見(jiàn)如下：
[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 創(chuàng)建對(duì)應(yīng)的目錄
[oracle@wwldb ~]$ mkdir /DBBak2/oradata/WWL
[oracle@wwldb WWL]$ pwd
/DBBak2/oradata/WWL
[oracle@wwldb WWL]$ ls -a
. ..
6.6 將數(shù)據(jù)文件恢復(fù)到/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
通過(guò)備份信息執(zhí)行如下恢復(fù)到新的磁盤(pán)上：
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
恢復(fù)腳本如下：
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/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文件，用來(lái)重建控制文件：
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.
兆柏?cái)?shù)據(jù)恢復(fù)公司
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
兆柏?cái)?shù)據(jù)恢復(fù)公司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模式啟動(dòng)數(shù)據(jù)庫(kù)：
SQL> alter database openresetlogs;
6.9 刪除原redo日志文件，重建redo新日志文件組到新的磁盤(pán)上：
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 查看日志組信息及狀態(tài)：
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 YESUNUSED 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 oracle修復(fù) 3 134217728 2 YESINACTIVE 1263861 06-JUL-12
6 1 4 134217728 2 YES INACTIVE 1263863 06-JUL-12
oracle修復(fù) 7 1 5 134217728 2 NO CURRENT 1263866 06-JUL-12
SQL>
至此恢復(fù)完成。
重要數(shù)據(jù)丟失請(qǐng)聯(lián)系兆柏?cái)?shù)據(jù)恢復(fù)公司 重要數(shù)據(jù)丟失請(qǐng)聯(lián)系兆柏?cái)?shù)據(jù)恢復(fù)公司 重要數(shù)據(jù)丟失請(qǐng)聯(lián)系兆柏?cái)?shù)據(jù)恢復(fù)公司
. oracle證書(shū),開(kāi)啟數(shù)據(jù)庫(kù)專(zhuān)業(yè)之旅的鑰匙
. 固態(tài)硬盤(pán)數(shù)據(jù)恢復(fù)一般多少錢(qián),固態(tài)硬盤(pán)修復(fù)手把手教你救治不認(rèn)盤(pán)的固態(tài)
. oracle歸檔日志設(shè)置,Oracle數(shù)據(jù)庫(kù)歸檔日志設(shè)置詳解
. 數(shù)據(jù)庫(kù)修復(fù),數(shù)據(jù)庫(kù)修復(fù)的重要性
. oracle ｄｒｏｐ表數(shù)據(jù)恢復(fù),Oracle數(shù)據(jù)庫(kù)中ｄｒｏｐ表數(shù)據(jù)恢復(fù)方法詳解
. oracle 刪除數(shù)據(jù)文件,Oracle 數(shù)據(jù)文件刪除指南
. win11家庭版裝Oracle,oracle官方網(wǎng)站
. 維修硬盤(pán)視頻,輕松掌握硬盤(pán)故障診斷與修復(fù)技巧
. Oracle數(shù)據(jù)庫(kù)還原,全面指南
. oracle價(jià)格,全面了解Oracle軟件的授權(quán)與成本
. oracle數(shù)據(jù)庫(kù)怎么修復(fù),全面指南
. oracle財(cái)務(wù)軟件,企業(yè)財(cái)務(wù)管理的新時(shí)代利器
. 什么是oracle數(shù)據(jù)庫(kù),什么是Oracle數(shù)據(jù)庫(kù)？
. 如何查看oracle錯(cuò)誤日志,Oracle數(shù)據(jù)庫(kù)錯(cuò)誤日志查看指南
. oracle數(shù)據(jù)庫(kù)執(zhí)行計(jì)劃怎么看,查看與分析技巧
. oracle刪除的數(shù)據(jù)怎么恢復(fù),Oracle數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)恢復(fù)指南
. oracle一個(gè)實(shí)例多個(gè)數(shù)據(jù)庫(kù),Oracle數(shù)據(jù)庫(kù)實(shí)例與多個(gè)數(shù)據(jù)庫(kù)的配置與管理
. ora-01578:oracle 數(shù)據(jù)塊損壞,oracle數(shù)據(jù)塊損壞
. mysql數(shù)據(jù)庫(kù)修復(fù),從預(yù)防到實(shí)戰(zhàn)
. oracle恢復(fù)歸檔日志文件,Oracle數(shù)據(jù)庫(kù)恢復(fù)歸檔日志文件的方法與技巧
QQ客服