I'm in need of an explanation for this issue I have.
I installed a docker container with oracle 19 on my Mac with M1 processor following this guide: https://itnext.io/oracle-on-arm-mac-m1-m2-docker-images-99ed67ed6ba6
The docker container is up and running and I can connect to a database with this connect string:
jdbc:oracle:thin:@localhost:1521:ORCLCDB
I use a user with name "SYS as SYSDBA" for it and the appropriate password.
To my knowledge there should also be a possibility to connect using this connect string:
jdbc:oracle:thin:@localhost:1521:orclpdb1
Using the same user here.
I do get an error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
I do not understand why I get this error. Can someone please explain?
This is the content of my tnsnames.ora file:
ORCLCDB=localhost:1521/ORCLCDB
ORCLPDB1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPDB1)
)
)
This is the output of: 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
and this is the output of 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
This are the network settings of my docker container(?):
"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
}
}
}
If I run ifconfig on the host machine then this is the ip address: 172.17.10.58
I can explain.
host:port:SIDhost:port/SERVICE_NAMEThis is all boils down to SID <> SERVICE_NAME .... but both SID and SERVICE_NAME can have the same value, but are different constructs. So it is confusing.
SID (System IDentifier) denotes the very database instance to connect to. If you have an 8 node db-cluster, you will each and every time hit this one instance endpoint host:1521:(memory+process). If you take this node down, your clients will no longer be able to connect. So connection by SID is not good for availability.
SERVICE_NAME is an abstraction construct where you can hide a beast of a db-cluster serving this SERVICE_NAME. A SERVICE_NAME can also have tons of properties for HA (timeouts,retries,trans replay, etc..). A pluggable database will always expose it's name as a SERVICE_NAME. This is fine to use for admin purposes, but not from any application.
But for most vanilla test environments you only have one instance and connecting by SID will work. But this was discouraged by Oracle some 20 years ago.... So, you should NEVER connect by SID, .. unless you have a very awkward application which does not support SERVICE_NAME.
For your spesific case, use (port is default 1521 and can be left out):
jdbc:oracle:thin:@localhost/orclpdb1
Only the CDB ORCLCDB is defined/identified by memory+process so you can connect to it by SID. Your PDB orclpdb1 is served by memory+process by the CDB ORCLCDB and can only be accessed by SERVICE_NAME. This is the Oracle Database multitenant architecture.
Best of luck!