For some reason, I cannot create pluggable database if there are entry setting for DB_CREATE_FILE_DEST. Have to overwrite the setting.
Refer to OCP Upgrade to Oracle Database 12C Exam Guide - Page 66-67. Page 67 did talk about letting Oracle to create the OMF datafile if the entry is set.
Seems like running the Create Pluggable command without specifying FILE_NAME_CONVERT.
Requirement: directory to the target database must be created prior to create the database.
[oracle@oracle12c data]$ pwd
/u01/data
[oracle@oracle12c data]$ ls -las
total 36
8 drwxrwxr-x 5 501 oinstall 4096 Jul 11 2013 .
4 drwxrwxrwx 6 root root 4096 Jul 11 2013 ..
8 drwxrwxrwx 3 501 oinstall 4096 Jul 11 2013 orcl
8 drwxr-x--- 6 oracle oracle 4096 Jul 11 2013 ORCL
8 drwxr-x--- 2 oracle oracle 4096 Jul 11 2013 pdbseed
[oracle@oracle12c data]$ ls -la
create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT) FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb');
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/data/ORCL/datafile/o1_mf_users_8xy20vdz_.dbf
/u01/data/ORCL/datafile/o1_mf_undotbs1_8xy20ykp_.dbf
/u01/data/ORCL/datafile/o1_mf_sysaux_8xy1tly9_.dbf
/u01/data/ORCL/datafile/o1_mf_system_8xy1y65o_.dbf
/u01/data/ORCL/datafile/vpxadmin.dbf
/u01/data/ORCL/datafile/vcdman.dbf
SQL> create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT) FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb');
create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT) FILE_NAME_CONVERT = ('/u01/data/ORCL','/u01/data/pdbvcdb')
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/data/pdbvcdb/datafile/o1_mf_system_8xy22ryn_.dbf. File has an Oracle
Managed Files file name.
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/data
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/data
SQL> alter session set DB_CREATE_FILE_DEST='/u01/data/pdbvcdb';
Session altered.
Session altered.
SQL> SHOW PARAMETER DB_CREATE_FILE_DEST;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/data/pdbvcdb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/data/pdbvcdb
SQL> create pluggable database pdbvcdb admin user vcadmin identified by passw0rd roles=(CONNECT);
Pluggable database created.
Pluggable database created.
SQL> col pdb_name format a20
SQL> col status format a20
SQL> select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
-------------------- --------------------
PDBORCL NORMAL
PDB$SEED NORMAL
PDBVCDB NEW
SQL> select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
-------------------- --------------------
PDBORCL NORMAL
PDB$SEED NORMAL
PDBVCDB NEW
thanks a lot.........
ReplyDeleteit greatly helps me