python oracledbを使って接続時にSID “xxxx” is not registered with the listener at host…が発生

やったこと
先にまとめ
背景
調査してわかったこと
最後に

やったこと

python のOracle Client lib oracledb を使用してOracle DBへ接続を試したが、接続エラーSID "xxxx" is not registered with the listener at host...が発生
dsnの書式を変えることで正常に接続できた
SERVICE_NAMEとSIDの違いを調べた

先にまとめ

SIDではなく、SERVICE_NAMEの指定が必要
OracleのSERVICE_NAMEとSIDの違い
- ホスト外からアクセスする場合(tnsnames.oraを使用してアクセスする場合)はSIDを指定して接続することは不可であることがわかった
- ローカルのインスタンスに接続する場合はSIDを使用する

背景

python のOracle Client lib oracledb を使用してOracle DBへ接続を試したが、下記の接続エラーが発生した

oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=wVMlnVM1FPZvRPUYWwP/LA==).
DPY-6003: SID "database_xxx" is not registered with the listener at host "host_name_xxx" port 1521. (Similar to ORA-12505)

ORA-12505について調べてみると、hostには接続できていてlistenerからDBへの接続でエラーになっている様子
検索してみると「接続情報が正しいか確認しろ」という内容の記事が多くヒットした

その時のpythonコードは下記

dsn = oracledb.makedsn('host_name_xxx', '1521', sid='database_xxx')
conn = oracledb.connect(user='user_name_xxx', password='password', dsn=dsn)

調査してわかったこと

参考: 簡易接続ネーミング・メソッドを使用した接続の指定

tnsnames.oraファイルがなくても、下記の書式のいずれかに従えばアクセスが可能
host/service_nameまたは(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)...

実際にdsnに設定した文字列と接続結果

oracledb.connect(user='user_name_xxx', password='password', dsn=dsn)のdsn部分にセットした文字列別の接続結果です

dsnに設定した文字列	結果
`(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name_xxx)(PORT=1521))(CONNECT_DATA=(SID=database_xxx)))`	失敗
`host_name_xxx:1521/database_xxx`	成功
`(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name_xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=database_xxx)))`	成功

なぜoracledb.makedsn()で生成された`(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)...`では接続できなかった？

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=shobeen) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sales_us)))”
易接続ネーミング・メソッドを使用した接続の指定

とoracledb.makedsn()で生成される文字列を比べてみると

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name_xxx)(PORT=1521))
(CONNECT_DATA=(SID=database_xxx)))

SERVICE_NAMEとSIDの部分が違っていたことに気がついた…
そういえば今までSIDとSERVICE_NAMEの違いを詳しく認識していなかったのでもう少し調べてみました

SIDとSERVICE_NAME

システム識別子(SID)	ホスト内でインスタンスにアクセスするための識別子
サービス名	主に一つのインスタンスを指す。外部から接続させる名前（インスタンス名とドメイン名を合わせた名前）

参考: Oracle SID、インスタンス、接続識別子など

tnsnames.oraの説明

tnsnames.oraはホスト外からアクセスするためにclient側に配置する接続情報のファイル
ホスト外からアクセスする場合(tnsnames.oraを使用してアクセスする場合)はSIDを指定して接続することは不可
ローカルのインスタンスに接続する場合はSIDを使用する

ホスト外からアクセスする場合はSIDではなくSERVICE_NAMEを指定してあげましょう、ということなのでoracledb.makedsn()のsid=部分をservice_name=に変えれば、(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host_name_xxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=database_xxx)))が生成され正常にアクセスできた