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 土 5月 8 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 土 5月 8 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 土 5月 8 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 稼働時間 0 日 0 時間 2 分 39 秒 トレース・レベル 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 土 5月 8 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>
これでログインできるようになった。外部ツールからの参照もさきほどの問題は解決してそう。
データベースを正常に使用するにはまだいくつか問題が残っていますが、それはまた別問題なのでここでは無視して解決を喜びました。
