Python OracleアラートログのORAエラー傾向分析をしてみる 

#Oracleアラートログとは
Oracleはアラートログに様々な情報を記録しています。

以下、主にアラートログに出力される情報です。

1. DB稼動している際に発生したエラー情報
   ※発生したすべての内部エラー(ORA-00600、ORA-7445)、デッドロック・エラー(ORA-00060)など
2. DB起動情報
3. DB停止情報
4. 構成変更(データファイル追加など)


アラートログは以下のように記録されます。※サンプルです

```
Mon Jun 25 11:47:21 2018
ORA-01555 caused by SQL statement below (SQL ID: abcb02h7f6vppt, Query Duration=7498 sec, SCN: 0x08c6.000bffaa):
SELECT  "COL1", "COL2", "COL3", "COL4" FROM "TBLABC" WHERE  "TBLABC.COL1" = :B1 AND ・・・・

Mon Jun 25 12:45:35 2018
ORA-07445: exception encountered: core dump [ktiXcbDump()+220] [SIGSEGV] [Address not mapped to object] [0x000000020] [] []
ORA-00600: internal error code, arguments: [xxxx], [0], [0], [], [], [], [], []
ORA-03135: connection lost contact
```

上記ログの内容は、以下２つのORAエラーが発生しています。
>・2018年6月25日 11時47分21秒 
　　ORA-01555「スナップショットが古すぎます」がエラーが発生。
　 　(SQL_ID(SQLを識別するID)は、[abcb02h7f6vppt] )

>・2018年6月25日 12時45分35秒 
　　ORA-07445、ORA-00600、ORA-03135のエラーが同じ時刻に発生しています。

ORAエラーが発生したかを確認する際、アラートログでORAで検索して時間を確認することになります。

アラートログを分析することで、事前になにか対策ができないのかをできたらいいなと思い、
以下のスクリプトを作成しました。

```
１．DBアラートログ加工
２．月別にORA-エラー発生件数を見える化
```
※本投稿で使用するアラートログは投稿用に作成していますのでご了承ください



#１．DBアラートログ加工
やりたいことは、アラートログの出力はORAエラー発生時刻とORAエラーが出力されている行が違うので、
エラー発生時刻とORAエラーを１行にします。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/484f414e-0221-59c4-9071-6f2ad90de283.png)

アラートログを加工してCSVにするpython スクリプト

```python:alerttocsv.py
import datetime
import re


def alertlog_to_csv(alertlog):
    
    filepath = r"'"+ alertlog + "'"
    filepath = filepath.replace("'","")
    
    csvpath = filepath + '.csv'
    print(filepath)
    #Oracle DBアラートログを読込み
    with open(filepath,'r',errors='ignore' ,encoding='utf-8') as f:
        
        lines = f.readlines()
        
    #CSV出力
    with open(csvpath,'w') as csvf:
        
        #CSVヘッダー出力
        s = "STIME,ORA-ERROR,ERROR DESCRIPTION" + "\n"
        csvf.writelines(s)
        
        #DBアラートログは運用によっては、時間が最初にない可能性があるのでダミー日付をいれる
        t = datetime.datetime.strptime('2018-Jan-01 00:00:00', '%Y-%b-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
        
        #1行読込み
        for linecr  in lines:
            
            #改行コードを取る
            line = linecr.rstrip()
            
            #時間を取得
            strdate = line[len(line)-4:len(line)]  + '-' + line[4:7] + '-' + line[8:10] + ' ' + line[11:19]  
            
            #先頭4文字を取得
            ora = (line[0:4])
            
            #日付チェック
            try:
                #日付の場合、変数 t に日付をいれる
                t = datetime.datetime.strptime(strdate, '%Y-%b-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S')
                
            except:
                #こっちは日付以外の場合
                
                #ORA が含まれている場合、csvファイル書き込み対象
                if ora == 'ORA-':
                    
                    #ORA-xxxxx で csvに区切りたいので 「:」 で区切る
                    oraerr= line[:line.find(":")]
                    
                    #ORAエラーコード取得  
                    oraerr= line[:line.find(":")]
                    
                    #時間 , ORA-エラー , エラー内容を１行にする
                    s = (t + ',' + oraerr + ','  + '"' +  line[line.find(":")+1:].replece('"','""') + '"' + "\n") 
                    
                    #CSV出力
                    csvf.writelines(s)
    

if __name__ == '__main__':
    
    #アラートログ名を入力
    print ('====== Enter alertlog  =====')
    alertlog = input('>  ')
    
    alertlog_to_csv(alertlog)
```

pythonスクリプト実行時に、アラートログ名を入力します。
スクリプト実行すると、アラートログからcsvファイルが作成されます。
※csvファイル名は、アラートログ名に「.csv」として作成します

例) alert_orcl.log  の場合、 alert_orcl.log.csv　として作成されます

- 作成されたサンプル
- 
　「２．月別にORA-エラー発生件数を見える化」で動作確認される場合のサンプルです。
　　また、本投稿ではこのcsvを使用しております。

```alert_orcl.log.csv
STIME,ORA-ERROR,ERROR DESCRIPTION
2018-02-19 13:01:01,ORA-00600,"internal error code"
2018-02-25 09:33:10,ORA-01555,"abc02h7f6vppt, Query Duration=4263 sec, SCN: 0x08c7.b8bcc363):"
2018-02-25 22:20:11,ORA-01653,"unable to extend table scott.TBLA by 8192 in tablespace TS"
2018-02-25 23:43:32,ORA-01654,"unable to extend index scott.IDX_A_1 by 16 in tablespace TSI"
2018-02-25 21:20:11,ORA-01653,"unable to extend table scott.TBLB by 8192 in tablespace TS"
2018-02-25 22:43:32,ORA-01654,"unable to extend index scott.IDX_B_1 by 16 in tablespace TSI"
2018-02-26 02:26:32,ORA-00060,"Deadlock detected"
2018-02-26 02:27:32,ORA-00060,"Deadlock detected"
2018-02-28 12:33:10,ORA-01555,"abc02h7f6vppt, Query Duration=3600 sec, SCN: 0x08bf.b8bcc823):"
2018-02-28 13:11:10,ORA-01555,"abc02h7f6vppt, Query Duration=4101 sec, SCN: 0x08bf.dra3f8dd):"
2018-02-28 13:22:10,ORA-01555,"abc02h7f6vppt, Query Duration=3703 sec, SCN: 0x08bf.9ui3b834):"
2018-02-28 13:34:10,ORA-01555,"abc02h7f6vppt, Query Duration=4767 sec, SCN: 0x08bf.84a3b823):"
2018-03-26 01:12:12,ORA-00060,"Deadlock detected"
2018-03-26 02:01:34,ORA-00060,"Deadlock detected"
2018-04-20 11:01:01,ORA-00600,"internal error code"
2018-04-26 01:12:12,ORA-00060,"Deadlock detected"
2018-04-26 02:01:34,ORA-00060,"Deadlock detected"
2018-05-19 13:01:01,ORA-00600,"internal error code"
2018-05-25 09:33:10,ORA-01555,"7zaxmbuwgvgsn, Query Duration=3121 sec, SCN: 0x08bf.b8bcc732):"
2018-05-25 21:19:11,ORA-01653,"unable to extend table scott.TBLA by 8192 in tablespace TS"
2018-05-25 22:20:11,ORA-01653,"unable to extend table scott.TBLB by 8192 in tablespace TS"
2018-05-25 23:43:32,ORA-01654,"unable to extend index scott.IDX_A_1 by 16 in tablespace TSI"
2018-05-25 23:52:32,ORA-01654,"unable to extend index scott.IDX_B_1 by 16 in tablespace TSI"
2018-05-25 23:55:32,ORA-01654,"unable to extend index scott.IDX_A_2 by 16 in tablespace TSI"
2018-05-26 02:26:32,ORA-00060,"detected"
2018-05-26 02:27:32,ORA-00060,"Deadlock detected"
2018-05-28 12:33:10,ORA-01555,"abc02h7f6vppt, Query Duration=10909 sec, SCN: 0x08bd.379e854a):"
2018-05-31 13:11:10,ORA-01555,"abc02h7f6vppt, Query Duration=10845 sec, SCN: 0x08bd.3973d944):"
2018-05-31 13:22:10,ORA-01555,"abc02h7f6vppt, Query Duration=5034 sec, SCN: 0x08bd.3fb8eaf1):"
2018-05-31 14:11:10,ORA-01555,"abc02h7f6vppt, Query Duration=9187 sec, SCN: 0x08bc.59b143ad):"
2018-05-31 15:34:10,ORA-01555,"abc02h7f6vppt, Query Duration=3940 sec, SCN: 0x08bc.5c97c1b0):"
2018-06-26 02:26:32,ORA-00060,"Deadlock detected"
2018-06-26 02:27:32,ORA-00060,"Deadlock detected"
```


#２．月別にORA-エラー発生件数を見える化

１．で作成されたCSVファイルを読込み、月別にORAエラーの傾向を確認します

実行するとcsvファイル名をきかれるので、ファイル名を入力して処理を実行。

```python:alertmonth.py
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import datetime
import numpy as np
from matplotlib.ticker import *

def alertcsv_to_year(csvfile):

    csvpath = r"'"+ csvfile + "'"
    csvpath = csvpath.replace("'","")
    
    #１．Oracle DBアラートCSVファイルを読込む
    dfcsv = pd.read_csv(csvpath)

    #２．インデックス列をSTIMEに設定
    dfcsv.set_index('STIME',inplace=True)

    #３．日付データ(文字型)を yyyy-mm 形式にして格納
    dfcsv.index = dfcsv.index.map(lambda x: datetime.datetime.strptime(x[0:7],"%Y-%m"))

    #４．get_dummies を使用して ダミー変数を作成
    dfdummy = pd.get_dummies(dfcsv["ORA-ERROR"])

    #５．yyyy-mm形式にした集計キーで、Oraエラーの数を数える
    df=dfdummy.groupby(['STIME']).sum()
    
    #６．棒グラフと日付は相性が悪いので、数字に変換
    df.index = df.index.map(lambda x: x.month)
    
    #表示設定
    fig = plt.figure(figsize=(8, 6))
    plt.style.use('ggplot')
    ax1 = fig.add_subplot(1,1,1)

    #棒グラフで表示
    df.plot(kind='bar',ax =ax1)
    
    #書式設定
    plt.title("ORA ERROR COUNT (month)", fontsize=12)   
    plt.xlabel("month", fontsize=12)
    plt.ylabel("count", fontsize=12)
    plt.tick_params(labelsize=12)
    
    plt.show()

if __name__ == '__main__':
    
    #csvファイル名を入力 (絶対パス or 相対パス)
    print ('====== Enter csv file  =====')
    csvfile = input('>  ')
    
    alertcsv_to_year(csvfile)

```

pythonスクリプトを実行するとcsvファイル名をきかれるので、ファイル名を入力して処理を実行。

※pythonスクリプトの実行方法詳細は、以下を参照ください
　・[Windows環境でPythonスクリプト(.py)の実行方法](https://qiita.com/kngsym2018/items/21b6efe4547a78661951)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/34fe5ded-6caa-0dfc-227a-27459d720279.png)


月別のORAエラー件数のグラフができた！
![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/cc08322d-7842-e506-0ad0-8a07c1fd568b.png)





グラフからは、２月、５月がORAエラーが多い傾向にあります。
※この傾向分析(日別、時間別分析)については次回、投稿します。

#月別にORA-エラー発生件数を見える化のコードの説明

csvから取込んだデータは、ORAエラー発生時間、ORAエラー番号になります。
このままではグラフ化できないので、月別にORAエラー別に発生件数を集計します。
（集計方法はDataFrameのgroupbyを使用）

１．Oracle DBアラートCSVファイルを読込む
　　　csvから取込んだデータは、ORAエラー発生時間、ORAエラー番号が並んでいます。
　　　このデータを加工して、月別のORAエラー件数を集計します。

　　　以下、csvがDataFrameに格納されているイメージです

```python
      import pandas as pd
      df=pd.DataFrame([
              ['2018-05-29 10:00:00','ORA-00001'],
              ['2018-05-29 11:00:00','ORA-00020'],
              ['2018-06-25 12:00:00','ORA-00060'],
              ['2018-06-26 18:00:00','ORA-00001'],
              ['2018-06-27 19:00:00','ORA-00020']
              ]
              ,columns=['STIME','ORAERROR'])
```
　　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/f50a2280-6193-4a46-8194-5b8fe12560d4.png)


２．インデックス列をSTIMEに設定
　　　STIMEをインデックスにする理由として３．で日付変換をかけるから実施

```python
    df.set_index('STIME',inplace=True)
```
　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/8e069ecd-f392-dcdf-bdc4-fdb5815185d1.png)

３．日付データ(文字型)を yyyy-mm 形式にして格納
　　　インデックスを日付形式に変換します。ここでは、yyyy-mm と変換します。
　　　⇒月単位で集計するための前準備

```python
      import datetime
      df.index = df.index.map(lambda x: datetime.datetime.strptime(x[0:7],"%Y-%m"))
```
　　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/9d9d5b16-d84a-123f-5053-f57246d07a2a.png)


４．get_dummies を使用して ダミー変数を作成
　　　ダミー変数を作成して、ORAエラーを展開します。

  ```python
    dfdummy = pd.get_dummies(df["ORAERROR"])
  ```
　　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/0cddbdfd-462f-33d7-e68f-4599eb8bac68.png)

５．yyyy-mm形式にした集計キーで、Oraエラーの数を数える
　　　インデックス[STIME]はyyyy-mm形式に変換しているので、
　　　STIMEで集計すれば、月別のORAエラー件数が算出できます。

```python
      df=dfdummy.groupby(['STIME']).sum()
```
　　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/9142dd0e-7d1f-6465-e29a-58000ce23efd.png)


６．棒グラフと日付は相性が悪いので、数字に変換
　　　pandasの棒グラフでx軸が日付型のデータだと、思うように表示されないので数字に変換してます。

```python
      df.index = df.index.map(lambda x: x.month)
```
　　　![image.png](https://qiita-image-store.s3.amazonaws.com/0/261475/9e1af31a-2299-7661-3a62-54603514bc4e.png)



　　※７．で加工したDataFrameを棒グラフで表示してます


#OracleアラートログのORAエラー傾向分析した分析と考えられる対策案

本投稿では月別の分析を実施しました。次回投稿では日別、月別でORAエラー分析を行って対策も考えてみました。

以下投稿を参照ください

・[(続き)OracleアラートログのORAエラー傾向分析](https://qiita.com/kngsym2018/items/ba19bd552cc12f2e4c72)


#補足．アラートログの日付書式が変更

Oracle12R2 からはアラートログの日付の表示形式が変更されておりますので
本ツールは、12R1以前のアラートログで動作するものとなります。

・    http://otndnld.oracle.co.jp/ondemand/od12c-oct2016/03_DB12201_coretech_InstallUpgradeCompati_forOTN_v2.4.pdf
    
    ※P41 「ログ、トレース出力の時間表記の変更」参照ください