OraFAQ Forum: Backup & Recovery » Language Problem while exporting and importing data

Home » RDBMS Server » Backup & Recovery » Language Problem while exporting and importing data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Language Problem while exporting and importing data [message #133792]

Mon, 22 August 2005 04:46

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,
I have Oracle version 8.1.7.0.0 installed on one server and 9.2.0.1.0 installed on new server.
I'm copying and pasting my version info from SQL*Plus:
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 22 10:46:31 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL>
----------------------------------------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Aug 22 12:30:06 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>

I created new user on my new server from enterprise manager.
Exported user from the old server and imported in the new server.

i.e: from Oracle8i Enterprise Edition Release 8.1.7.0.0, I did
c:\>exp system/manager file=abc.dmp owner=abc

Then on the new server Release 9.2.0.1.0, I did
c:\>imp system/manager file=abc.dmp fromuser=abc touser=abc

I'm using Arabic Language on my both servers. NLS_LANG parameter on both the servers is AMERICAN_AMERICA.WE8MSWIN1252.

On both the servers I'm able to insert and select data in arabic.
However, after I export the data from old server to the new server, the arabic data comes in question marks.
If I create new table and insert arabic data on new server's user abc it is displaying well. Only the data which I exported and imported is not showing arabic.
On both old and new servers operating system is Windows XP.

I'm stuck with this problem. Anybody having any idea about how to solve this problem please help.

Thank you all in advance.

Regards

[Updated on: Mon, 22 August 2005 04:48]

Report message to a moderator

Re: Language Problem while exporting and importing data [message #133846 is a reply to message #133792]

Mon, 22 August 2005 07:10

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

set your NLS_LANG in your environment during export
set NLS_LANG=your_NLS_LANG.
from 8i machine do the export.

c:\>exp system/manager@8idatabase file=abc.dmp owner=abc

In the same session with the same NLS_LANG setting,
from the same 8i machine do import into 9i machine.
Create tnsservicename that talks to 9i machine.

c:\>imp system/manager@9idatabase file=abc.dmp fromuser=abc touser=abc

Report message to a moderator

Re: Language Problem while exporting and importing data [message #133874 is a reply to message #133792]

Mon, 22 August 2005 08:43

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,
First of all, I would like to thank you for the reply.
I added the entry to connect to new server from old server in tnsnames.ora file on the old server.

I checked the NLS_LANGUAGE setting in the registry in my old server and it is AMERICAN_AMERICA.AR8MSWIN1256 (for arabic).

Then I exported the data from the old server
c:\>exp system/manager@myoldserver file=abc.dmp owner=abc
After that I imported the data on new server by
c:\>imp system/manager@mynewserver file=abc.dmp fromuser=abc touser=abc
No success. The result is same.
However, there is onething worth mentioning here.
On my old server I connected to SQL*Plus with system/manager and wrote these queries.

SQL> SELECT * FROM nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AR8ISO8859P6
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         AR8ISO8859P6
NLS_RDBMS_VERSION              8.1.7.0.0

18 rows selected.

SQL> SELECT * FROM v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-YY
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 AR8ISO8859P6
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           AR8ISO8859P6
NLS_COMP                                                         BINARY

17 rows selected.

Here I found that NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET=AR8ISO8859P6 as you can see.
Do I need to change this to AR8MSWIN1256?
If so then how I can change this?
I tried this

SQL>ALTER SESSION SET NLS_LANGUAGE=AMERICAN_AMERICA.AR8MSWIN1256;
It's giving me the error "missing or invalid option"

You suggested :

set your NLS_LANG in your environment during export
set NLS_LANG=your_NLS_LANG.

I tried to change it in SQL*PLus but it returned the error. Can you explain this sentence. With this, if you mean the setting in the registry or setting in SQL*Plus? Please verify because in the registry, my language setting is right.
Now please suggest, what should I do now?

Thank you again for having patience with my long question.

Regards.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #133876 is a reply to message #133874]

Mon, 22 August 2005 09:02

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I should have been clear.
I asked to set NLS_LANG in Environment ( which may or may not directly fix the issue. But always it is the place to start)
Which means, in your OS session. NOt in sql*plus session.

C:\ set NLS_LANG=yourNLS_LANG

try the NLS_LANG as inyour source database first.
using the same NLS_LANG import into target.
then
try the reverse ( NLS_LANG as in your target, export, import).
Idea is to have NLS_LANG common for both export/import.
In a few cases oracle will automatically do the conversion. I am not sure with arabic.
Please try. we shall give a try.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #133889 is a reply to message #133876]

Mon, 22 August 2005 11:06

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,
Thank you again for your reply. I tried with what you suggested but still in vain.
Can you suggest an alternative method for this issue?
Thanks again for your prompt replies.

Regards.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #133890 is a reply to message #133889]

Mon, 22 August 2005 11:14

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It has always worked for me.
Could you post a snapshot of what you did?
(Environment settings, export / import snaphshot first 10 lines enoug)

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134000 is a reply to message #133889]

Tue, 23 August 2005 03:37

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,
Thanks again for taking so much interest to solve thiis issue.
I'm sorry I didn't reply you soon. It's because I was checking different things to fix this issue but still no success.
Let me review again the things for your consideration.
I mistakenly wrote before that the servers are using Windows Xp.
This is the configuration:
Source Server: Microsoft Windows2000 Advanced Server (Service Pack4)
Target Server: Microfost Windows Server 2003 (Service Pack 1)

Source Database: Release 8.1.7.0.0
Target Database: Release 9.2.0.1.0

Let me SELECT the characterset from both the databases for your consideration.

SQL> SELECT * FROM v$nls_parameters   (from Release 8.1.7.0.0)
  2  WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------------
NLS_CHARACTERSET                                                 AR8ISO8859P6
NLS_NCHAR_CHARACTERSET                                     AR8ISO8859P6

SQL> conn system/manager@anba.world   (from Release 9.2.0.1.0)
Connected.
SQL> SELECT * FROM v$nls_parameters
  2  WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------------
NLS_CHARACTERSET                                                 WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                                      AL16UTF16

1- You can see that the characterset is same in source database and for this set I have NLS_LANG entry in the registry of the old server as AMERICAN_AMERICA.AR8MSAWIN
Note: Arabic is working fine on old server for this characterset.

2- NLS_PARAMETERS are different on the new server but Arabic language is working fine with these parameters.

There is one more thing to mention.
On my old server, I have only one user named merlin75.
On my new server, I have 2 users plus I'm adding this user merlin75, which mean I'll use 3 users on my new server.
If I change the NLS_LANG on my new server from AMERICAN_AMERICA.WE8MSWIN1252 to AMERICAN_AMERICA.AR8MSAWIN, arabic language dosn't work for all the 3 users on new server.
(Please keep in mind that the 2 users on the new server were created and the programs are developed on the new database.)

Can you tell me onething, how can I ALTER the session for NLS_PARAMETERS. I tried this :

SQL> SELECT * FROM v$nls_parameters
  2  WHERE parameter LIKE '%CHARACTERSET%'
  3  /

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------------
NLS_CHARACTERSET                                                 WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                                           AL16UTF16

SQL> ALTER SESSION
  2  SET NLS_CHARACTERSET = AR8ISO8859P6;
ALTER SESSION
*
ERROR at line 1:
ORA-00922: missing or invalid option

I'm attaching one file. This snapshot is from the old server. You can check what I did. If you need some more information please tell me.
After exporting and importing the user on new server from the old server, I went to new server, exported the user and reimported the it again but still no success.

Thank you again for your cooperation.

Regards.

Attachment: one.JPG
(Size: 264.21KB, Downloaded 1487 times)

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134064 is a reply to message #134000]

Tue, 23 August 2005 08:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

YOu no need do set anything in sql*plus.
Obviously from your DOS session, there is a typo

You have given
NLS_LAN=xxxxx
it is supposed to be
NLS_LANG=xxxxx
You can see from your session during import it is picking UTF8 from nowhere.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134077 is a reply to message #134064]

Tue, 23 August 2005 08:25

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

ooops.....I repeat the process again without the typo mistake and come to you again with the results.

Thank you.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134136 is a reply to message #134077]

Tue, 23 August 2005 11:33

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,
I did it this time without the typo mistake but the result is same...(sorry to say).
The output from the command prompt is the same as before.

SQL> SELECT * FROM v$nls_parameters   (from Release 8.1.7.0.0)
  2  WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------------
NLS_CHARACTERSET                                                 AR8ISO8859P6
NLS_NCHAR_CHARACTERSET                                           AR8ISO8859P6

SQL> conn system/manager@anba.world   (from Release 9.2.0.1.0)
Connected.
SQL> SELECT * FROM v$nls_parameters
  2  WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ------------------------
NLS_CHARACTERSET                                                 WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                                           AL16UTF16

The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET on the old server is the same while it is different on the new server. Does it make any difference?
How can I make the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET same on the new server like they are same on the old server?
After exporting the user, I can Insert as well as select the arabic data in this user's table. But the old data is not showing. Instead question marks appear.
Can you please tell me how can I make both the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET entries same on the new server.
ALTER SESSION command is not working as you can see from my previous post.
Please suggest what changes should I do on the new server?
I have attached the new file, you can check. It is same as the previous one.
I'm looking forward to your reply.

Thanks a lot.

Attachment: expimp.JPG
(Size: 210.16KB, Downloaded 1412 times)

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134142 is a reply to message #134136]

Tue, 23 August 2005 11:53

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

if your source NLS_CHARACTERSET is AR8ISO8859P6
then use
set NLS_LANG=AMERICAN_AMERICA.AR8ISO8859P6 for export and import.

Why are you setting it as AMERICAN_AMERICA.AR8MSAWIN?

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134161 is a reply to message #134142]

Tue, 23 August 2005 13:44

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

hi,

Thanks again for having patience with my posts.
set NLS_LANG=AMERICAN_AMERICA.AR8ISO8859P6 also doesn't work. Someone on the OTN forum suggested me the FAQs from oracle for NLS_LANG.
http://forums.oracle.com/forums/thread.jspa?threadID=321487&tstart=0
I better have a look on that before moving ahead.

However, I really appreciate your feedbacks for my post.

Thank you again and regards.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134165 is a reply to message #134161]

Tue, 23 August 2005 13:54

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

That is also a possibility.
Justin Caves's question is very valid and holds truth.

Report message to a moderator

Re: Language Problem while exporting and importing data [message #134350 is a reply to message #134165]

Wed, 24 August 2005 07:51

saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn

Senior Member

Hi,
The issue is resolved. I deleted the database from dca.exe on the new server and created it again for the characterset AR8ISO8859P6 and National Characterset UTF8.
Now, after importing the user, I can get the arabic data.
Thank you so much for your prompt replies and suggestions.

Regards.

Report message to a moderator

Previous Topic:	help for export
Next Topic:	How can I change oracle service name.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 11:10:33 CST 2024