数据库出现异常，排查原因与解决问题的整个过程

[复制链接]

gaosmile 发布时间：2020-5-29 11:49

技术帖
文章封面:	-
文章简介:	-

导读：某客户单位数据库出现异常，大致现象是：数据库状态是open的，但是其中一个数据文件无法访问，本文分享排查原因与解决问题的整个过程。

通过ls 查看文件都报错，如下所示：

( v" b1 a2 c( B9 z( \ }
[oracle@oracledata01 oracle]$ ls8 X% ^- n1 C) ]( v) \- b6 O+ y0 Q
ls: 无法访问zf4.dbf: 输入/输出错误* a0 v T2 z" c) a( c/ P

复制代码

同时dd命令也无法读取该文件：

! ?8 a2 K& D8 ?# _2 T6 I/ I
[oracle@oracledata01 fd]$ dd if=zf4.dbf bs=8192 count=1 skip=1|od -x|header -1
; W7 ^1 U$ T2 M; H! J2 l- k
bash: header: command not found
7 T+ N0 H U P% s2 B$ A- k
dd: 正在打开"zf4.dbf": 没有那个文件或目录
0 S0 m" m; j, {! ]) b

复制代码

此时在从数据库层面看到的信息类似如下：

. m2 ^' p5 C2 Y. D+ R2 L$ U1 T
Tue Apr 28 16:49:51 20202 M4 u ^2 l. L& C8 X4 m' _/ s5 P
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_9146.trc:5 N5 E5 x# l0 i: d
ORA-01157: 无法标识/锁定数据文件 11 - 请参阅 DBWR 跟踪文件) s* b: t* R0 l
ORA-01110: 数据文件 11: '/oracle_data/oracle/zf4.dbf'9 E9 y* I* p: c8 U3 f
ORA-1157 signalled during: ALTER DATABASE OPEN...
$ T" r {2 B& O) _& f/ o
Tue Apr 28 16:49:51 2020
+ p0 o! g- L$ g7 A ?4 c4 ^4 R& m7 q
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_m000_9152.trc:
* e4 C, [6 b, Q9 S* p/ }
ORA-27037: 无法获得文件状态2 i, i* D. v0 S9 U- h- \, T {, r
Linux-x86_64 Error: 5: Input/output error1 O- X4 c2 ?* H
Additional information: 3: r) H) e5 _! o
ORA-01122: 数据库文件 11 验证失败
5 a: D& \* z) ^% W @3 S4 N
ORA-01110: 数据文件 11: '/oracle_data/oracle/zf4.dbf'
: r- I# X' Q8 V+ j8 _1 E1 a
ORA-01565: 标识文件 '/oracle_data/oracle/zf4.dbf' 时出错* B( U6 i4 F) ?, h/ }5 i! A7 i
ORA-27037: 无法获得文件状态0 ?# A3 Q3 c1 i$ f
Linux-x86_64 Error: 5: Input/output error
% ^; o2 y& n, @ O* V( A
Additional information: 3( m/ d0 T4 M9 m6 Q- {9 l, W
Tue Apr 28 16:50:19 2020 C s! u9 ^/ E8 S' s. V# `
USER (ospid: 9309): terminating the instance* J+ ^' e0 v P' Q! a

复制代码

从上述信息来看，数据库第11号文件无法读取。

在我们进行恢复之前，客户进行了多次尝试，还遭遇了一些数据库坏块，也遇到了不少ora-00600 错误：

& w/ R% j2 c X- N* I1 ~1 `6 ]
Wed Apr 29 03:10:12 2020
) A2 [' a4 y {# p
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_j009_65816.trc:2 `, D3 Y" z @; n2 R6 Y; H, x
ORA-12012: 自动执行作业 "ZX_XT"."JS_XT_XZGLXX_XZXK_FR" 出错: _& |3 M, M4 }
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
& ~' F& o5 w7 s! v8 Q5 z/ r& a
ORA-06512: 在 "ZX_XT.PKG_双公示接收_邢_2019", line 1071& |$ r1 ~& a$ a7 d
ORA-00942: 表或视图不存在; n/ m! j' W; x) T, k# u, B
ORA-02063: 紧接着 line (起自 BM_SJJH)8 \! X* z" G- _/ v9 ^: N9 K
alter database datafile 11 resize 20g
% d% T J4 [, v- c _. { E: |
Completed: alter database datafile 11 resize 20g
0 Z, I+ [6 M) l6 C; O0 x8 _
Wed Apr 29 03:10:15 2020. O0 b& l7 U9 c1 m8 A; U( f
Corrupt Block Found
, z% R* v! ^, X# K/ f( N
CONT = 0, TSN = 1, TSNAME = SYSAUX; i$ a) a* G/ o, f; E9 D
RFN = 3, BLK = 3, RDBA = 125829158 z; O; i+ e' u1 n
OBJN = 1, OBJD = -1, OBJECT = _NEXT_OBJECT, SUBOBJECT =: b5 Z8 l. l& A7 D
SEGMENT OWNER = SYS, SEGMENT TYPE = Invalid Type. m' E I; J/ n' L* c9 I5 u
Wed Apr 29 03:10:15 2020
, `* U _$ I- c9 \
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_m006_66106.trc (incident=246250):- X: o! H& Y3 @. E
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [3], [3], [18018], [], [], [], [], [], [], [], []
5 `! B- K5 ]4 n6 Y
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_246250/orcl_m006_66106_i246250.trc
& y2 c* Q: A. k$ O- ~
Wed Apr 29 03:10:15 2020
9 I: e5 h5 n# _) H
......
4 d9 ^+ c0 u3 J- x+ i9 R& [7 X
Wed Apr 29 03:11:00 2020
7 [1 w5 q- j- J" C* \
Doing block recovery for file 3 block 33 A2 p1 P/ O! A0 O2 i
Resuming block recovery (PMON) for file 3 block 3' g9 h; q* J N6 f' ]$ t
Block recovery from logseq 50078, block 15827 to scn 14514058257813
. R+ L) g) J1 P* D" h
Wed Apr 29 03:11:00 2020
# x: \# Y$ h2 o. T8 e. Z$ ^
Recovery of Online Redo Log: Thread 1 Group 3 Seq 50078 Reading mem 0! |6 o: b' G0 W' j! M
Mem# 0: /oracle_data/oradata/orcl/redo03.log: ]- R; k: q2 I+ d
Block recovery completed at rba 50078.23957.16, scn 3379.1363764630
: @; C) H+ C; a. o6 d
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_m004_66271.trc (incident=245043):' U4 b m4 v6 J) m
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [3], [3], [18018], [], [], [], [], [], [], [], []' j P3 s& i* x( k) G' ^% |
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_245043/orcl_m004_66271_i245043.trc
$ Q+ y- A4 [: d& X! g
Use ADRCI or Support Workbench to package the incident.
( F9 `9 Y2 U" m/ O
See Note 411.1 at My Oracle Support for error and packaging details.& ^+ ]% ~/ U- ]5 E* ^! f5 l- K
Wed Apr 29 03:11:01 2020
& q v/ C( B/ K4 n1 a
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_m004_66271.trc:
% z$ y4 |+ K! m
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [3], [3], [18018], [], [], [], [], [], [], [], []
% G( W( j+ l6 R" b6 `! z
Wed Apr 29 03:11:02 20201 m1 T3 Z0 m" `& I5 M2 K: v" R
...... [ N/ ]! ]0 Y
Wed Apr 29 03:15:49 2020
9 E" b6 h) H! s9 O6 U4 w
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_j000_65779.trc (incident=245026):
! s) s0 A$ q, i) u
ORA-00600: internal error code, arguments: [13011], [7907], [12713924], [44], [12687314], [0], [], [], [], [], [], []
9 I4 u; b0 q, Q4 k! u* u9 b
ORA-01403: no data found/ ]- P% [ H# u; K! O6 Q
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248
6 x2 |/ u5 a& q. T( s& m
ORA-06512: at line 1
) ]8 l) |/ z" ?: x8 s
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_245026/orcl_j000_65779_i245026.trc
4 O7 e- } s! j# ~3 I" N
Wed Apr 29 03:15:52 2020- S( S! T. i1 {: @5 G! Y$ c
Dumping diagnostic data in directory=[cdmp_20200429031552], requested by (instance=1, osid=65779 (J000)), summary=[incident=245026]." z$ N% p) |3 d% ]6 } B
Use ADRCI or Support Workbench to package the incident.
4 y" `# a" V/ T: \0 r
See Note 411.1 at My Oracle Support for error and packaging details.' {' H" X3 \% B" _8 z5 i* a6 S
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_j000_65779.trc (incident=245027):
, T5 {( ?. n! N. H6 m4 _0 `) e9 x( C
ORA-00600: internal error code, arguments: [13011], [7907], [12713924], [44], [12687314], [0], [], [], [], [], [], []8 ^/ q0 o0 R/ Q% |9 v3 k l9 q
ORA-01403: no data found& W& d- H5 k, q4 @" a8 u
ORA-06512: at "SYS.DBMS_AQ_INV", line 12484 H( S: I- S; Y* f/ S, [+ k
ORA-06512: at line 1
; o }7 j# }& l
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_245027/orcl_j000_65779_i245027.trc4 E$ ?5 m1 ^( f
Use ADRCI or Support Workbench to package the incident.
4 _4 D H% S+ Y- y
......8 O% _6 m4 N; R8 o# A3 v- K
Wed Apr 29 04:05:07 2020/ L( F+ f9 z( @- A$ j9 h6 P
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_m003_76294.trc:8 |# l( o. N7 O. C
ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [3], [3], [18018], [], [], [], [], [], [], [], []
% O8 x4 s- V J" ?. m4 R/ B; q
Wed Apr 29 04:05:08 2020
" k: |2 R3 ]- K* H% H
Dumping diagnostic data in directory=[cdmp_20200429040508], requested by (instance=1, osid=76263), summary=[incident=282250].: G* z3 \5 x; n; O. _4 a
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_76263.trc (incident=282251):
1 Y! {' x% z+ V( G
ORA-00600: 内部错误代码, 参数: [qosdFindObjRead: objcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
( w' Q% T8 g& n* s. {7 c
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_282251/orcl_ora_76263_i282251.trc; Z8 x8 Z# O5 e% G% G4 a! h
Use ADRCI or Support Workbench to package the incident.
: d" @3 @/ P& F' Y/ c% d# @- f
See Note 411.1 at My Oracle Support for error and packaging details.& Q) N+ h5 i" k7 ]7 b' O
Wed Apr 29 04:05:10 2020
' H1 J0 ?* J# L8 n5 j. j8 z' d" R4 Z
Sweep [inc][282251]: completed
' N. X% F1 d6 x4 c$ H9 F( Y2 A
Sweep [inc2][282265]: completed, P5 [$ F0 p H: g7 U
Sweep [inc2][282251]: completed9 |" y' h1 {0 z; I
Wed Apr 29 04:05:10 20203 }" z* N2 E# ^5 s6 ^, |; W
Dumping diagnostic data in directory=[cdmp_20200429040510], requested by (instance=1, osid=76263), summary=[incident=282251].3 P0 ]; J0 B6 l6 ~' {' _& Y
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_76263.trc (incident=282252):# s0 c4 |1 P1 p+ [. n" p
ORA-00600: 内部错误代码, 参数: [qosdFindObjRead: objcnt mismatch], [], [], [], [], [], [], [], [], [], [], []9 N8 E- f h6 N5 {9 H# c
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_282252/orcl_ora_76263_i282252.trc
1 D( o2 B( |& W; v! W9 J
Use ADRCI or Support Workbench to package the incident.8 _ @9 e1 Z9 J6 w2 {% w1 h" n6 u+ D
See Note 411.1 at My Oracle Support for error and packaging details.. d) D8 M7 P: u" @7 b0 y' C. r5 f
Wed Apr 29 04:07:26 2020
" E& u* H' K% x0 l" \7 |
......
3 x4 ~3 d, B4 r, a3 U
Wed Apr 29 04:24:01 2020( { d6 b( d8 |4 W$ ^- N5 x+ G" a
Sweep [inc][279857]: completed+ a* g) I; a: }7 L
Sweep [inc2][279857]: completed0 y3 `/ B/ U, r! B. Q1 I
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_78503.trc (incident=279858):' M4 I% m1 v- _# a A3 a
ORA-00600: 内部错误代码, 参数: [qosdFindObjRead: objcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
f4 Y) \# F8 R. N+ H8 w
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_279858/orcl_ora_78503_i279858.trc
7 H! b, C# w% ?6 X: _8 t
Use ADRCI or Support Workbench to package the incident.
, _% M2 x( C( o
See Note 411.1 at My Oracle Support for error and packaging details.5 N3 P1 f* ^/ U \% [' u
Wed Apr 29 04:24:03 2020
$ {7 S+ r6 ^7 E' W1 X
Sweep [inc][279858]: completed
X$ ?* i% z$ b- x) U3 C+ G6 M
Sweep [inc2][279858]: completed# R4 H8 ^; P G+ h$ |: y# O4 K1 z, d
Wed Apr 29 04:24:04 20205 d+ r7 h2 Q8 T* }, f u/ L. ?
Dumping diagnostic data in directory=[cdmp_20200429042404], requested by (instance=1, osid=78503), summary=[incident=279858]./ v" m) B( v8 N, M) Q3 Y% d
Wed Apr 29 04:25:57 2020& p$ d: R c4 \( Q1 |
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_78738.trc (incident=282222):2 K' k3 [9 x& C1 e% z
ORA-00600: 内部错误代码, 参数: [qosdFindObjRead: objcnt mismatch], [], [], [], [], [], [], [], [], [], [], []9 V$ ~( N3 p6 S
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_282222/orcl_ora_78738_i282222.trc3 _5 g& a& ^9 ?5 t- E
Use ADRCI or Support Workbench to package the incident.0 V: p6 t8 t1 {
See Note 411.1 at My Oracle Support for error and packaging details.: b6 I5 M% S/ q. c
Wed Apr 29 04:25:58 2020! G6 m( I# R" @2 u9 p! u
Sweep [inc][282222]: completed& V7 x0 s' q% t" ~
Sweep [inc2][282222]: completed2 t) |- u2 z. D2 c" I
Wed Apr 29 04:25:58 2020
8 |4 R1 |% M) A
Dumping diagnostic data in directory=[cdmp_20200429042558], requested by (instance=1, osid=78738), summary=[incident=282222].8 W: w, M& R* E5 n% O: X i4 v% `9 d
Errors in file /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/trace/orcl_ora_78738.trc (incident=282223):, z3 x3 r( f, Q- \# K$ g
ORA-00600: 内部错误代码, 参数: [qosdFindObjRead: objcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
' v# N& k$ q7 a% }
Incident details in: /oracle_app/Oracle12c/diag/rdbms/orcl/orcl/incident/incdir_282223/orcl_ora_78738_i282223.trc

复制代码

上述ora-00600 错误，总的来都是常见的，几乎都可以定性为数据坏块。

对于file 3的坏块，处理相对简单，直接drop或者truncate相关对象即可，然后重建awr。

但是对于第11号数据文件，怎么办呢？这里我们的处理方法是当文件丢失处理（很久之前处理过Windows环境数据文件大小为 0 kb的问题，这几种情况都类似。）

这里我说一下简单的处理思路：

1. 通过odu 扫盘，分别以不同offset方式来扫（0和4096）。

2. 对比两次扫描结果，组合extent信息，抽取文件。

由于这里文件只有1个，大小20g左右，通过dd 拼接即可。

最终文件拼接完成后，由于这里是非归档环境，无法进行正常recover，因此还需要通过bbed来修改数据文件头的checkpoint信息。

对于文件系统来讲，目前已经不是Oracle存储的最佳选择了，建议使用ASM。当然，其他数据库比如MySQL、PostgreSQL除外。