Solved

Oracle export/import characterset error

Posted on 2010-11-18
18
952 Views
Last Modified: 2012-05-10
Hi

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 10.2.0.5.0 - 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?

Clues:
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.
0
Comment
Question by:ATOCONS
  • 11
  • 4
  • 2
  • +1
18 Comments
 
LVL 8

Expert Comment

by:Rindbaek
Comment Utility
the database and the user exporting the data may have different language settings. I sugegst you have a look at this article:
http://oraclespin.wordpress.com/2008/09/28/setting-nls_lang-for-exportimport/

i think it pretty much covers it
0
 

Author Comment

by:ATOCONS
Comment Utility
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

OR

- 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)

0
 

Author Comment

by:ATOCONS
Comment Utility
Actually, it turns out we tried the first option too, in desparation, but that didn't work either.
0
 
LVL 8

Expert Comment

by:Rindbaek
Comment Utility
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
?
0
 

Author Comment

by:ATOCONS
Comment Utility
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
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
LOCPATH=/usr/lib/nls/loc
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
0
 

Author Comment

by:ATOCONS
Comment Utility
Straighyt copy from my DBA. Jayesh not available to tell me mainframe non-database settings (OS settings etc)
0
 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
hi
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.
0
 

Author Comment

by:ATOCONS
Comment Utility
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 9.2.0.8.21 - 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 9.2.0.8.21 - Production      
With the Partitioning and Oracle Label Security options                        
JServer Release 9.2.0.8.20 - 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                                                      

            :
0
 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
Hi

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
---------------------------  -----------
NLS_CHARACTERSET    TH8TISASCII

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.


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ATOCONS
Comment Utility
Reports WE8ISO8859P1
0
 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
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

0
 

Author Comment

by:ATOCONS
Comment Utility
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
0
 

Author Comment

by:ATOCONS
Comment Utility
I'll have to wait to Monday to find out whether they specified a user, and which user it was.
0
 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
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.
0
 

Author Comment

by:ATOCONS
Comment Utility
Will check on Monday but why would that cause a Taiwanese character set issue/error?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
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)
0
 

Accepted Solution

by:
ATOCONS earned 0 total points
Comment Utility
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
0
 

Author Closing Comment

by:ATOCONS
Comment Utility
Resolved outside EE
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now