in Oracle

12c MultiTenant Posts — 1 : Creating a PDB in a different directory

What if the current (default) location for Pluggable Databases is running out of space and, yet, you want to create an Oracle Managed Files Pluggable Database ?

First, I start with this configuration :

SQL> select con_id, file#, substr(name,1,56)
2 from v$datafile
3 order by 1,2;

CON_ID FILE#
---------- ----------
SUBSTR(NAME,1,56)
--------------------------------------------------------------------------------
1 1
/u01/app/oracle/oradata/orcl12c/system01.dbf

1 3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

1 7
/u01/app/oracle/oradata/orcl12c/users01.dbf

1 15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

2 5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

2 6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

2 8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

3 9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

3 10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

3 11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

3 12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

3 13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19913751733706

3 14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19931956603709


13 rows selected.

SQL>

Currently, I have one Pluggable Database (CON_ID=3) but none of the database files are OMF. I don’t have enough space in /u01 and want to create the new PDB in /u02 and also use OMF. As long as I have /u02/oradata precreated by the system administrator, I can :

SQL> show parameter db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL> create pluggable database NEWPDB admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL> alter pluggable database NEWPDB open;

Pluggable database altered.

SQL> select con_id, open_mode
2 from v$pdbs
3 where name = 'NEWPDB'
4 /

CON_ID OPEN_MODE
---------- ----------
4 READ WRITE

SQL>
SQL> select file#, name
2 from v$datafile
3 where con_id=4
4 order by file#
5 /

FILE#
----------
NAME
--------------------------------------------------------------------------------
16
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

17
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

18
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf


SQL>
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl12c
SQL>

Note how the parent folder name “ORCL12C” is inherited from the DB_UNIQUE_NAME. I can now proceed to setup this new PDB. Later, I can migrate it as an OMF PDB to another Container Database.
.
.
.