Oracle 11G: Import fails: not a valid export file, header failed verification

José Perez
José Perez used Ask the Experts™
on
Hi,
I am trying to import an Oracle 11G dmp file but it is failing with the error: "not a valid export file, header failed verification"
I dont know what is wrong, my client provide me a copy of his Oracle 11G Databse dump.
My client have an Oracle 11G database (Unix)
I have an Oracle 11G database (Windows).

Can somebody help me to fix this issue?

Firstly, I created a new user:

create user userTarget identified by PASSWORD123;
grant connect, unlimited tablespace, resource to userTarget;

Open in new window


This is the Oracle command I used to import:
imp system/ABCDE FROMUSER=usrSource TOUSER=userTarget file=D:\Temp\bd_pruebas_abcde\BD_FULL_21082015.dmp full=yes

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
90% of the time when I see this it is because the file was transferred incorrectly.  When transferring the file, it was transferred in ASCII mode and not in binary mode.  Therefore translations were done on certain characters and the file is not what it was originally.

There is typically an option within the transfer program that was used that specifies the type of transfer.  If there is an automatic, do not use it, explicitly set it as a binary transfer.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Normally I agree with the above statement about the transfer mode.  When Oracle introduced the datapump utilities, transfer mode became less of an issue.

My guess is the file was created with the datapump version of export: expdp.

If so, you need to datapump version of import: impdp.

The file needs to be on the database server ion a folder that is pointed to by a database directory.

Author

Commented:
Can you please tell what commands should I use? (I am an SQL DB Adminsitrator not Oracle)
Tahnks.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
mmm probably you're perfectly right!
I was checking the logfile for the exported file and it contains:

dumpfile=DATA_PUMP_DIR:DCTM_FULL_21082015.dmp logfile=DATA_PUMP_DIR:DCTM_FULL_21082015.log full=y

Open in new window


Now if you're correct, I just need the commands to import it, can you... please?
johnsoneSenior Oracle DBA
Commented:
For file transfer typically ftp (or one of its variants) would be used.

For importing, impdp would be used.  Probably the same parameters that were used for the export would be ideal.  The DATA_PUMP_DIR object would have to be created before you imported.

I didn't think that expdp created a human readable file.  I haven't actually looked that closely at one yet.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Can you please tell what commands should I use?

It really isn't that easy.  If you don't know Oracle, it will likely be confusing and problematic.  I'm afraid I cannot provide the "run this" command because I don't know your system and requirements.

Log into the destination database as a DBA level user and run the following:
select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';

Put the file in that folder.  If it isn't large enough, you'll need to create the directory:
create or replace directory mydir as 'C:\SOME_PATH';

Now the problematic parts:
Do the tablespaces in the source database already exist in the target database?
Do all the users exist?
Do the tables already exist?
etc...  you get the idea.

If you don't have this information, just running the import can really mess with things.  It will likely attempt to create and tablespace with the data file paths from the source database.

It might just fail because things aren't set up right.

Anyway:
The parameters should be the same as the export parameters with one exception:  It looks like you want to change schema names.  I think the fromuser and touser from the original imp will also work in datapump but the new parameter is remap_schema.

If the tabpespaces are different, remap_tablespace, etc...

There are examples of these and how to use them in the online documentation and on the web.


The run the impdp with the same parameters as the export.

Author

Commented:
I created the user in target database, so the user is new and epmty but it has the same name than in its source database.

I know the client did a full database export.
I have the default tablespaces of the Oracle 11G installation in the 'oradata' folder.

This is the result of the command you suggest:

select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';
C:\app\Administrator\admin\dctm\dpdump\

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>I have the default tablespaces of the Oracle 11G installation in the 'oradata' folder.

You cannot mix and match SQL Server and Oracle terms.  I would encourage you to work directly with a local Oracle person.  They can help educate you about the differences in a way that is difficult in a Q&A site like this.

'default' probably doesn't matter unless the source database was created with 'default' options.

You really need to have a basic understanding of Oracle to understand what it might attempt to do on import.

I'll try to cover the short list:
A user that owns objects in Oracle is a schema.
Those objects exist in a tablespace.
A tablespace can have one or more data files on disk.
A data file belongs to one and only one tablespace.

Files on disk don't matter as far as the import goes as long as the tablespace names are the same.  If the import has an object that exists in the 'BOB' tablespace and 'BOB' doesn't exist in the destination database, the import might try to create it.  It will do so by using the same PATH as the source database.  If that path doesn't exist, the import will likely fail.

Since I doubt you have access to a local Oracle person:
Can you possibly trash the destination database?  If so, just try the import and post the likely errors.

If you cannot trash the destination database, get the specifics from the person that gave you the dump.  At a minimum you'll need to know the tablespaces used by ALL objects exported.

You can generate a SQLFILE from the export that will capture all the commands without executing any of the statements.    You can then go though the output and make sure you have everything you need.  Not an easy process but doable.
Geert GOracle dba
Top Expert 2009

Commented:
you could start by reading the concepts
> to see what's different in architecture between sql and ora
http://docs.oracle.com/cd/E11882_01/server.112/e40540/toc.htm

the 2day dba might also help
http://docs.oracle.com/cd/E11882_01/server.112/e10897/toc.htm

and check the import utility impdp
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1007653

it's a lot of reading to just run an import

as slight indicated, for a local oracle hire, will be a lot faster

Author

Commented:
Finally the issue was that the Export I received was don using expdp. That was the reason of the error.
Thanks all for your comments and answers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial