8

We were handed a dump file by another team who are gone away for the rest of the year. When we try to import the dump into our own database, we get the following error:

IMP-00038: Could not convert to environment character set's handle

Upon some research, our speculation is that we have a mismatch between the NLS_LANG setting of the source machine and our local machine. We currently don't have any means to check what the value of NLS_LANG is on the source machine.

So, having just a dump file in our hands, is there a way to figure out the NLS_LANG value with which the export was done? From the looks of it, we should be able to override the NLS_LANG environment variable before running the import client (imp).

Another thing is, the dump was done from an 11g instance and our imp version is 10. I read that imp is not forward compatible. Could this be the issue here (instead of the NLS_LANG mismatch)?

Ates Goral
  • 137,716
  • 26
  • 137
  • 190

5 Answers5

11

Ates, try impdp - sometimes that could help :-)

Stephan
  • 111
  • 2
  • 6
    Using `imp` to import a dump file that was exported using `expdp` can also cause this error. – Ken Liu Sep 19 '11 at 14:57
  • you can identify an impdp dmp via the head of the dmp file (e.g. get-content abc.dmp -First 10) and then look for "system.your_schema". See: http://www.acehints.com/2013/04/how-to-identify-check-find-out-oracle.html – timB33 May 10 '16 at 12:08
  • I got the same error and it was actually because it was exported with **expdp** and **impdp** worked. – detoro84 Jul 19 '18 at 14:20
6

easiest way on unix is:

#>imp username/password file=test.dmp show=y

Import: Release 10.2.0.3.0 - Production on Fri Nov 26 08:38:47 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)

Version exp/imp is a problem: I usually use the V10 export program and make it connect to the V11 database. Make sure you have a alias for dev11 in your tnsnames.ora in dev10's oracle_home.

hostname{oracle}# . oraenv
ORACLE_SID = [oracle] ? dev10
hostname{oracle}#
hostname{oracle}#>exp username/password@dev11 full=y dumpfile=dump.exp
Rob van Laarhoven
  • 8,737
  • 2
  • 31
  • 49
  • Is it possible to use show=y with only an Oracle client installation? So that I don't need any username/password? – guerda Mar 18 '16 at 12:24
3

Even though the file is a binary garble, there are some human-readable text excerpts. I saw the following strings in there, and I think this answers my question:

<CHARSET>AL32UTF8</CHARSET><NCHARSET>AL16UTF16</NCHARSET>
...
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'
Ates Goral
  • 137,716
  • 26
  • 137
  • 190
  • 1
    Thanks. I don't think I would have got the idea to look for that in a dump. On Linux this worked nicely (even with quite a large dump file): `strings mydumpfile.dmp | grep "NLS_"` – stefitz Mar 04 '19 at 10:38
1

Maybe it was exported using expdp....try impdp ....that is what I saw when searching google, and truly it worked for me for this same issue.

Asraful
  • 1,241
  • 18
  • 31
1

Another thing is, the dump was done from an 11g instance and our imp version is 10. I read that imp is not forward compatible. Could this be the issue here (instead of the NLS_LANG mismatch)?

A: You 're right; You cannot import a dump file created with a given Oracle Client, since the Oracle Client of your Target Oracle Database is older.

Although it is not recommended, you can export a newer Source Oracle Database (i.e. 10g+) with an older Oracle Client (i.e. 10g), since you are using the same Oracle Client version to import into your older Target oracle Database (i.e. 10g).

Assumption: The Oracle Client version of your Source Database is the same -or newer- as your Target Oracle Database version. Note that mixing tools Datapump (11g) and imp (-10g) import utility does not work.

Interessant link Using Different Releases and Versions of Export

philippeko
  • 501
  • 5
  • 16