PDB Connection with SID

Problem Statement

Lately, there have been some questions post upgrading an oracle database from version 12c to 19c and moving to Multi-tenant architecture. One specific question catches attention, where a third-party application only uses the SID (not service) method as a connection string to connect with the database. It was working fine with 12c legacy architecture, as using the SID method one can connect to the specific instance of an oracle database. With Multi-tenant architecture when the application needs to connect to a pluggable database aka PDB, PDB connection with SID can only land the connection in the root container and that too will not function because connection credentials will exist in a pluggable database (unless it’s a common user).
Since this is a third-party application and it’s not possible to change or improve its behavior overnight, administrator intervention is required. An initial view shows that there is no solution to this problem. Connection with PDB must only be connected using the service method.
Error code while making a connection to pluggable database using SID can be “ORA-12505: TNS: listener does not currently know of SID given in connect descriptor”

Findings

Usually, if one look at listener status where pluggable database is registered, the PDB database will be registered as a service, not an instance. This instance will be registered with the container database. Oracle provides a parameter USE_SID_AS_SERVICE_<listener_name> = ON, which once set under listener.ora will allow the SID connection for the pluggable database.

Solution

As mentioned in the finding section above, using below-mentioned parameter to be placed in the listener.ora for the database listener used for PDB connection.

                  USE_SID_AS_SERVICE_<listener_name> = ON

This parameter will allow the system identifier (SID) in the connection descriptor to be replaced or interpreted as a service name. This will allow clients using SID as a pluggable database service name to make a connection, with the PDB database like they use to do before moving to Multi-tenant architecture.
For Example, the snippet below will show usage of this parameter in listener.ora for pluggable database orcl:


  LISTENER =      (DESCRIPTION =         (ADDRESS=(protocol = tcp)(HOST=<myhostname.domain>)(port = 1777))        (CONNECT_DATA=(SERVER=DEDICATED)(SID = <orcl>))     )      

USE_SID_AS_SERVICE_LISTENER = ON

Note: Bounce listener after parameters are entered into the configuration.

Summary

For some legacy applications, we may need to allow the use of the SID connection descriptor. Connection through the service method is the preferred way. Use of parameter USE_SID_AS_SERVICE_<listener_name> = ON would come to rescue from this situation, but this may not be the case with every application. Service-based connections are preferred over SID based connections. The PDB connection with SID can now be established.