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:
Post a Comment