oracle数据库控制文件丢失故障处理过程

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:50

环境 aix 5.3+oracle 9.2.0.8+adtx存储 ,

一.出现以下现象:
1.sqlplus "/as sysdba" 一直hang在那里
2.more alert_sttest.log 一直hang在那里
3.errpt|more 一直hang在那里
做任何操作都不行

二.查找故障
没办法,只有重启主机了,
重启主机后,马上启动数据库
su - oracle
sqlplus "/as sysdba"
startup
ORACLE instance started.
Total System Global Area 4449853568 bytes
Fixed Size 62848 bytes
Variable Size 65683456 bytes
Database Buffers 2621440000 bytes
Redo Buffers 172032 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

检查数据库日志,报以下错误
Sat Jun 20 18:30:20 2009
ARC0: Becoming the heartbeat ARCH
ARC0: Becoming the heartbeat ARCHARC0: Thread not mounted
Sat Jun 20 18:30:20 2009
ALTER DATABASE MOUNT
Sat Jun 20 18:30:20 2009
ORA-00202: controlfile: '/ora9idata02/oradata/sttest/control02.ctl'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

说明/ora9idata02/oradata/sttest/control02.ctl不可访问

文件系统/ora9idata02没有正常mount起来,所属vg不能正常varyon

通过检查/ora9idata02vg 有几个硬盘丢失,导致不能正常访问

其他逻辑备份和物理备份vg 都有硬盘丢失,导致备份不可用。

幸运的是存放oracle代码vg和ora9idata01 vg可以正常mount,

ora9idata01 vg存放的是数据文件,控制文件,和redo。

ora9idata02 vg存放的是控制文件的镜象和redo组的成员。

问题确定是adtx存储部分硬盘丢失,导致存放在/ora9idata02文件系统下的控制文件丢失,而引起
数据库实例down掉。

三.问题解决
sqlplus "/as sysdba"
create pfile from spfile;
编辑$ORACLE_HOME/dbs/initsttest.ora文件,去掉丢失的控制文件,用好的控制文件启动。
保存如下:
[oracle@p550:/oracle/app/oracle/product/9.2.0/dbs]$more initsttest.ora
*.background_dump_dest='/oracle/app/oracle/admin/sttest/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/ora9idata01/oradata/sttest/control01.ctl'
*.core_dump_dest='/oracle/app/oracle/admin/sttest/cdump'
*.db_block_size=8192
*.db_cache_size=2621440000
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_files=800
*.db_name=sttest
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=sttest
*.java_pool_size=10485760
*.job_queue_processes=10
*.large_pool_size=20971520
*.log_archive_dest_1='location=/oraarch/arch'
*.log_archive_start=TRUE
*.open_cursors=800
*.pga_aggregate_target=1073741824
*.processes=800
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=268435456
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/app/oracle/admin/sttest/udump'

然后用pfile启动
startup pfile=/oracle/app/oracle/product/9.2.0/dbs/initsttest.ora
ORACLE instance started.
Total System Global Area 4449853568 bytes
Fixed Size 62848 bytes
Variable Size 65683456 bytes
Database Buffers 2621440000 bytes
Redo Buffers 172032 bytes
database mounted
database open
数据库正常启动
日志切换也正常

数据库日志中还有日志组成员不能更新的错误提示
Errors in file /oracle/app/oracle/admin/sttest/bdump/stjj_arc1_168176.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ARC1: Beginning to archive log 4 thread 1 sequence 1584
Creating archive destination LOG_ARCHIVE_DEST_1: '/oraarch/arch/1_1584.dbf'
ARC1: Completed archiving log 4 thread 1 sequence 1584
Sat Jun 20 19:16:39 2009
Errors in file /oracle/app/oracle/admin/sttest/bdump/sttest_lgwr_282680.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/ora9idata02/oradata/sttest/redo22.log'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Jun 20 19:16:39 2009
Errors in file /oracle/app/oracle/admin/sttest/bdump/sttest_lgwr_282680.trc:
ORA-00321: log 2 of thread 1, cannot update log file header
ORA-00312: online log 2 thread 1: '/ora9idata02/oradata/sttest/redo22.log'
Sat Jun 20 19:16:39 2009
Errors in file /oracle/app/oracle/admin/sttest/bdump/sttest_lgwr_282680.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Sat Jun 20 19:16:39 2009

采用如下方法,删除有问题的日志组成员
alter database drop logfile member '/ora9idata02/oradata/stjj/redo22.log';
alter database drop logfile member '/ora9idata02/oradata/stjj/redo12.log';
alter database drop logfile member '/ora9idata02/oradata/stjj/redo32.log';
alter database drop logfile member '/ora9idata02/oradata/stjj/redo42.log';

就赶紧做了个数据库逻辑备份导出,通过ftp传送到pc机器上,确保一份有效的备份,
就建议用户找主机工程师去解决硬盘丢失的故障和原因。

总结:
这次数据库比较幸运的能正常打开,主要是存放数据文件,控制文件,日志文件的vg能正常起来,
但做好控制文件和redo的镜象也是非常有必要的。备份也最好放几份在不同的存储上。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】