Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

We have a Oracle database 8.1.7 running for many years on Win-2000. We wanted to move the database on a newer machine running Win 2003. I did the following

  • I made a cold backup of old database.
  • Installed the oracle database same ver on the new machine and moved the data files from the old to the new server.
  • Imported the data dump taken from export (taken from old server) on the new server.
  • Without a full test we shifted live to the new server.
  • Everything was OK on the new oracle server except for the Arabic characters. I am getting ?? (questions marks) characters in the Arabic fields.

I checked the nls_database_parameters on both the servers. 

Select * from nls_database_paramters;

The old database had: nls_char & nls_nchar = Ar8mswin1256 The new database had nls_char & nls_nchar = We8iso8859p1

I then did the following in the new server:

  • Changed the character set (nls_char)in the new database to AR8mswin1256.
  • Changed the Nls Nchar set to Ar8mswin1256.

All the above done were by an alter database command as below: 

SVRMGR> STARTUP MOUNT;  
SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION;  
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;  
SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
SVRMGR> ALTER DATABASE OPEN;  
SVRMGR> AALTER DATABASE NATIONAL CHARACTER SET ar8mswin1256;  
SVRMGR> SHUTDOWN IMMEDIATE;  
SVRMGR> STARTUP;

I checked nls_database_parameters in the new server both nls_char, nls_nchar was changed to Ar8mswin1256.

I checked the database through our ERP applicatoin but still Arabic characters displaying in ??.

I then checked the registry of new Oracle server. The value of nls_lang is We8iso8859p1
whereas the registry of the old server had value of nls_lang as Ar8mswin1256. I changed
the value in new server to be American_america.Ar8mswin1256. Checked the database still
arabic characters displaying as ??.

The registry of Client has nls_lang as American_america.Ar8mswin1256.

It is a production database. How can I solve the problem of Arabic characters being displayed as ??? in the new server?

I still have the old server database but it is not live. It doesnt contain updated
data.

share|improve this question
2  
The Arabic characters that are being displayed as ???, are any of those characters that had been added to the database after changing the character set? My guess is that the import into a database with character set We8iso8859p1 from an export of ar8mswin1256, probably resulted in translation of Arabic characters to ? that will not be recoverable, except by going back to the original database/export file. No idea of the steps taken are enough to make the database the character set you wanted. – Shannon Severance Sep 4 '12 at 19:39
How did you change the character set in the new database? You can't change the character set easily with a database that contains data. Everything needs to be re-encoded. I suggest you export your data and import your data into a newly created Ar8mswin1256 database. – Vincent Malgrat Sep 5 '12 at 10:01
Your alter database command doesn't seem to be changing the database character set, just the national character set. Did you re-run the import after changing the database character set (assuming you did, in fact, change the database character set)? And are you stating that new transactions have been executed against the database after the import but before the character set was changed? – Justin Cave Sep 5 '12 at 15:04

migrated from stackoverflow.com Sep 5 '12 at 8:51

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.