Exadata X7-2一體機(jī)IO導(dǎo)致日志無(wú)法歸檔問(wèn)題處理
點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！?。?/strong>
上午十一點(diǎn)，客戶發(fā)來(lái)erp核心數(shù)據(jù)庫(kù)后臺(tái)日志告警截圖，節(jié)點(diǎn)2數(shù)據(jù)庫(kù)所有的online log無(wú)法進(jìn)行歸檔，客戶反饋時(shí)好時(shí)壞，而且出現(xiàn)這個(gè)問(wèn)題的時(shí)候應(yīng)用就不行了，這里可以肯定的是如果無(wú)法進(jìn)行歸檔，日志就無(wú)法寫入，應(yīng)用肯定會(huì)收到影響，跟客戶大概了解情況后，立馬登陸服務(wù)器進(jìn)行診斷。
1. 首先登陸服務(wù)器后查看了在線日志狀態(tài)
這里發(fā)現(xiàn)節(jié)點(diǎn)2所有的在線redo無(wú)法進(jìn)行歸檔，節(jié)點(diǎn)1部分在線redo無(wú)法歸檔。2. 查看磁盤組相關(guān)空間利用率信息
這里可以看到磁盤空間使用沒(méi)有問(wèn)題：SQL> select free_mb,total_mb from v$asm_diskgroup ;
FREE_MB TOTAL_MB
---------- ----------
59352904 134535168
28793712 33623424
3. 查看數(shù)據(jù)庫(kù)當(dāng)前等待事件
截圖查看此時(shí)等待事件，發(fā)現(xiàn)最明顯的就是log file sequential read，arch進(jìn)程從redolog日志中讀取數(shù)據(jù)塊，下圖標(biāo)記的信息正好對(duì)應(yīng)節(jié)點(diǎn)2在線日志的SEQUENCE#。也就是說(shuō)所有的未歸檔在線日志arch進(jìn)程都在嘗試進(jìn)行讀取并歸檔。4. 再次查看數(shù)據(jù)庫(kù)當(dāng)前等待事件
經(jīng)過(guò)一段時(shí)間后，再次查詢等待事件相關(guān)信息，如下截圖，log file sequential read等待事件仍然存在說(shuō)明arch進(jìn)程仍然在嘗試對(duì)在線日志進(jìn)程歸檔處理，且log file switch (archiving needed)等待事件出現(xiàn)，等待事件后面出現(xiàn)了阻塞sid。5. 查看被阻塞回話在做什么
到這里我的思路很簡(jiǎn)單，先查看下被阻塞的回話執(zhí)行的SQL是什么，如下查詢，被阻塞session顯然是一個(gè)更新操作，且log file switch (archiving needed)對(duì)應(yīng)的回話都是進(jìn)行一些更新插入操作。UPDATE XXXXXX1 SET TIME_OUT = :B2 WHERE SESSION_ID = :B1
Plan hash value: 4160479998
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 3 (100)| | | | |
| 1 | UPDATE | XXXXXX1 | | | | | | |
|* 2 | INDEX UNIQUE SCAN| XXXXXX1_U1 | 1 | 2 (0)| 00:00:01 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------
6. 這里我進(jìn)一步查詢了2731這個(gè)回話具體在干什么以及是什么進(jìn)程
這里查看到的信息是Oracle自己的進(jìn)程通過(guò)OSUSER列。這里我通過(guò)主機(jī)層面直接查詢了spid進(jìn)程，發(fā)現(xiàn)對(duì)應(yīng)的2731回話是lgwr進(jìn)程。[oracle@dm01dbadm02 ~]$ ps -ef |grep 288576
oracle 126003 67533 0 11:28 pts/2 00:00:00 grep 288576
oracle 288576 1 1 2021 ? 6-05:36:52 ora_lgwr_PROD2
7. 查看數(shù)據(jù)庫(kù)歸檔路徑
進(jìn)一步查看歸檔路徑信息，主要是查看判斷是否有adg且是最大保護(hù)模式，這個(gè)數(shù)據(jù)庫(kù)是為了遷移X8一體機(jī)做了ADG但是已經(jīng)停用，且log_archive_dest_state_2已經(jīng)設(shè)置為defer，因此可以排除導(dǎo)致原因。7. 查看數(shù)據(jù)庫(kù)等待事件關(guān)系
到這里我又進(jìn)行查看了下鏈?zhǔn)降却?/span>從下圖基本上可以了解到的信息是arch進(jìn)程一直在嘗試讀取在線日志，dbwr進(jìn)程也在等待將臟塊寫入磁盤，dbwr將臟塊寫入磁盤肯定需要lgwr進(jìn)程將log buffer寫入到在線日志中，lgwr進(jìn)程出現(xiàn)了控制文件讀寫等待。從這里我得到的信息是控制文件好像是讀寫出現(xiàn)了問(wèn)題，導(dǎo)致一些列問(wèn)題的產(chǎn)生。SYS:(Wnnn) log file switch (archivingneeded)
-> SYS:(LGWR) enq: CF - contention[mode=5]
-> SYS:(CKPT) control file parallel write
8. 再次對(duì)故障事件排查
又對(duì)ash視圖進(jìn)行了相關(guān)查詢，下面數(shù)據(jù)進(jìn)行了整理，發(fā)現(xiàn)大量事務(wù)在等待2731這個(gè)回話，且通過(guò)如下查詢發(fā)現(xiàn)2731回話在進(jìn)行控制文件讀寫，且伴隨著Log file parallel write以及enq:CF-contention。從上圖中的信息可以看到enq:CF-contention等待事件也是被controlfile file parallel write阻塞。所有的事件又都指向了控制文件。隨即讓客戶進(jìn)行排查硬件層面問(wèn)題，經(jīng)過(guò)反饋硬件各方面監(jiān)控都沒(méi)有報(bào)錯(cuò)，而且上周剛剛對(duì)X7一體機(jī)進(jìn)行了主機(jī)巡檢。9. 查看awr信息
對(duì)當(dāng)前系統(tǒng)系統(tǒng)問(wèn)題時(shí)間抓取了一個(gè)awr報(bào)告，top10事件如下，這里發(fā)現(xiàn)平均延遲和總等待都特別的高。10. 抓取hanganalyze
問(wèn)題分析到這里已經(jīng)沒(méi)有了頭緒，隨即抓了一個(gè)hanganalyze，相關(guān)信息截圖如下，滿屏信息都是log file switch (archiving needed)在等待2731這個(gè)回話。查看對(duì)應(yīng)的2731回話發(fā)現(xiàn)回話在進(jìn)行控制文件讀。這里的相關(guān)信息還是指向了控制文件。11. 再次排查主機(jī)硬件
協(xié)調(diào)客戶再次進(jìn)行主機(jī)排查，去機(jī)房發(fā)現(xiàn)內(nèi)存條壞了一個(gè)，這里是機(jī)房拍照信息，以為是內(nèi)存調(diào)原因，因節(jié)點(diǎn)2問(wèn)題已經(jīng)影響業(yè)務(wù)，隨即將節(jié)點(diǎn)2關(guān)閉，讓業(yè)務(wù)在節(jié)點(diǎn)1上運(yùn)行。但是節(jié)點(diǎn)1在運(yùn)行一段時(shí)間后發(fā)生了同樣的問(wèn)題，這里從剛開(kāi)始的日志截圖就能發(fā)現(xiàn)端倪，節(jié)點(diǎn)1的部分在線日志也存在無(wú)法歸檔的現(xiàn)象，只不過(guò)是沒(méi)有那么明顯。12. 再次協(xié)調(diào)客戶進(jìn)行一體機(jī)排查
最終一體機(jī)工程師在主機(jī)層面發(fā)現(xiàn)問(wèn)題，一體機(jī)工程師發(fā)現(xiàn)磁盤IO達(dá)到瓶頸，磁盤IO平均相應(yīng)在100ms以上，磁盤非常繁忙，達(dá)到瓶頸。Validate all the physical disks are in NORMAL state before modifying Exadata Smart Flash Cache.
The following command should return no rows:
# dcli –l root –g cell_group cellcli –e "list physicaldisk attributes name,status"|grep –v NORMAL
Drop the Flash Cache.
# dcli –l root –g cell_group cellcli -e drop flashcache
Set the flashCacheMode attribute to writeback.
# dcli –l root – g cell_group cellcli -e "alter cell flashCacheMode=writeback"
Re-create the Flash Cache.
# dcli –l root –g cell_group cellcli -e create flashcache all
Verify the flashCacheMode has been set to writeback.
# dcli –l root –g cell_group cellcli -e list cell detail | grep flashCacheMode
Validate the grid disk attributes cachingPolicy and cachedby.
# cellcli –e list griddisk attributes name,cachingpolicy,cachedby
cellclie -e list cell detail
13. 通過(guò)存儲(chǔ)工程師提供的關(guān)鍵信息在mos上進(jìn)行搜索2812622.1
當(dāng)前mos描述的信息和從數(shù)據(jù)庫(kù)查詢出來(lái)的一些狀態(tài)信息是吻合的。14. 修改flashcacha卡模式
修改前從如下信息看到在線日志無(wú)法進(jìn)行歸檔。15. 補(bǔ)充信息說(shuō)明
一體機(jī)工程師給出的IO消耗原因具體到了以下對(duì)象，針對(duì)改對(duì)象排查是一個(gè)定時(shí)任務(wù)，在10點(diǎn)開(kāi)始執(zhí)行，在1分左右任務(wù)就會(huì)結(jié)束。如下查詢可以得出針對(duì)問(wèn)題對(duì)象的更新變化操作都基本上在10點(diǎn)02的時(shí)候結(jié)束。以下是一體機(jī)工程師在存儲(chǔ)層面獲取到的信息，從信息可以看到，IO請(qǐng)求也基本上在10點(diǎn)開(kāi)始，然后IO一直未釋放。后臺(tái)日志無(wú)法歸檔的問(wèn)題出現(xiàn)在10:09左右，在這個(gè)時(shí)間點(diǎn)之前，數(shù)據(jù)庫(kù)中存在未應(yīng)用在線日志，當(dāng)這些日志信息被填滿，log buffer信息無(wú)法寫入到在線日志，所有的DML操作被掛起，對(duì)應(yīng)的等待事件db file parallel write和log file switch completion也開(kāi)始出現(xiàn)。
1. write-through mode和write-back模式比較
對(duì)于非極端閃存Exadata存儲(chǔ)服務(wù)器，默認(rèn)情況下，智能閃存緩存配置為以直寫模式運(yùn)行。在此模式下，寫入通過(guò)Exadata存儲(chǔ)服務(wù)器磁盤控制器上的緩存定向到磁盤。因此，寫I/O延遲通常不是性能問(wèn)題。因此，直寫閃存為大多數(shù)應(yīng)用程序提供了出色的性能。對(duì)于非常寫密集的應(yīng)用程序，寫卷會(huì)淹沒(méi)磁盤控制器緩存，使其實(shí)際上毫無(wú)用處。寫回閃存緩存為寫密集型應(yīng)用程序提供了解決方案。當(dāng)智能閃存高速緩存在寫回模式下運(yùn)行時(shí)，寫操作直接轉(zhuǎn)到閃存，只有當(dāng)數(shù)據(jù)從高速緩存中老化時(shí)，數(shù)據(jù)才會(huì)寫入磁盤。因此，最常見(jiàn)的讀寫數(shù)據(jù)保存在智能閃存緩存中，而訪問(wèn)較少的數(shù)據(jù)保存在磁盤上。請(qǐng)注意，對(duì)于寫操作沒(méi)有瓶頸的應(yīng)用程序，回寫智能閃存緩存所啟用的額外寫吞吐量幾乎沒(méi)有或根本沒(méi)有好處。此外，在回寫模式下，數(shù)據(jù)的所有鏡像副本都寫入智能閃存緩存，與直寫模式相比，這顯著的地減小了緩存大小。由于每個(gè)緩存模式的特性不同，建議在默認(rèn)情況下使用直寫模式，并且只有在將寫I/O視為性能瓶頸時(shí)才啟用寫回模式。確定寫瓶頸的最佳方法是在數(shù)據(jù)庫(kù)等待事件統(tǒng)計(jì)信息中查找空閑緩沖區(qū)等待。管理員還可以檢查Exadata存儲(chǔ)服務(wù)器指標(biāo)是否存在高磁盤I/O延遲和大比例的寫入。2. write-through mode
In write-through mode, Exadata Smart Flash Cache works as follows:For write operations, CELLSRV writes data to disk and sends an acknowledgement to the database so it can continue without any interruption. Then, if the data is suitable for caching, it is written to Exadata Smart Flash Cache. Write performance is not improved or diminished using this method. However, if a subsequent read operation needs the same data, it is likely to benefit from the cache. When data is inserted into a full cache, a prioritized least recently used (LRU) algorithm determines which data to replace.For read operations, CELLSRV must first determine if the request should use the cache. This decision is based on various factors including the reason for the read, the CELL_FLASH_CACHE setting for the associated object, and the current load on the cell. If it is determined that the cache should be used, CELLSRV uses an in-memory hash table, to quickly determine if the data resides in Exadata Smart Flash Cache. If the requested data is cached, a cache lookup is used to satisfy the I/O request.For read operations that cannot be satisfied using Exadata Smart Flash Cache, a disk read is performed and the requested information is sent to the database. Then if the data is suitable for caching, it is written to Exadata Smart Flash Cache.3. write-back mode
Commencing with Exadata Storage Server release 11.2.3.2.0, Exadata Smart Flash Cache can operate in write-back mode. In this mode, write operations work as follows:- CELLSRV receives the write operation and uses intelligent caching algorithms to determine if the data is suitable for caching.
- If the data is suitable for caching, it is written to Exadata Smart Flash Cache only. If the cache is full, CELLSRV determines which data to replace using the same prioritized least recently used (LRU) algorithm as in write-though mode.
- After the data is written to flash, an acknowledgment is sent back to the database.
- Data is only written back to disk when it is aged out of the cache.
Note the following regarding write-back flash cache:Write-back flash cache is ideal for write-intensive applications that would otherwise saturate the disk controller write cache.The large flash capacity of Exadata Storage Server means that for many applications a high proportion of all I/O can be serviced by flash.An active data block can remain in write back flash cache for months or years. Also, Exadata Smart Flash Cache is persistent through power outages, shutdown operations, cell restarts, and so on.1. 生產(chǎn)問(wèn)題無(wú)小事，發(fā)現(xiàn)問(wèn)題需要及時(shí)進(jìn)行排查做到問(wèn)題閉環(huán)。2. 問(wèn)題的定位需要全方面考慮，驗(yàn)證。本次故障主要體現(xiàn)在Redo日志無(wú)法歸檔，log buffer無(wú)法刷新，所有的業(yè)務(wù)變更都被阻塞。3. 知識(shí)面需要擴(kuò)展，不能僅僅停留在軟件層面，硬件層面問(wèn)題排查也很關(guān)鍵，包括磁盤IO、網(wǎng)絡(luò)，交換機(jī)配置，存儲(chǔ)卡配置等。4. 業(yè)務(wù)層面批量業(yè)務(wù)也需要重點(diǎn)考慮，往往因?yàn)橐淮嗡矔r(shí)的操作導(dǎo)致系統(tǒng)夯實(shí)，本次定位到10點(diǎn)開(kāi)始的一個(gè)定時(shí)任務(wù)，任務(wù)結(jié)束后，IO未能釋放為觸發(fā)問(wèn)題主要原因。
本文來(lái)源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/129489.html
