Rename the PDB database in Oracle

Rename the PDB database

Rename the PDB1 database to PDB2 in the following example. Following are the steps involved for PDB1 to PDB2 rename:

1. Check the PDB present in Container database.

select name from v$pdbs;

2. Open the database in Restricted Mode.

alter pluggable database PDB1 close;
alter pluggable database PDB1 open restricted;

3. Check database in restricted Mode.

select con_id, name, open_mode,restricted from v$containers;

CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
1 CDB$ROOT READ WRITE NO
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES

show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES

4. Rename the PDB1 database by connecting it.

alter session set container=PDB1;
alter pluggable database rename global_name to PDB2;

Note: Database name is rename but for naming convention you can move all datafiles to PDB2 folder for better understanding.

5. Make a new directory with name PDB2 in Oradata folder and check datafiles path
Note: ORCL is our CDB database

mkdir C:\oracle\oradata\orcl\pdb2

SQL> show con_name
CON_NAME
---------
PDB2

select file_name from dba_data_files;
FILE_NAME
----------------------------------------
C:\ORACLE\ORADATA\ORCL\PDB1\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SAMPLE_SCHEMA_USERS01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SYSAUX01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SYSTEM01.DBF

6. Move the datafile from PDB1 folder to PDB2 folder.

alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\EXAMPLE01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\EXAMPLE01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SAMPLE_SCHEMA_USERS01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SAMPLE_SCHEMA_USERS01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SYSAUX01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SYSAUX01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SYSTEM01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SYSTEM01.DBF';

7. Check Create and Drop the temp file.

SQL> select file_name from dba_Temp_files;
FILE_NAME
---------------------------------------------
C:\ORACLE\ORADATA\ORCL\PDB1\PDB1_TEMP01.DBF

alter tablespace TEMP add tempfile 'C:\ORACLE\ORADATA\ORCL\PDB2\PDB2_TEMP01.DBF' size 20M reuse;

alter database tempfile 'C:\ORACLE\ORADATA\ORCL\PDB1\PDB1_TEMP01.DBF' drop including datafiles;

8. Check the PDB database.

select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------ ----------
PDB2 READ WRITE

9. Close the PDB2 database and open in normal mode.

alter pluggable database pdb2 close;
alter pluggable database pdb2 open;
-- to save the state in UPPER VERSION 12.1.0.2
alter pluggable database pdb2 save state;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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