【Oracle】ORA-03113: end-of-file on communication channel 完全ガイド｜原因の特定・アラートログ調査・SQLNET.EXPIRE_TIME・ファイアウォール対策まで解説

# Oracle 11g 以降: 診断ログは ADR（Automatic Diagnostic Repository）に格納される
# デフォルトパス: $ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/

# アラートログの場所を SQL*Plus から確認
# SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

# アラートログのパス例（Linux）
ls /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

# 最新のエラーを確認（ORA- で始まる行を抽出）
tail -200 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log | grep -E "ORA-|Error|error|Crash|crash|shutdown"

# ORA-00600 の発生確認
grep "ORA-00600" /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log | tail -20

# インスタンスのシャットダウン履歴を確認
grep -E "Shutting down|Starting up|Instance terminated" /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log | tail -20

# ORA-00600 / ORA-07445 が発生した場合はトレースファイルが生成される
# ファイル名: <instance_name>_ora_<PID>.trc

# 最新のトレースファイルを探す
ls -lt /u01/app/oracle/diag/rdbms/orcl/orcl/trace/*.trc | head -10

# エラー内容の確認
grep -A 10 "ORA-00600" /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12345.trc

# ADR コマンドで診断情報を表示（adrci ツール）
adrci
# adrci> show incident
# adrci> show problem

-- 診断ファイルのパスを SQL で確認
SELECT name, value FROM V$DIAG_INFO
ORDER BY name;

-- 主要な出力:
-- Diag Trace : アラートログとトレースファイルのパス
-- Diag Alert : アラートログ専用のパス（XML 形式）
-- Diag Incident: インシデントトレースのパス

-- 現在の接続セッション情報（切断前にセッションIDを記録しておくと調査しやすい）
SELECT sid, serial#, status, username, program,
       TO_CHAR(logon_time, 'YYYY/MM/DD HH24:MI:SS') AS logon_time,
       machine
FROM V$SESSION
WHERE username IS NOT NULL
ORDER BY logon_time;

# ファイル: $ORACLE_HOME/network/admin/sqlnet.ora
# または TNS_ADMIN 環境変数で指定したディレクトリ

# Dead Connection Detection を有効化（10分間隔でプローブ）
SQLNET.EXPIRE_TIME = 10

# ファイアウォールのアイドルタイムアウト値より短い値を設定する
# 例: ファイアウォールが 30 分でタイムアウトする場合 → 10 分に設定

# 設定値の目安:
# SQLNET.EXPIRE_TIME = 0  : 無効（デフォルト）
# SQLNET.EXPIRE_TIME = 10 : 10分間隔（推奨の基本設定）
# SQLNET.EXPIRE_TIME = 5  : 5分間隔（より確実だがプローブ通信が増える）

# Out-of-Band break（OOB）はファイアウォールによってはブロックされる
# OOB を使わないように設定する場合（サーバー・クライアント両側の sqlnet.ora に設定）
DISABLE_OOB = ON

# この設定が必要な典型的なシナリオ:
# - ファイアウォール越しの接続で Ctrl+C キャンセルが効かない
# - リモート接続でクエリのキャンセル後に ORA-03113 が発生する
# ※ DISABLE_OOB = ON にすると Ctrl+C によるクエリキャンセルが遅くなる

# Oracle の SQLNET.EXPIRE_TIME に加えて OS の TCP keepalive も設定できる
# TCP keepalive はファイアウォールに対して定期的に空のパケットを送り続ける

# 現在の TCP keepalive 設定を確認
cat /proc/sys/net/ipv4/tcp_keepalive_time     # 最初の keepalive 送信までの時間（秒、デフォルト 7200）
cat /proc/sys/net/ipv4/tcp_keepalive_intvl    # keepalive 間隔（秒、デフォルト 75）
cat /proc/sys/net/ipv4/tcp_keepalive_probes   # 失敗を判断するまでの試行回数（デフォルト 9）

# ファイアウォールタイムアウトが 30 分の場合: 1800 秒より短く設定
echo 1200 > /proc/sys/net/ipv4/tcp_keepalive_time   # 20分に変更（一時的）

# 恒久的に設定する場合は /etc/sysctl.conf に記述
echo "net.ipv4.tcp_keepalive_time = 1200" >> /etc/sysctl.conf
sysctl -p

-- インスタンスのクラッシュ後は STARTUP コマンドで再起動
-- Oracle が自動的に Instance Recovery を実行する

-- SQL*Plus で SYSDBA として接続して再起動
-- sqlplus / as sysdba

-- インスタンスの状態を確認
SELECT STATUS FROM V$INSTANCE;
-- STARTED / MOUNTED / OPEN

-- 手動での Instance Recovery（通常は OPEN 時に自動実行）
RECOVER DATABASE;   -- MOUNT 状態から

-- データベースをオープン
ALTER DATABASE OPEN;

-- ※ クラッシュ後の OPEN では SMON が自動的に前進ロールフォワードとロールバックを実行する
-- → 未コミットのトランザクションは自動的にロールバックされる