Oracle Database Express Edition 11g R2 にて ORA-12154 エラーが発生して困った話し

2018年1月26日2019年9月26日Oracle,開発

おはようございます。

昨日も激さむでしたね。
みなさん風邪にお気を付け下さい。

最近のことなんですが、よくわからない現象にハメられてしまい、よくわからないまま解決しちゃったので備忘のために記事を書いておきます。

1. 現象
- 1.1. ORA-12514とは
2. 一般的な発生原因
- 2.1. リスナーにサービスが登録されていない
- 2.2. tnsnames.oraファイルの定義に誤りがある
3. 対応したこと
4. まとめ

現象

新入社員が入社したので、空いているPCを使ってもらうことにした。
元々、別の社員が作業用PCとして使用していたため、既に Oracle XE 11g がインストール済みだったものの、システムユーザのパスワードがわからなかったので、再度インストールすることに。

無事にインストールを完了し、いざ接続しようとしたところ以下のエラーが発生

ORA-12514 リスナーは現在、接続識別子でリクエストされているサービスを認識していません

まあ、よく見るエラーなんですが、listener.oraファイルも、tnsnames.oraファイルも特に変更してないし、内容を確認しても特に問題なさそう。

ということでしばらくハマりました。

ORA-12514とは

リスナー（DBと接続元のソフトなどを橋渡しするサービス）がクライアントからの接続要求を受け付けた時に、

接続先となるサービスを認識していない時に発生するエラーです。

一般的な発生原因

リスナーにサービスが登録されていない
tnsnames.oraファイルの定義に誤りがある

ということなので、それぞれ確認してみる。

リスナーにサービスが登録されていない

Oracleがインストールされている端末上でコマンドプロンプトを起動し、「lsnrctl services」コマンドを実行。

C:\Users\Administrator>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 25-1月 -2018 16:44:10

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))に接続中
サービスのサマリー...
サービス"CLRExtProc"には、1件のインスタンスがあります。
  インスタンス"CLRExtProc"、状態UNKNOWNには、このサービスに対する1件のハンドラがあります...
    ハンドラ:
      "DEDICATED" 確立:0 拒否:0
         LOCAL SERVER
サービス"PLSExtProc"には、1件のインスタンスがあります。
  インスタンス"PLSExtProc"、状態UNKNOWNには、このサービスに対する1件のハンドラがあります...
    ハンドラ:
      "DEDICATED" 確立:0 拒否:0
         LOCAL SERVER
         
コマンドは正常に終了しました。

Oracle Express Edition の場合、デフォルトでサービス名「XE」が使われるはずなのに、情報が表示されない。
どうもリスナーがおかしい模様。

tnsnames.oraファイルの定義に誤りがある

一応こちらも確認。
（tnsnames.ora はデフォルトインストールで次の場所にあります。）

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  )

SERVICE_NAME がリスナーに登録されているサービス名と一致している必要がありますが、
こちらもデフォルトから変更していないので特におかしい箇所は見つかりませんでした。

対応したこと

とりあえずリスナーまわりが怪しい感じだったので、次のことを試してみました。

リスナーの再読み込み

コマンドプロンプトから「lsnrctl reload」を実行して、「listener.ora」を再定義してみました。
→状況変わらず

リスナーの再起動

Windowsのサービス画面より、リスナーのサービス「OracleXETNSListener」を再起動
→状況変わらず

Oracleインスタンスも再起動

Windowsのサービス画面より、リスナーのサービス「OracleServiceXE」を再起動、
その後リスナーも再起動
→状況変わらず

リスナーに手動でサービスを追加

「listener.ora」に自分でサービス「XE」を追加

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

→別のエラーが発生（おそらく、そもそも記述が間違っている）

（解決）コマンドからデータベースの起動をしてみる

コマンドプロンプトからシステムユーザで接続し、「startup」コマンドを試してみる。

sqlplus /NOLOG
connect sys/sys as sysdba
startup

→なぜか接続できるように

うーん。分からん。
まあこれ以上時間もかけられないので、忘れないように記事にして人生を楽しむことに。

まとめ

こういった、よく分からないまま解決してしまうこともたまにありますが、
時間をかけられなかったり、他の環境では再現しなかったりで仕方なくそれ以上の調査を止めることもありますよね。

今回は研修用の環境だったのでいいですが、本番の環境だったらと考えると恐ろしいですね。

そんなことがないことを祈るばかり。

ではでは。

2018年1月26日2019年9月26日Oracle,開発Oracle,トラブルシューティング

Posted by doradora