Mittwoch, 8. März 2017

force connections to use SERVICE_NAME

During the setup of a project I had a small observation: The Oracle Database is running on a 4 node RAC, but we (DBAs) wanted to provide only 2 instances to the application team.

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 EDWP123
so I created a binary there:
ln -s /bin/false  /tmp/berx1/bin/oracle
and 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.