0

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
LW001
  • 2,452
  • 6
  • 27
  • 36
user1863063
  • 21
  • 7

1 Answers1

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:

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;
/
pmdba
  • 6,457
  • 2
  • 6
  • 16
  • I was trying to add to tnsnames.ora two entries where I specify SERVICE_NAME and SID per each entry. Service name is the same in both cases however SID is different. Using sqlplus `@`Entry1 or `@`Entry2 connects me to the same pluggable, as if not looking at SID at the same time, hence no joy. How do add/modify service name? – user1863063 Jul 17 '20 at 16:05
  • service_name and sid can't be used together in the same connect string, and PDBs don't have an SID. – pmdba Jul 17 '20 at 16:11
  • I created MY10PDBCL service and connected to the my pluggable db. I cannot delete the old MY10PDB (it says it is running, so I tried to stop it). exec DBMS_SERVICE.stop_service('MY10PDB'); BEGIN DBMS_SERVICE.stop_service('MY10PDB'); END; * ERROR at line 1: ORA-44793: cannot stop internal services ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 95 ORA-06512: at "SYS.DBMS_SERVICE", line 519 ORA-06512: at line 1 The same error when I try to stop it from CDB level, even though dba_services do not have a service my10pdb on this CDB level. – user1863063 Jul 21 '20 at 18:41
  • And yet, looking at the link to 12c database. Why would you use: ALTER SESSION SET CONTAINER=pdb2 SERVICE=my_new_service_1b; if ALTER SESSION SET CONTAINER=pdb2 works on its own (without SERVICE). Is it because for some reason they want to explicitly use service my_new_service_1b, not service my_new_service_2b? I am testing that in 19c. – user1863063 Jul 21 '20 at 18:45
  • You can only use the "alter session" commands if you are connected to the _container_ database, not the pluggable database directly. Ideally you should have renamed your cloned database when you created it, and all the related service names would have been changed automatically. You really shouldn't have two pdb's with the same name on the same server, even if they are in different containers. – pmdba Jul 21 '20 at 20:29