oracle 9i 裸设备数据文件损坏的恢复过程

 环境 oracle 9.2.0.4+AIX 5300-02

1.问题描述
    今天客户数据库前台应用报追加记录失败，以前一直都是正常的，我就赶紧查看数据库日志，发现在9:49分-10:02之间有很多以下报错，
Errors in file /ora/app/oracle/admin/ora9i/bdump/ora9i_ckpt_1011798.trc:
ORA-01171: datafile 76 going offline due to error advancing checkpoint
ORA-01110: data file 76: '/dev/rdata2_4_01_rw'
ORA-01115: IO error reading block from file 76 (block # 1)
ORA-27063: skgfospo: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 16: Device busy
Additional information: -1
Additional information: 8192
Mon Feb 23 10:02:20 2009
Errors in file /ora/app/oracle/admin/ora9i/bdump/ora9i_ckpt_1011798.trc:
ORA-01171: datafile 77 going offline due to error advancing checkpoint
ORA-01110: data file 77: '/dev/rdata2_4_02_rw'
ORA-01115: IO error reading block from file 77 (block # 1)
ORA-27063: skgfospo: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 16: Device busy
通过查询vg所在的lv裸设备，有问题的数据文件全是closed状态
oracle9i@p550b> lsvg -l ora04vg
ora04vg:
LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
data4_4_01_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_02_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_03_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_04_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_05_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_06_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_07_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_08_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_09_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_10_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_11_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_12_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_13_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_14_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_15_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_16_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_17_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_18_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_19_rw       jfs        128   128   2    closed/syncd  N/A
data4_4_20_rw       jfs        128   128   2    closed/syncd  N/A
data1_4_01_rw       jfs        32    32    2    closed/syncd  N/A
data1_4_02_rw       jfs        32    32    2    closed/syncd  N/A
data1_4_03_rw       jfs        32    32    2    closed/syncd  N/A
data2_4_01_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_02_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_03_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_04_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_05_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_06_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_07_rw       jfs        64    64    2    closed/syncd  N/A
data2_4_08_rw       jfs        64    64    2    closed/syncd  N/A
oracle9i@p550b>

2.问题跟踪
  我就和客户到了机房，我自己也就用前台那个应用程序做了下测试，插入一条记录提交后，报追加记录失败的错误,
  但没有任何ora-相关错误代码体现出来,用pl/sql developer工具
  连到数据库做测试
   insert into stck (编号,户号) values(100000000,'test');
   出错信息如下:
   ORA-00376: file 106 cannot be read at this time
   ORA-01110: data file 106: '/dev/rdata4_4_13_rw'
   看到这个错误提示后，就联想到上午看的那些错误日志有关了，重启数据库后根本没解决,脑海中闪出的就是这个
   裸设备出问题了
   马上查这个数据文件的状态
   select status,name from v$datafile where name='/dev/rdata2_4_01_rw';  
   发现状态是recover，想到上午很多裸设备报错,通过以下sql查询，很多数据文件都是recover状态
   select status,name from v$datafile where status='RECOVER';
        RECOVER /dev/rdata4_4_08_rw
 RECOVER /dev/rdata4_4_09_rw
 RECOVER /dev/rdata4_4_10_rw
 RECOVER /dev/rdata4_4_11_rw
 RECOVER /dev/rdata4_4_12_rw
        ..........
   然后查询数据文件所属表空间都是online状态,就只能用抱着用介质恢复recover datafile file_name 应用
   归档来看看能不能解决。

3.问题解决
  3.1 先恢复今天的归档日志,调用备份软件的参数
      p550b# su - oracle9i
oracle9i@p550b> rman target / catalog=rman/rman@rman

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: ORA9I (DBID=2362852444)
connected to recovery catalog database

RMAN> run{
2> allocate  channel t1 type 'SBT_TAPE'
3> parms 'ENV=(NB_ORA_SERV=backup_server,
4> NB_ORA_CLIENT=p550b,
5> NB_ORA_POLICY=p550b_arch)';
6> restore archivelog from logseq 26861;
7> release channel t1;
8> }

   allocated channel: t1
channel t1: sid=70 devtype=SBT_TAPE
channel t1: VERITAS NetBackup for Oracle - Release 5.0GA (2004111820)

Starting restore at 2009-02-23:16:04:58

archive log thread 1 sequence 26871 is already on disk as file /arch/1_26871.dbf
archive log thread 1 sequence 26872 is already on disk as file /arch/1_26872.dbf
archive log thread 1 sequence 26873 is already on disk as file /arch/1_26873.dbf
archive log thread 1 sequence 26874 is already on disk as file /arch/1_26874.dbf
archive log thread 1 sequence 26875 is already on disk as file /arch/1_26875.dbf
archive log thread 1 sequence 26876 is already on disk as file /arch/1_26876.dbf
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=26861
channel t1: restored backup piece 1
piece handle=oracle_arch_2810_1_679584083 tag=TAG20090223T132122 params=NULL
channel t1: restore complete
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=26862
channel t1: restoring archive log
archive log thread=1 sequence=26863
channel t1: restoring archive log
archive log thread=1 sequence=26864
channel t1: restoring archive log
archive log thread=1 sequence=26865
channel t1: restoring archive log
archive log thread=1 sequence=26866
channel t1: restoring archive log
archive log thread=1 sequence=26867
channel t1: restoring archive log
archive log thread=1 sequence=26868
channel t1: restored backup piece 1
piece handle=oracle_arch_2809_1_679584083 tag=TAG20090223T132122 params=NULL
channel t1: restore complete
channel t1: starting archive log restore to default destination
channel t1: restoring archive log
archive log thread=1 sequence=26869
channel t1: restoring archive log
archive log thread=1 sequence=26870
channel t1: restored backup piece 1
piece handle=oracle_arch_2811_1_679584143 tag=TAG20090223T132122 params=NULL
channel t1: restore complete
Finished restore at 2009-02-23:16:07:21

released channel: t1

RMAN>

 3.2 介质恢复
   p550b# su - oracle9i
oracle9i@p550b> sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 23 15:54:22 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> recover datafile 106;
ORA-00279: change 9463530758543 generated at 02/23/2009 08:37:55 needed for
thread 1
ORA-00289: suggestion : /arch/1_26862.dbf
ORA-00280: change 9463530758543 for thread 1 is in sequence #26862

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/arch/1_26862.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/arch/1_26862.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

SQL> recover datafile 106;
ORA-00279: change 9463530758543 generated at 02/23/2009 08:37:55 needed for
thread 1
ORA-00289: suggestion : /arch/1_26862.dbf
ORA-00280: change 9463530758543 for thread 1 is in sequence #26862

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 9463818933167 generated at 02/23/2009 09:38:47 needed for
thread 1
ORA-00289: suggestion : /arch/1_26863.dbf
ORA-00280: change 9463818933167 for thread 1 is in sequence #26863
ORA-00278: log file '/arch/1_26862.dbf' no longer needed for this recovery

Log applied.
Media recovery complete.
SQL> alter database datafile '/dev/rdata4_4_13_rw' online;

Database altered.

SQL>
其他数据文件也是用同样方法，也可以用
recover datafile '/dev/rdata4_4_13_rw'

 3.3问题确认解决
   
    insert into stck (编号,户号) values(100000000,'test');
    能正常插入
    这时查那那些有问题的lv状态，属于open状态
    ora04vg:
    LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
    data4_4_07_rw       jfs        128   128   2    open/syncd    N/A
    data4_4_08_rw       jfs        128   128   2    open/syncd    N/A
    data4_4_09_rw       jfs        128   128   2    open/syncd    N/A
    data4_4_13_rw       jfs        128   128   2    open/syncd    N/A
    data4_4_20_rw       jfs        128   128   2    open/syncd    N/A
    data1_4_01_rw       jfs        32    32    2    open/syncd    N/A
    data1_4_02_rw       jfs        32    32    2    open/syncd    N/A
    data1_4_03_rw       jfs        32    32    2    open/syncd    N/A
    data2_4_01_rw       jfs        64    64    2    open/syncd    N/A
    data2_4_02_rw       jfs        64    64    2    open/syncd    N/A
    oracle9i@p550b>
    客户经过测试也已经正常了。

 

  

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-557505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-557505/