Oracle12_create_pdb_CREATE_FILE_DEST

oracle12.1.0.2在创建pdb时增加了CREATE_FILE_DEST这个参数，也就是可以在创建pdb的时候指定omf管理文件的位置 pdb在创建时数据文件的管理和命名受以下参数影响 FILE_NAME_CONVERT CREATE_FILE_DEST DB_CREATE_FILE_DEST PDB_FILE_NAME_CONVERT SQL SELECT C

oracle12.1.0.2在创建pdb时增加了CREATE_FILE_DEST这个参数，也就是可以在创建pdb的时候指定omf管理文件的位置

pdb在创建时数据文件的管理和命名受以下参数影响

• FILE_NAME_CONVERT
• CREATE_FILE_DEST
• DB_CREATE_FILE_DEST
• PDB_FILE_NAME_CONVERT

SQL> SELECT CON_ID,DBID,NAME,OPEN_MODE FROM v$pdbs;
 
    CON_ID       DBID NAME                                                         OPEN_MODE
---------- ---------- ------------------------------------------------------------ --------------------
         2 3576943994 PDB$SEED                                                     READ ONLY
         3 3306636010 PDB                                                          MOUNTED
         4 3034388805 PDB2                                                         MOUNTED
SQL> !ls -l /oradata/cdb
total 2034208
-rw-r----- 1 oracle oinstall  17973248 Jul 24 13:16 control01.ctl
-rw-r----- 1 oracle oinstall  17973248 Jul 24 13:16 control02.ctl
drwxr-x--- 2 oracle oinstall      4096 Jul 24 12:37 pdb
drwxr-x--- 2 oracle oinstall      4096 Jul 24 13:08 pdb2
drwxr-x--- 2 oracle oinstall      4096 Jul 24 12:29 pdbseed
-rw-r----- 1 oracle oinstall  52429312 Jul 24 13:12 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 24 13:15 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 24 13:12 redo03.log
-rw-r----- 1 oracle oinstall 671096832 Jul 24 13:13 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Jul 24 13:12 system01.dbf
-rw-r----- 1 oracle oinstall 206577664 Jul 24 13:13 temp01.dbf
-rw-r----- 1 oracle oinstall 225452032 Jul 24 13:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 24 13:12 users01.dbf

从pdbseed创建pdb

SQL> !mkdir -p /oradata/cdb/pdb3
 
SQL> SHOW parameter db_create_file_dest
 
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_create_file_dest                  string
SQL> 
 
 
SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb3adm IDENTIFIED BY pdb3
  2      CREATE_FILE_DEST = '/oradata/cdb/pdb3'
  3  ;
 
Pluggable DATABASE created.

alert日志

CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb3adm IDENTIFIED BY *    CREATE_FILE_DEST = '/oradata/cdb/pdb3'
Thu Jul 24 13:25:54 2014
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.WWV_FLOW_PAGE_PLUG_TEMPLATES (TEMPLATE) - CLOB populated
****************************************************************
Pluggable Database PDB3 with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB3 is AL32UTF8
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#14 to file$(old file#5)
Adding new file#15 to file$(old file#7)
Successfully created internal service pdb3 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=5 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB3 with pdb id - 5 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb3adm IDENTIFIED BY *    CREATE_FILE_DEST = '/oradata/cdb/pdb3'

SQL>  SELECT FILE#,name,CON_ID FROM v$datafile;
 
     FILE# NAME                                                                                                     CON_ID
---------- ---------------------------------------------------------------------------------------------------- ----------
         1 /oradata/cdb/system01.dbf                                                                                     1
         3 /oradata/cdb/sysaux01.dbf                                                                                     1
         4 /oradata/cdb/undotbs01.dbf                                                                                    1
         5 /oradata/cdb/pdbseed/system01.dbf                                                                             2
         6 /oradata/cdb/users01.dbf                                                                                      1
         7 /oradata/cdb/pdbseed/sysaux01.dbf                                                                             2
         8 /oradata/cdb/pdb/system01.dbf                                                                                 3
         9 /oradata/cdb/pdb/sysaux01.dbf                                                                                 3
        10 /oradata/cdb/pdb/pdb_users01.dbf                                                                              3
        11 /oradata/cdb/pdb2/system01.dbf                                                                                4
        12 /oradata/cdb/pdb2/sysaux01.dbf                                                                                4
        13 /oradata/cdb/pdb2/pdb2_users01.dbf                                                                            4
        14 /oradata/cdb/pdb3/CDB/FEEAB0813C6A07C4E0435C38A8C07619/datafile/o1_mf_system_9x1672m0_.dbf                    5
        15 /oradata/cdb/pdb3/CDB/FEEAB0813C6A07C4E0435C38A8C07619/datafile/o1_mf_sysaux_9x1672m9_.dbf                    5
 
14 ROWS selected.

从文件里可以看出新创建的pdb使用了OMF特性管理，在12.1.0.1版本在创建pdb时让omf管理需要设置db_create_file_dest

SQL> SHOW pdbs
    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2  PDB$SEED     READ ONLY  NO
 
SQL> SHOW parameter db_create_file_dest
 
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest       string  /u02/app/oradata/
 
SQL> SHOW spparameter db_create_file_dest;
 
SID  NAME          TYPE    VALUE
-------- ----------------------------- ----------- ----------------------------
*  db_create_file_dest        string    /u02/app/oradata
 
 
SQL> CREATE PLUGGABLE DATABASE PDB_T0 ADMIN USER t0 IDENTIFIED BY t0 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 
 
Pluggable DATABASE created.
 
 
 
SQL> ALTER SESSION SET db_create_file_dest='/u02/app/oradata/pdb1' ;
 
SESSION altered.
 
 
 
SQL> CREATE PLUGGABLE DATABASE PDB_T1 ADMIN USER t1 IDENTIFIED BY t1 roles=(DBA)
 
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 
 
Pluggable DATABASE created.
 
SQL> SELECT con_id, file_name FROM cdb_data_files ORDER BY 1;
    CON_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------------
  1 /u02/app/oradata/CDB/system01.dbf
  1 /u02/app/oradata/CDB/sysaux01.dbf
  1 /u02/app/oradata/CDB/undotbs01.dbf
  1 /u02/app/oradata/CDB/users01.dbf
  2 /u02/app/oradata/CDB/pdbseed/sysaux01.dbf
  2 /u02/app/oradata/CDB/pdbseed/system01.dbf
  3 /u02/app/oradata/CDB/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclbjk1_.dbf
  3 /u02/app/oradata/CDB/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclbjk0_.dbf
  3 /u02/app/oradata/CDB/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_0_9bcng0ml_.dbf
  4 /u02/app/oradata/pdb1/CDB/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf
  4 /u02/app/oradata/pdb1/CDB/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf
  4 /u02/app/oradata/pdb1/CDB/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_1_9bcn4moz_.dbf

SQL> !mkdir -p /oradata/cdb/pdb4
 
SQL> CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb4adm IDENTIFIED BY pdb4
  2      CREATE_FILE_DEST = '/oradata/cdb/pdb4'
  3      FILE_NAME_CONVERT = ('/oradata/cdb/pdbseed/',
  4      '/oradata/cdb/pdb4/')
  5  ;
 
Pluggable DATABASE created.

CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb4adm IDENTIFIED BY *    CREATE_FILE_DEST = '/oradata/cdb/pdb4'
    FILE_NAME_CONVERT = ('/oradata/cdb/pdbseed/',
    '/oradata/cdb/pdb4/')
Thu Jul 24 13:29:16 2014
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.WWV_FLOW_PAGE_PLUG_TEMPLATES (TEMPLATE) - CLOB populated
****************************************************************
Pluggable Database PDB4 with pdb id - 6 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB4 is AL32UTF8
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#16 to file$(old file#5)
Adding new file#17 to file$(old file#7)
Successfully created internal service pdb4 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=6 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB4 with pdb id - 6 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb4adm IDENTIFIED BY *    CREATE_FILE_DEST = '/oradata/cdb/pdb4'
    FILE_NAME_CONVERT = ('/oradata/cdb/pdbseed/',
    '/oradata/cdb/pdb4/')

原文地址：Oracle12_create_pdb_CREATE_FILE_DEST, 感谢原作者分享。