为提升业务系统连续性,基于现有硬件资源,使用两套一体机资源部署两套数据库,互为Oracle主、备库角色,简要架构示例如图:
某客户报表系统、经分系统经过系统架构调整后,报表系统主库运行于集群A、备库运行于集群B,经分系统主库运行于集群B、备库运行于集群A。架构调整稳定运行数月后,在某个周五的深夜,运维小哥收到了集群A主库A1实例宕机、备库B1实例宕机的告警、主机无法PING通告警,小哥第一时间开机接入环境,经过检查,发现“一体机1”主机发生重启,重启恢复后,在连续2天多次出现实例重启等情况。为保障业务稳定运行,随即决策停止一体机A-节点1数据库实例及集群,由一体机2单点提供数据库服务,保障业务运行。
通过分析故障时间段的主机系统日志、数据库日志,相关信息如下:
故障时段主机进程状态变为“D”
主机内核参数启用hung状态自动重启机制
初步确认主机重启原因是因进程转变为“D”,触发内核重启重启机制。
排查进程状态转变原因
通过分析“D”状态进程种类,用户等信息,不仅仅是Oracle用户进程状态转变为“D”。排除数据库进程本身引发的“D”僵尸进程,并重点检查IO情况。
VBS共享存储无明显异常:
经过分析“D”状态进程存在共性,均与/u01(sda、sda8)本地磁盘相关,随即排查本地磁盘的IO性能情况。
发现本地磁盘使用率持续偏高,存在性能瓶颈;存在/u01读写慢,引发进程僵死的可能性。
排查Oracle trc、trm异常输出原因
因为/u01目录仅作为Oracle软件安装目录,使用类型比较单一,我们的排查重点又再次回到了Oracle软件层面,因为该套主机上部署了2套数据库实例,数据库日志、集群日志、trc、trm等文件输出频率较单实例资源更高、IO压力更大;随即分析目录大小、文件数量,更新频率等信息,发现Oracle实例trc、trm跟踪文件持续高频率异常输出。
BUG排查
通过查阅OracleMOS支持文档、疑似命中Bug29039510 - drm messages are continuously generated with kjqghd() andkjshash() (Doc ID 29039510.8),描述信息如下:
检查当前数据库应用补丁应用情况
-- Log File --
File Name or Source
-------------------------
opatch output
Description
--------------
Relevant Information Collection
---------------------------------------
Interim patches (3) :
Patch 29774415 : applied on Wed Nov 27 00:56:04 CST 2019
Unique Patch ID: 22954229
Patch description: "OJVM RELEASE UPDATE: 12.2.0.1.190716 (29774415)"
Created on 19 Jun 2019, 22:08:05 hrs PST8PDT
Bugs fixed:
25811105, 25890046, 26023042, 26570134, 27000702, 27461740, 27952586
27642235, 27539876, 28502098, 28915870, 29254623, 29774362
Patch 29770040 : applied on Wed Nov 27 00:53:05 CST 2019
Unique Patch ID: 22917824
Patch description: "OCW JUL 2019 RELEASE UPDATE 12.2.0.1.190716 (29770040)"
Created on 19 Jun 2019, 13:36:44 hrs PST8PDT
Bugs fixed:
12816839, 13250991, 18701017, 20559126, 20674742, 21477269, 21679331
21887932, 22144696, 22322021, 22734786, 22900591, 22986384, 22999793
23087119, 23170674, 23297046, 23301767, 23340259, 23346234, 23706534
23722215, 23760401, 23762756, 23840305, 24330981, 24372941, 24396050
24403376, 24403950, 24411578, 24422155, 24423011, 24445255, 24451580
24462646, 24464953, 24493684, 24509984, 24546078, 24578464, 24583356
24584419, 24600431, 24608727, 24651269, 24657753, 24663993, 24664849
24665035, 24670214, 24673586, 24686368, 24696235, 24732650, 24736511
24792446, 24796183, 24797972, 24801915, 24808260, 24820676, 24824518
24831158, 24831217, 24902553, 24908629, 24932026, 24953946, 25025157
25026470, 25037011, 25037836, 25049033, 25066549, 25089164, 25100966
25131365, 25131739, 25137752, 25164540, 25197395, 25197670, 25205861
25216003, 25233797, 25245759, 25251382, 25269635, 25291358, 25301463
25304126, 25307145, 25325522, 25326099, 25327670, 25329178, 25354530
25360754, 25361169, 25369170, 25371632, 25385515, 25397498, 25397620
25408895, 25409838, 25412450, 25448462, 25448476, 25485737, 25492219
25493588, 25496699, 25499276, 25505841, 25509979, 25515359, 25518447
25537905, 25540583, 25541343, 25556203, 25564644, 25569634, 25575414
25576767, 25591658, 25604442, 25633406, 25644425, 25646592, 25655495
25662028, 25667162, 25685286, 25697364, 25698636, 25714525, 25715140
25715179, 25716769, 25717212, 25728787, 25729214, 25730614, 25742471
25742704, 25774482, 25776294, 25784424, 25785073, 25789051, 25790699
25795447, 25801922, 25802473, 25803563, 25810476, 25825732, 25825755
25832312, 25832375, 25851874, 25856024, 25865563, 25885834, 25890662
25893570, 25915863, 25927065, 25943805, 25947195, 25948946, 25951778
25956724, 25987595, 25996923, 25998093, 26002739, 26035888, 26052877
26088770, 26111524, 26115835, 26116932, 26121788, 26144044, 26185271
26185431, 26187578, 26198627, 26230199, 26238801, 26248428, 26257547
26264417, 26271545, 26282865, 26301456, 26307166, 26308703, 26313403
26316238, 26326556, 26333037, 26357098, 26372145, 26379711, 26387283
26391418, 26418606, 26419112, 26431982, 26433972, 26433979, 26439462
26439893, 26473232, 26474627, 26477705, 26493466, 26494006, 26494536
26527054, 26529142, 26546632, 26547521, 26552807, 26554987, 26567325
26581118, 26586174, 26587652, 26588288, 26591240, 26593581, 26624184
26633228, 26654615, 26657593, 26664476, 26709107, 26710631, 26716037
26747738, 26754328, 26755613, 26791882, 26817081, 26817275, 26817995
26818064, 26818368, 26822082, 26827699, 26860285, 26875825, 26877592
26884984, 26943660, 26950548, 26958202, 26966588, 26973611, 26979409
26996813, 27006180, 27012234, 27018734, 27023569, 27040560, 27047274
27068526, 27070729, 27080761, 27092991, 27094352, 27096108, 27106915
27114112, 27119820, 27130514, 27133637, 27148384, 27153755, 27154002
27174938, 27177852, 27182006, 27186837, 27190262, 27212837, 27213140
27222423, 27222938, 27238077, 27249544, 27252023, 27267992, 27274143
27297582, 27300007, 27309182, 27309269, 27358232, 27370933, 27377219
27378959, 27379846, 27399499, 27399762, 27403244, 27404599, 27426277
27440094, 27447782, 27465480, 27475272, 27496806, 27507494, 27516078
27525224, 27526362, 27532009, 27534289, 27536738, 27541863, 27542831
27554103, 27554304, 27555886, 27560928, 27566153, 27577122, 27581484
27593587, 27595801, 27600706, 27609819, 27624923, 27628221, 27664844
27668379, 27682288, 27686197, 27691717, 27695334, 27702244, 27703242
27704335, 27708711, 27712736, 27735534, 27739957, 27747407, 27753602
27757427, 27757979, 27766679, 27768034, 27769638, 27786669, 27803672
27811439, 27850736, 27862636, 27870441, 27877830, 27899060, 27922179
27953102, 27970426, 27971015, 27972271, 27984314, 27990479, 28030440
28032758, 28050201, 28071462, 28084656, 28086550, 28097951, 28105136
28127661, 28143609, 28146584, 28161214, 28161727, 28220651, 28266751
28285234, 28310558, 28375138, 28383213, 28393555, 28396217, 28396311
28447958, 28462215, 28477613, 28494942, 28504730, 28515917, 28526389
28535778, 28540666, 28553832, 28558622, 28578962, 28593407, 28642469
28647065, 28655841, 28666719, 28697242, 28747282, 28805158, 28865162
28871945, 28887933, 28901519, 28912188, 28915251, 28958624, 28969877
28973538, 29131772, 29139727, 29146157, 29307090, 29307109, 29311336
29379299, 29380201, 29400176, 29505730, 29632978
Patch 29757449 : applied on Wed Nov 27 00:52:17 CST 2019
Unique Patch ID: 23009673
Patch description: "Database Jul 2019 Release Update : 12.2.0.1.190716 (29757449)"
Created on 9 Jul 2019, 21:38:38 hrs PST8PDT
Bugs fixed:
8480838, 8932139, 12763598, 13554903, 14221306, 14690846, 15931756
16002385, 16438495, 16727454, 16942578, 17027695, 17533661, 17947871
18308268, 18521691, 18594510, 18774543, 18878420, 19072655, 19211433
19285025, 19327292, 19526548, 19614243, 19647894, 19649997, 19702201
19721304, 20003668, 20087519, 20118035, 20120236, 20324049, 20436508
20532077, 20588486, 20591151, 20617383, 20620169, 20736227, 20756305
20866970, 20976443, 21089435, 21095391, 21143725, 21147908, 21159907
21178363, 21186167, 21197098, 21216226, 21320338, 21433452, 21479706
21517767, 21520266, 21547051, 21638600, 21744603, 21882528, 21981529
21985256, 22007324, 22070473, 22070853, 22072543, 22087683, 22104866
22179537, 22310426, 22347493, 22364044, 22367053, 22379010, 22446455
22495673, 22503283, 22503297, 22504793, 22522515, 22530986, 22564336
22568728, 22581771, 22594071, 22599050, 22628825, 22645009, 22654475
22700845, 22726044, 22729345, 22826067, 22843979, 22845846, 22864303
22898198, 22950945, 22970869, 22981722, 23019710, 23026585, 23035249
23055900, 23061453, 23065002, 23066146, 23080557, 23104033, 23105538
23110523, 23125560, 23126545, 23127945, 23151677, 23169712, 23177923
23179662, 23184263, 23197730, 23234232, 23237091, 23249829, 23271203
23278750, 23300142, 23306590, 23310101, 23312077, 23328639, 23336559
23481673, 23491861, 23499004, 23499160, 23521523, 23527363, 23533647
23548817, 23567857, 23572982, 23581777, 23588722, 23599216, 23600861
23602213, 23645516, 23665623, 23709062, 23715460, 23715518, 23730961
23733981, 23735292, 23741944, 23746128, 23749454, 23761724, 24010030
24289874, 24294174, 24303148, 24307571, 24308349, 24326444, 24326846
24328857, 24332831, 24334708, 24336249, 24337882, 24341675, 24343905
24345420, 24346821, 24348685, 24350620, 24352981, 24355111, 24357348
24368004, 24371491, 24373528, 24373756, 24374976, 24376875, 24376878
24385983, 24401351, 24403922, 24409977, 24415926, 24416451, 24421668
24423416, 24425056, 24425998, 24435982, 24437162, 24443539, 24457597
24460392, 24461826, 24467122, 24468470, 24470606, 24471079, 24471473
24473736, 24485034, 24485161, 24485174, 24486059, 24486237, 24509056
24534401, 24554533, 24555417, 24556967, 24560906, 24563422, 24570214
24570598, 24573817, 24578718, 24578797, 24589081, 24589590, 24591506
24593740, 24595699, 24600330, 24609592, 24609996, 24611527, 24616637
24617969, 24623975, 24624166, 24642495, 24654629, 24655717, 24664211
24668398, 24669189, 24674197, 24674955, 24676172, 24677696, 24680959
24689376, 24692973, 24693290, 24697323, 24699619, 24710696, 24713381
24714096, 24717183, 24717859, 24718260, 24719799, 24735430, 24737064
24737403, 24737581, 24744383, 24744686, 24757934, 24759556, 24760407
24764085, 24766309, 24786669, 24792678, 24793511, 24796092, 24797119
24800423, 24801152, 24802934, 24811725, 24812047, 24827228, 24827654
24831514, 24835919, 24841671, 24843188, 24844549, 24844841, 24845157
24848746, 24848923, 24850622, 24907917, 24908321, 24911709, 24912588
24922704, 24923080, 24923215, 24923338, 24923790, 24924667, 24926999
24929210, 24938784, 24940060, 24942749, 24953434, 24957555, 24960044
24965426, 24966594, 24966788, 24967993, 24968162, 24976007, 24978100
25022574, 25027852, 25028996, 25029022, 25029423, 25032818, 25034396
25036474, 25042823, 25044977, 25045228, 25050160, 25051465, 25051628
25057811, 25058080, 25062592, 25063971, 25065563, 25072986, 25078611
25086233, 25087436, 25092777, 25093872, 25098160, 25099339, 25099497
25099758, 25100063, 25100579, 25103996, 25107662, 25110233, 25114561
25120284, 25120668, 25120742, 25121089, 25123585, 25124363, 25129925
25140197, 25145163, 25145215, 25150925, 25159176, 25162645, 25164293
25166187, 25171041, 25171084, 25175723, 25176408, 25178032, 25178101
25178179, 25179774, 25182817, 25184555, 25186079, 25191872, 25192044
25192528, 25192729, 25199585, 25201454, 25202355, 25203656, 25206864
25207410, 25209912, 25210268, 25210499, 25211628, 25223839, 25224242
25225795, 25226665, 25227381, 25230870, 25230945, 25237577, 25240188
25240590, 25241448, 25241625, 25244807, 25248384, 25251648, 25257085
25259611, 25262869, 25263960, 25265499, 25283790, 25287072, 25293659
25296876, 25299227, 25299807, 25300427, 25303756, 25305405, 25307368
25309116, 25313154, 25313411, 25316758, 25317989, 25320555, 25323525
25328518, 25329664, 25335249, 25335360, 25335790, 25337332, 25337640
25348956, 25353983, 25356118, 25357142, 25360661, 25362958, 25367588
25367721, 25382812, 25383204, 25384462, 25386748, 25388896, 25392535
25395696, 25397936, 25398306, 25404202, 25405100, 25405687, 25405813
25410017, 25410180, 25410802, 25410877, 25411036, 25415713, 25417050
25417056, 25417958, 25425451, 25425760, 25427662, 25429959, 25430120
25433696, 25435038, 25437699, 25440818, 25442559, 25444961, 25445168
25451531, 25455795, 25457409, 25459958, 25462714, 25463844, 25472112
25476149, 25477657, 25478885, 25479164, 25482971, 25489342, 25489367
25489607, 25492379, 25498930, 25498994, 25516250, 25524955, 25528838
25530080, 25530814, 25535668, 25536819, 25537470, 25539063, 25540738
25546580, 25546608, 25547901, 25551676, 25553616, 25554787, 25555252
25557886, 25558986, 25560487, 25561296, 25569149, 25570929, 25575348
25575369, 25575628, 25579458, 25579761, 25594901, 25597525, 25598473
25599425, 25600342, 25600421, 25602488, 25603923, 25606091, 25607726
25612095, 25614866, 25616268, 25616359, 25616417, 25616645, 25631933
25633101, 25634317, 25634348, 25635149, 25638456, 25639019, 25643818
25643889, 25643931, 25646373, 25647325, 25648731, 25653109, 25654459
25654936, 25655390, 25655966, 25659655, 25660847, 25661819, 25662088
25662101, 25662524, 25663488, 25669791, 25670786, 25671354, 25672640
25674386, 25680221, 25685152, 25686739, 25687460, 25691904, 25694206
25695903, 25699321, 25700654, 25709368, 25710420, 25715167, 25717371
25722055, 25722608, 25722720, 25723158, 25728085, 25729507, 25734963
25736747, 25739065, 25741955, 25743479, 25747569, 25749273, 25752755
25754606, 25757748, 25760195, 25762221, 25764020, 25766822, 25768681
25772669, 25774077, 25775213, 25780343, 25783447, 25784002, 25785331
25785441, 25788879, 25789041, 25789277, 25789579, 25790353, 25792911
25795865, 25797092, 25797124, 25797305, 25800464, 25802510, 25803545
25807997, 25810263, 25810704, 25811650, 25813931, 25818707, 25822410
25823754, 25825910, 25826740, 25830492, 25832935, 25834581, 25835365
25838361, 25838755, 25852885, 25856821, 25858672, 25861398, 25865785
25870579, 25871177, 25871639, 25871753, 25872127, 25872389, 25873336
25874050, 25874678, 25882264, 25883438, 25885148, 25888073, 25888984
25890056, 25890673, 25894239, 25895224, 25897615, 25904273, 25904490
25906117, 25906886, 25908728, 25911724, 25914276, 25919622, 25932524
25932728, 25933494, 25941836, 25943271, 25945130, 25947799, 25951571
25953857, 25954022, 25954054, 25957038, 25963024, 25964954, 25967544
25967985, 25970731, 25971286, 25973152, 25975723, 25977302, 25980605
25980770, 25981498, 25982666, 25986062, 25990907, 25995938, 25997810
26006257, 26007010, 26019148, 26024732, 26024784, 26025681, 26029075
26029777, 26029780, 26032573, 26034119, 26036748, 26037215, 26038086
26039623, 26040483, 26045732, 26051656, 26078437, 26078493, 26080410
26083298, 26088426, 26088836, 26090767, 26091640, 26091786, 26095327
26095405, 26096382, 26108080, 26108337, 26110259, 26110632, 26111842
26112621, 26115103, 26121990, 26124078, 26137367, 26138085, 26149904
26153977, 26168933, 26169341, 26169345, 26170659, 26170715, 26176002
26187943, 26189861, 26198757, 26198926, 26201113, 26203182, 26223039
26237338, 26237431, 26237773, 26238195, 26242031, 26243698, 26244115
26245237, 26248143, 26249718, 26256131, 26257953, 26259265, 26261327
26263328, 26263721, 26268756, 26269790, 26271001, 26275023, 26275415
26277439, 26281476, 26285062, 26285933, 26308650, 26309047, 26317991
26318627, 26323308, 26324206, 26324769, 26327624, 26330994, 26331743
26333141, 26336977, 26338953, 26351334, 26353617, 26358670, 26359091
26362155, 26362821, 26366517, 26367012, 26367460, 26371725, 26374791
26375052, 26375250, 26380097, 26385189, 26388538, 26396790, 26399626
26399691, 26406387, 26407408, 26412540, 26418088, 26420561, 26421667
26422277, 26423085, 26426526, 26426967, 26430323, 26430737, 26434436
26434999, 26435073, 26436168, 26438612, 26439748, 26440169, 26440749
26442308, 26444601, 26444887, 26446098, 26452606, 26474662, 26474703
26475419, 26476244, 26478970, 26479173, 26482376, 26486365, 26492866
26493289, 26498354, 26513067, 26513709, 26521043, 26522439, 26523432
26526726, 26526799, 26536320, 26537307, 26542135, 26542236, 26544823
26545688, 26546070, 26546664, 26546754, 26548363, 26556014, 26569225
26575788, 26580633, 26582460, 26584641, 26597140, 26599395, 26608137
26608238, 26609942, 26615291, 26615690, 26617804, 26623652, 26626879
26629381, 26633355, 26633558, 26635897, 26637273, 26637824, 26639167
26641610, 26650226, 26654363, 26658759, 26659182, 26680105, 26712331
26714486, 26714910, 26717528, 26725687, 26727397, 26729494, 26729611
26740700, 26744595, 26745002, 26751106, 26751171, 26755171, 26758193
26764561, 26765212, 26775602, 26784509, 26794786, 26797591, 26798516
26802503, 26816582, 26820076, 26822620, 26828994, 26829845, 26833932
26837702, 26840654, 26844406, 26844870, 26849779, 26875822, 26883456
26895149, 26896659, 26898563, 26907327, 26908788, 26909100, 26909504
26911000, 26939314, 26943004, 26944190, 26958896, 26963310, 26966616
26966916, 26967713, 26969321, 26970175, 26970717, 26981902, 26983259
26985002, 26986173, 26992964, 27006120, 27006664, 27009164, 27013146
27028251, 27032785, 27034890, 27037839, 27038986, 27039712, 27044297
27052607, 27058530, 27060167, 27060859, 27061736, 27073314, 27079140
27087426, 27090765, 27092508, 27093423, 27097854, 27101105, 27105900
27106179, 27110878, 27115422, 27117822, 27119621, 27122162, 27124624
27125872, 27133662, 27134734, 27135647, 27135993, 27138325, 27142373
27142529, 27151826, 27153641, 27161071, 27162390, 27162405, 27163928
27165231, 27169796, 27170305, 27181537, 27181897, 27199245, 27200959
27207110, 27208795, 27213224, 27216046, 27223075, 27229389, 27231051
27236722, 27242226, 27244337, 27248917, 27249531, 27250547, 27251690
27255377, 27256000, 27258578, 27259307, 27262945, 27266245, 27274456
27274536, 27275533, 27276231, 27283960, 27284499, 27285244, 27288638
27292213, 27293599, 27302711, 27302730, 27303287, 27304410, 27305039
27308088, 27314206, 27314390, 27320576, 27321179, 27329612, 27333106
27334316, 27338912, 27338946, 27339115, 27345231, 27346709, 27348081
27349393, 27350267, 27351628, 27359178, 27364854, 27367194, 27370965
27375542, 27381498, 27386467, 27394703, 27395416, 27396624, 27396672
27396813, 27397048, 27400416, 27400598, 27404668, 27405645, 27416997
27423251, 27424405, 27426363, 27432062, 27433870, 27434193, 27439835
27441326, 27442041, 27445727, 27457891, 27466597, 27468303, 27493674
27501373, 27501413, 27502420, 27504770, 27505229, 27508985, 27510959
27525909, 27533819, 27534509, 27540613, 27544973, 27548131, 27554074
27555481, 27558861, 27560602, 27562488, 27565906, 27567477, 27576342
27593501, 27595973, 27607563, 27611612, 27613080, 27613530, 27617978
27620808, 27629756, 27634676, 27634991, 27658186, 27666312, 27671633
27680669, 27686599, 27687880, 27688036, 27688099, 27688692, 27691920
27691939, 27693416, 27693713, 27695063, 27698953, 27700466, 27704237
27709046, 27710072, 27726780, 27740424, 27748954, 27751755, 27757567
27757888, 27758972, 27759077, 27793533, 27799032, 27801337, 27824540
27824543, 27825241, 27829295, 27833672, 27834551, 27835925, 27846298
27846499, 27847259, 27855490, 27861226, 27882176, 27898015, 27931299
27940876, 27945870, 27951817, 27959048, 27959594, 27966472, 27986817
27994325, 27995215, 27995248, 27997875, 27998003, 27999073, 27999638
28000269, 28022101, 28023081, 28023399, 28023482, 28026866, 28033429
28040776, 28045903, 28067846, 28072567, 28074713, 28090453, 28099662
28108003, 28111583, 28120036, 28120951, 28124631, 28129791, 28140658
28165439, 28171079, 28174827, 28180464, 28181021, 28184554, 28188330
28194173, 28199085, 28201419, 28218832, 28220398, 28223871, 28226179
28229360, 28236305, 28271119, 28279837, 28282606, 28290434, 28302049
28305001, 28305362, 28320399, 28354603, 28357401, 28361221, 28365111
28378446, 28390273, 28396445, 28420042, 28420457, 28423598, 28432129
28434028, 28437315, 28454242, 28468312, 28483184, 28489150, 28501075
28502343, 28507324, 28508053, 28508557, 28522441, 28528349, 28535272
28545134, 28547068, 28571483, 28572834, 28578164, 28587723, 28600233
28612674, 28617631, 28617959, 28621470, 28627255, 28636676, 28691965
28692275, 28708023, 28728272, 28742555, 28749289, 28758722, 28774416
28803345, 28819640, 28849751, 28852691, 28856060, 28891984, 28951382
28956908, 28960211, 28965787, 28986231, 28987439, 28991884, 28993590
29006527, 29024054, 29027694, 29032276, 29125374, 29158680, 29189889
29200700, 29224605, 29250230, 29301463, 29339155, 29347943, 29376346
29378913, 29379978, 29388020, 29405462, 29436454, 29437712, 29511611
29542449, 29676089, 29690625, 29813494, 29836659
修复方式:
可通过如下几种方式来修复:
alter system set events 'trace [rac_enq] disk disable'; |
现场决定采取第2种方案进行修复,实施后sda本地目录读写效率恢复正常,问题得到解决。
通过本次的故障分析,现有的数据库服务器多关注于CPU、内存、共享存储等性能,往往忽略了软件安装本地目录的性能瓶颈。