Search This Blog

Total Pageviews

Sunday, 23 July 2017

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  

No comments:

Oracle DBA

anuj blog Archive