9 Replies Latest reply: Jan 14, 2014 3:50 AM by Suntrupth RSS

Store Multi Byte Characters in WE8ISO8859P1 Database without Migration

user8599674 Newbie
Currently Being Moderated

Hi - I am looking for a solution where I can store the Multi Byte Character's under the WE8ISO8859P1 Database.

 

Below are the DB NLS_PARAMETERS

 

NLS_CHARACTERSET = WE8ISO8859P1

NLS_NCHAR_CHARACTERSET = AL32UTF8

NLS_LENGTH_SEMANTICS = BYTE

 

Size of DB = 2 TB.

DB Version = 11.2.0.4

 

Currently there is a need to store the Chinese Characters under NAME and ADDRESS Columns only. Below are the description of the columns.

 

Column Name            DataType

GIVEN_NAME_ONEVARCHAR2(120 BYTE)
GIVEN_NAME_TWOVARCHAR2(120 BYTE)
LAST_NAMEVARCHAR2(120 BYTE)
ADDR_LINE_ONEVARCHAR2(100 BYTE)
ADDR_LINE_TWOVARCHAR2(100 BYTE)
ADDR_LINE_THREEVARCHAR2(100 BYTE)

 

What are my option's over here without considering the Migration WE8ISO8859P1  DB to AL32UTF8 ?

 

1. Can I increase the size of the Column i.e make it n x 4. e.g NAME will be 480 Byte  and ADDRESS will be 400 Byte.? What are pros and cons ?

2. Convert the existing Column from VARCHAR2 to NVARCHAR2 with the Same Size ? i.e NVARCHAR2(120 BYTE) ?

3. Add the extension to an table with new columns - NVARCHAR2. e.g NAME - NVARCHAR2(120 CHAR) and ADDRESS (100 - CHAR) ?

4. Database got Clobs,Blobs, Long etc. got Varied Data, Is it a good idea to Migrate to AL32UTF8  with Minimal Downtime ?

 

Please suggest the best alternatives. Thanks.

 

 

Thanks

Jitesh

  • 1. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Suntrupth Expert
    Currently Being Moderated

    Hi Jitesh,

     

    NLS_NCHAR_CHARACTERSET can either be AL16UTF16 or UTF8. So mostly your DB would have UTF8.

     

     

    You can definitely insert Unicode characters into N-type columns. Size of the N-type column will depend on the characters you plan to store in them.

     

     

    If you use N-types, do make sure you use the (N'...') syntax when coding it so that Literals are denoted as being in the national character set by prepending letter 'N'.

     

     

    Although you can use them, N-types are  not very well supported in 3the party client/programming environments, you may need to adapt a lot of code to use N-types properly and there are some limitations.

    While at first using N-types for a (few) columns seems like a good idea to avoid the conversion of a whole database , in many cases the end conclusion is that changing the NLS_CHARACTERSET is simply the easiest and fastest way to support more languages in an Oracle database.

     

     

    So, It depends on how much of your data will be unicode which you would store in N-type characters.

     

     

    If you do have access to My Oracle Support you can check Note 276914.1 :The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g , 11g and 12c, For more details.

     

     

    With respect to your Downtime, The actual conversion (CSALTER or in case using DMU) shouldn't take too much time, if you have run CSSCAN on your DB and made sure you have taken care of all your truncation, convertible and lossy data (if any).

     

     

    It would be best for you to run CSSCAN initially to gauge how much convertible/lossy/truncation data you need to take care.

     

     

    $ CSSCAN FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=P1TOAl32UTF8 ARRAY=1000000 PROCESS=2 CAPTURE=Y FULL=Y

     

     

     

    Regards,

    Suntrupth

  • 2. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl clarify your question - WE8ISO8859P1 is a single-byte characterset - it cannot store multi-byte characters.

     

    You cannot store Chinese characters using this characterset. What is the reason for not converting to AL32UTF8 ?

     

    ISO/IEC 8859-1 - Wikipedia, the free encyclopedia

  • 3. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    user8599674 Newbie
    Currently Being Moderated

    Thanks Suntrupth. I am going to run CSSCAN as a part of initial investigation and will take it from there. One more thing, I was exploring was to expand the size of column. Let's say name = varchar2(120 Byte) , I will expand name column size to Varchar2(480 Byte). Basically this will store English Character upto 480 characters and if it's 4 byte character set ( Japanese/Chinese), will hold up-to 120 byte ?

     

    Will these solution feasible ? or One need to have  AL32UTF8 to store the multi byte character set.

     

    Thanks once again....

     

    Thanks

    Jitesh

  • 4. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    user8599674 Newbie
    Currently Being Moderated

    Srini - Thanks for the feedback.

     

    To answer you question - What is the reason for not converting to AL32UTF8 ?

     

    I haven't ran the CSSCAN as a part of initial investigation, so not sure how much is truncation, convertible and lossy data. Another thing is Database is real time and can't afford huge downtime. Before presenting the solution to Management for DB migration to new character set, I am evaluating the pros and cons of alternative.

     

     

    Thanks

    Jitesh

  • 5. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Other than converting to AL32UTF8, I do not believe you have an alternative. Oracle Consulting has a solution where an in-place conversion to AL32UTF8 is possible - pl check with them for the solution and pricing

     

    HTH
    Srini

  • 6. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated

    Some recommendations from Oracle:

     

    0. Do not store Chinese in WE8ISO8859P1!

    1. Do not generally use NCHAR data types for reasons mentioned already by Suntrupth. Even the Oracle Database itself does not fully support them. For example, you cannot create an Oracle Text index on NCHAR columns. There are also serious limitations in XDB.

    2. If the need to store non-English text is very limited, like just to two columns, and your test migration to AL32UTF8 shows that the downtime is not acceptable, you can consider using NCHAR-type columns. You need to confirm that your application can be modified to access NCHAR-type columns without data loss. Specific configuration/coding is usually required to assure this. You must also confirm that all RDBMS features that you want to use with the columns are supported with NCHAR data types.

    3. Do not use CSSCAN/CSALTER with 11.2.0.4. Use Database Migration Assistant for Unicode (DMU).

    4. For mission-critical environments, a possible (though not yet well tested) solution is to use Oracle Streams to minimize the downtime. The idea is to create a physical copy of the production database, set up Streams logical replication between original and the copy, stop the replication (changes accumulate in archive log files), migrate the copy to AL32UTF8, re-establish replication to catch up with the accumulated changes (Streams will convert the changes to AL32UTF8 before applying them), and once the copy catches up with production, fail over production to the copy.

     

     

    Thanks,

    Sergiusz

  • 7. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Suntrupth Expert
    Currently Being Moderated

    Hi Jitesh,

     

    It is always advised to change your DB characterset to AL32UTF8 as I see you would be planning for future insertion of Japanese / Chinese characters.

     

    N-Type hasn't been designed for huge amount of unicode data insertions as stated it has several limitations.

     

    Sergiusz has shown you couple more limitations which make N-types not the best fit.


    Most applications do not work well with N-type columns.


    Thanks to Sergiusz who has pointed out the Streams option.


    If you do not have too much data that needs conversion, your best bet would be to move to AL32UTF8.


    Basically this will store English Character upto 480 characters and if it's 4 byte character set ( Japanese/Chinese), will hold up-to 120 byte ?

    ---> You will not be able to store Japanese/chinese in a varchar2 column in a WE8ISO8859P1 DB.


    Regards,

    Suntrupth

  • 8. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    user8599674 Newbie
    Currently Being Moderated

    Thanks Sergiusz & Suntrupth, your comments are really appreciated and helpful.

     

    Thanks

    Jitesh

  • 9. Re: Store Multi Byte Characters in WE8ISO8859P1 Database without Migration
    Suntrupth Expert
    Currently Being Moderated

    Hi Jitesh,

     

    You're most welcome. Good luck.

     

    Regards,

    Suntrupth

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points