Creating pluggable databases


1. Creating new pluggable database by OMF

SQL> create pluggable database PDB12C3 admin user daniel identified by daniel;
Pluggable database created.

SQL> select * from v$pdbs
CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED READ ONLY NO 08-JUL-13 08.58.27.582 PM 1720729 283115520
3 2256791230 2256791230 E0F3B0FA664F417FE0430100007FBBCB PDB12C1 READ WRITE NO 08-JUL-13 11.03.49.436 PM 1914453 288358400
4 1952495537 1952495537 E0F3B3FAFF6441ABE0430100007F14F8 PDB12C2 READ WRITE NO 08-JUL-13 11.03.49.436 PM 1915366 288358400
5 2002390471 2002390471 E107AB473BD61383E0430100007F4418 PDB12C3 MOUNTED 08-JUL-13 11.26.20.831 PM 1947773 0

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> select * from v$pdbs;
CON_ID DBID CON_UID GUID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
2 4062846046 4062846046 E0F388EB55323E35E0430100007FF91C PDB$SEED READ ONLY NO 08-JUL-13 08.58.27.582 PM 1720729 283115520
3 2256791230 2256791230 E0F3B0FA664F417FE0430100007FBBCB PDB12C1 READ WRITE NO 08-JUL-13 11.03.49.436 PM 1914453 288358400
4 1952495537 1952495537 E0F3B3FAFF6441ABE0430100007F14F8 PDB12C2 READ WRITE NO 08-JUL-13 11.03.49.436 PM 1915366 288358400
5 2002390471 2002390471 E107AB473BD61383E0430100007F4418 PDB12C3 READ WRITE NO 08-JUL-13 11.26.55.113 PM 1947773 283115520

Interesting are the default file paths. As you can see the PDB GUID does match the OFM directory for OFM structure.

SQL> alter session set container=PDB12C3;
Session altered.

SQL> select name from v$datafile;
NAME
-----------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/CDB12C/E107AB473BD61383E0430100007F4418/datafile/o1_mf_system_8xpcm81p_.dbf
/opt/oracle/app/oradata/CDB12C/E107AB473BD61383E0430100007F4418/datafile/o1_mf_sysaux_8xpcmckz_.dbf

As you can see the default is OFM for the new datafile directory structure. To change you can use

FILE_NAME_CONVERT=('CDB12C','pdb12c','pdbseed','pdb12c3')
PATH_PREFIX = '/opt/oracle/app/oradata/PDB12C3'

Problem with convert and OFM
If the SEED database has been created as OMF than you get errors like below:

SQL> create pluggable database PDB12C3 admin user daniel identified by daniel FILE_NAME_CONVERT=('CDB12C','pdb12c');
create pluggable database PDB12C3 admin user daniel identified by daniel FILE_NAME_CONVERT=('CDB12C','pdb12c')
*
ERROR at line 1:
ORA-01276: Cannot add file /opt/oracle/app/oradata/pdb12c/datafile/o1_mf_system_8xmq0g7t_.dbf. File has an Oracle Managed Files file name.

To correct this error you have to convert all datafile and tempfiles excluding UNDO in the convert string:

create pluggable database PDB12C1 admin user daniel identified by daniel FILE_NAME_CONVERT = (
-- SYSTEM Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmq0g7t_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/system01.dbf',
-- SYSAUX Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmq0g3g_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/sysaux01.dbf',
-- TEMP Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/pdbseed_temp01.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/temp01.dbf');

SQL> alter session set container=PDB12C1;
Session altered.

SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/PDB12C1/datafile/system01.dbf
/opt/oracle/app/oradata/PDB12C1/datafile/sysaux01.dbf

SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/PDB12C1/datafile/temp01.dbf

Here an example with non OFM seed db and FILE_NAME_CONVERT
First check PDB$SEED file paths:

SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/pdbseed/system01.dbf
/opt/oracle/app/oradata/pdbseed/sysaux01.dbf

SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/pdbseed/temp01.dbf

Create pluggable database:

SQL> create pluggable database PDB12C4 admin user daniel identified by daniel FILE_NAME_CONVERT = ('pdbseed', 'PDB12C3');

Pluggable database created.

Check file paths:

SQL> alter session set container=PDB12C3;
Session altered.

SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_undotbs1_8xmpygh6_.dbf
/opt/oracle/app/oradata/PDB12C3/system01.dbf
/opt/oracle/app/oradata/PDB12C3/sysaux01.dbf

SQL> select name from v$tempfile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/app/oradata/PDB12C3/temp01.dbf

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s