CODEWITHSUNDEEP
oracle-database
Pedro Nunes
Pedro Nunes

Reputation: 31

Invalid file name when importing a DMP file to very different database

I created a completely new Oracle database and I am trying to import a DMP file from a full backup of another database and I am getting several errors.

Command: 

impdp system/welcome1 full=yes directory=BACKUPSDR dumpfile=bck_full_AXISPROD_15012018.dmp logfile=bck_full_AXISPROD_15012018.LOG

Error:

Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name
Failing sql is:
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 209715200 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

Upvotes: 3

Views: 5189

Answers (2)

arve0
arve0

Reputation: 3647

As you can see in the SQL listed, DATAFILE does not have a value. This means that Oracle will try to create a datafile at the default location. If that location is not set, CREATE will fail.

You can check the default location for tablespaces with

SQL> show parameter DB_CREATE_FILE_DEST;

NAME                  TYPE     VALUE
--------------------- -------- ------------------------------
db_create_file_dest   string

Above, it has no value. To set the value, use alter system set: 

SQL> alter system set DB_CREATE_FILE_DEST='/ORCL/u02/app/oracle/oradata/ORCL/orclpdb1';

System altered.

SQL> show parameter DB_CREATE_FILE_DEST;

NAME                  TYPE     VALUE
--------------------- -------- ------------------------------------------
db_create_file_dest   string   /ORCL/u02/app/oracle/oradata/ORCL/orclpdb1

Here, /ORCL/u02/app/oracle/oradata/ORCL/orclpdb1 is the path for tables spaces in the first pluggable database (PDB), using the Oracle 12.2.0.1 container from https://container-registry.oracle.com/

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

The following doesn't have to be right, but might give you some ideas.

I've formatted a long you posted, just to emphasize --> here:

ORA-02236: invalid file name

CREATE UNDO TABLESPACE "UNDOTBS1" 
DATAFILE                                     --> here
SIZE 209715200 AUTOEXTEND ON 
NEXT 5242880 MAXSIZE 32767M 
BLOCKSIZE 8192 EXTENT 
MANAGEMENT LOCAL AUTOALLOCATE

Datafile name is, as you can see, missing. Is it valid?

If you - in the source database - extract DDL used to create tablespaces, such as the following example on my 11g XE, you'll see something like this:

SQL> select dbms_metadata.get_ddl ('TABLESPACE', tablespace_name)
  2  from dba_tablespaces;

  CREATE UNDO TABLESPACE "UNDOTBS1" 
  DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA   --> here

  CREATE TABLESPACE "SYSAUX" 
  DATAFILE                                   --> here
  SIZE 10485760
  AUTOEXTEND ON NEXT 104

UNDO tablespace contains datafile name. SYSAUX does not. How come? If you show current value of DB_CREATE_FILES_DEST (which, if set, tells Oracle where to create datafiles by default):

SQL> show parameter DB_CREATE_FILE_DEST;
db_create_file_dest                  string

you might see something. In my XE, that parameter isn't set.

Therefore, I suppose that IMPDP expected the same datafile location as it was set in the source database. If it doesn't exist, it raised the error.

Could you check it?

If it appears that it is the cause of your problems, you should extract CREATE TABLESPACE commands (as I did), modify datafile names so that they aren't invalid any more and pre-create tablespaces. If you're unsure of how large they should be, run

SQL> select tablespace_name, sum(bytes) / (1024 * 1024) size_in_MB
  2  from dba_segments
  3  group by tablespace_name;
SYSAUX                           643,8125
UNDOTBS1                          10,1875
USERS                              4,1875
SYSTEM                            355,875

Then repeat the IMPDP and exclude tablespaces, such as

exclude=tablespace:"IN ('UNDOTBS1', 'USERS')"

Upvotes: 1

Related Questions