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
Solved

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

Posted on 2016-09-18
8
83 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

837 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