• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1145
  • Last Modified:

Oracle export/import characterset error


I have a couple of DBAs trying to transfer some data from a mainframe Oracle 9i database to a Unix 10g database.
The export is taken, setting no explicit NLS_LANG, as per Oracle recommendation and using the 9i Export utility.

The file is transferred using binary mode FTP to the Unix box, and Import from the 10g is used for the import, explicitly setting NLS_LANG to the target database setting.

The following error occurs:
Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

Export file created by EXPORT:V09.02.00 via direct path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export client uses VN8MSWIN1258 character set (possible charset conversion)
IMP-00069: Could not convert to environment national character set's handle
IMP-00000: Import terminated unsuccessfully

Now, the source database is NOT in Vietnamese. If the database language is queried it says it is WE8ISO8859P1.
And yet the Import utility thinks it is Vietnamese!
The mainframe itself is set to English locale, obviously.

What can be going on here?

these may be clues or red herrings, but are observations...
The export is a complete export and is 148GB

If we take export of a small number of schemas, as we have recently, and we do it remotely (that is, connecting via a remote Unix 9i box ansd exporting directly to the 9i server, then transferring the dump to the 10g box) it loads just fine. We haven't been able to try the full export remotely because it impacts the mainframe server for many hours whereas the local export takes only a few hours (3ish).

Setting the Import to use the reported VN8MSWIN1258 character set still throws an error.

VN8MSWIN1258 looks like a Windows characterset. Where could a mainframe export pick this up?Can the client PC used to connect to Oracle affect anything?

Thanks for any help.
  • 11
  • 4
  • 2
  • +1
1 Solution
the database and the user exporting the data may have different language settings. I sugegst you have a look at this article:

i think it pretty much covers it
ATOCONSAuthor Commented:
Thanks for the link.

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

- Before starting export set NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.
- Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

This is what the Oracle documentation tells you not to do


- Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.
- Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

This is what we have tried and results in Import telling us the export used Vietnamese character set (and I see no way we could have done as it would have to be the default character set (see first point) and we are running an IBM mainframe in English with EBCDIC)

ATOCONSAuthor Commented:
Actually, it turns out we tried the first option too, in desparation, but that didn't work either.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Normally i would use the first option where NLS settings are the same for the user doing the export and the import (and it works fine for me)

I normally works on Unix and on occation on windows but i have no experience with mainframe but i think we  can sort it out anyhow ;-)

what NLS settings are set in the environment
a) the mainframe
b) the unix where the schema exports works during import
ATOCONSAuthor Commented:
What NLS settings are set in the environment
a) the mainframe
Parameter                                                        Value                                                            
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CALENDAR                                                     GREGORIAN                                                        
NLS_CHARACTERSET                                                 WE8EBCDIC1047                                                    
NLS_COMP                                                         BINARY                                                          
NLS_CURRENCY                                                     £                                                                
NLS_DATE_FORMAT                                                  DD-MON-RR                                                        
NLS_DATE_LANGUAGE                                                ENGLISH                                                          
NLS_DUAL_CURRENCY                                                ¿                                                                
NLS_ISO_CURRENCY                                                 UNITED KINGDOM                                                  
NLS_LANGUAGE                                                     ENGLISH                                                          
NLS_LENGTH_SEMANTICS                                             BYTE                                                            
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                        
NLS_NCHAR_CONV_EXCP                                              FALSE                                                            
NLS_NUMERIC_CHARACTERS                                           .,                                                              
NLS_SORT                                                         BINARY                                                          
NLS_TERRITORY                                                    UNITED KINGDOM                                                  
NLS_TIME_FORMAT                                                  HH24.MI.SSXFF                                                    
NLS_TIME_TZ_FORMAT                                               HH24.MI.SSXFF TZR                                                
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH24.MI.SSXFF                                          
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH24.MI.SSXFF TZR      
This is for the database, Jayesh can tell you what it is on the mainframe:

b) the unix where the schema exports works during import
ATOCONSAuthor Commented:
Straighyt copy from my DBA. Jayesh not available to tell me mainframe non-database settings (OS settings etc)
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
i want to know on which user was your export and on which user you are importing?

which is the verision of 10g?

can you give import and export steps which you performed.
ATOCONSAuthor Commented:
Here's what the logs show when the instance starts - note the character set info:
22:31:29.76 00010009  ARC0: Archival started                                                              
22:31:29.76 00010009  ARC0: Becoming the 'no FAL' ARCH                                                    
22:31:29.76 00010008  CJQ0 started with pid=9, OS id=65544                                                
22:31:29.76 0001000A  ARC1 started with pid=11, OS id=65546                                                
22:31:29.76 0001000A  ARC1: Archival started                                                              
22:31:29.81 0001000A  ARC1: Becoming the heartbeat ARCH                                                    
22:31:29.81 00010009  ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted                            
22:31:29.86 0001000A  ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted                            
22:31:29.87 00020000  ALTER DATABASE MOUNT                                                                
22:31:34.07 00010004  Successful mount of redo thread 1, with mount id 3870235393                          
22:31:34.07 00020000  Database mounted in Exclusive Mode.                                                  
22:31:34.07 00020000  Completed: ALTER DATABASE MOUNT                                                      
22:31:34.07 00020000  ALTER DATABASE OPEN                                                                  
22:31:34.68 00010001  ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=NM00)(PORT=1521))'
22:31:41.55 00010004  LGWR: Primary database is in CLUSTER CONSISTENT mode                                
22:31:41.78 00010004  Thread 1 opened at log sequence 220513                                              
22:31:41.78 00010004    Current log# 4 seq# 220513 mem# 0: //'ORACLE.NSSL.REDO.F02'                        
22:31:41.78 00010004  Successful open of redo thread 1                                                    
22:31:41.78 00020000  MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set                  
22:31:41.79 00010006  SMON: enabling cache recovery                                                        
22:31:42.17 00010006  SMON: enabling tx recovery                                                          
22:31:42.20 00020000  Database Characterset is WE8EBCDIC1047                                              
22:31:42.79 00020000  replication_dependency_tracking turned off (no async multimaster replication found)  
22:31:42.89 00020000  Completed: ALTER DATABASE OPEN                                                      
==> Date for following entries is 2010/10/25                                                              

And here is part of the log during export:
Export: Release - Production on Thu Oct 14 02:00:04 2010            
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.            
Note: RECORDLENGTH=65536 truncated to 65535                                    
Connected to: Oracle9i Enterprise Edition Release - Production      
With the Partitioning and Oracle Label Security options                        
JServer Release - Production                                        
Export done in WE8EBCDIC1047 character set and AL16UTF16 NCHAR character set    
About to export the entire database ...                                        
. exporting tablespace definitions                                              
. exporting profiles                                                            
. exporting user definitions                                                    
. exporting roles                                                              
. exporting resource costs                                                      

Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:

before importing check this in ur unix server it should be same as in ur mainframes if not first set it to same then import.

eg :
SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER                VALUE
---------------------------  -----------

imp scott/tiger@fuju file=nicoleData.dmp
import done in TH8TISASCII character set and TH8TISASCII NCHAR character set
. importing NICOLE's objects into NICOLE
Import terminated successfully without warnings.

ATOCONSAuthor Commented:
Reports WE8ISO8859P1
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
i asked used from which user you are importing the dump in Unix  box

e.g imp scott/tiger@fuju file=nicoleData.dmp

from which user you have exported the dump from mainframe box

e.g exp scott/tiger@fuju file=nicoleData.dmp

ATOCONSAuthor Commented:
I'm not sure of user, but since they are both DBAs with administrator access, and are running Import and Export utilities rather than doing something "as a user" I'm not sure why the question
ATOCONSAuthor Commented:
I'll have to wait to Monday to find out whether they specified a user, and which user it was.
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
sys and system user both has aministrator access but still system doesn't have all the right that sys user thats why i asked you for the user.
ATOCONSAuthor Commented:
Will check on Monday but why would that cause a Taiwanese character set issue/error?
Wasim Akram ShaikCommented:
Users, from user and to user should not make any difference as  the import error shows that its related to CHARACTER SET of dataase...

as you said that you had tried option of matching the character set too..!!

just wanted to look at the output(error what have you got after changing the character set of target database)

select * from nls_database_parameters where name in('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET')

these two queries will return different outputs in source(from which the export has taken)
and target(on which the import is  being applied)

if you match the character set of both the databases and retry the import, you shouldn't see these two lines

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export client uses VN8MSWIN1258 character set (possible charset conversion)
ATOCONSAuthor Commented:
None of the suggestions here worked but my Oracle resources fixed it eventually. I don't know what they did so can't help anyone else reading this, but it's all done now

Thanks for sugestions anyway
ATOCONSAuthor Commented:
Resolved outside EE
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 11
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now