The USE_SID_AS_SERVICE_LISTENER=ON saga
If you are migrating to multi-tenant architecture of the Oracle database, you’ll find out that in order to connect to the pluggable database (PDB) you must use the service name for the PDB. But, what if you have a legacy application that can only use SID instead of the service name?
For that, you can enable the parameter USE_SID_AS_SERVICE_LISTENER in the listener configuration, so the service name will act as an SID.
Problem solved, right? Well, the problem is now with applications that require the service name and not the SID. One of them is Oracle Enterprise Manager (EM). If you turn on this parameter at the listener level then, you won’t be able to connect to the databases and they will show down (red) in the EM console.
So, what can we do? In EM 13.5 RU16 or higher there’s functionality that allows EM to connect to the database by using an Agent preferred connect descriptor. This way, you can provide the connect descriptor using the service name.
One important note to mention is that you must have RU16 or higher applied to both, the OMS and the Agents in order to enable this functionality.
Follow the My Oracle Support Note “EM 13c: How To Enable Agent Preferred Connect Descriptor Support For RAC Instance, Single Instance DB (Doc ID 2963079.1) ” to enable this functionality. You will have to restart your OMS, so be prepared.
After you enable this setting, navigate to the Monitoring Configuration of your target and look for the new Agent preferred descriptor section. It looks like the image below.
Add the proper connect descriptor and viola! EM is now able to connect to your target.
Is always recommended to turn the USE_SID_AS_SERVICE_LISTENER parameter off after migrations and upgrades. Sometimes this is not an option if you have legacy applications though. This feature allows you connect your EM when the parameter USE_SID_AS_SERVICE_LISTENER must be turned on.
Thanks,
Alfredo