Oracle数据库的文件有哪些种类呢？这些文件在什么情况下容易被损坏呢？损坏以后又该怎么样去修复呢？本文就介绍了这些内容，包括了10中Oracle文件的损坏与修复的过程，这10种文件几乎涵盖了Oracle数据库中所有类型的文件，接下来就让我们一起来了解一下这10种文件的损坏与修复的过程吧。
一、数据库服务器基本情况
OS：RHEL 3
CPU：4个Intel(R) Xeon(TM) MP CPU 2.70GHz
Mem：8G
Swap：16G
Disk：120G
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0
二、备份方式
数据库以archive模式运行，RMAN多级增量备份。策略如下：
设置控制文件自动备份。
每三个月做一个数据库的全备份（包括所有得数据库和只读表空间）,并备份归档日志。
每一个月做一次零级备份（不包含只读表空间），并备份归档日志。
每周做一次一级备份，并备份归档日志。
每天做一次二级备份，并备份归档日志。
三、恢复案例
所有恢复的前提：已经做过数据库全备份（包括归档日志），控制文件和spfile自动备份。
1.损坏一个数据文件
（1）故障模拟
删除数据文件：rm /u02/oradata/dbnms/users01.dbf
关闭数据库：shutdown immediate;
ORA-01116:errorinopeningdatabasefile4 ORA-01110:datafile4:'/u02/oradata/dbnms/users01.dbf' ORA-27041:unabletoopenfile LinuxError:2:Nosuchfileordirectory Additionalinformation:3
强行关闭：sutdown abort;
启动数据库：startup;
ORA-01157:cannotidentify/lockdatafile4-seeDBWRtracefile ORA-01110:datafile4:'/u02/oradata/dbnms/users01.dbf'
（2）恢复步骤
rmantargetsys@dbnmscatalogrmanuser@cata run{ allocatechannelc1typedisk; restoredatafile4; recoverdatafile4; sql'alterdatabasedatafile4online'; sql'alterdatabaseopen'; releasechannelc1; } sqlplussysassysdba selectinstance_name,statusfromv$instance; INSTANCE_NAMESTATUS ---------------------------- dbnmsOPEN
恢复成功。
2.损坏全部数据文件
（1）故障模拟
删除数据文件：rm /u02/oradata/dbnms/*.dbf
强行关闭：sutdown abort;
启动数据库：startup;
ORA-01157:cannotidentify/lockdatafile1-seeDBWRtracefile ORA-01110:datafile1:'/u02/oradata/dbnms/system01.dbf'
（2）恢复步骤
rmantargetsys@dbnmscatalogrmanuser@cata run{ allocatechannelc1typedisk; restoredatabase; recoverdatabase; sql'alterdatabaseopen'; releasechannelc1; } sqlplussysassysdba selectinstance_name,statusfromv$instance; INSTANCE_NAMESTATUS ---------------------------- dbnmsOPEN
恢复临时文件：
alterdatabasetempfile'/u02/oradata/dbnms/temp01.dbf'drop; altertablespacetempaddtempfile'/u02/oradata/dbnms/temp01.dbf'size50Mautoextendonnext5Mmaxsizeunlimited;
恢复成功。
3.损坏非当前联机日志成员
（1）故障模拟
删除日志文件：rm /u02/oradata/dbnms/redo01.log
关闭数据库：shutdown immediate;
启动数据库：startup;
select*fromv$logfile; GROUP#STATUSTYPEMEMBERIS_ --------------------------------------------------------- 3ONLINE/u02/oradata/dbnms/redo03.logNO 2STALEONLINE/u02/oradata/dbnms/redo02.logNO 1INVALIDONLINE/u02/oradata/dbnms/redo01.logNO 1STALEONLINE/u02/oradata/dbnms/redo11.logNO 1STALEONLINE/u02/oradata/dbnms/redo21.logNO 2STALEONLINE/u02/oradata/dbnms/redo12.logNO 3ONLINE/u02/oradata/dbnms/redo13.logNO 2STALEONLINE/u02/oradata/dbnms/redo22.logNO 3ONLINE/u02/oradata/dbnms/redo23.logNO 4ONLINE/u02/oradata/dbnms/redo31.logNO 4ONLINE/u02/oradata/dbnms/redo32.logNO GROUP#STATUSTYPEMEMBERIS_ --------------------------------------------------------- 4ONLINE/u02/oradata/dbnms/redo33.logNO
（2）恢复步骤
alterdatabasedroplogfilemember'/u02/oradata/dbnms/redo01.log'; alterdatabaseaddlogfilemember'/u02/oradata/dbnms/redo01.log'togroup1;
恢复成功.
4.损坏非当前联机日志组
（1）故障模拟
删除日志文件组1的所有文件：
rm/u02/oradata/dbnms/redo01.log rm/u02/oradata/dbnms/redo11.log rm/u02/oradata/dbnms/redo21.log
关闭数据库：shutdown immediate;
启动数据库：startup;
Databasemounted. ORA-00313:openfailedformembersofloggroup1ofthread1 ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo11.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo21.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo01.log'
（2）恢复步骤
alterdatabaseclearlogfilegroup1;
如果该日志组还没有归档，则用：
alterdatabaseclearunarchivedlogfilegroup1;
打开数据库：
alterdatabaseopen;
恢复成功。
#p#
5.损坏全部联机日志
（1）故障模拟
删除日志文件：rm /u02/oradata/dbnms/*.log
关闭数据库：shutdown immediate;
启动数据库：startup;
Databasemounted. ORA-00313:openfailedformembersofloggroup1ofthread1 ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo11.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo21.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo01.log'
（2）恢复步骤
shutdownimmediate; createpfilefromspfile; cd$ORACLE_HOME/dbs viinitdbnms.ora
加一个参数：_allow_resetlogs_corruption=true
createspfilefrompfile; startup; Databasemounted. ORA-00313:openfailedformembersofloggroup1ofthread1 ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo11.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo21.log' ORA-00312:onlinelog1thread1:'/u02/oradata/dbnms/redo01.log' recoverdatabaseuntilcancel; alterdatabaseopenresetlogs;
同步catalog：
rmantargetsys@dbnmscatalogrmanuser@cata resetdatabase; newincarnationofdatabaseregisteredinrecoverycatalog startingfullresyncofrecoverycatalog fullresynccomplete
做一个full备份：
/home/oracle/dbbat/backup_full.sh
恢复成功
6.损坏一个控制文件
（1）故障模拟
删除控制文件：rm /u02/oradata/dbnms/control01.ctl
关闭数据库：shutdown immediate;
ORA-00210:cannotopenthespecifiedcontrolfile ORA-00202:controlfile:'/u02/oradata/dbnms/control01.ctl' ORA-27041:unabletoopenfile LinuxError:2:Nosuchfileordirectory Additionalinformation:3
（2）恢复步骤
拷贝一个好的控制文件：
cpcontrol02.ctlcontrol01.ctl shutdownimmediate; startup; Databasemounted. ORA-01122:databasefile1failedverificationcheck ORA-01110:datafile1:'/u02/oradata/dbnms/system01.dbf' ORA-01207:fileismorerecentthancontrolfile-oldcontrolfile rmantargetsys@dbnmscatalogrmanuser@cata run{ allocatechannelc1typedisk; restoredatabase; recoverdatabase; sql'alterdatabaseopen'; releasechannelc1; } RMAN-00571:=========================================================== RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS=============== RMAN-00571:=========================================================== RMAN-03009:failureofsqlcommandondefaultchannelat09/08/200517:29:04 RMAN-11003:failureduringparse/executionofSQLstatement:alterdatabaseopen ORA-00322:log3ofthread1isnotcurrentcopy ORA-00312:onlinelog3thread1:'/u02/oradata/dbnms/redo03.log' ORA-00312:onlinelog3thread1:'/u02/oradata/dbnms/redo13.log' ORA-00312:onlinelog3thread1:'/u02/oradata/dbnms/redo23.log' alterdatabaseclearunarchivedlogfilegroup3; alterdatabaseopen;
恢复成功。
7.损坏全部控制文件
（1）故障模拟
删除控制文件：rm /u02/oradata/dbnms/control01.ctl
关闭数据库：shutdown immediate;
ORA-00210:cannotopenthespecifiedcontrolfile ORA-00202:controlfile:'/u02/oradata/dbnms/control01.ctl' ORA-27041:unabletoopenfile LinuxError:2:Nosuchfileordirectory Additionalinformation:3
（2）恢复步骤
shutdownabort; startupnomount; rmantargetsyscatalogrmanuser@cata run{ allocatechannelc1typedisk; restorecontrolfile; restoredatabase; sql'alterdatabasemount'; recoverdatabase; sql'alterdatabaseopenresetlogs'; releasechannelc1; } RMAN-00571:=========================================================== RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS=============== RMAN-00571:=========================================================== RMAN-03002:failureofrecovercommandat09/08/200517:43:31 RMAN-06054:mediarecoveryrequestingunknownlog:thread1seq9lowscn670233 alterdatabaseopenresetlogs;
做一个full备份：
/home/oracle/dbbat/backup_full.sh
恢复成功
8.损坏临时数据文件
（1）故障模拟
删除临时数据文件：rm /u02/oradata/dbnms/temp01.dbf
关闭数据库：shutdown immediate;
启动数据库：startup;
（2）恢复步骤
alterdatabasetempfile'/u02/oradata/dbnms/temp01.dbf'drop; ERRORatline1: ORA-01516:nonexistentlogfile,datafile,ortempfile "/u02/oradata/dbnms/temp01.dbf" altertablespacetempaddtempfile'/u02/oradata/dbnms/temp01.dbf'size50Mautoextendonnext5Mmaxsizeunlimited;
恢复成功
9.损坏spfile参数文件
（1）故障模拟
删除spfile文件：rm $ORACLE_HOME/dbs/spfiledbnms.ora
关闭数据库：shutdown immediate;
启动数据库：startup;
（2）恢复步骤
startupnomount; rmantargetsyscatalogrmanuser@cata restorespfile; shutdownimmediate; startup;
恢复成功
10.损坏全部文件（包括全部数据文件、控制文件、临时数据文件、联机日志文件）
（1）故障模拟
删除全部文件：rm /u02/oradata/dbnms/*
关闭数据库：shutdown immediate;
ORA-03113:end-of-fileoncommunicationchannel
（2）恢复步骤
sqlplussysassysdba startupnomount; rmantargetsyscatalogrmanuser@cata
还原控制文件：restore controlfile;
还原数据库：restore database;
mount 数据库：alter database mount;
恢复数据库：recover database;
RMAN-00571:=========================================================== RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS=============== RMAN-00571:=========================================================== RMAN-03002:failureofrecovercommandat09/09/200510:33:13 RMAN-06054:mediarecoveryrequestingunknownlog:thread1seq19lowscn718284
用resetlogs方式打开数据库：alter database open resetlogs;
databaseopened newincarnationofdatabaseregisteredinrecoverycatalog startingfullresyncofrecoverycatalog fullresynccomplete
重建临时文件：
sqlplussysassysdba alterdatabasetempfile'/u02/oradata/dbnms/temp01.dbf'drop; ERRORatline1: ORA-01516:nonexistentlogfile,datafile,ortempfile "/u02/oradata/dbnms/temp01.dbf" altertablespacetempaddtempfile'/u02/oradata/dbnms/temp01.dbf'size50Mautoextendonnext5Mmaxsizeunlimited;
执行一次全库备份：/home/oracle/dbbat/backup_full.sh，恢复成功。
关于Oracle数据库损坏文件的修复方法就就介绍到这里了，希望本次的介绍能够带给您一些收获吧！
【编辑推荐】
- 浅析Oracle 10g isqlplus使用DBA账户的方法Oracle数据库中如何检查索引碎片并重建索引数据库优化技术之Oracle数据库动态绑定变量PL/SQL Developer导入导出数据库方法及说明关于在极高负荷下Oracle redolog搭配的一些建议