Solved

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

Posted on 2016-09-18
8
114 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 37

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 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 37

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

691 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