sid and pluggable

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with


sqlplus "scott/tiger@
(description=(address=
(host=srv01)(port=1521)(protocol=tcp))
(connect_data=(service_name=DB01.example.com)))"


But one of your application has hardcoded SID=DB01


sqlplus "scott/tiger@
(description=(address=
(host=srv01)(port=1521)(protocol=tcp))
(connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of
service requested in connect descriptor


How do you do the trick?

• Remove the domain name in the pluggable


alter session set container=DB01;
alter system set db_domain=''
container=current
scope=spfile;


• You use the listener parameter USE_SID_AS_SERVICE

listener.ora

USE_SID_AS_SERVICE_LISTENER = on


You restart the listener and the database.

sqlplus "scott/tiger@
(description=(address=
(host=srv01)(port=1521)(protocol=tcp))
(connect_data=(sid=DB01)))"
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0


You just connected with SID to a pluggable database