背景：

​ 工作中，我们可能会遇到一些客户，需要通过SID的方式连接到PDB下面操作，尤其在RAC环境中，需要连到单独的节点上。

以下是基于19.3环境下，测试如何通过SID的方式，连接到PDB中。

1. 检查监听状态

[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

结论：数据库监听状态正常，包含CDB和PDB服务 。

2. 检查数据库状态

[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

结论：数据库状态正常，读写状态。 

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.

已使用的参数文件:
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.

已使用的参数文件:
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 毫秒)

结论：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: 监听程序当前无法识别连接描述符中所给出的 SID

请输入用户名:

结论：SERVICE_NAME 方式可以正常连接到数据库，而SID 方式连接数据库，抛出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，所以需要增加的参数是USE_SID_AS_SERVICE_LISTENER_ORCLCDB = ON .

2. 在RAC 环境下，我们需要修改grid用户下的listener.ora文件.

6.2 重启监听

[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>

 结论：SID的方式，也可以正常连到PDB 。

综上：以上就是在 12.1.0.1 以上版本中，需要通过SID 连接PDB数据库的解决办法。

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