How to rename a Pluggable Database

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:

Both issues are fixed in 12.2.0.1 and with certain patch levels on Oracle 12.1.0.2.

–Mike

 

4 thoughts on “How to rename a Pluggable Database

    • 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

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree