Go Premium for a chance to win a PS4. Enter to Win


Oracle export/import characterset error

Posted on 2010-11-18
Medium Priority
Last Modified: 2012-05-10

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.
Question by:ATOCONS
  • 11
  • 4
  • 2
  • +1

Expert Comment

ID: 34164426
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

Author Comment

ID: 34164499
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)


Author Comment

ID: 34164565
Actually, it turns out we tried the first option too, in desparation, but that didn't work either.
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Expert Comment

ID: 34164641
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

Author Comment

ID: 34165635
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

Author Comment

ID: 34165650
Straighyt copy from my DBA. Jayesh not available to tell me mainframe non-database settings (OS settings etc)
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 34166913
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.

Author Comment

ID: 34174232
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                                                      

LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 34174987

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.


Author Comment

ID: 34176131
Reports WE8ISO8859P1
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 34179570
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


Author Comment

ID: 34180094
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

Author Comment

ID: 34180098
I'll have to wait to Monday to find out whether they specified a user, and which user it was.
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 34180357
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.

Author Comment

ID: 34180608
Will check on Monday but why would that cause a Taiwanese character set issue/error?
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37529427
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)

Accepted Solution

ATOCONS earned 0 total points
ID: 37833250
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

Author Closing Comment

ID: 37850321
Resolved outside EE

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question