How to change character set after migration from 10g to 11g ?

Hi experts,
I have created a new database db2 in 11g. The reason is to import all data (using datapump) from existing database db1 which is Oracle 10g.
The import process went well. I manage to see most of imported tables. However there are few tables that shows strange character of specific column.

After some checking, then I realised that our existing db1 is using different character set as the new db2.

--SELECT * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

Output from DB2
NLS_CHARACTERSET WE8MSWIN1252
NSL_NCHAR_CHARACTERSET AL16UTF16

Output from DB1
NLS_CHARACTERSET UTF8;
NSL_NCHAR_CHARACTERSET UTF8;

Based on this, all imported data from DB1 was using different character set as new one.
So then I changed DB2 to UTF8 character set.
ALTER DATABASE CHARACTER SET UTF8;

Output from DB2
NLS_CHARACTERSET UTF8
NSL_NCHAR_CHARACTERSET AL16UTF16

But when I try to display the data from the table, it show error message : ORA-29275: partial multibyte character

How can I fix this ?

Thanks.
VW 63654Asked:
Who is Participating?
 
Geert GOracle dbaCommented:
so in your create database statement, you have characterset AL32UT8 like in this sample ?
http://docs.oracle.com/database/121/SQLRF/statements_5005.htm#sthref4697

you don't need to change the national character set

export in a cmd :
set nls_lang=american_america.UTF8
exp ...

and then import also with
set nls_lang=american_america.UTF8
imp ...
0
 
DansDadUKCommented:
I know nothing about Oracle databases, but I'd guess that when you imported data from the DB1 database, which used UTF-8 encoding (which can reference Unicode code-points in the range U+000000 to U+10FFFF), some of the characters quite possibly had no equivalent in the much smaller 8-bit CP1252 (Windows Latin-1) coded character set.

The characters for which no mapping exists were probably replaced by a 'substitute' character; all replaced characters may now have the same substitute value, so it is probably not possible to retrieve the original code-point values without going back to the original DB1 tables.
0
 
Geert GOracle dbaCommented:
You'll have to recreate db2 with characterset al32utf8 and run the export with nls_lang ending on .utf8
You wiil have lost most unicode text anyway
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
VW 63654Author Commented:
I have created new db3 but with AL32UTF8 & UTF8.
I can't set character set to utf8. It show an error ora-12712: new character set must be a superset of old character set.

When I imported the table, it show same output as initial import.
Is there any workaround to get exactly same output as the sourceDB db1?
The db1 still running. Is there anything I can do to change the setting to db1 before I re-export the table?
0
 
VW 63654Author Commented:
Geert Gruwez,
Actually I am using Oracle 11g Database Configuration Assistance GUI to create the database not from the script as you refer to the oracle link. For export and import, I am using Data Pump tools.
expdp and impdp.

Can you tell me how to include nls_lang parameter in both export and import command?

Thanks.
0
 
Geert GOracle dbaCommented:
you can't include the nls_lang parameter in expdp or impdp
either you set it in the windows registry in the oracle client settings or at runtime

if you are executing this on the database host, don't change the registry, but use the runtime approach

registry:
Hkey_local_machine/Software/Oracle
> below that you should find a node for Oracle_Client_Home01 or similar
and in that: NLS_LANG
if you change that to end with UTF8, all apps will use unicode to communicate with the client
if you have a 32-bit client on a 64-bit machine, look under HKLM\Software\Wow6432Node\Oracle and then for the client settings

runtime:
if you want to just set it 1 time in a command prompt (or in a batch.cmd):
set NLS_LANG=AMERICAN_AMERICA.UTF8
EXPDP user/password@alias DIRECTORY=SERVERDIR DUMPFILE=...
0
 
VW 63654Author Commented:
Hi guys,
It works now. But I have to re-import after charset changes, which include drop schema and tables.
The one that I posted on 2016-09-20 at 11:52:41 is without drop & reimport. I just change the charset but not reimport schema/table.

So the current set is db3  with AL32UTF8 & UTF8.

Thanks guys.
0
 
VW 63654Author Commented:
Thanks to all for your kind assistance. Your advice did direct me to solve the problem and I have to do my own research and testing to accomplish.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.