If an application insists on connecting to Oracle using SID instead of service, Oracle provides a way to work around the problem. You add
use_sid_as_service_listener=on
to listener.ora (in <GI_Home>/network/admin if you have both <GI_Home> and <DB_Home>) and stop and re-start listener (just the plain listener, not any SCAN listener if you have them). After that, a TNS entry like
myconn =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydbhost)(PORT = 1521))
(CONNECT_DATA =
(SID = myservice)
)
)
will be interpreted by the listener as if the line
(SID = myservice)
is
(service_name = myservice)
Two precautions:
1. If the database is RAC, HOST must not be SCAN. It has to be the VIP or hostname of a specific node. Of course you would use VIP instead of the actual hostname, because at least it gives you failover capability when this node completely fails and the VIP is relocated to the other node. Since you can't use SCAN, to achieve failover and load balance capability, you can specify multiple ADDRESS lines like in the old days of Oracle10g, with LOAD_BALANCE directive.
2. If you do want to connect to a specific SID as Oracle Enterprise Manager does, it will fail, because
(SID = mysid)
is wrongly interpreted as
(service_name = mysid)
and of course there's no such service named mysid. See also Oracle Doc 2099053.1.
The ultimate solution is to talk to the software developer. They may be aware of the problem and may provide a yet-to-be-documented solution, as in the case of Oracle MICROS POS Systems, which is installed with the DB_CONN_TYPE parameter, undocumented as of November 2022:
C:\temp>Install_9.1.0030.12301.exec -DDB_CONN_TYPE=Service
We are two decades after Oracle initially suggested we not use SID but use service to connect to the database. With the advent of multitenant architecture, not making this change is equivalent to refusing to support Oracle as the backend.
Ref:
Documentation
Oracle Net 12c: How to enable Clients using SID to connect to PDB? (Doc ID 1644355.1)