背景：

? 工作中，我們可能會遇到一些客戶，需要通過SID的方式連接到PDB下面操作，尤其在RAC環(huán)境中，需要連到單獨的節(jié)點上。

以下是基于19.3環(huán)境下，測試如何通過SID的方式，連接到PDB中。

1. 檢查監(jiān)聽狀態(tài)

[oracle@ora19c ~]$ lsnrctl  status  LISTENER_ORCLCDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2022 22:44:46

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522)))

STATUS of the LISTENER

Alias                     LISTENER_ORCLCDB
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                12-JUL-2022 22:40:32
Uptime                    0 days 0 hr. 4 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/ora19c/listener_orclcdb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "a8be8cc09f902cd2e0530d0011ac912e" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

結(jié)論：數(shù)據(jù)庫監(jiān)聽狀態(tài)正常，包含CDB和PDB服務 。

2. 檢查數(shù)據(jù)庫狀態(tài)

[oracle@ora19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 12 22:44:51 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

結(jié)論：數(shù)據(jù)庫狀態(tài)正常，讀寫狀態(tài)。?

3. 配置客戶端tnsnames.ora

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

ORCLPDB1_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = ORCLPDB1)
    )
  )

4. tnsping測試

-- 測試SERVICE_NAME 方式
C:\Users\admin>tnsping orclpdb1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-7月 -2022 15:35:08

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

已使用的參數(shù)文件:
D:\app\admin\product\11.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1)))
OK (20 毫秒)


-- 測試SID方式
C:\Users\admin>tnsping orclpdb1_sid

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-7月 -2022 15:35:13

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

已使用的參數(shù)文件:
D:\app\admin\product\11.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 適配器來解析別名
嘗試連接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCLPDB1)))
OK (0 毫秒)

結(jié)論：SERVICE_NAME 方式和SID 方式，tnsping測試都沒有問題。?

5. 連接測試

-- 測試SERVICE_NAME 方式
C:\Users\admin>sqlplus sxc/sxc@orclpdb1

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:34:47 2022

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


連接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> exit
從 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 斷開


-- 測試SID 方式
C:\Users\admin>sqlplus sxc/sxc@orclpdb1_sid

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:34:52 2022

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12505: TNS: 監(jiān)聽程序當前無法識別連接描述符中所給出的 SID

請輸入用戶名:

結(jié)論：SERVICE_NAME 方式可以正常連接到數(shù)據(jù)庫，而SID 方式連接數(shù)據(jù)庫，拋出ORA-12505錯誤。

6. 解決辦法

Oracle Net 12c: How to enable Clients using SID to connect to PDB? (Doc ID 1644355.1)

SOLUTION：
Set the following control parameter in the listener.ora file and restart the listener:
USE_SID_AS_SERVICE_<listener_name> = ON

6.1 修改listener.ora文件

[oracle@ora19c admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORCLCDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 增加
USE_SID_AS_SERVICE_LISTENER_ORCLCDB = ON

注意：

?1. 我們這里的listene名稱是LISTENER_ORCLCDB，不是默認的LISTENER，所以需要增加的參數(shù)是USE_SID_AS_SERVICE_LISTENER_ORCLCDB = ON .

2. 在RAC 環(huán)境下，我們需要修改grid用戶下的listener.ora文件.

6.2 重啟監(jiān)聽

[oracle@ora19c admin]$ lsnrctl  reload  LISTENER_ORCLCDB

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-JUL-2022 22:50:32

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522)))
The command completed successfully

6.3 再次連接

C:\Users\admin>sqlplus sxc/sxc@orclpdb1_sid

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 11 15:38:15 2022

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


連接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

?結(jié)論：SID的方式，也可以正常連到PDB 。

綜上：以上就是在 12.1.0.1 以上版本中，需要通過SID 連接PDB數(shù)據(jù)庫的解決辦法。

思考： 如果需要通過SID的方式，連接CDB 也需要修改listener.ora文件嗎？ 答案: 不需要。