Docker容器中Oracle 19c PDB连接报错ORA-12505问题咨询

在M1芯片Mac上通过Docker部署Oracle 19c容器后，容器运行正常，能通过以下JDBC连接字符串以SYS as SYSDBA身份连接CDB：

jdbc:oracle:thin:@localhost:1521:ORCLCDB

但尝试用相同用户连接PDB时，使用以下字符串报错ORA-12505, TNS:listener does not currently know of SID given in connect descriptor：

jdbc:oracle:thin:@localhost:1521:orclpdb1

相关配置文件与命令输出 #

tnsnames.ora内容 #

ORCLCDB=localhost:1521/ORCLCDB
ORCLPDB1= 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

lsnrctl status输出 #

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-MAY-2024 11:09:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                02-MAY-2024 10:37:43
Uptime                    0 days 0 hr. 31 min. 50 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/8f9c98887a34/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=8f9c98887a34)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "175ff9ecdffe0bf1e063020011acc4b1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
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 "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

lsnrctl service输出 #

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-MAY-2024 11:21:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "175ff9ecdffe0bf1e063020011acc4b1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: 8f9c98887a34, pid: 127>
         (ADDRESS=(PROTOCOL=tcp)(HOST=8f9c98887a34)(PORT=43941))
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Docker容器网络设置 #

"NetworkSettings": {
        "Bridge": "",
        "SandboxID": "5350ff9f19145e9b4edac8d3bbfecb6d0d71dd8cbb926dfbbff5a5df650184fc",
        "SandboxKey": "/var/run/docker/netns/5350ff9f1914",
        "Ports": {
            "1521/tcp": [
                {
                    "HostIp": "0.0.0.0",
                    "HostPort": "1521"
                }
            ]
        },
        "HairpinMode": false,
        "LinkLocalIPv6Address": "",
        "LinkLocalIPv6PrefixLen": 0,
        "SecondaryIPAddresses": null,
        "SecondaryIPv6Addresses": null,
        "EndpointID": "2ab49d1627fa8f9b3f332530bfdd01f70625ff48eb86d35dc2435b21281aa522",
        "Gateway": "172.17.0.1",
        "GlobalIPv6Address": "",
        "GlobalIPv6PrefixLen": 0,
        "IPAddress": "172.17.0.2",
        "IPPrefixLen": 16,
        "IPv6Gateway": "",
        "MacAddress": "02:42:ac:11:00:02",
        "Networks": {
            "bridge": {
                "IPAMConfig": null,
                "Links": null,
                "Aliases": null,
                "MacAddress": "02:42:ac:11:00:02",
                "NetworkID": "937f9ef40dec7144bd373b8505b7611207c1f8f699620288b81c0923d3cec830",
                "EndpointID": "2ab49d1627fa8f9b3f332530bfdd01f70625ff48eb86d35dc2435b21281aa522",
                "Gateway": "172.17.0.1",
                "IPAddress": "172.17.0.2",
                "IPPrefixLen": 16,
                "IPv6Gateway": "",
                "GlobalIPv6Address": "",
                "GlobalIPv6PrefixLen": 0,
                "DriverOpts": null,
                "DNSNames": null
            }
        }
    }

主机IP地址 #

主机ifconfig输出的IP地址：172.17.10.58

核心原因 #

你使用的JDBC连接字符串是SID格式（最后一段用冒号分隔），但ORCLPDB1是PDB的服务名，不是SID。Oracle 19c中，CDB的SID与实例名一致（此处为ORCLCDB），而PDB默认没有独立SID，只能通过服务名连接。

从lsnrctl status输出可以确认，监听器已注册orclpdb1服务，但没有对应的SID记录。

解决方法 #

修改JDBC连接字符串为服务名格式（用斜杠分隔）：

jdbc:oracle:thin:@localhost:1521/orclpdb1

或者使用完整TNS描述符格式：

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)(SERVER=DEDICATED)))

额外说明 #

• SID是实例标识符，一个Oracle实例对应一个SID；服务名是客户端连接用的逻辑名称，一个实例可对应多个服务名（如CDB和PDB各对应一个）。
• 你的tnsnames.ora中ORCLPDB1的配置是正确的服务名方式，也可以通过TNS别名连接：

  jdbc:oracle:thin:@ORCLPDB1
  

  前提是客户端已正确配置tnsnames.ora并指定TNS_ADMIN环境变量。

内容的提问来源于stack exchange，提问作者Rob The Ranger