Have you ever asked yourself how to rename a pluggable database? I did. And today a colleague from ACS Support told me how easy it is.
Of course I had to try it out. And it seems to work quite nice in Oracle Database 12.2.0.1. In the previous release I found some nits documented in MOS.
How to rename a Pluggable Database
There’s no obvious command to rename a pluggable database such as “alter pluggable database PDB1 rename to PDB2”. But it’s not complicated if you know the right command.
I’m creating a fresh pluggable database:
create pluggable database PDB1 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB2/pdbseed','/u02/oradata/CDB2/pdb1'); alter pluggable database PDB1 open;
For a rename operation it has to be in RESTRICTED mode:
alter pluggable database PDB1 close; alter pluggable database PDB1 open restricted;
My environment has the following PDBs:
show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY YES 4 PDB1 READ WRITE YES
Then I connect to PDB1 and rename it:
alter session set container=PDB1; alter pluggable database rename global_name to HUGO; show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 HUGO READ WRITE YES
Finally … please note that the directory does not get renamed. As I placed my PDB1 in /u02/oradata/CDB2/pdb1
it does not get moved into a directory reflecting the new name.
[CDB2] oracle@localhost:/u02/oradata/CDB2/pdb1 $ ls sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf
If you’d like to move it into a directory having the PDB’s name you can do for instance the following action: Create a directory first:
$ mkdir /u02/oradata/CDB2/hugo
And then use the ONLINE MOVE feature in Oracle Database 12.1.0.2 onwards:
alter database move datafile '/u02/oradata/CDB2/pdb1/undotbs01.dbf' to '/u02/oradata/CDB2/hugo/undotbs01.dbf'; alter database move datafile '/u02/oradata/CDB2/pdb1/sysaux01.dbf' to '/u02/oradata/CDB2/hugo/sysaux01.dbf'; alter database move datafile '/u02/oradata/CDB2/pdb1/system01.dbf' to '/u02/oradata/CDB2/hugo/system01.dbf';
You can also combine the rename action into one single command.
But the temporary tablespace’s tempfile is still located in the old directory. There’s no online move for this file available. Instead I can drop the tempfile and recreate it in another directory. The temporary tablespace does not need to be dropped.
alter database tempfile '/u02/oradata/CDB2/pdb1/temp01.dbf' drop including datafiles; alter tablespace TEMP add tempfile '/u02/oradata/CDB2/hugo/temp01.dbf' size 20M reuse;
Final check:
[CDB2] oracle@localhost:/u02/oradata/CDB2 $ ls -lrt ./pdb1 total 0 [CDB2] oracle@localhost:/u02/oradata/CDB2 $ ls -lrt ./hugo total 671776 -rw-r-----. 1 oracle dba 20979712 Feb 8 23:19 temp01.dbf -rw-r-----. 1 oracle dba 220209152 Feb 8 23:20 system01.dbf -rw-r-----. 1 oracle dba 246423552 Feb 8 23:20 sysaux01.dbf -rw-r-----. 1 oracle dba 220209152 Feb 8 23:21 undotbs01.dbf
I’m guessing that it’s not supported to rename the PDB$SEED to something else – so please don’t do it!
Additional Information
Philippe Fierens pointed me to this MOS Note which is important in case you try the above with TDE (Transparent Data Encryption) in place:
And somebody else asked me about the same process in Oracle Database 12.1.0.2. It works – but you will have to check MOS first for issues such as:
- Bug 17982838 – ORA-60 during PDB rename
- Bug 19075256 – ORA-600 [kcfmis_internal: enq] from PDB RENAME
Both issues are fixed in 12.2.0.1 and with certain patch levels on Oracle 12.1.0.2.
–Mike
Not supported does not means not allowed
Well, don’t expect me to explain π But in theory of course it works with the _oracle_script parameter. But I imagine that the trouble will start later with patches, upgrades etc. And I wouldn’t do it. “Not supported” means that you won’t get support for it π I wouldn’t risk it π
Cheers,
Mike
alter database tempfile ‘/u02/oradata/CDB2/pdb1/temp01.dbf’ drop including datafiles;
Syntax error?
=====
Safer to move temporary tablespaces by creating new temporary tablespace, alter database default temporary using the new temporary tablespace name, and drop “other” temporary tablespace. Dropping the old, and possibly only, temp tablespace may hang for a long time and/or possibly lead to transaction error should a transaction need temporary space before temporary tablespace is available. Someone in the crowd may not take this into consideration ;-\
Tim,
as you do this with a single PDB – and it is in restricted mode I don’t expect this to hang as there shouldn’t be any transactions in it.
But thanks for your hint!
Cheers,
Mike
Thanks Mike.
How about renaming PDB in ASM case ? Is there any MOS note or article working on ?
BR,
Yahya
The same procedure should work.
Have you tried it?
Thanks!
Mike
I have completed Mike’s steps above on two of my Oracle DB 12.2.0.1 with ASM on Windows servers. Works well. No issues.
Thanks Ryan – and I had totally forgotten that I did post this π
Cheers,
Mike
War gerade super nΓΌtzlich, danke Mike π
Danke – und gerne π
Herzliche Gruesse,
Mike
Hi Mike, there seems to be a still unkown problem with Non-Unicode PDBs that has been plugged into Unicode-CDBs and has been renamed afterwards. The character set conversion between Client and PDB no longer works as it should after the renaming! MOS ticket in progress.
Details (nly german): https://frankgerasch.de/2021/01/risiken-beim-umbenennen-von-pluggable-databases-pdb/
Hi Frank,
excellent blog post – can you share the SR for the multi-charset issue with me please (either here or via email).
Cheers,
Mike
Hi Mike,
sure: SR 3-24828076501. Unfortunately there is no progress for 22 days.
Best regards,
Frank
Hi Frank,
I dropped the folks in Support an email – and I dropped you an email with advice to get this speed up.
In addition, I will have a look into it as well.
Cheers,
Mike
Mike
I tried the case of frank gerasch..
So i have a PDB with charset WE8MSWIN1252 in a container with AL32UTF8.
I renamed my PDB and after the rename i did not encounter the problem of Frank.
So the query: select ‘Γ’ Character, rawtohex(‘Γ’) HEXCODE, lengthb(‘Γ’) Byte_Length, lengthc(‘Γ’) Character_Length from dual;
gave exactly the same output before and after renaming the PDB. I am doing that select also from within SQL-developer.
Database version is 19.8
regards
danny
Hi Danny,
I couldn’t reproduce it either. Neither with SQL Plus nor with SQL Developer.
But Frank is working with Support on this topic.
Cheers, and thanks a lot for testing it as well!
Mike
Hi Danny, Oracle Support ist still working on this issue. I was able to reproduce it on a second test environment as well and provided my VM to Oracle Support. By the way, I have found out that bouncing the containter database database ist solving the problem. The problem occurred first with a customer of mine and it took us a while to find the cause.
Good stuff, Mike. They tell you you cannot do something, but then someone finds a workaround solution. Somewhat convoluted, but often necessary!
Thanks Michael!
Mike
I wonder if it would be faster/easier to simply clone it with a new name and drop the original one? Sure you’d face some restrictions like disk space for both temprarily co-existing, the data integrity with the ongoin activity (maybe lost data?) and also the copy time if the DB is too big, but this could make the operation significantly simpler, couldn’t it?
Just three commands (CREATE new FROM old / ALTER OPEN new / DROP old). No need to put in restricted mode, no need to move files, no need to rebuild the temporary tablespace.
Aside the inconviniences I already mentioned, would there be other problems? (Don’t know about the TDE thing, would it be correctly replicated too?).
Sure, this would work, too – but it may take longer, and as you’ve mentioned already, you will need extra space.
Cheers,
Mike
Hi Mike
Just tried this as I am creating PDBs via manifest files after a restore and upgrade from 11.2.0.4
Realised halfway through creating a PDB for a 43Tb database that I had used the wrong PDB name !!
Once it completed I ran through the noncdb_to_pdb.sql etc and had it up and running.
Closed the pluggable etc and tried to rename it only to be hit with an error
ORA-00001: unique constraint (SYS.I_PDB_ALERT1) violated
MoS Note 2215331.1 is a good match and apparently it is a bug (16192980) and to do with values being in PDB_PLUG_IN_VIOLATIONS.
The workaround is to recreate the pluggable which is not helpful.
I was wondering if there was a way to clear the PDB_PLUG_IN_VIOLATIONS safely if one finds an entry prior to running the rename otherwise it could be a lot of leg work to fix.
Cheers
Al
Hi Al,
there is no way to clean out PDB_PLUG_IN_VIOLATIONS as long as entries are not “resolved”.
I guess, you received an ERROR?
Can you try to start your PDB in UPGRADE mode and clear the violation manually?
Or, you could try to delete the entry in PDB_PLUG_IN_VIOLATIONS manually in this mode … but no guarantee from my side here …
You may use “event 10046” with level 12 to see on a test environment what an UPDATE (ERROR => RESOLVED) would do in the background.
Cheers,
Mike
Hi all,
small advise not to forget instances=all when stopping the pdb if you do it in RAC,
and modifying the RAC services reffering to old pdb name with srvctl modify service if there are any…
Hope this helps
Hello Mike, Can I do it on a VM at OCI? Or on OCI should execute another procedure? I check my OCI page and I don’t have the option to rename a pdb, also I don’t have any dbaascli tool when we created this enviroment.
Please try – I think this should work there as well.
Cheers
Mike
Mike,
Great stuff, as always. I have, of course, an end-case that I’m hoping you can shed some light on.
I have a CDB that I have cloned which contains a single PDB whose name is in lowercase. I would like to rename it and the new name should also be lowercase. I figured that would be simply accomplished by enclosing the new global_name in double quotes, but it didn’t work!
SYS@cebspc> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
————- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ebs21prd READ WRITE YES
SYS@cebspc> alter session set container = “ebs21prd”;
Session altered.
SYS@cebspc> alter pluggable database rename global_name to “ebspclon”;
Pluggable database altered.
SYS@cebspc> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
————- —————————— ———- ———-
3 EBSPCLON READ WRITE YES
Any idea how I can do that?
Thanks,
Bill
Mike,
It turns out that while this is supported, it’s probably a bad habit. Look no further than this ML doc: EBS 19c Database Fail With ORA-65011 Trying to Start Clusterware Services Using A Lowercase PDB Name (Doc ID 2764123.1).
So I should probably just leave it uppercase and deal with it, eh?
Thanks,
Bill
Hi Bill,
EBS is always VERY special – and I think the ORACLE_PDB_SID came in for EBS only.
Thanks for sharing the note.
Cheers
Mike