Oracleのリスナーとホワイトリスト
リスナーの基本概念と設定：
経験から言うと、データベースのSIDとグローバルデータベース名は分けておくのが良い。設定時に混同しないようにするためだ。サーバ側とクライアント側の両方を設定する必要がある場合、両方の設定方法は同じだ。
Oracleリスナーは何をするのか？サーバ側に設置され、サーバベースのネットワークサービスであり、クライアントからデータベースへの接続要求を監視する。接続要求が正しいかどうかを判断し、要求が有効であれば接続し、失敗すれば拒否する。したがって、リモートデータベースに接続するには、リモートデータベース側でリスナーを設定する必要があり、クライアント側では設定は不要である。
一、リスナー(LISTENER)
リスナーはOracleのサーバベースのネットワークサービスで、主にクライアントからデータベースサーバへの接続要求を監視するために使用される。サーバベースのサービスであるため、データベースサーバ側にのみ存在し、リスナーの設定もデータベースサーバ側で行う。
Oracleの一般的な接続方式
- ServiceName方式：
jdbc:oracle:thin:@//<host>:<port>/ServiceName
sqlplus scott/scott@//192.168.21.142:1521/adg
- SID方式：（リスナーが起動していれば接続可能）
jdbc:oracle:thin:@<host>:<port>:<SID>
sqlplus scott/scott@192.168.21.142:1521/adg （SID方式）
- RAC方式（古い書き方。現在は一般的にServiceName方式を使用）
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.2)(PORT=1521)))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxrac)))
（この方式では、接続を作成するクライアント側でtnsnames.oraの設定が必要。設定がないと接続できない）
- jdbc:oracle:oci:@service （通常は使用されない）
sqlplus scott/scott@adg
例：
ADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.142)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = adg)
)
)
二、ローカルサービス名(Tnsname)
Oracleクライアントとサーバ間の接続は、クライアントが接続要求を送信し、サーバ側のリスナーが接続要求の正当性をチェックすることで行われる。要求が有効であれば接続し、そうでなければ拒否する。
ローカルサービス名はOracleクライアントのネットワーク設定の1つであり、他にOracle Names Serverなどがある。Oracleで一般的に使用されるクライアント設定はローカルサービス名であり、本記事でも主にローカルサービス名の設定について説明する。
クライアントからサーバへの接続に関する一般的な問題の解決方法
クライアントとサーバ間の接続問題を解決するには、まずクライアント設定が正しいか確認し（クライアント設定はデータベースサーバ側のリスナー設定と一致している必要がある）、次にエラーメッセージに基づいて解決する。以下に一般的な接続問題をいくつか挙げる：
1. ORA-12541: TNS: リスナーがありません
明らかに、サーバ側のリスナーが起動していない。また、クライアントのIPアドレスやポートが正しいか確認する。リスナーを起動する：
$ lsnrctl start
または
C:/lsnrctl start
2. ORA-12500:
ORA-12500: TNS: リスナーが専用サーバプロセスを起動できません（PL/SQLでログインする際、クライアントがある場合はクライアントのサービス名と一致している必要がある。サーバ接続の場合はサーバ設定のサービス名が表示される）
Windowsの場合、Oracleインスタンスサービスが起動していない。インスタンスサービスを起動する：
C:/oradim –startup -sid myoracle
3. ORA-12535: TNS: 操作がタイムアウトしました
この問題の原因は多く、主にネットワークに関連する。まずクライアントとサーバ間のネットワークが正常か確認し、ネットワークが接続可能であれば、両側のファイアウォールが接続を遮断していないか確認する。
4. ORA-12154: TNS: サービス名を解決できません
入力したサービス名と設定したサービス名が一致しているか確認する。また、生成されたローカルサービス名ファイル（WindowsではD:/oracle/ora92/network/admin/tnsnames.ora、Linux/Unixでは$ORACLE_HOME/network/admin/tnsnames.ora）内の各サービスの先頭行のサービス名の前にスペースがないか確認する。
5. ORA-12514: TNS: リスナーは接続記述子で指定された SERVICE_NAME を解析できません
Net Managerを開き、サービス名を選択し、サービス識別フィールドのサービス名が正しいか確認する。このサービス名はサーバ側のリスナー設定のグローバルデータベース名と一致している必要がある。
6. Windowsでリスナーサービスを起動時にパスが見つからないというエラー
エラーメッセージ：ORA-28009 SYSとしての接続はsysdbaまたはsysoperとして行う必要があります
原因：SYSユーザでデータベースにログインする場合、他の操作ユーザとは構文が異なり、ログイン権限を明示する必要がある。（インストール時にロックが解除されていない）
解決方法：正しい記述方法を使用する。
例：SYS/パスワード@サービス名 AS SYSDBA；
cmdで実行
sqlplus / as sysdba
alter user sys identified by password; その後問題なくなる
リスナーの設定と説明：
通常、サーバ側ではリスナープログラムファイルlistener.oraを設定する必要がある。
クライアント接続には、tnsnames.oraを設定する必要がある。
一般的なリスナー設定：
listener.ora ファイル：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME = ORCL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(GLOBAL_DBNAME=ORCL)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora ファイルの設定：
std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
静的リスナーと動的リスナーの違い：
静的登録：リスナーはインスタンスの状態を知らない。プロセスがデータベースに接続する際に初めて知る。リモート管理データベースを使用したい場合は静的リスナーを使用する。
動的登録：リスナーはインスタンスの状態をリアルタイムに知っている。データベースがシャットダウンされると、動的にリスナーから登録解除される。そのため、リモート管理でのデータベースの起動・停止はできない。
sqlnet.oraファイルの役割：
ファイル内容
cat sqlnet.ora
This file is actually generated by netca. But if customers choose to
install "Software Only", this file wont exist and without the native
authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NTSをNONEに変更すると、sqlplus / as sysdba というパスワード不要のコマンドでOracleシステムにログインできなくなる。
以下の方法でIPアドレスのホワイトリストを開放する：
ADR_BASE = /oracle/app/grid
tcp.validnode_checking=yes
tcp.invited_nodes=(10.25.71.47,10.25.71.88)
sqlnet.oraファイルによるパスワードログイン方式の制御：SQLNET.AUTHENTICATION_SERVICES
SQLNET.AUTHENTICATION_SERVICES のパラメータには以下の3種類がある：
1. コメントアウトまたはSQLNET.AUTHENTICATION_SERVICES =(none)
2. SQLNET.AUTHENTICATION_SERVICES =(nts)
3. SQLNET.AUTHENTICATION_SERVICES =(all)
シナリオ1： AUTHENTICATION_SERVICES を設定しないかコメントアウト
このシナリオでは、パスワードなし、誤ったパスワード、正しいパスワードのいずれでもsysdbaにログインできる。
cat sqlnet.ora
#SQLNET.AUTHENTICATION_SERVICES =(none)
パスワードを入力せずにデータベースにログインできる（以下の3つの方法すべてでログイン可能）：
sqlplus / as sysdba
sqlplus sys/error_pass as sysdba
sqlplus sys/123456 as sysdba --正しいパスワード
シナリオ2：(none) または (nts) このシナリオでは、Oracleのユーザーパスワードの検証が必要
------------------------------------------------------------------------------------
sqlplus / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
sqlplus sys/error_pass as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
sqlplus sys/123456 as sysdba
------------------------------------------------------------------------------------
シナリオ3：(all) -- シナリオ1と同様、パスワードなしでログイン可能
クライアント接続時のエイリアスとインスタンス名の制御：NAMES.DIRECTORY_PATH
現在tnsnames.oraに以下の設定があるとする
cat tnsnames.ora
------------------------------------------------------------------------------------
MYCDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XAG193)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYCDB)
)
)
LISTENER_MYCDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = XAG193)(PORT = 1521))
------------------------------------------------------------------------------------
クライアントの接続方式は以下の通り：
シナリオ1：(TNSNAMES)
この場合、クライアントはtnsnames.oraから接続文字列（db_alias）を検索する。
tnsnames.oraファイルにレコードがない場合はデータベースに接続できない。
cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (none)
NAMES.DIRECTORY_PATH = (TNSNAMES)
以下の方法でのみデータベースに接続可能：
sqlplus C##XAG/xag123@MYCDB2
シナリオ2：(EZCONNECT)
tnsnames.oraファイルでdb_aliasを検索しなくても、Service Nameを使用してデータベースに接続できる。
sqlplus C##XAG/xag123@XAG193:1521/MYCDB
sqlplus C##XAG/xag123@XAG193:1521/MYPDB
インスタンス名では接続できない：
sqlplus C##XAG/xag123@MYCDB2
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
シナリオ3：(TNSNAMES,EZCONNECT)
cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (none)
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)
以下の方法すべてでデータベースに接続可能：
sqlplus C##XAG/xag123@MYCDB2
sqlplus C##XAG/xag123@XAG193:1521/MYCDB
sqlplus C##XAG/xag123@XAG193:1521/MYPDB
デッドプロセス接続の検出時間：SQLNET.EXPIRE_TIME （単位：分）
DCD：デッド接続検出。デッドセッションを検出・マークし、PMONがクリーンアップしてリソースを解放するために使用される。DCDを有効にするには、サーバ側のsqlnet.oraファイルにSQLNET.EXPIRE_TIMEパラメータを追加する。単位は分。この時間に達すると、サーバ側はクライアントに"probe"パケットを送信する。クライアントが正常であれば、このパケットは無視され、タイマーがリセットされる。クライアントが異常終了した場合、サーバ側はメッセージを受信して接続を解放する。
DCDはファイアウォールのタイムアウト防止にも使用できる。ファイアウォールのタイムアウト設定により、クライアントとサーバ間でタイムアウト時間内にデータ転送がない場合、セッションがファイアウォールによって切断される。SQLNET.EXPIRE_TIMEパラメータをファイアウォールのタイムアウト時間より短く設定することで、この状況を回避できる。
——————————————————————————————
ORA-03135: connection lost contact.
——————————————————————————————
あるB/Sアーキテクチャのアプリケーションでテスト中、1～2時間ごとに「エラーメッセージ：ORA-03135: 接続が失われました」というエラーが発生した。詳細なエラーメッセージは以下の通り：
ORA-03135の原因は多く、ネットワーク機器、オペレーティングシステム、データベースに問題がある可能性がある。最も可能性が高いのはネットワークの瞬間的な断絶とファイアウォール設定である。
テストケース： SQLNET.EXPIRE_TIME=1 （テストの便宜上、通常は10分程度に設定する）
[oracle@XAG193 admin]$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (none)
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)
SQLNET.EXPIRE_TIME=1
#クライアント側
D:\Tools\sqlcl\bin>sql C##XAG/xag123@MYCDB2 ---MYCDB2 はクライアントのtnsnames.oraで設定されたエイリアス
または
D:\Tools\sqlcl\bin>sql C##XAG/xag123@192.168.40.193:1521/MYPDB
または
D:\Tools\sqlcl\bin>sql /nolog
SQL> CONN C##XAG/xag123@192.168.40.193:1521/MYPDB
または
SQL> CONN C##XAG/xag123@//192.168.40.193:1521/MYPDB
#サーバ側（192.168.40.1 のクライアントがサーバに接続していることを確認）
[root@XAG193 ~]# netstat -pan | grep 192.168.40.193:1521
tcp 0 0 192.168.40.193:33741 192.168.40.193:1521 ESTABLISHED 1651/ora_lreg_MYCDB
tcp6 0 0 192.168.40.193:1521 192.168.40.193:33741 ESTABLISHED 1565/tnslsnr
tcp6 0 0 192.168.40.193:1521 192.168.40.1:62171 ESTABLISHED 3631/oracleMYCDB
[root@XAG193 ~]# tcpdump -i ens33 -nnn host 192.168.40.1 and port 1521
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on ens33, link-type EN10MB (Ethernet), capture size 262144 bytes
-------------------------------------------------------------------------------------------------------------------------
01:44:11.882228 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1131350432, win 353, length 0
01:44:11.882496 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0
01:45:12.042737 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1, win 353, length 0
01:45:12.043181 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0
01:46:12.203380 IP 192.168.40.193.1521 > 192.168.40.1.62171: Flags [.], ack 1, win 353, length 0
01:46:12.204089 IP 192.168.40.1.62171 > 192.168.40.193.1521: Flags [.], ack 1, win 4105, length 0
1分ごとにサーバ側がクライアントに接続し、クライアントが存在するか確認している（クライアントから常にACK応答があり、正常であることを示している）。
ユーザー接続タイムアウトの設定：SQLNET.INBOUND_CONNECT_TIMEOUT （単位：秒）
これはユーザー認証のタイムアウトを待つ時間を示し、単位は秒。デフォルト値は60秒。ユーザー認証がタイムアウトした場合、alert.logにエラーメッセージ「WARNING: inbound connection timed out (ORA-3136)」が表示され、sqlnet.logに「TNS-12535: TNS:operation timed out」エラーメッセージが表示される。
[oracle@XAG193 admin]$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (none)
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)
SQLNET.EXPIRE_TIME=1
SQLNET.INBOUND_CONNECT_TIMEOUT=10
#クライアントが誤ったパスワードでログインした場合の監視ログ
[oracle@XAG193 trace]$ tail -20f /u01/app/oracle/diag/rdbms/mycdb/MYCDB/trace/alert_MYCDB.log
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.3.0.0.0
Time: 10-JAN-2021 23:13:38
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.40.1)(PORT=50359))
2021-01-10T23:13:38.159872+08:00
WARNING: inbound connection timed out (ORA-3136)
sqlnet.oraの一般的な設定：
LOG_FILE_SERVER
[oracle@XAG193 admin]$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (none)
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)
SQLNET.EXPIRE_TIME=1
SQLNET.INBOUND_CONNECT_TIMEOUT=10
DIAG_ADR_ENABLED=off
LOG_DIRECTORY_SERVER=$ORACLE_HOME/network/log
LOG_FILE_SERVER=sqlnet_server.log
LOG_DIRECTORY_CLIENT=$ORACLE_HOME/network/log
LOG_FILE_CLIENT=sqlnet_client.log