how to connect to pluggable database when there are two instances

Question

I have two container databases.

1. SID my10, with service name M10CDB
2. SID my10c with service name MY10CL (it is a clone of the first one)

Both of these containers have two pluggable databases, my10pdb and pdbseed.

How do I distinguish between these two pluggables, i.e. my10pdb, belonging to different containers, if I want to connect directly to them?

lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 17-JUL-2020 14:45:26

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYUPG05.myworld.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                16-JUN-2020 02:22:27
Uptime                    31 days 12 hr. 22 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\oracle\diag\tnslsnr\MYUPG05\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MYUPG05.myworld.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "3541dad180144e9fb63afda77b213f89" has 2 instance(s).
  Instance "my10", status READY, has 1 handler(s) for this service...
  Instance "my10c", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "MY10CDB" has 1 instance(s).
  Instance "my10", status READY, has 1 handler(s) for this service...
Service "MY10CL" has 1 instance(s).
  Instance "my10c", status READY, has 1 handler(s) for this service...
Service "MY10XDB" has 2 instance(s).
  Instance "my10", status READY, has 1 handler(s) for this service...
  Instance "my10c", status READY, has 1 handler(s) for this service...
Service "my10pdb" has 2 instance(s).
  Instance "my10", status READY, has 1 handler(s) for this service...
  Instance "my10c", status READY, has 1 handler(s) for this service...
The command completed successfully

Answer 1

They will have to have unique service names. If they share the same service name then the listener will treat them like it would a RAC database and connect you to one of them randomly.

Take a look at these articles, which describes your issue in detail:

• https://oracle-base.com/articles/12c/multitenant-rename-services-during-pdb-creation-12cr2

• https://oracle-base.com/articles/10g/dbms_service#modify-service

Use DBMS_SERVICE to create a new service in your new PDB and drop the old one.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'my_new_service',
    network_name => 'my_new_service'
  );
END;
/

BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'my_new_service'
  );
END;
/

BEGIN
  DBMS_SERVICE.stop_service(
    service_name => 'my_old_service'
  );
END;
/

BEGIN
  DBMS_SERVICE.delete_service(
    service_name => 'my_old_service'
  );
END;
/