14 May, 2017

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

No comments: