Oracle Database 12cからのプラガブル・データベース(PDB)へ接続するには、クライアントはサービス名(SERIVCE_NAME)を指定して接続する必要があります。
ただ、希にシステム識別子(SID)指定のみでしか接続できないレガシーなアプリケーションがあります。
この場合、USE_SID_AS_SERVICE_'listener名' パラメータをONにすることで、SIDをSERIVCE_NAMEとして解釈されるようにすることができるので、SID指定でPDBへ接続することができます。
ということでRAC環境でやってみてみます。
■ RACの設定
Oracle Cloud: Real Application Clusters（RAC）作成してみてみたで構築したRACを使用してみます。
■ 設定前確認
USE_SID_AS_SERVICE_パラメータ設定前ではSID指定で接続できないことを確認します。
● tnsnames.ora設定
SERVICE_NAME指定の"pdb01"とSID指定の”pdb01_sid”２つの接続を設定します
[oracle@rac19c1 ~]$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@rac19c1 admin]$ vi tnsnames.ora
[oracle@rac19c1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pdb01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-scan.publicsunbnet01.vcn102000.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb01.publicsunbnet01.vcn102000.oraclevcn.com)
)
)
pdb01_sid =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac19c-scan.publicsunbnet01.vcn102000.oraclevcn.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = pdb01.publicsunbnet01.vcn102000.oraclevcn.com)
)
)
● 接続テスト
SERVICE_NAME指定のpdb01接続は成功し、
SID指定のpdb01_sid接続は失敗することを確認
① SERVICE_NAME指定接続
[oracle@rac19c1 admin]$ sqlplus system/Password@pdb01
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 11:38:24 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Nov 14 2019 11:37:46 +00:00
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDB01
② SID指定接続
SID指定では接続できないことを確認
[oracle@rac19c1 admin]$ sqlplus system/Password@pdb01_sid
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 11:27:32 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
Enter user-name: ^C
■ USE_SID_AS_SERVICE_listenerパラメータ設定
listener.oraへUSE_SID_AS_SERVICE_listenerパラメータを設定します。
本環境はRACなので１つのデフォルトリスナーと３つのSCANリスナーがあるため、次の４つを全ノードへ設定します。
またRACの場合、gridユーザーのORACLE_HOME内にあるので、gridユーザーで実行します
・設定内容
USE_SID_AS_SERVICE_listener=ON
USE_SID_AS_SERVICE_listener_scan1=ON
USE_SID_AS_SERVICE_listener_scan2=ON
USE_SID_AS_SERVICE_listener_scan3=ON
① gridユーザーへログイン
[opc@rac19c1 ~]$ sudo su - grid
② listener.oraバックアップ
[grid@rac19c1 ~]$ cd /u01/app/19.0.0.0/grid/network/admin
[grid@rac19c1 admin]$ cp listener.ora listener.ora_20191114
[grid@rac19c1 admin]$ ls -la
total 36
drwxr-xr-x 3 grid oinstall 4096 Nov 14 07:51 .
drwxr-xr-x 10 grid oinstall 4096 Apr 17 2019 ..
-rw-r--r-- 1 grid oinstall 1470 Nov 14 05:24 listener.ora
-rw-r--r-- 1 grid oinstall 1232 Nov 14 05:24 listener.ora.bak.rac19c1.grid
-rw-r--r-- 1 grid oinstall 1470 Nov 14 07:51 listener.ora_20191114
-rw-r----- 1 grid oinstall 1470 Nov 14 05:28 listener1911145AM2845.bak
drwxr-xr-x 2 grid oinstall 4096 Apr 17 2019 samples
-rw-r--r-- 1 grid oinstall 1536 Feb 14 2018 shrept.lst
-rw-r----- 1 grid oinstall 206 Nov 14 05:28 sqlnet.ora
③ 設定と確認
[grid@rac19c1 admin]$ vi listener.ora
[grid@rac19c1 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent
USE_SID_AS_SERVICE_listener=ON
USE_SID_AS_SERVICE_listener_scan1=ON
USE_SID_AS_SERVICE_listener_scan2=ON
USE_SID_AS_SERVICE_listener_scan3=ON
④ 他ノード設定
同様にノード２のrac19c2でも同様に設定
■ USE_SID_AS_SERVICE_listenerパラメータ設定反映
デフォルトリスナーとSCANリスナーを再起動します
RACの場合、srvctlで全ノードまとめて再起動します
① リスナー停止
[grid@rac19c1 admin]$ srvctl stop scan_listener
[grid@rac19c1 admin]$ srvctl stop listener
[grid@rac19c1 admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.COMMONSTORE.advm
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.LISTENER.lsnr
OFFLINE OFFLINE rac19c1 STABLE
OFFLINE OFFLINE rac19c2 STABLE
ora.chad
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.data.commonstore.acfs
ONLINE ONLINE rac19c1 mounted on /opt/orac
le/dcs/commonstore,S
TABLE
ONLINE ONLINE rac19c2 mounted on /opt/orac
le/dcs/commonstore,S
TABLE
ora.net1.network
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.ons
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.proxy_advm
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.LISTENER_SCAN1.lsnr
1 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN2.lsnr
1 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN3.lsnr
1 OFFLINE OFFLINE STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE rac19c1 Started,STABLE
2 ONLINE ONLINE rac19c2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.cvu
1 ONLINE ONLINE rac19c2 STABLE
ora.orcl_phx12j.db
1 ONLINE ONLINE rac19c1 Open,HOME=/u01/app/o
racle/product/19.0.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE rac19c2 Open,HOME=/u01/app/o
racle/product/19.0.0
.0/dbhome_1,STABLE
ora.rac19c1.vip
1 ONLINE ONLINE rac19c1 STABLE
ora.rac19c2.vip
1 ONLINE ONLINE rac19c2 STABLE
ora.qosmserver
1 ONLINE ONLINE rac19c2 STABLE
ora.scan1.vip
1 ONLINE ONLINE rac19c1 STABLE
ora.scan2.vip
1 ONLINE ONLINE rac19c2 STABLE
ora.scan3.vip
1 ONLINE ONLINE rac19c2 STABLE
--------------------------------------------------------------------------------
② リスナー起動
[grid@rac19c1 admin]$ srvctl start listener
[grid@rac19c1 admin]$ srvctl start scan_listener
[grid@rac19c1 admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.COMMONSTORE.advm
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.chad
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.data.commonstore.acfs
ONLINE ONLINE rac19c1 mounted on /opt/orac
le/dcs/commonstore,S
TABLE
ONLINE ONLINE rac19c2 mounted on /opt/orac
le/dcs/commonstore,S
TABLE
ora.net1.network
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.ons
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
ora.proxy_advm
ONLINE ONLINE rac19c1 STABLE
ONLINE ONLINE rac19c2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.DATA.dg(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac19c1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE rac19c2 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE rac19c1 STABLE
ora.RECO.dg(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE rac19c1 Started,STABLE
2 ONLINE ONLINE rac19c2 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE rac19c1 STABLE
2 ONLINE ONLINE rac19c2 STABLE
ora.cvu
1 ONLINE ONLINE rac19c2 STABLE
ora.orcl_phx12j.db
1 ONLINE ONLINE rac19c1 Open,HOME=/u01/app/o
racle/product/19.0.0
.0/dbhome_1,STABLE
2 ONLINE ONLINE rac19c2 Open,HOME=/u01/app/o
racle/product/19.0.0
.0/dbhome_1,STABLE
ora.rac19c1.vip
1 ONLINE ONLINE rac19c1 STABLE
ora.rac19c2.vip
1 ONLINE ONLINE rac19c2 STABLE
ora.qosmserver
1 ONLINE ONLINE rac19c2 STABLE
ora.scan1.vip
1 ONLINE ONLINE rac19c1 STABLE
ora.scan2.vip
1 ONLINE ONLINE rac19c2 STABLE
ora.scan3.vip
1 ONLINE ONLINE rac19c1 STABLE
--------------------------------------------------------------------------------
[grid@rac19c1 admin]$
● Listnerステータス確認
設定反映後、ListnerステータスにSIDに関するリスニング情報があるかと思ったのですが、確認すると、設定による変化はみられません
[grid@rac19c1 admin]$ lsnrctl stat
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 14-NOV-2019 08:27:15
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 14-NOV-2019 08:03:30
Uptime 0 days 0 hr. 23 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac19c1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.0.3)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.0.5)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB.publicsunbnet01.vcn102000.oraclevcn.com" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl_phx12j.publicsunbnet01.vcn102000.oraclevcn.com" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb01.publicsunbnet01.vcn102000.oraclevcn.com" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
■ 設定後動作確認
● SERVICE_NAME指定接続確認
[oracle@rac19c1 admin]$ sqlplus system/Password@pdb01
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 11:47:32 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Nov 14 2019 11:38:24 +00:00
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDB01
● SID指定接続確認
SID指定で接続できること確認
[oracle@rac19c1 admin]$ sqlplus system/Password@pdb01_sid
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 11:47:50 2019
Version 19.4.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Nov 14 2019 11:47:32 +00:00
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDB01
■ おまけ
● 19c 新機能 ORACLE_PDB_SID
Version 19.3.0.0.0 から OS環境変数 ORACLE_PDB_SID を使用することで OS 認証によって PDB に接続することができるようになりました。
1) ORACLE_PDB_SID 設定
[oracle@rac19c1 admin]$ export ORACLE_PDB_SID=pdb01
2) ORACLE_PDB_SID 設定確認
[oracle@rac19c1 admin]$ env | grep ORA
ORACLE_PDB_SID=pdb01
ORACLE_UNQNAME=CDB_TOKYO
ORACLE_SID=CDB1
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
3) SQL*PLUS 接続確認
[oracle@rac19c1 admin]$ sqlplus system/Password
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 3 05:41:17 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Fri Mar 03 2023 05:32:03 +00:00
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDB01
■ 参考
・Database Net Servicesリファレンス: USE_SID_AS_SERVICE_listener_name
・My Oracle Cupport: How To Connect to PDB By OS Authentication In 19c Container Database Environment (Doc ID 2729416.1)