Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-18
8
Medium Priority
?
143 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

618 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