Oracle数据库中各种类型的文件损坏与修复过程详解
Oracle数据库的文件有哪些种类呢？这些文件在什么情况下容易被损坏呢？损坏以后又该怎么样去修复呢？本文就介绍了这些内容，包括了10中Oracle文件的损坏与修复的过程，这10种文件几乎涵盖了Oracle数据库中所有类型的文件，接下来就让我们一起来了解一下这10种文件的损坏与修复的过程吧。
创新互联公司从2013年开始，先为阳东等服务建站，阳东等地企业，进行企业商务咨询服务。为阳东企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
一、数据库服务器基本情况
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: error in opening database file 4
- ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
强行关闭：sutdown abort;
启动数据库：startup;
- ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
- ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'
（2）恢复步骤
- rman target sys@dbnms catalog rmanuser@cata
- run{
- allocate channel c1 type disk;
- restore datafile 4;
- recover datafile 4;
- sql 'alter database datafile 4 online';
- sql 'alter database open';
- release channel c1;
- }
- sqlplus sys as sysdba
- select instance_name,status from v$instance;
- INSTANCE_NAME STATUS
- ---------------- ------------
- dbnms OPEN
恢复成功。
2.损坏全部数据文件
（1）故障模拟
删除数据文件：rm /u02/oradata/dbnms/*.dbf
强行关闭：sutdown abort;
启动数据库：startup;
- ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
- ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'
（2）恢复步骤
- rman target sys@dbnms catalog rmanuser@cata
- run{
- allocate channel c1 type disk;
- restore database;
- recover database;
- sql 'alter database open';
- release channel c1;
- }
- sqlplus sys as sysdba
- select instance_name,status from v$instance;
- INSTANCE_NAME STATUS
- ---------------- ------------
- dbnms OPEN
恢复临时文件：
- alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
- alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;
恢复成功。
3.损坏非当前联机日志成员
（1）故障模拟
删除日志文件：rm /u02/oradata/dbnms/redo01.log
关闭数据库：shutdown immediate;
启动数据库：startup;
- select * from v$logfile;
- GROUP# STATUS TYPE MEMBER IS_
- ---------- ------- ------- ------------------------------ ---
- 3 ONLINE /u02/oradata/dbnms/redo03.log NO
- 2 STALE ONLINE /u02/oradata/dbnms/redo02.log NO
- 1 INVALID ONLINE /u02/oradata/dbnms/redo01.log NO
- 1 STALE ONLINE /u02/oradata/dbnms/redo11.log NO
- 1 STALE ONLINE /u02/oradata/dbnms/redo21.log NO
- 2 STALE ONLINE /u02/oradata/dbnms/redo12.log NO
- 3 ONLINE /u02/oradata/dbnms/redo13.log NO
- 2 STALE ONLINE /u02/oradata/dbnms/redo22.log NO
- 3 ONLINE /u02/oradata/dbnms/redo23.log NO
- 4 ONLINE /u02/oradata/dbnms/redo31.log NO
- 4 ONLINE /u02/oradata/dbnms/redo32.log NO
- GROUP# STATUS TYPE MEMBER IS_
- ---------- ------- ------- ------------------------------ ---
- 4 ONLINE /u02/oradata/dbnms/redo33.log NO
（2）恢复步骤
- alter database drop logfile member '/u02/oradata/dbnms/redo01.log';
- alter database add logfile member '/u02/oradata/dbnms/redo01.log' to group 1;
恢复成功.
4.损坏非当前联机日志组
（1）故障模拟
删除日志文件组1的所有文件：
- rm /u02/oradata/dbnms/redo01.log
- rm /u02/oradata/dbnms/redo11.log
- rm /u02/oradata/dbnms/redo21.log
关闭数据库：shutdown immediate;
启动数据库：startup;
- Database mounted.
- ORA-00313: open failed for members of log group 1 of thread 1
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'
（2）恢复步骤
- alter database clear logfile group 1;
如果该日志组还没有归档，则用：
- alter database clear unarchived logfile group 1;
打开数据库：
- alter database open;
恢复成功。
#p#
5.损坏全部联机日志
（1）故障模拟
删除日志文件：rm /u02/oradata/dbnms/*.log
关闭数据库：shutdown immediate;
启动数据库：startup;
- Database mounted.
- ORA-00313: open failed for members of log group 1 of thread 1
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'
（2）恢复步骤
- shutdown immediate;
- create pfile from spfile;
- cd $ORACLE_HOME/dbs
- vi initdbnms.ora
加一个参数：_allow_resetlogs_corruption=true
- create spfile from pfile;
- startup;
- Database mounted.
- ORA-00313: open failed for members of log group 1 of thread 1
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
- ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'
- recover database until cancel;
- alter database open resetlogs;
同步catalog：
- rman target sys@dbnms catalog rmanuser@cata
- reset database;
- new incarnation of database registered in recovery catalog
- starting full resync of recovery catalog
- full resync complete
做一个full备份：
- /home/oracle/dbbat/backup_full.sh
恢复成功
6.损坏一个控制文件
（1）故障模拟
删除控制文件：rm /u02/oradata/dbnms/control01.ctl
关闭数据库：shutdown immediate;
- ORA-00210: cannot open the specified controlfile
- ORA-00202: controlfile: '/u02/oradata/dbnms/control01.ctl'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
（2）恢复步骤
拷贝一个好的控制文件：
- cp control02.ctl control01.ctl
- shutdown immediate;
- startup;
- Database mounted.
- ORA-01122: database file 1 failed verification check
- ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'
- ORA-01207: file is more recent than controlfile - old controlfile
- rman target sys@dbnms catalog rmanuser@cata
- run{
- allocate channel c1 type disk;
- restore database;
- recover database;
- sql 'alter database open';
- release channel c1;
- }
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03009: failure of sql command on default channel at 09/08/2005 17:29:04
- RMAN-11003: failure during parse/execution of SQL statement: alter database open
- ORA-00322: log 3 of thread 1 is not current copy
- ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo03.log'
- ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo13.log'
- ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo23.log'
- alter database clear unarchived logfile group 3;
- alter database open;
恢复成功。
7.损坏全部控制文件
（1）故障模拟
删除控制文件：rm /u02/oradata/dbnms/control01.ctl
关闭数据库：shutdown immediate;
- ORA-00210: cannot open the specified controlfile
- ORA-00202: controlfile: '/u02/oradata/dbnms/control01.ctl'
- ORA-27041: unable to open file
- Linux Error: 2: No such file or directory
- Additional information: 3
（2）恢复步骤
- shutdown abort;
- startup nomount;
- rman target sys catalog rmanuser@cata
- run{
- allocate channel c1 type disk;
- restore controlfile;
- restore database;
- sql 'alter database mount';
- recover database;
- sql 'alter database open resetlogs';
- release channel c1;
- }
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: failure of recover command at 09/08/2005 17:43:31
- RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 670233
- alter database open resetlogs;
做一个full备份：
- /home/oracle/dbbat/backup_full.sh
恢复成功
8.损坏临时数据文件
（1）故障模拟
删除临时数据文件：rm /u02/oradata/dbnms/temp01.dbf
关闭数据库：shutdown immediate;
启动数据库：startup;
（2）恢复步骤
- alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
- ERROR at line 1:
- ORA-01516: nonexistent log file, datafile, or tempfile
- "/u02/oradata/dbnms/temp01.dbf"
- alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;
恢复成功
9.损坏spfile参数文件
（1）故障模拟
删除spfile文件：rm $ORACLE_HOME/dbs/spfiledbnms.ora
关闭数据库：shutdown immediate;
启动数据库：startup;
（2）恢复步骤
- startup nomount;
- rman target sys catalog rmanuser@cata
- restore spfile;
- shutdown immediate;
- startup;
恢复成功
10.损坏全部文件（包括全部数据文件、控制文件、临时数据文件、联机日志文件）
（1）故障模拟
删除全部文件：rm /u02/oradata/dbnms/*
关闭数据库：shutdown immediate;
- ORA-03113: end-of-file on communication channel
（2）恢复步骤
- sqlplus sys as sysdba
- startup nomount;
- rman target sys catalog rmanuser@cata
还原控制文件：restore controlfile;
还原数据库：restore database;
mount 数据库：alter database mount;
恢复数据库：recover database;
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: failure of recover command at 09/09/2005 10:33:13
- RMAN-06054: media recovery requesting unknown log: thread 1 seq 19 lowscn 718284
用resetlogs方式打开数据库：alter database open resetlogs;
- database opened
- new incarnation of database registered in recovery catalog
- starting full resync of recovery catalog
- full resync complete
重建临时文件：
- sqlplus sys as sysdba
- alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
- ERROR at line 1:
- ORA-01516: nonexistent log file, datafile, or tempfile
- "/u02/oradata/dbnms/temp01.dbf"
- alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;
执行一次全库备份：/home/oracle/dbbat/backup_full.sh，恢复成功。
关于Oracle数据库损坏文件的修复方法就就介绍到这里了，希望本次的介绍能够带给您一些收获吧！
【编辑推荐】
- 浅析Oracle 10g isqlplus使用DBA账户的方法
- Oracle数据库中如何检查索引碎片并重建索引
- 数据库优化技术之Oracle数据库动态绑定变量
- PL/SQL Developer导入导出数据库方法及说明
- 关于在极高负荷下Oracle redolog搭配的一些建议
分享题目：Oracle数据库中各种类型的文件损坏与修复过程详解
本文链接：http://www.36103.cn/qtweb/news4/5504.html
网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等
声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联