SEワンタンの独学備忘録

IT関連の独学した内容や資格試験に対する取り組みの備忘録

【Oracle19c】構築したてのOracle Databaseにリスナー経由でログインできない

Oracleに詳しくなくて、oracleをインストール、構築してみたときに単純ながらハマってしまったのでメモ。
有識者は当たり前に解決することかもしれないが、途中で途方に暮れて一人で泣きそうになっていたため。

深くは分かっていないので参考程度に。

参考情報

OSはCentOS7。
Oracleのバージョンは19.3.0.0.0。
oracleをインストールしてすぐ、いろいろ試しながら構築中に発生した事象。
今まで正常に使用できていたのに、突然同じような事象が発生した場合には多分別のところに原因があると思います。

事象

Oracle DatabaseをCentOS7にインストールを行い、データベースを作成した。
sqlplusでのログインはsysdbaではできる。

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 土 58 08:56:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.



Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
SQL>

が、リスナーを経由したログインができない。

[oracle@localhost dbs]$ sqlplus wantan/wantan@test_db

SQL*Plus: Release 19.0.0.0.0 - Production on 土 58 08:58:29 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

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


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

リスナーにサービスが登録されていないようなので、調べつつとりあえずlistener.oraを編集してみる。
サービスの静的登録というやつ?

・listener.ora設定例

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.142.137)(PORT=1521))))
#      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=off

SID_LIST_LISTENER=
 (SID_LIST=
  (SID_DESC=
   (GLOBAL_DBNAME=test_db)
   (ORACLE_HOME=/u01/app/oracle/product/19.0.3/dbhome_1/)
   (SID_NAME=test_db)
   (SERVICE_NAME=test_db)))

再度ログインを試すがやはりだめ。

[oracle@localhost dbs]$ sqlplus wantan/wantan@test_db

SQL*Plus: Release 19.0.0.0.0 - Production on 土 58 09:02:58 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS: 接続がクローズされました。


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

外部ツールから接続しようとすると次のようなメッセージ。

ORA-03113: end-of-file on communication channel

リスナーのステータスを確認してみる。

[oracle@localhost admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 08-5-2021 09:04:29

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.142.137)(PORT=1521)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for Linux: Version 19.0.0.0.0 - Production
開始日                    08-5月 -2021 09:01:49
稼働時間                  00 時間 239 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
パラメータ・ファイル      /u01/app/oracle/product/19.0.3/dbhome_1/network/admin/listener.ora
ログ・ファイル            /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.142.137)(PORT=1521)))
サービスのサマリー...
サービス"test_db"には、1件のインスタンスがあります。
  インスタンス"test_db"、状態UNKNOWNには、このサービスに対する1件のハンドラがあ ります...
コマンドは正常に終了しました。

なんとなくサービスは登録されているようにも見えるが。。
上記の示されているログファイルを確認してみる。
ログインを試行したときには以下のようなメッセージが表示されていた。

</msg>
<msg time='2021-05-08T09:09:52.426-07:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='localhost.localdomain'
 host_addr='::1' pid='13916'>
 <txt>TNS-12518: TNS: リスナーはクライアント接続をハンドオフできません。
 TNS-12547: TNS: 接続を失いました。
  TNS-12560: TNS: プロトコル・アダプタ・エラー
   TNS-00517: コンタクトがなくなりました。
    Linux Error: 32: Broken pipe
 </txt>
</msg>

ここからドツボにハマった。
メッセージが微妙に具体的じゃない。
いろいろ検索してlistener.oraを弄り倒してみるが、これといった成果が得られず。。

解決方法

原因は恐らくやっぱり、リスナーにサービスが正常に登録されていないこと。
参考:https://ameblo.jp/ganhoo-2006/entry-12420942691.html

パラメータファイルの編集により解決した。
リスナーからDBインスタンスを見に行く感じじゃなくて、DBインスタンスの方からリスナーに登録しにいくイメージ。

動的サービス登録がうまくいっていなくて、その後静的サービス登録を行おうとしたけどやっぱり静的サービス登録がうまくいってなかったと思われる。

一旦、DBインスタンスを停止させてから初期化パラメータファイルに以下の一行を追加した。

・$ORACLE_HOME/dbs/inittest_db.ora

local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.142.137)(PORT=1521))'

local_listenerはサービスを登録するリスナーを明示的に示すもの?だと思う。
設定値に関してはのLISTNERの設定値をそのままコピーしてくればよい。

私の場合は、試行錯誤の中でlistener.oraに静的サービス登録の記述(SID_LIST_LISTENER)をしてあったけど、紛らわしいのでその記述は削除した。設定を変更した場合にはリスナーも再起動しておく。

DBインスタンスを起動し直す。SPFILEを使用している場合には設定を反映させるために再作成を行う。

・SPFILE再作成
必要に応じて。

[oracle@localhost dbs]$ sqlplus / as sysdba
SQL> CREATE SPFILE FROM PFILE;

File created.

SQL>

・DBインスタンスの起動

SQL> startup
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             650117120 bytes
Database Buffers          411041792 bytes
Redo Buffers                3674112 bytes
データベースがマウントされました。
データベースがオープンされました。
SQL>

しばらく待てば多分動的登録されるが、すぐに確認する場合には明示的にDBインスタンスからリスナーへサービス登録を行う。

SQL> alter system register;

システムが変更されました。

SQL>

リスナー経由でのログインを再試行。

[oracle@localhost dbs]$ sqlplus wantan/wantan@test_db

SQL*Plus: Release 19.0.0.0.0 - Production on 土 58 09:31:00 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
に接続されました。
SQL>

これでログインできるようになった。外部ツールからの参照もさきほどの問題は解決してそう。

データベースを正常に使用するにはまだいくつか問題が残っていますが、それはまた別問題なのでここでは無視して解決を喜びました。