Today's little thought provoking exercise is - what if you need to change the name of an existing PDB (pluggable database) in Oracle?
The reason this is worthy of this write-up is as follows: back in the old days (I am thinking version 12c), this was a fairly complex operation. It involved "unplugging" and "replugging" and exporting and importing keys and Geesh!!! A colleague was about to start this operation when I caught on and notified him there is a much better way!
If you're not on at least 19c, get there as soon as you can!!!
So we had a little problem - we are pumping out PDBs for projects and suddenly realized we would soon run out of letters and numbers...not to mention that while it's not always good to add intelligence to a technical name, in the case of a database, one minor keystroke due to an unrecognizable sequence could be disastrous. So we came up with a little standard. First 3 letters are a mnemonic for the application, second 3 were environment purpose. In our case these are:
1 - GLD for Gold environment. Used as the golden image to build out the others. No code changes.
2 - DEV for a development environment. Where change rules, and all the fun happens (haha)!
3 - TST is a place for testing changes which pass muter in DEV.
4 - QAS the Quality Assurance System. This is where project leaders make final integration tests.
5 - PRD is Production, where real end users and complex and costly transactions involving data occur.
So, in our case, the app is EBS and the environment is GLD.
We have 8 letter positions to work with, so the seventh is a static P, standing for pluggable, and the eighth is a number designation so we can have more than one (needed during migrations, or for cloning for new environments, or so that parallel development an take place.
So now that I've laid out the reasons for a PDB rename, let's get into the mechanics. I found the syntax in an Oracle support manual. I cannot publish the full statement and all options verbatim here, because it is copyrighted, but here is a link. You may need a valid Oracle support contract to view it.
Here is what we actually used:
alter pluggable database rename global_name to EBSGLDP1;
It is really that simple. But it's not. Please note the slightly tricky global_name syntax.
I have this theory. There is code in the back of all complex software that prevents someone from executing a statement for the first time to have success, even with perfect syntax, and dedicated research. Now, I do understand that all sorts of havoc can ensue were you to rename a database on the fly, but a novice is often prone, or even led to believe, that it can be so easy. Likely from reading a google post like this one! Not to "throw shade" on any other posts, but I do think renaming needs to be thought through.
Even though I am not a novice, in a development environment, with a good backup, sometimes it is useful to try simple things first and learn from them.
So here is the actual sequence of statements which must occur to rename a PDB in an open CDB (Container database), and have network end users be able to connect to it with the new name:
$ sqlplus / as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> alter pluggable database ebsgld1 close; -- You must close the PDB using the OLD name.
Pluggable database altered.
SQL> alter pluggable database ebsgld1 open restricted; -- Restricted mode is required for renaming
Pluggable database altered.
SQL> alter pluggable database rename global_name to EBSGLDP1; -- Here is the actual rename!
Pluggable database altered.
SQL> alter pluggable database ebsgldp1 close; -- Now it must be closed because it is "restricted".
Pluggable database altered.
SQL> alter pluggable database ebsgldp1 open read write; -- Now end users can connect but only locally.
Pluggable database altered.
SQL> alter session set container = ebsgldp1; -- now we need to connect to the PDB
Session altered.
SQL> alter system register; -- this is the first step in getting the PDB "registered" with the Oracle Listener.
System altered.
Now we have to move over to the network side of things. The network listener configuration needs to be updated to reflect the new name.
First we will check to see if the listener "sees" the database with it's new name. The output below is abbreviated and masked for confidentiality, but I will highlight what we are looking for in red:
lsnrctl stat
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-SEP-2021 13:48:16
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 16-SEP-2021 16:54:45
Uptime 0 days 20 hr. 53 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/yourhostname/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "ebsgldp1" has 1 instance(s).
Instance "DB0720", status READY, has 1 handler(s) for this service...
The command completed successfully
Now, each end device will need this name recorded, unless it's entered in DNS. The connect string needs to look something like this:
EBSGLDP1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = EBSGLDP1)
)
)
The proof is in the pudding! A sqlplus "loopback" connection shows if we can successfully connect:
sqlplus system/password@ebsgldp1
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 17 13:54:33 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Fri Sep 17 2021 13:46:21 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> show con_name
CON_NAME
------------------------------
EBSGLDP1
