Arabic characters not displaying in oracle database

oracle

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.

Best Answer

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE AR8MSWIN1256;

SHUTDOWN IMMEDIATE;

 -- or SHUTDOWN NORMAL; 

STARTUP;
Related Question