One of the major annoyances with Oracle EBS CDB/PDB environment settings I found that, I cannot connect to the PDB (for EBS) from the CDB environment (default). I always have to source the PDB environment in order to connect the PDB and do my stuffs.
As usual, I am doing this for my LAB & you may not copy this to your PRODUCTION environment.
So this what I get when I try to connect to PDB from CDB environment.
email@example.com:/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin>tnsping DEVP TNS Ping Utility for Linux: Version 184.108.40.206.0 - Production on 22-MAR-2022 14:59:03 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: /u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora TNS-03505: Failed to resolve name
The easiest solution was to add an additional entry for the PDB within the $TNS_ADMIN tnsnames.ora file. Let us see how to. Please note these might get overwritten when you run autoconfig.
Save the file & you are ready to go. Notice carefully the service name and instance name values for “DEVP” and “DEVPCDB”. The PDB will have own name for the service, however the instance name will point towards the CDB name & this is the way it should be.
firstname.lastname@example.org:/home/oracle>tnsping DEVP TNS Ping Utility for Linux: Version 220.127.116.11.0 - Production on 22-MAR-2022 15:06:20 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: /u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST=erp-devp.xyz.com)(PORT=1526)) (CONNECT_DATA = (SERVICE_NAME=DEVP)(INSTANCE_NAME=DEVPCDB))) OK (0 msec) email@example.com:/home/oracle>
That’s all. Regardless whether it is EBS or not, you can keep adding the PDB details into the tnsnames.ora file & if the service is registered with the listener, will able to connect without sourcing the specific environment variables.