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

Leave a Reply