資訊專欄INFORMATION COLUMN
從Oracle數(shù)據(jù)庫官方服務支持生命周期表，我們可以清晰看到Oracle11g已過主支持生命周期，2020年后不再支持，取而代之的是12C及以上版本，大部分客戶開始了新一輪的數(shù)據(jù)庫升級工作，常見升級目標版本為Oracle19C。本文主要剖析一個升級后的隱藏“福利（BUG）”，希望對大家有所啟發(fā)。
某客戶系統(tǒng)基于版本迭代要求，將核心數(shù)據(jù)庫Oracle11.2.0.4升級至Oracle19.5版本，割接后的一天小哥剛剛結(jié)束一天工作回到家中，突然接收到一條（有且僅有一條）數(shù)據(jù)庫無法鏈接的告警，出于職業(yè)的敏感性和核心庫的重要性，小哥第一時間開啟電腦接入環(huán)境、檢查數(shù)據(jù)庫運行情況，發(fā)現(xiàn)數(shù)據(jù)庫實例進行了一次快速重啟。
通過檢查數(shù)據(jù)庫日志，發(fā)現(xiàn)提示ORA-04031報錯,相關(guān)信息如下：
---》alert.log 部分信息
--- alert.log 部分信息
2020-06-20T02:01:38.420323+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lms3_99464_99479.trc (incident=5532401):
ORA-04031: unable to allocate 168 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","gcs dynamic shadows lms")
2020-06-20T02:01:38.443599+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_43189.trc (incident=5542961):
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT * FROM (SELECT A.work...","SQLA","tmp")
2020-06-20T02:01:38.443611+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_114982.trc (incident=5546153):
ORA-04031: ... ... ("shared pool","select 1 from dual","SQLA","tmp")
2020-06-20T02:01:38.443607+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_162835.trc (incident=5536633):
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT EXWORKSHTSN,TRIM(INST...","SQLA","tmp")
2020-06-20T02:01:38.443651+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_21132.trc (incident=5539833):
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select phone_no from (select...","SQLA","tmp")
進一步檢查DUMP文件發(fā)現(xiàn)：
--》dump文件
--- dump文件
Analysis collection on ORA-04031 in orcl1_ora_90488_i5543897.trc
=================== Begin Dump 1 ===================
Subpool 1: total 7918845952, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2338161760 29.53
"KGLH0 " 2276830888 28.75
"KGLHD " 569601504 7.19
"gcs resources " 415678808 5.25
"free memory " 400990320 5.06
Subpool 2: total 7784628224, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2369048496 30.43
"KGLH0 " 2284414544 29.35
"KGLHD " 572064856 7.35
"gcs resources " 416991392 5.36
"free memory " 407643816 5.24
Subpool 3: total 7650410496, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"gcs dynamic shadows lms " 4623872696 60.44
"free memory " 1226407536 16.03
"gcs resources " 415411008 5.43
"gcs shadows " 223176360 2.92
"kglsim object batch " 93756096 1.23
Subpool 4: total 7516192768, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2313270192 30.78
"KGLH0 " 2261483192 30.09
"KGLHD " 565488808 7.52
"gcs resources " 415938480 5.53
"free memory " 384745336 5.12
Subpool 5: total 7650410496, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2366846024 30.94
"KGLH0 " 2289281224 29.92
"KGLHD " 572632304 7.48
"gcs resources " 415937464 5.44
"free memory " 397985672 5.20
Subpool 6: total 7784628224, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2324705160 29.86
"KGLH0 " 2282608664 29.32
"KGLHD " 572510928 7.35
"gcs resources " 415675760 5.34
"free memory " 394786560 5.07
Subpool 7: total 7381975040, top 5 components:
Allocation Name Size Percent
___________________________ ____________ ________
"SQLA " 2296580576 31.11
"KGLH0 " 2261142832 30.63
"KGLHD " 565777688 7.66
"gcs resources " 416201200 5.64
"free memory " 370771864 5.02
--》Trace File
Trace File
=========
Filename = orcl1_ora_36390_i5541393.trc
Version 19.5.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /u01/app/oracle/product/19.0.3/dbhome_1
System name: Linux
Node name:
Release: 3.10.0-957.el7.x86_64
Version: #1 SMP Thu Oct 4 20:48:51 UTC 2018
Machine: x86_64
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 1154
Unix process pid: 36390, image: oracle@
*** 2020-06-20T02:01:38.742076+08:00
*** SESSION ID:(8786.52180) 2020-06-20T02:01:38.742092+08:00
*** CLIENT ID:() 2020-06-20T02:01:38.742097+08:00
*** SERVICE NAME:(orcl) 2020-06-20T02:01:38.742102+08:00
*** MODULE NAME:(db_in_workmsg01333@xxxxx(TNS V1-V3)) 2020-06-20T02:01:38.742107+08:00
ORA-04031: unable to allocate 120 bytes of shared memory ("shared pool","INSERT INTO ...","SQLA^b8cce596","strdef: qcopCreateStr")
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"gcs dynamic shadows lms " 4410 MB 60%
"free memory " 1170 MB 16%
"gcs resources " 396 MB 5%
"gcs shadows " 213 MB 3%
"kglsim object batch " 89 MB 1%
"gcs resv res hash bucket " 75 MB 1%
"db_block_hash_buckets " 74 MB 1%
"KJSC rnb slots " 72 MB 1%
"ges resource dynamic " 69 MB 1%
"ges big msg buffers " 69 MB 1%
-----------------------------------------
free memory 1170 MB
memory alloc. 6126 MB
Sub total 7296 MB
==============================================
TOP 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"gcs dynamic shadows lms " 4410 MB
"SQLA " 3048 MB
"KGLH0 " 2342 MB
"free memory " 1178 MB
"KGLHD " 553 MB
"gcs resources " 396 MB
"KGLS " 306 MB
"KGLDA " 238 MB
"gcs shadows " 213 MB
"KQR X PO " 117 MB
Comment
========
The allocation in sub pool 3 shows that same situation in file orcl1_ora_90488_i5543897.trc.
初步發(fā)現(xiàn)“gcsdynamic shadows lms”組件使用率占比較高，疑似異常。
對比新老庫參數(shù)設置
| --- 新庫 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _PX_use_large_pool boolean TRUE buffer_pool_keep string buffer_pool_recycle string java_pool_size big integer 1G large_pool_size big integer 10G memoptimize_pool_size big integer 0 olap_page_pool_size big integer 0 shared_pool_reserved_size big integer 2560M shared_pool_size big integer 50G streams_pool_size big integer 8G pga_aggregate_limit big integer 150G pga_aggregate_target big integer 75G --- 老庫 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _PX_use_large_pool boolean TRUE buffer_pool_keep string buffer_pool_recycle string global_context_pool_size string java_pool_size big integer 1G large_pool_size big integer 5G olap_page_pool_size big integer 0 shared_pool_reserved_size big integer 768M shared_pool_size big integer 15G streams_pool_size big integer 1536M pga_aggregate_target big integer 25G |
發(fā)現(xiàn)在相同業(yè)務情況下新庫參數(shù)值遠大于老庫參數(shù)，依舊提示ORA-04031，初步懷疑OracleBUG。
BUG排查：
通過查閱OracleMOS支持文檔、疑似命中BUG：Bug30223374 - Memory Leak Due to "gcs dynamic shadows lms"(Doc ID 30223374.8)，描述信息如下：
Bug30223374已被取代，隨即查閱替代BUG：
檢查當前數(shù)據(jù)庫應用補丁應用情況：
2020-06-20T02:02:38.171124+08:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 30122149
Patch Description: OCW RELEASE UPDATE 19.5.0.0.0 (30122149)
Patch Id: 30125133
Patch Description: Database Release Update : 19.5.0.0.191015 (30125133)
(includes) ... 29452936 ...
Patch Id: 30128191
Patch Description: OJVM RELEASE UPDATE: 19.5.0.0.191015 (30128191)
Patch Id: 30396974
Patch Description: REBALANCE ABORTING WITH ORA-15424
Patch Id: 26724511 Patch Description: AUTO OPTIMIZER STATS RUN MULTIPLE JOBS DURING MAINTENANCE WINDOWS
Patch Id: 28589509 Patch Description: ORA 600 [KCL_SHRINK_ANTI_BG_1] AND INSTANCE CRASHED
Patch Id: 28681153 Patch Description: INTERNAL PDCDB ORA-00600 [QOSDEXPSTATREAD EXPCNT MISMATCH]
Patch Id: 28751498
Patch Id: 29182901
Patch Id: 29312889
Patch Id: 29717901
Patch Id: 29965888
Patch Id: 30186706
Patch Id: 30157766
Patch Id: 31190412
Patch Description: MERGE ON DATABASE RU 19.5.0.0.0 OF 28572407 30614411
修復方式：
當前BUG30223374已被BUG30318638取代，可通過如下幾種方式來修復：
| 1. Apply one-off patch 30318638 on top of 19.5 or 2. Upgrade with 19.5.1.0 (Jan 2020) DB Release Update Revision(DB RUR) or 3. Upgrade with 19.6.0.0.200114 (Jan 2020) Database Release Update (DB RU) |
現(xiàn)場決定采取第1種方案進行修復，修復后無重啟發(fā)生。
隨著軟硬件的不斷升級迭代，主機、數(shù)據(jù)庫等重啟時間也在不斷縮短，作為一線運維小哥要時刻保持對生產(chǎn)的敬畏之心，不能忽略任何一次的告警提示，遇到故障時要及時匯總整理故障診斷信息，保持清晰處理思路，從而達到事半功倍的效果。
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/130173.html
19C?DG?Broker配置和測試 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20
