?
Solved

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

Posted on 2016-09-18
8
Medium Priority
?
125 Views
Last Modified: 2016-09-25
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.
0
Comment
Question by:VW 63654
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:DansDadUK
ID: 41804592
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41805652
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
 

Assisted Solution

by:VW 63654
VW 63654 earned 0 total points
ID: 41805854
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 41805993
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
 

Author Comment

by:VW 63654
ID: 41806053
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41806072
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
 

Assisted Solution

by:VW 63654
VW 63654 earned 0 total points
ID: 41813370
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
 

Author Closing Comment

by:VW 63654
ID: 41815260
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

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