Infrastructure at your Service

Franck Pachot

When PDB name conflicts with CDB name

By Franck Pachot

.
Going to multitenant architecture is not a big change. The administration things (DBA, monitoring, backups) connect to the CDB and the application things connect to the PDB. Without the multitenant option, it is still recommended to go to the CDB architecture. The non-CDB is deprecated and the multitenant architecture brings interesting features. People often ask how to name the CDB and the PDB, especially when they have naming rules or policies in the company. My recommendation is to name the PDB as you are used to naming the databases: the name often gives an idea of the data that is inside, the application, and the environment. The CDB is the container, and in my opinion, you should apply the same naming rules as for servers. Don’t forget that pluggable databases are made to be moved across CDB, so the CDB name should not depend on the content.

But, with single tenant, you have a one-to-one relationship between the CDB and the PDB and then may come the idea to set the same name for CDB and PDB… I’m not sure if it is supported or not and please, don’t do that.

Service Name

There’s one rule: the service name must be unique on a server, especially when registered to the same listener. The PDB name will be the default service name registered by the PDB. And the DB_UNIQUE_NAME of the CDB will be the default service name registered by the CDB. Then the PDB name must be different than the DBA_UNIQUE_NAME.

With this rule, it should be possible to have the same name for the CDB (the DB_NAME) and the PDB, given that we have set a different DB_UNIQUE_NAME.

Here is an example. The name of my Container Database is CDB1. But as it is part of a Data Guard configuration I changed the unique name to CDB1A (and standby will be CDB1B).

Here are the services from by CDB:


SQL> select * from v$services;
 
SERVICE_ID  NAME            NAME_HASH   NETWORK_NAME  CREATION_DATE  CREATION_DATE_HASH  GOAL  DTP  AQ_HA_NOTIFICATION  CLB_GOAL  COMMIT_OUTESSION_STATE_CONSISTENCY  GLOBAL  PDB       SQL_TRANSLATION_PROFILE  MAX_LAG_TIME  STOP_OPTION  FAILOVER_RESTORE  DRAIN_TIMEOUT  CON_ID
----------  ----            ---------   ------------  -------------  ------------------  ----  ---  ------------------  --------  ----------------------------------  ------  ---       -----------------------  ------------  -----------  ----------------  -------------  ------
7           CDB1A           3104886812  CDB1A         27-AUG-17      1962062146          NONE  N    NO                  LONG      NO                                  NO      CDB$ROOT                                         NONE         NONE              0              1
1           SYS$BACKGROUND  165959219                 26-JAN-17      1784430042          NONE  N    NO                  SHORT     NO                                  NO      CDB$ROOT                                         NONE         NONE              0              1
2           SYS$USERS       3427055676                26-JAN-17      1784430042          NONE  N    NO                  SHORT     NO                                  NO      CDB$ROOT                                         NONE         NONE              0              1
0           pdb1            1888881990  pdb1                         0                   NONE  N    NO                  SHORT     NO                                  NO      PDB1                                             NONE         NONE              0              4
6           CDB1XDB         1202503288  CDB1XDB       27-AUG-17      1962062146          NONE  N    NO                  LONG      NO                                  NO      CDB$ROOT                                         NONE         NONE              0              1

All are default services: CDB1A is the DB_UNIQUE_NAME, SYS$BACKGROUND for background processes, SYS$USERS when connecting without a service name, CDB1XDB is used to connec to XDB dispathers. PDB1 is the default service of my pluggable database PDB1.

I can also look at the services registred in the listener:


SQL> host lsnrctl status
 
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-AUG-2017 20:34:36
 
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                27-AUG-2017 20:41:33
Uptime                    0 days 23 hr. 53 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/VM104/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM104)(PORT=1521)))
Services Summary...
Service "57c2283990d42152e053684ea8c05ea0" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1A" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

There is just one additional service here: the GUI of my PDB (see https://blog.dbi-services.com/service-696c6f76656d756c746974656e616e74-has-1-instances/)

ORA-65149

Do you see any service named ‘CDB1’ here? No. Then I should be able to create a PDB with this name.


SQL> create pluggable database CDB1 admin user admin identified by covfefe file_name_convert=('pdbseed','cdb1');
 
Error starting at line : 1 in command -
create pluggable database CDB1 admin user admin identified by covfefe file_name_convert=('pdbseed','cdb1')
Error report -
ORA-65149: PDB name conflicts with existing service name in the CDB or the PDB
65149. 00000 -  "PDB name conflicts with existing service name in the CDB or the PDB"
*Cause:    An attempt was made to create a pluggable database (PDB) whose
           name conflicts with the existing service name in the container
           database (CDB) or the PDB.
*Action:   Choose a different name for the PDB.

Ok. This is impossible. However, the error message is not correct. My PDB name does not conflict with existing service names. It may conflict with instance name or DB_NAME, but not with any service.

NID

As I’m not satisfied with this, I try to find another way to have the same name for CDB and PDB. I have a pluggable database named ‘PDB1’ and I’ll try to change the CDB name to this:


[[email protected] ~]$ nid dbname=PDB1 target=sys/oracle
 
DBNEWID: Release 12.2.0.1.0 - Production on Mon Aug 28 20:40:08 2017
 
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
 
Connected to database CDB1 (DBID=926862412)
 
Connected to server version 12.2.0
 
Control Files in database:
    /u01/oradata/CDB1A/control01.ctl
    /u01/fast_recovery_area/CDB1A/control02.ctl
 
The following datafiles are read-only:
    /u01/oradata/CDB1A/PDB1/USERS2.db (17)
These files must be writable by this utility.
 
Change database ID and database name CDB1 to PDB1? (Y/[N]) => Y
 
Proceeding with operation
Changing database ID from 926862412 to 3460932968
Changing database name from CDB1 to PDB1
    Control File /u01/oradata/CDB1A/control01.ctl - modified
    Control File /u01/fast_recovery_area/CDB1A/control02.ctl - modified
    Datafile /u01/oradata/CDB1A/system01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/users01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/pdbseed/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/system01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/USERS.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/USERS2.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/temp01.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/pdbseed/temp012017-08-27_18-30-16-741-PM.db - dbid changed, wrote new name
    Datafile /u01/oradata/CDB1A/PDB1/temp012017-08-27_18-30-16-741-PM.db - dbid changed, wrote new name
    Control File /u01/oradata/CDB1A/control01.ctl - dbid changed, wrote new name
    Control File /u01/fast_recovery_area/CDB1A/control02.ctl - dbid changed, wrote new name
    Instance shut down
 
Database name changed to PDB1.
Modify parameter file and generate a new password file before restarting.
Database ID for database PDB1 changed to 3460932968.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  859832320 bytes
Fixed Size                  8798552 bytes
Variable Size             784338600 bytes
Database Buffers           58720256 bytes
Redo Buffers                7974912 bytes
ORA-01103: database name 'PDB1' in control file is not 'CDB1'
 
SQL> alter system set db_name=PDB1 scope=spfile;
 
System altered.
 
SQL> shutdown immediate
ORA-01507: database not mounted
 
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
 
Total System Global Area  859832320 bytes
Fixed Size                  8798552 bytes
Variable Size             784338600 bytes
Database Buffers           58720256 bytes
Redo Buffers                7974912 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
SQL> alter database open resetlogs;
 
Database altered.

That’s done.
My CDB is named PDB1:

SQL> select * from v$database;
 
DBID        NAME  CREATED    RESETLOGS_CHANGE#  RESETLOGS_TIME  PRIOR_RESETLOGS_CHANGE#  PRIOR_RESETLOGS_TIME  LOG_MODE    CHECKPOINT_CHANGE#  ARCHIVE_CHANGE#  CONTROLFILE_TYPE  CONTROLFILE_CREATED  CONTROLFILE_SEQUENCE#  CONTROLFILE_CHANGE#  CONTROLFILE_TIME  OPEN_RESETLOGS  VERSION_TIME  OPEN_MODE   PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_ARCHIVE  ACTIVATION#  SWITCHOVER#  DATABASE_ROLE  ARCHIVELOG_CHANGE#  ARCHIVELOG_COMPRESSION  SWITCHOVER_STATUS  DATAGUARD_BROKER  GUARD_STATUS  SUPPLEMENTAL_LOG_DATA_MIN  SUPPLEMENTAL_LOG_DATA_PK  SUPPLEMENTAL_LOG_DATA_UI  FORCE_LOGGING  PLATFORM_ID  PLATFORM_NAME     RECOVERY_TARGET_INCARNATION#  LAST_OPEN_INCARNATION#  CURRENT_SCN  FLASHBACK_ON  SUPPLEMENTAL_LOG_DATA_FK  SUPPLEMENTAL_LOG_DATA_ALL  DB_UNIQUE_NAME  STANDBY_BECAME_PRIMARY_SCN  FS_FAILOVER_STATUS  FS_FAILOVER_CURRENT_TARGET  FS_FAILOVER_THRESHOLD  FS_FAILOVER_OBSERVER_PRESENT  FS_FAILOVER_OBSERVER_HOST  CONTROLFILE_CONVERTED  PRIMARY_DB_UNIQUE_NAME  SUPPLEMENTAL_LOG_DATA_PL  MIN_REQUIRED_CAPTURE_CHANGE#  CDB  CON_ID  PENDING_ROLE_CHANGE_TASKS  CON_DBID    FORCE_FULL_DB_CACHING
----        ----  -------    -----------------  --------------  -----------------------  --------------------  --------    ------------------  ---------------  ----------------  -------------------  ---------------------  -------------------  ----------------  --------------  ------------  ---------   ---------------      ----------------     --------------  -----------  -----------  -------------  ------------------  ----------------------  -----------------  ----------------  ------------  -------------------------  ------------------------  ------------------------  -------------  -----------  -------------     ----------------------------  ----------------------  -----------  ------------  ------------------------  -------------------------  --------------  --------------------------  ------------------  --------------------------  ---------------------  ----------------------------  -------------------------  ---------------------  ----------------------  ------------------------  ----------------------------  ---  ------  -------------------------  --------    ---------------------
3460932968  PDB1  27-AUG-17  1495032            28-AUG-17       1408558                  27-AUG-17             ARCHIVELOG  1495035             0                CURRENT           27-AUG-17            2574                   1496538              28-AUG-17         NOT ALLOWED     27-AUG-17     READ WRITE  MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED         3460947145   3460947145   PRIMARY        0                   DISABLED                NOT ALLOWED        DISABLED          NONE          NO                         NO                        NO                        NO             13           Linux x86 64-bit  3                             3                       1497050      NO            NO                        NO                         CDB1A           0                           DISABLED                                        0                                                                               NO                                             NO                                                      YES  0       NOT APPLICABLE             3460932968  NO

And I have a PDB with the same name:


SQL> show pdbs
CON_ID  CON_NAME  OPEN MODE  RESTRICTED
------  --------  ---- ----  ----------
2       PDB$SEED  READ ONLY  NO
4       PDB1      MOUNTED
 
SQL> alter pluggable database PDB1 open;
 
Pluggable database PDB1 altered.
 
SQL> show pdbs
CON_ID  CON_NAME  OPEN MODE   RESTRICTED
------  --------  ---- ----  ----------
2       PDB$SEED  READ ONLY   NO
4       PDB1      READ WRITE  NO

What was forbidden with a wrong error message was made possible with this other way.

So what?

Please, do not take this as a solution. There is clearly a problem here. Maybe the documentation and error message are wrong. Maybe the NID has a bug, allowing to do something that should be blocked. Or the create pluggable database has a bug, blocking something that should be possible. Until this is fixed (SR opened) I would recommend that the PDB name is always different than the CDB name, independently of service names. Well, I would recommend it anyway as it brings a lot of confusion: when you mention a database name, people will not know whether you are referring to the CDB or the PDB.

3 Comments

  • Hi Franck – I am in the process of “nonCDB” to “SingleTenant PDB” engineering (Multitenant is out of scope at this time). So I invested quite a bit of time about CDB/PDB naming and as a result, I think I found “THE” solution… Summary: Naming the PDB with the same name as the original nonCDB name I think is not optimal. If you are interested in this, please let me know by sending me an email. I’d like to share this with you.
    Regards, Andreas

  • chandrashekar Pitta says:

    Hi Andreas, Could you please share your findings on the topic of having the same name for CDB and PDB even though we are in a single tenant Oracle EBS R12.2.8.

  • james senior says:

    The documents say not to use the same CDB name as the PDB name in EBS. The autoconfig scripts setup service names so you can still connect using the same tns strings as before.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod