ORA-1537 ORA-3280 Reported For Create Tablespace When Runing DataPump Import
(Doc ID 1351252.1)
Last updated on MARCH 05, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.2 [Release 11.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Full DataPump import shows the errors below while creating the tablespaces:
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01537: cannot add file '<PATH>/redo04.log' - file already part of database
SQL statement: CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M,'<PATH>/redo02.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M;
ORA-01537: cannot add file '<PATH>/redo04.log' - file already part of database
SQL statement: CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M,'<PATH>/redo02.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M;
or:
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-03280: invalid DATAFILE filename specified
Failing sql is:
CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE
'<PATH>/redo08_3.log' SIZE 10485760,
'<PATH>/redo08_2.log' SIZE 10485760,
'<PATH>/redo08_1.log' SIZE 10485760,
'' SIZE 10485760,
ORA-03280: invalid DATAFILE filename specified
Failing sql is:
CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE
'<PATH>/redo08_3.log' SIZE 10485760,
'<PATH>/redo08_2.log' SIZE 10485760,
'<PATH>/redo08_1.log' SIZE 10485760,
'' SIZE 10485760,
Checking the tablespace definition with dbms_metadata.get_ddl in the source database:
SQL> connect / as sysdba
SQL> set pagesize 50 long 20000
SQL> select dbms_metadata.get_ddl ('TABLESPACE', '<TABLESPACE_NAME>') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','BDM_DATA')
--------------------------------------------------------------------------------
CREATE TABLESPACE "<TABLESPACE_NAME>"
DATAFILE '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M LOGGING ONLINE
PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS
SEGMENT SPACE MANAGEMENT AUTO;
SQL> set pagesize 50 long 20000
SQL> select dbms_metadata.get_ddl ('TABLESPACE', '<TABLESPACE_NAME>') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','BDM_DATA')
--------------------------------------------------------------------------------
CREATE TABLESPACE "<TABLESPACE_NAME>"
DATAFILE '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, '<PATH>/redo04.log' SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M, SIZE 106496 AUTOEXTEND ON NEXT 65536 MAXSIZE 32767M LOGGING ONLINE
PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS
SEGMENT SPACE MANAGEMENT AUTO;
But for this tablespace, the data dictionary views show different information:
SQL> select FILE_NAME, STATUS from dba_data_files where TABLESPACE_NAME = 'BDM_DATA';
FILE_NAME STATUS
---------------------------------------------------------- ------------------------
<PATH>/o1_mf_bdm_data_72zrk0tm_.dbf AVAILABLE
FILE_NAME STATUS
---------------------------------------------------------- ------------------------
<PATH>/o1_mf_bdm_data_72zrk0tm_.dbf AVAILABLE
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |