oracle 12c 以上，如何使用sid 连接pdb ? -m6米乐安卓版下载

背景：

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

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

[oracle@ora19c ~]$ lsnrctl  status  listener_orclcdb
lsnrctl for linux: version 19.0.0.0.0 - production on 12-jul-2022 22:44:46
米乐app官网下载 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服务 。

[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
米乐app官网下载 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

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

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

-- 测试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
米乐app官网下载 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
米乐app官网下载 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测试都没有问题。 

-- 测试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
米乐app官网下载 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
米乐app官网下载 copyright (c) 1982, 2010, oracle.  all rights reserved.
error:
ora-12505: tns: 监听程序当前无法识别连接描述符中所给出的 sid
请输入用户名:

结论：service_name 方式可以正常连接到数据库，而sid 方式连接数据库，抛出ora-12505错误。

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_ = 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
米乐app官网下载 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
米乐app官网下载 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文件吗？ 答案: 不需要。