Written by Emad Al-Mousa
Oracle’s Multitenant Option was introduced in 12c Release as an efficient response to the Cloud technology challenges by reducing costs, streamlining processes and managing resources effectively. The architectural model consists of a Container Database (CDB) that controls the instance-wide maintenance activities and contains zero or more isolated pluggable databases (PDB) independently managed in terms of resources and security.
Initially, Oracle did not enforce the model but kept the legacy structures (the older Non-CDB instances) intact. Recently the non-CDB Option was deprecated, a decision that drives Oracle users to start adopting and converting their infrastructure to the new Option. In turn, this posed several challenges of its own and one of those is changing Pluggable Database level name, Container Database name, or Database ID (DBID).
One possible use case is when a backup file of a PDB is received from a third-party software vendor whose name needs to change to meet the organization’s naming standards or to better convey the purposes of the database. Another scenario is to change the DBID of a cloned database to maintain its unique backup information repository in recovery catalog. In these, and similar situations, we can follow the methods explained below to achieve the required modifications.
So, here I will provide detailed steps for three different changes that can be implemented in your Multitenant Architecture CDB environment:
- Changing PDB name
- Changing CDB name
- Changing Oracle DBID for all PDB’s
Please note that these changes might have consequences such as impacting backup & recovery. They should not be deployed in a production environment without proper planning and thorough testing.
Changing Pluggable Database Name
Accessing through CDB$root execute the following:
SQL> alter pluggable database PDB_TEST2 close;
SQL> alter pluggable database PDB_TEST2 open restricted;
SQL> select name, open_mode, restricted from V$PDBS where name=’PDB_TEST2′;
Connect to the pluggable database PDB_TEST2 and execute the following:
SQL> alter session set container=PDB_TEST2;
SQL> alter pluggable database rename global_name to PDB_X;
SQL> alter pluggable database close immediate;
SQL> alter pluggable database open;
As shown below the pluggable database name has changed successfully:
One last thing to consider, the data files will be kept as is on the old directory pluggable database name and this can be checked by executing the following SQL query:
SQL> select * from DBA_DATA_FILES;
If you want to change this, then create another sub-directory and then use the SQL command alter database move datafile.
Changing Oracle CDB Name
My current oracle CDB name is set to oracle.
Connect to the CDB$ROOT and execute the following:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
Open a new session and execute the following command for nid utility:
nid TARGET=SYS DBNAME=oracle18
Final message showing successful execution:
SQL> STARTUP NOMOUNT
SQL> alter system set db_name=’oracle18′ scope=spfile;
SQL> alter database mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
To verify:
SQL> select * from V$DATABASE;
Changing Oracle DB ID
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
Open a new session and use DBNEWID and I will specify the parameter PDB=ALL to change all pluggable database ID’s:
nid TARGET=SYS PDB=ALL
SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN RESETLOGS;
To compare before and after DBID values for PDB_X pluggable database:
Before:
After:
Conclusion
The Multitenant Option (MO) offers immense advantages related to cost and resource management when implemented. Once implemented, some old issues need to be tackled in new ways. One of those is manipulating the “identity” of a database – its name and ID. The operational and organizational drivers for this change include conformity to standards and providing uniqueness to cloned databases.
The article traced the procedures to perform in order to alter the identifiers of the MO components. It presented the steps needed to modify the CDB and PDB names as well as the DBID’s for the pluggable databases. It also illustrated the usage of the DBNEWID Utility in a multitenant environment and the commands to complete the modifications.
A final note: One possible area of enhancement to the DBID modification process is to enable it to be executed on individual PDB level. This will increase the degree of flexibility of the “pluggable” nature of the PDB’s.
Additional Resources
COLLABORATE 20 will take place April 19-23, 2020 at the Mandala Bay Resort and Casino in Las Vegas, NV! Call for Presentations is now open. Don’t miss your chance to share your stories, insights, and advice with the Quest Oracle Community! Submit an abstract by October 11, 2019, to be considered to be a speaker at the conference.
About the Author
Emad Al-Mousa is a Senior System Analyst at Saudi Aramco and has been working with Oracle technologies since 2006. He is an expert with the Oracle Database platform in different areas such as High Availability, Security, and Performance Tuning. In addition, he is an expert with Oracle Spatial technology for GIS Systems. Emad posses multiple Oracle Database Certifications.