I had a talk with a customer/colleague that got me thinking about a strange case of multitenant configuration. The chat was about an environment with two data centers and data guard.
During the discussion, I realized that there might be an issue with listener services, so I checked it and this post is the result
The thing that got me thinking about this configuration was a case where we have 2 data centers, and a database server in each data center, while we are using data guard between the two.
Let me explain by example:
I have one data center here in Vancouver (BC), and the other in Toronto (ON). I have a database server in each data center, and I wish to use the same physical server to hold the standby of the database in the other data center. So I’ll have 2 multitenant instances on each server (a primary one and a standby one):
BC_SERVER will contain the instances CA1_BC (primary) and CA2_BC (standby)
ON_SERVER will contain the instances CA2_ON (primary) and CA1_ON (standby)
I hope it makes sense.
And now the bottom line. In this configuration we have 2 multitenant instances on the same server. If, for some reason, we need a PDB with the same name on both sites, we will run into a service issue.
Let’s say we have PDB called DATA on both servers. What will happen with the services on the servers? We know that a PDB is dynamically registered in the listener with the PDB name. In this case we will have 2 entries for the same service. And even if this works in standby mode (meaning, the listener won’t connect us to the standby one, but to the primary one), what will happen when we failover one of the instances to the other server?
I didn’t check it with a standby database, but I did with 2 regular instances. In my environment I have 2 instances (DB1 and DB2), each has a single PDB called MYPDB.
[oracle@ORA122 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-JAN-2018 15:31:09 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA122)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 15-JAN-2018 15:29:07 Uptime 0 days 0 hr. 2 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/product/12.2/network/admin/listener.ora Listener Log File /oracle/diag/tnslsnr/ORA122/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA122)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "db1" has 1 instance(s). Instance "db1", status READY, has 1 handler(s) for this service... Service "db2" has 1 instance(s). Instance "db2", status READY, has 1 handler(s) for this service... Service "mypdb" has 2 instance(s). Instance "db1", status READY, has 1 handler(s) for this service... Instance "db2", status READY, has 1 handler(s) for this service... The command completed successfully
See the last service? MYPDB service has 2 instances, db1 and db2. What happens if I connect to it?
[oracle@ORA122 ~]$ sqlplus system/*****@//127.0.0.1:1521/mypdb SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 15 15:50:57 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Fri Jan 05 2018 22:47:25 -08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- db1 SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
And connect again?
[oracle@ORA122 ~]$ sqlplus system/*****@//127.0.0.1:1521/mypdb SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 15 15:51:11 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Fri Jan 05 2018 22:47:34 -08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- db2
I guess this configuration is not very common (having 2 multitenant instances on the same server with the same PDB), but this is definitely something to remember.
Hi.
Not just not common, but Oracle don’t support it. 🙂 I discussed this here.
https://oracle-base.com/articles/12c/multitenant-pluggable-database-names-12cr1
The important bit is the quote from the documentation,
“Specify the name of the PDB to be created. The first character of a PDB name must be alphanumeric and the remaining characters can be alphanumeric or the underscore character (_).
The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener.”
The second paragraph being the important bit. 🙂
Cheers
Tim…
Hey Tim,
Thanks for the comment, I just had a discussion with Franck Pachot about it and he suggested adding a listener.
I wasn’t aware of this in the documentation, but I guess that the second paragraph is there exactly because of this problem.
Cheers,
Liron
Hi.
It’s under the pdb_name section here.
https://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686
If you search for listener on that page you will see the section that talks about collisions.
Franck is correct. A separate listener would work, but you have to make sure someone doesn’t goof up. 🙂
Cheers
Tim…