Good morning all,
I am trying to import a client dmp file into our database, however I kept getting the same error messages. The versions that the client and us using are the same, Oracle 7.3.2 on Unix HP, same database setting including schema. When I import the dmp file, I used,
imp system/pwd file=file.dmp fromuser=dail touser=dail grants=n ignore=y indexes=n
Then I got these errors,
Import: Release 220.127.116.11.0 - Production on Thu Mar 28 08:30:08 2002
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Connected to: Oracle7 Server Release 18.104.22.168.0 - Production Release
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 22.214.171.124.0 - Production
IMP-00037: Character set marker unknown
IMP-00021: operating system error - error code (dec 2, hex 0x2)
IMP-00000: Import terminated unsuccessfully
I checked the metal-link, and found couple documents discussing IMP-00037, said it was caused by corrupted export file. I asked the client to export the dmp file again, and ensure that the file was exported successfully, compressed with size around 2M, and ftp in binary mode. And again I got the same error message.
The IMP-00021: operating system error said the file was not found. Can anyone tell me why the OS cannot find the file while the Oracle was able to open the file (appear to be)? Is there possibly a corruption in the dmp file or I need to reset the NLS_CHARACTERSET during imp? If I do, how would I do that? Please advise how to import this file.
Check out the character set on both the servers using
select * from v$nls_parameters;
Make sure they are the same.If not ,then just change the import or export script to make the NLS settings same.
Compare the character sets between the two databases. Are they the same?
You might need to change your character set as you have mentioned.
A neat possible work-around to this problem (i believe I ran into this problem also) is to use the IMPORT GUI 'Data Manager' that is provided with Oracle (comes with Oracle 8 toolset). For some reason, the GUI ignores or corrects the character-set problem. When I used the GUI to Import my file, I didn't get the errors I was getting from command line. It is worth a try.
Not sure what tools are provided with Oracle 7. Did they have GUIs with that version? I doubt you could use an 8.0 import/eport manager with a 7.3.2 DB.
You might have to use command line to change character sets.
Ok, I'll play Captain Obvious here.
You did un-compress the .DMP file after you FTP'ed it over didn't you?
Does it have the proper .dmp extension?
If it is only 2M why not try NOT compressing it and sending it over.
[Edited by Mr.Hanky on 03-28-2002 at 10:13 AM]
Manpreet, here are what we have for the NSL parameters in both databases,
PARAMETER VALUE-local OREGON-client
-------------------- ----------- ---------
NLS_LANGUAGE AMERICAN AMERICAN
NLS_TERRITORY AMERICA AMERICA
NLS_CURRENCY $ DEFAULT
NLS_ISO_CURRENCY AMERICA DEFAULT
NLS_NUMERIC_CHARACTE ., DEFAULT
NLS_DATE_FORMAT DD-MON-YY D EFAULT
NLS_DATE_LANGUAGE AMERICAN DEFAULT
NLS_CHARACTERSET US7ASCII DEFAULT
NLS_SORT BINARY DEFAULT
NLS_CALENDAR GREGORIAN NOT DEFINED
Some of the parameters are different, but Language, territory, and character set are the same. Do I need to reset the parameters that are different? How do I do that in import script, can you provide an example?
Jgmagnus, thanks for the advise, but we don't have IMPORT GUI 'Data Manager' installed. So command line is the only option that is available.
Mr.Hanky, the un-compress file does not have .dmp extension. How would this make the different, please explain? I have getting several dmp files without .dmp extention and they were imported fine. I am going to try to use uncompress dmp file for import, hope that solve the poblem.
Thanks for all you help!
doesn't matter what the extension is on the file (.dmp or no .dmp) as long as export created the file. You can name the file when you export. Some dbas like to name their dumps with extensions like .exp so they know they are exports. It makes no difference.
try importing the uncompressed file that has no .dmp extension and see what happens.
The client re-dumped the file and ftp without compressed. This time the import went successfully. My guess is for the original attempt that somewhere in the process of compressing and ftp, the file was corrupted. Thank you very much for all of your helps!