Pluggable Database Connection Using 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 my 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 we 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, the SID connection can only land the connection in the root container and that too will not function as connection credentials will exist in a pluggable database (unless it's a common user).


Since it's a third-party application, it's not possible to change or enhance the behavior overnight and needed an administrator intervention. An initial look would make you feel there is no solution to this problem, as PDB can 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 you look at listener status where your 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, we can use the 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 the listener once the parameter has been placed.



Summary

For some legacy applications, we may need to allow the use of the SID connection descriptor even though 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. If they can use a service-based connection, then would suggest modifying the connection to use the service method in place of SID. 


Reference

Oracle Net 12c: How to enable Clients using SID to connect to PDB? (Doc ID 1644355.1)




Comments

  1. Agam25 August 2021 at 08:25

    one quick question: if i i understand correctly, it would only be possible if we have only 1 PDB in that CDB.

    if we have more than 1 PDB in that CDB then only way to connect is using service name?

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Solve - "WAIT FOR EMON PROCESS NTFNS"

Event Monitor Process in short called as EMON is generally used by oracle to send notification. Often applications uses Database Change Notification process also called as DBCN to be aware of changes in the database, based on these notifications they take further actions. Often clients subscribe to the database and notifications are send as callback to them. In some cases you will notice database sessions are showing wait event "WAIT FOR EMON PROCESS NTFNS" and it can even make your database un-responsive. One observation can be these processes are taking more CPU (may not in be in all cases). As stated EMON process deals with database notification and it's spawn and gets down itself and Administrators have no control on theses processes. Anyone can go through oracle documents to read more EMON functioning process.  EMON uses oracle advanced queues and database queue notifications into them and EMON process these notifications to clients based on their subscriptions. In s
Read more

Query Regression - "OR" Transformation Oracle 19c

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte
Read more

ORA - 12537: TNS: connection closed

Often when active clone to be done to create a new environment, we land up into couple of issues. I will discuss about one issues during this process. Action : Create a new database on different machine from a source. Error : ORA - 12537: TNS: connection closed In order to duplicate the environment was using below rman log /tmp/duplicate.log connect target sys/<password>@<source tns alias> connect auxiliary sys/<password>@<target tns alias> run{ allocate channel ch1 device type disk; allocate channel ch2 device type disk; allocate channel ch3 device type disk; allocate channel ch4 device type disk; allocate auxiliary channel ax1 device type disk; allocate auxiliary channel ax2 device type disk; allocate auxiliary channel ax3 device type disk; allocate auxiliary channel ax4 device type disk; DUPLICATE TARGET DATABASE TO <TARGET_DBNAME> FROM ACTIVE DATABASE NOFILENAMECHECK; } While making connection to Auxiliary database received mentioned error ORA - 12537:
Read more