リスナー接続トラブルの基本的な対処法まとめ

あるあるすぎる接続トラブルの基本的な対処法をまとめたいと思いました
先に項目を列挙して、細かい内容はちょくちょく充実させていきます
基本的にはDBサーバ側でリスナーがサービス登録できていて、sqplusでリスナー経由で接続できればOKです(アプリケーションから接続できる状態です)

エラー#1 TNS-12541/The Network Adapter could not establish the connection

sqlplus

ERROR:
ORA-12541: TNS: リスナーがありません


ユーザー名を入力してください:

JDBC

java.sql.SQLRecoverableException: IOエラー: The Network Adapter could not establish the connection
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
	at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at sena.App1.main(App1.java:17)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
	at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
	at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
	... 6 more
Caused by: java.net.ConnectException: Connection refused: connect
	at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
	at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
	at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
	at java.net.Socket.connect(Socket.java:589)
	at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:161)
	at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:428)
	... 11 more

原因

ネットワーク的にリスナーにつながっていない。接続先の指定が正しくないか、名前解決ができていないか、ネットワーク的に到達できないIPアドレスか、ファイアウォールで遮断されているか、リスナーが起動していない、のどれか。

エラー#2 ORA-12514/TNS-12505

sqlplus

ERROR:
ORA-12514: TNS:
リスナーは接続記述子でリクエストされたサービスを現在認識していません


ユーザー名を入力してください: 

JDBC

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
	at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at sena.App1.main(App1.java:17)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:275)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
	... 6 more

原因

SID(orサービス名)指定が正しくない。リスナーには到達したが、指定されたSID(orサービス名)がリスナーに登録されているものと合致しない。またはDBインスタンスがopenしていない

補足) 接続方法まとめ#編集中

sqlplus

• 2種類ある
• ローカル接続: DBサーバ自身からリスナーを経由せずに接続する
• 環境変数ORACLE_HOME, ORACLE_SID必須
• sqlplus user/pass
• リモート接続: リスナーを経由して接続する
• 環境変数ORACLE_HOME必須
• sqlplus user/pass@host:port:service

JDBC

• jdbc:oracle:thin:@host:port:sid
• SID(DBインスタンス名)指定
• jdbc:oracle:thin:@host:port/service
  • サービス指定
    • OracleRAC(SCANリスナー宛), MultiTenantは基本こっち
    • OracleEngineredSystem(ExaData, OracleDatabaseAppliance)はOracleRACなのでこっち

補足) DB起動状態まとめ#編集中

close(停止状態)
(起動を実行)
↓
(初期化パラメータファイルを読み込み、DBインスタンスを起動)
nomount
↓
(制御ファイルを読み込み, SCNやDBのファイルパスを認識)
mount
↓
(データファイルを読み込み, DBを読み書き可能/sys以外でのユーザのログイン可能)
open

• 接続状態の確認方法
  • アラートログをエディタで開く(Linuxではtail -fする)
  • sysでsqlplus接続し、select status from v$instance;

その他TNSまわりのエラー#1

ERROR:
ORA-12560: TNS: プロトコル・アダプタ・エラーが発生しました

WindowsのDBインスタンス用サービス(OracleServicexxxx)が停止したままの状態でsqlplusでつなごうとするとでる。Windows版Oracleのsqlplusの仕様として、DBインスタンス用サービスを起動していないとsqlplusが実行できない。Linux慣れした人が陥る罠。

その他TNSまわりのエラー#2

ERROR:
ORA-12557: TNS: プロトコル・アダプタをロードできません

リスナーが格納されてるORACLE_HOMEではないORACLE_HOME環境変数でリスナーを起動しようとしてる。Windowsでしか見たことない。システム環境変数に変なORACLE_HOMEが設定されてるか、リスナー用サービス(11gデフォ: OracleOraDb11g_home1TNSListener, 12cデフォ :OracleOraDB12Home1TNSListener)のサービス実行アカウントに指定されてるユーザにおかしなORACLE_HOME環境変数が指定されているなど。

Oracleにつながらないときのトラシュー手順まとめ#編集中

• a. 動的リスナーを使ってる場合、lsnrctl statusで対象のDBのステータスがreadyになってるか
• listener.oraを編集してなければデフォルトは動的リスナー。動的リスナーは、DBインスタンスの起動を自動的に認識してリスナーに登録し、ネットワークからつながるようにしてくれる
• $ORACLE_HOME/network/admin/listener.oraを開いて、SID_LIST_LISTENERにGLOBAL_DBNAME/SID_NAMEで対象のDB名の記述があれば静的リスナー、なければ動的リスナー
• b. 静的リスナーを使ってる場合
• lsnrctl statusのエントリはDBインスタンスの起動に関わらずUNKNOWSになるので、lsnrctl statusからはDBインスタンスがopenしてるかの確認は不可。アラートログ見るか、ローカル接続してselect status from v$instanceでopenできてるか確認する(openしてないとリスナー経由での接続を受け付けない)