Oracle DBのセッションを増やすor強制終了する【ORA-12516対応】

ここでは、Oracle DBのセッションを強制終了する手順を説明します。

強制終了しなければならない状況は色々とあるとは思いますが、以下のエラーが発生した場合なんかもそうかと思います。

ORA-12516: TNS: リスナーは、一致するプロトコル・スタックが使用可能なハンドラを検出できませんでした。

このエラーメッセージはかなりわかりにくいですね。意訳すると、「接続可能なリスナーが見つかりません」ぐらいでしょうか。基本的に処理が完了したら接続は開放されるのですが、異常終了した場合など、ゾンビ化して残り続けることがたまにあるようです（ただし1~2時間で切れるよう）。もし、頻発するようであれば切断処理に何かしらの問題があるやもしれません。

手順

Ⅰ. sqlplusでsysdba権限でOracleにログインする

DBがどこにあるかによるので、以下のいづれかの方法でsqlplusにログインしてください。

1. ローカルにDBがある場合
   
   sqlplus sys/パスワード as sysdba
2. 外部サーバにDBがある場合
   sqlplus sys/パスワード@データベース接続子 as sysdba
   
   ここで、データベース接続子の記述方法についてもパターンがあります。
   1. 「ホスト名:ポート番号/サービス名」の直接指定
      例）@192.168.1.1:1521/orcl
   2. tnsnames.oraに接続定義されている場合
      例）@SAMPLE

1# tnsnames.ora
2SAMPLE =
3  (DESCRIPTION =
4    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
5    (CONNECT_DATA =
6      (SERVICE_NAME = orcl)
7    )
8  )

Ⅱ. V$RESOURCE_LIMITを検索して、セッション数を確認する

以下をsqlplusで実行します。

1. 表示幅を増やす

場合によっては必要ないかもしれませんが、表示幅が長くなって表が崩れる可能性があるため、行サイズをとりあえず500にします。

1set linesize 500

2. V$RESOURCE_LIMITを検索します

1select * 
2from v$resource_limit

すると以下のような検索結果が出てきます。

• RESOURCE_NAMEはリソース名（おそらく、processes:プロセスとsessions:セッションが出てくるはずです）
• CURRENT_UTILIZATIONは現在使用数
• MAX_UTILIZATIONは最大使用数
• LIMIT_VALUEは上限値です。

3. 検索結果を確認

このとき、セッション数の現在使用数または最大使用数が上限値と同じもしくは近い場合は、セッション数不足ということがわかります。もし、そうでない場合は別の要因が考えられますので、残念ながらここでは解決できません。

セッション数不足の対応としては、当たり前ではありますが、セッション上限を増やすか/不要なセッションを消す（終了させる/殺す）という手段があります。どちらを選択すべきかはサーバ性能などにも依りますので一概には言えません。しかし、あくまで方針として言えば、以下を判断基準としてもよいと思います。

• 定常的に使用するセッション数よりも過小に設定されてたり、サーバの性能（特にメモリ）に余裕があるのであればセッション上限を増やす↓
• アプリが異常終了してしまったことによる不要なセッションが大量に発生してしまったのが分かっていたり、取り急ぎアプリをDBアクセスしたい場合などはセッションを消す↓

Ⅲ-a. セッション上限を増やす

1. セッション上限のパラメータがどこで定義されているか

Oracleのインスタンスを起動するときにこのセッション上限が決められるわけですが、その設定情報がどう定義されるかは2パターンあるため、まずどちらで定義されているかを確認する必要があります。

• pfile：テキスト形式の初期化パラメータファイル
• SPFILE：バイナリ形式のサーバパラメータファイル

2. 定義情報の確認方法

sqlplusで以下を実行し、結果のVALUE列に、nullであればpfile、パスの情報があればSPFILEということがわかります。

1show parameter spfile

• pfileの場合
  Oracle公式のこちらを参照してください。
• SPFILEの場合
  以下のDDLを実行します。ここでは”500″を指定していますので、適宜必要な数字に変更してください。
  また、ロールバックができないことに留意してください。

1alter system set processes = 500 scope=BOTH;

実行例：

Ⅲ-b. 不要なセッションを削除する

1. 詳細なセッション一覧を検索する

以下のSQLを実行して、削除に必要な情報を取得します。

1select MACHINE, LOGON_TIME, SID, SERIAL#, USERNAME, OSUSER
2from V$SESSION

以下のような結果が取得されると思います（一部抜粋&マスキングしてます）

2. セッションを削除する

上記で削除したいセッションが特定できていれば、以下のSQLで削除します。繰り返しになりますが、自己責任で。

また、DDL文でもありますので、ロールバックができないことにも留意してください。

1alter system kill session 'ここに対象のSIDを指定'

実行例：

参考

http://out2dev.blogspot.jp/2012/03/ora-12516.html

https://sql-oracle.com/?p=395

http://at-j.co.jp/blog/?p=5468