SID in a multitenant world

I came across an interesting issue today that i thought was worth sharing that will affect some legacy style connections to the database.

Many years ago (and I'm talking a lot here) oracle introduced the concept of using SERVICE_NAMES rather than SIDS for connecting to the database - there are lots of reasons for doing this (which i won't go into) - suffice to say the change to SERVICE_NAMES is a good thing.

However many many application connect strings still continue to use SID - however in 12c with multitenant (and only multitenant) there is an issue.

SID's can't work with multitenant

So for example

sidmarker =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
    (CONNECT_DATA =
      (SID = marker)
    )
  )

The tnsalias above cannot connect to a PDB - it just won't work - a SID relates directly to an instance and that doesn't fit in a PDB world

So how do we resolve that - are we forced to changed everything to service names?

No is thankfully the answer - Oracle have obviously realised this will be an issue for some and have modified the listener to be able to cope with this - it's not switched on by default but if you add the config line

USE_SID_AS_SERVICE_LISTENER=on 

What that does is allow the listener to transparently route any connection requests asking for a SID to a SERVICE_NAME of the same name

So in the case above the tnsnames entry gets transformed essentially into this

sidmarker =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = marker)
    )
  )

And everything works OK - no changes needed on the client.

I've not checked but i would imagine this completely removes the need for any static entries now in the listener.ora for SIDS - everything could be replaced with dynamic service names as long as this parameter is set.

This means we get to see all connections and metrics relative to servies.