So we created a dedicated service with Preferred instances: inst1,inst2 and Available instances: inst3.inst4
But after some minutes I saw sessions running on all 4 instances. Those sessions used the Service SYS$USERS - so they managed to "guess" the SID and created a connetionstring with SID, not SERVICE_NAME.
We contacted them and asked to use the connection string we provided (with SERVICE_NAME) which was no problem at all.
But the question remains - how to avoid connections with SID?
One idea was to create a after logon on database trigger and there check for special allowed users, hosts, or whatever. This seems fine, but there are situations, where SYS$USERS are used by default even from users, which should not use it for connections. One example are scheduler jobs. this can be solved by changing the SERVICE of DEFAULT_JOB_CLASS to the desired service:
DBMS_SCHEDULER.SET_ATTRIBUTE ('DEFAULT_JOB_CLASS','SERVICE','PREFERRED_SERVICE');
This requires additional actions and care - and a good documentation to avoid future issues.
As my initial question was to stop connections using SID through the listener, not interfere with generic bequeath connections. So I focused on the listener:
Even the SID is registered automatically when an instance registers to the listener, it can be specified manually as well.
So I tried to abuse this feature a little bit.
My config:
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (ORACLE_HOME=/tmp/berx1) (SID_NAME=ORCLSID)) )
but the listener searches for an oracle binary in this fake ORACLE_HOME
TNS-01201: Listener cannot find executable /tmp/berx1/bin/oracle for SID EDWP123so I created a binary there:
ln -s /bin/false /tmp/berx1/bin/oracleand now the listener started fine.
A connection attempt using the SID fails now with
ERROR: ORA-12537: TNS:connection closed
and the listener.log shows the errors
08-MAR-2017 11:14:20 * (CONNECT_DATA=(SID=ORCLSID)(CID=(PROGRAM=sqlplus)(HOST=myhost)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=50714)) * establish * ORCLSID * 12518 TNS-12518: TNS:listener could not hand off client connection TNS-12547: TNS:lost contact TNS-12560: TNS:protocol adapter error TNS-00517: Lost contact Linux Error: 32: Broken pipe
That's more or less a solution to my question.
Of course I do not recommend to implement it, at least not without proper testing.
Especially in a DataGuard setup a SID connection is required for some activities, so there a dedicated listener for DataGuard activities is required in such a configuration.