Listener parameter use_sid_as_service_listener

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 (SID = myservice) is (service_name = myservice).

Two precautions:

1. 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.

2. If the database is RAC, it's tricky to set HOST to SCAN of the cluster. Setting it to the VIP or hostname of a specific node readily works. (Of course VIP is preferred over 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.) To achieve failover and load balance capability, you can specify multiple ADDRESS lines like in the old days of Oracle10g, with LOAD_BALANCE parameter. The only problem is that the old client that insists on SID and cannot use service_name may not be able to specify multiple ADDRESS'es.

There are two ways to get SCAN to work. You can set the parameter for SCAN listeners, i.e. add
USE_SID_AS_SERVICE_LISTENER_SCAN1=ON
USE_SID_AS_SERVICE_LISTENER_SCAN2=ON
USE_SID_AS_SERVICE_LISTENER_SCAN3=ON
to listener.ora (in addition to USE_SID_AS_SERVICE_LISTENER=ON) and bounce the scan listeners. Or add a TCP ADDRESS to the LISTENER line, i.e.
change
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
to
LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))))
and bounce the listener LISTENER. Note the new LISTENER line is different on each node due to hostname.

The problem with using SCAN is that you still don't get the failover and load balance features as you would when you use a real service, i.e. when the TNS entry ends with service_name=.... That's the limitation of using this parameter to fake a service.[note]

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)

______________________________
[note]
You can easily test the failover feature. Once your session using the fake sid (i.e. service for real) is connected, from another session, you disconnect the first session with alter system disconnect session '<session sid>,<serial#>'. Then in the first session, try to run any SQL, and you'll get "ORA-00028: your session has been killed". Test again by changing "sid" in the TNS stanza to "service_name" so that the connect identifier really uses service name instead of the one "faked" as sid through the magic of the listener parameter. The session thus established can survive a disconnect from another session. The error yuo get will be "ORA-25408: can not safely replay call". And on next SQL execution, it happily connects to an instance and runs the new SQL. It seems that USE_SID_AS_SERVICE_LISTENER_<listener> does its job by fooling the clients that don't know service. But there may be a small bug in its implementation that prevents the session to fully take advantage of the service, i.e. not just for the client to get into PDB (instead of CDB), but also being able to fail over and (I didn't test) load balance.