Tuesday, November 11, 2014

Oracle 12C: Creating pluggable database - error: ORA-01276: Cannot add file



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

SQL> alter session set DB_CREATE_FILE_DEST='/u01/data/pdbvcdb';
Session altered.


SQL> SHOW PARAMETER DB_CREATE_FILE_DEST;
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.

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




1 comment: