ORA-01276: Cannot add file has an Oracle Managed Files file name

Pluggable database ORA-01276: Cannot add file has an Oracle Managed Files file name

Create Pluggable database ..

pluggable database ORA-01276: Cannot add file has an Oracle Managed Files file name

ORA-01276: Cannot add file has an Oracle Managed Files file name

col FILE_NAME for a150

set linesize 200

SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES

where 1=1

and CON_ID> 2;

    CON_ID TABLESPACE_NAME                FILE_NAME

---------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------

         3 SYSTEM                         /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf

         3 SYSAUX                         /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf

         3 UNDOTBS1                       /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf

CREATE PLUGGABLE DATABASE anuj1 FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/');

SQL>

CREATE PLUGGABLE DATABASE anuj1 FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/')

*

ERROR at line 1:

ORA-01276: Cannot add file

/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/o1_mf_system_dq898gom_.dbf.  File

has an Oracle Managed Files file name.

You will get this error on OMF 

We have two option here .

--Convert all the  explicitly 

--/anuj1/anuj1 <<< put file name after dir 

                 

CREATE PLUGGABLE DATABASE anuj1 FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anuj1')

                                                                                                                                                                                                                                                                                               ^

                                                                                                                                                                                                                                                                                               |

SQL> SQL>

CREATE PLUGGABLE DATABASE anuj1 FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anuj1')

SQL>   2

SQL> /

Pluggable database created.

!ls -ltr /u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/

total 655448

-rw-r----- 1 oracle oinstall  67117056 Jul 23 08:01 anuj1o1_mf_temp_dq898gpx_.dbf

-rw-r----- 1 oracle oinstall 104865792 Jul 23 08:05 anuj1o1_mf_undotbs1_dq898gpx_.dbf

-rw-r----- 1 oracle oinstall 304095232 Jul 23 08:05 anuj1o1_mf_sysaux_dq898gpw_.dbf

-rw-r----- 1 oracle oinstall 262152192 Jul 23 08:05 anuj1o1_mf_system_dq898gom_.dbf

set linesize 300 

col FILE_NAME for a120

col Pdb_name for a20

col TABLESPACE_NAME for a20 

SELECT name Pdb_name,TABLESPACE_NAME,c.CON_ID, c.TABLESPACE_NAME,c.FILE_NAME FROM CDB_DATA_FILES c,v$pdbs p

where 1=1

and c.CON_ID> 2

and c.CON_ID=p.CON_ID;

PDB_NAME             TABLESPACE_NAME          CON_ID TABLESPACE_NAME      FILE_NAME

-------------------- -------------------- ---------- -------------------- ------------------------------------------------------------------------------------------------------------------------

ANUJ                 SYSTEM                        3 SYSTEM               /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf

ANUJ                 SYSAUX                        3 SYSAUX               /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf

ANUJ                 UNDOTBS1                      3 UNDOTBS1             /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf

Still we are not able to see newly created a database file . 

Check the pluggable database  status  

set linesize 200 

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           READ WRITE NO         NORMAL

         4 ANUJ1                          MOUNTED    n/a        NEW         <<<<<<----- 

SQL> alter pluggable database ANUJ1 open ;

Pluggable database altered.

set linesize 200 

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           READ WRITE NO         NORMAL

         4 ANUJ1                          READ WRITE NO         NORMAL

set linesize 300

col FILE_NAME for a120

col Pdb_name for a20

col TABLESPACE_NAME for a20

SELECT name Pdb_name,TABLESPACE_NAME,c.CON_ID, c.TABLESPACE_NAME,c.FILE_NAME FROM CDB_DATA_FILES c,v$pdbs p

where 1=1

and c.CON_ID> 2

and c.CON_ID=p.CON_ID;

PDB_NAME             TABLESPACE_NAME          CON_ID TABLESPACE_NAME      FILE_NAME

-------------------- -------------------- ---------- -------------------- ------------------------------------------------------------------------------------------------------------------------

ANUJ                 SYSTEM                        3 SYSTEM               /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf

ANUJ                 SYSAUX                        3 SYSAUX               /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf

ANUJ                 UNDOTBS1                      3 UNDOTBS1             /u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf

ANUJ1                SYSTEM                        4 SYSTEM               /u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anuj1o1_mf_system_dq898gom_.dbf

ANUJ1                SYSAUX                        4 SYSAUX               /u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anuj1o1_mf_sysaux_dq898gpw_.dbf

ANUJ1                UNDOTBS1                      4 UNDOTBS1             /u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anuj1o1_mf_undotbs1_dq898gpx_.dbf

6 rows selected.

========
Create duplicate pluggable database in Oracle 12cR2 

SQL> def

DEFINE _DATE           = "23-JUL-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER           = "SYS" (CHAR)

DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR         = "vi" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

set linesize 200

col NAME for a30

COLUMN "RESTRICTED" FOR A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

     PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

 ---------- ------------------------------ ---------- ---------- ----------

          2 PDB$SEED                       READ ONLY  NO         NORMAL

          3 ANUJ                           MOUNTED    n/a        NORMAL <<<<< database is in mount stage 

          4 ANUJ1                          MOUNTED    n/a        NORMAL

SQL> alter pluggable database ANUJ open ;

Pluggable database altered.

set linesize 200

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

   PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           READ WRITE NO         NORMAL  <<<<<<<<<<<  create pluggable database from anuj to anujd 

         4 ANUJ1                          MOUNTED    n/a        NORMAL

SQL> !mkdir -p /u01/app/oracle/oradata/orcl/ORCL/pdb/anujd

SQL> !ls -ltr /u01/app/oracle/oradata/orcl/ORCL/pdb/anujd

total 0

CREATE PLUGGABLE DATABASE anujd FROM anuj FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/ORCL/pdb/anujd/anujd') ;

SQL> SQL>

Pluggable database created.

set linesize 200

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           READ WRITE NO         NORMAL

         4 ANUJ1                          MOUNTED    n/a        NORMAL

         5 ANUJD                          MOUNTED    n/a        NEW

SQL> alter pluggable database ANUJD open ;

Pluggable database altered.

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           READ WRITE NO         NORMAL

         4 ANUJ1                          MOUNTED    n/a        NORMAL

         5 ANUJD                          READ WRITE NO         NORMAL  

How to Drop a Pluggable Database...

Drop a Pluggable Database

set linesize 200 

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           MOUNTED    n/a        NORMAL

         4 ANUJ1                          MOUNTED    NO         NORMAL

 alter pluggable database ANUJ1 open;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           MOUNTED    n/a        NORMAL

         4 ANUJ1                          READ WRITE NO         NORMAL

set linesize 200 

col NAME for a30

select con_id,name,open_mode,open_time from v$pdbs;

    CON_ID NAME                           OPEN_MODE  OPEN_TIME

---------- ------------------------------ ---------- ---------------------------------------------------------------------------

         2 PDB$SEED                       READ ONLY  23-JUL-17 07.12.37.823 AM +01:00

         3 ANUJ                           MOUNTED

         4 ANUJ1                          READ WRITE 23-JUL-17 07.20.06.798 AM +01:00

alter pluggable database ANUJ1 close immediate;   ------ <<<<< First close the database 

set linesize 200 

col NAME for a30

select con_id,name,open_mode,open_time from v$pdbs;

     CON_ID NAME                           OPEN_MODE  OPEN_TIME

---------- ------------------------------ ---------- ---------------------------------------------------------------------------

         2 PDB$SEED                       READ ONLY  23-JUL-17 07.12.37.823 AM +01:00

         3 ANUJ                           MOUNTED

         4 ANUJ1                          MOUNTED    23-JUL-17 07.32.54.795 AM +01:00

SQL> drop pluggable database ANUJ1 including datafiles;

Pluggable database dropped.

set linesize 200 

col NAME for a30

select con_id,name,open_mode,open_time from v$pdbs;

   CON_ID NAME                           OPEN_MODE  OPEN_TIME

---------- ------------------------------ ---------- ---------------------------------------------------------------------------

         2 PDB$SEED                       READ ONLY  23-JUL-17 07.12.37.823 AM +01:00

         3 ANUJ                           MOUNTED

Pluggable Database status ....

On Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

SQL> def

DEFINE _DATE           = "23-07-2017 07:16:22" (CHAR)

DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE      = "1202000100" (CHAR)

set head off verify off echo off pages 1500 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select

'PDB_ID..................................................: '||PDB_ID ,

'PDB_NAME................................................: '||PDB_NAME ,

'DBID....................................................: '||DBID ,

'CON_UID.................................................: '||CON_UID,

'GUID....................................................: '||GUID ,

'STATUS..................................................: '||STATUS,

'CREATION_SCN............................................: '||CREATION_SCN,  

'VSN.....................................................: '||VSN, 

'LOGGING ................................................: '||LOGGING ,  

'FORCE_LOGGING ..........................................: '||FORCE_LOGGING ,

'FORCE_NOLOGGING ........................................: '||FORCE_NOLOGGING, 

'APPLICATION_ROOT........................................: '||APPLICATION_ROOT,  

'APPLICATION_PDB.........................................: '||APPLICATION_PDB, 

'APPLICATION_SEED........................................: '||APPLICATION_SEED,

'APPLICATION_ROOT_CON_ID.................................: '||APPLICATION_ROOT_CON_ID,

'IS_PROXY_PDB............................................: '||IS_PROXY_PDB,

'CON_ID..................................................: '||CON_ID, 

'UPGRADE_PRIORITY........................................: '||UPGRADE_PRIORITY,

'APPLICATION_CLONE.......................................: '||APPLICATION_CLONE,

'FOREIGN_CDB_DBID........................................: '||FOREIGN_CDB_DBID,

'UNPLUG_SCN..............................................: '||UNPLUG_SCN,

'FOREIGN_PDB_ID..........................................: '||FOREIGN_PDB_ID,

'CREATION_TIME...........................................: '||CREATION_TIME, 

'REFRESH_MODE............................................: '||REFRESH_MODE,

'REFRESH_INTERVAL........................................: '||REFRESH_INTERVAL

from CDB_PDBS

where 1=1

-- and PDB_ID=3

;

PDB_ID..................................................: 3

PDB_NAME................................................: ANUJ

DBID....................................................: 2259250378

CON_UID.................................................: 2259250378

GUID....................................................: 54F57A42B6BF1AD0E055000000000001

STATUS..................................................: NORMAL

CREATION_SCN............................................: 1524245

VSN.....................................................: 203424000

LOGGING ................................................: LOGGING

FORCE_LOGGING ..........................................: NO

FORCE_NOLOGGING ........................................: NO

APPLICATION_ROOT........................................: NO

APPLICATION_PDB.........................................: NO

APPLICATION_SEED........................................: NO

APPLICATION_ROOT_CON_ID.................................:

IS_PROXY_PDB............................................: NO

CON_ID..................................................: 3

UPGRADE_PRIORITY........................................:

APPLICATION_CLONE.......................................: NO

FOREIGN_CDB_DBID........................................: 1477822556

UNPLUG_SCN..............................................: 1523784

FOREIGN_PDB_ID..........................................: 2

CREATION_TIME...........................................: 23-07-2017 05:24:43

REFRESH_MODE............................................: NONE

REFRESH_INTERVAL........................................:

set linesize 200 

SET SERVEROUTPUT ON

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

    PDB_ID NAME                           OPEN_MODE  RESTRICTED STATUS

---------- ------------------------------ ---------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO         NORMAL

         3 ANUJ                           MOUNTED    n/a        NORMAL

         4 ANUJ1                          READ WRITE NO         NORMAL