oracle 故障案例,oracle 案例-控制文件丢失故障处理过程

今天下午在酒店休息时，下午5点左右接到用户电话，说数据库出问题了，导致应用不能访问

环境 aix 5.3+oracle 9.2.0.8+adtx存储 ，就赶紧用电脑远程连接上去

一.出现以下现象：

1.sqlplus "/as sysdba"  一直hang在那里

2.more alert_stjj.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/stjj/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/stjj/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/initstjj.ora文件,去掉丢失的控制文件，用好的控制文件启动。

保存如下:

[oracle@p550:/oracle/app/oracle/product/9.2.0/dbs]$more initstjj.ora

*.background_dump_dest='/oracle/app/oracle/admin/stjj/bdump'

*.compatible='9.2.0.0.0'

*.control_files='/ora9idata01/oradata/stjj/control01.ctl'

*.core_dump_dest='/oracle/app/oracle/admin/stjj/cdump'

*.db_block_size=8192

*.db_cache_size=2621440000

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_files=800

*.db_name='stjj'

*.fast_start_mttr_target=300

*.hash_join_enabled=TRUE

*.instance_name='stjj'

*.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/stjj/udump'

然后用pfile启动

startup pfile=/oracle/app/oracle/product/9.2.0/dbs/initstjj.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/stjj/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/stjj/bdump/stjj_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/stjj/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/stjj/bdump/stjj_lgwr_282680.trc:

ORA-00321: log 2 of thread 1, cannot update log file header

ORA-00312: online log 2 thread 1: '/ora9idata02/oradata/stjj/redo22.log'

Sat Jun 20 19:16:39 2009

Errors in file /oracle/app/oracle/admin/stjj/bdump/stjj_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的镜象也是非常有必要的。备份也最好放几份在不同的存储上。