LoginSignup
12

More than 1 year has passed since last update.

Oracle Database: SID指定でPDBにアクセスしてみてみた

Last updated at Posted at 2019-11-14

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”2つの接続を設定します

[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なので1つのデフォルトリスナーと3つのSCANリスナーがあるため、次の4つを全ノードへ設定します。
また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

④ 他ノード設定

同様にノード2の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)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
12