0

I'm quite new to Oracle. I created a CDB like cdb1 and then a PDB within like pdb1. Now I can connect to my PDB using something like:

sqlplus user/password@server:port/pdb1

But can't I have two PDBs with the same name in different CDBs? If that happened, sqlplus wouldn't know which PDB I wanted to connect to. Does the listener have some robust way to map the PDB passed in the connection to an actual PDB within a CDB on the machine where the listener is running?

EDIT: I found out that when I write pdb1 in the sqlplus connection identifier, that is actually not the name of the PDB, but the name of the service, which just happens to be the same as the name of the PDB.

As far as I understand, I can map the service name to PDB name using select name, pdb from v$services;.

It seems to map services to CDBs I need to type lsnrctl service. And then I get output like:

Service "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

So I guess the instance cdb1 is referring to the CDB cdb1? Or does it also just happen to have the same name?

Then again, surely the service stating that it has 1 instance, means it could have 2 instances and these could point to separate CDBs, which brings me back to my original question of how to differentiate different PDBs with the same name.

  • Simply configure a separate listener for these two CDBs. – JSapkota Feb 9 '18 at 22:49
  • Why would you have two CDB's on the same server? Much less with each having a pdb of the same name? That defeats the whole purpose of the multi-tenant architecture. – EdStevens Feb 9 '18 at 22:54
  • @EdStevens maybe you wouldn't, but I am trying to broaden my understanding of how the connection identifier works so I ask this theoretical question. Certainly Oracle as a product supports you having more than one CDB. There are perhaps a couple of reasons why you want this, (1) the two CDBs may have completely different people who have DBA access to each; (2) you may want to have two PDBs of the same name for different purposes so you would have to put them in different CDBs. – Kidburla Feb 9 '18 at 23:10
  • @EdStevens Also, as a data architect, my alarm bells were ringing when I saw this syntax. It seems that the PDB name is something which is underneath a CDB, but here it is being referred to directly under a listener. This "feels" wrong as a listener may potentially represent multiple CDBs. So this made me think maybe I have misunderstood how the metadata model works in Oracle. – Kidburla Feb 9 '18 at 23:13
  • With server:port you select a listener, and each service on that listener must have a unique name. If you want to have two CDBs with the same PDB and service name (which is not a good idea), then you need to have different listeners. Or you make sure the service name is different from the PDB name (which is fine for application specific services but not so good for the default service) – eckes Feb 10 '18 at 4:32
0

Quite easily.

The full syntax for Easy Connect is:

Understanding the Easy Connect Naming Method

CONNECT username@[//]host[:port][/service_name][:server][/instance_name]

You just need to specify the service_name and the instance_name as well.

$ lsnrctl services
...
Service "pdb1" has 2 instance(s).
  Instance "RAIN", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
  Instance "WIND", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

You will have more than 1 instance serving the same service. The below may connect to any of the instances, but the listener decides which instance it forwards to your request, not you:

sqlplus user/password@localhost:1521/pdb1

The below commands always connect to just one of the instances. If that instance is down, the connection attempt fails, even though there is another instance providing the same service:

sqlplus user/password@localhost:1521/pdb1/rain
sqlplus user/password@localhost:1521/pdb1/wind

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.