Oracle 10 to 11: charset issue

character-setoracle

I was redirected from SE's root site to this one.

I'm not a DBA, so I shouldn't be doing this, but I have to, so I apologize beforehand to all of you DBAs that will read this post:

I'm migrating an Oracle database from 10.2.0.4.0 to 11.2.0.1.0 from win 2003 to win 2008 both 64-bit.

I planned, for starters, to export/import and see what happened, and I found that while most of it went well, I had an issue with different charsets: On 10 I have WE8MSWIN1252 and on the new 11 it's using AL32UTF8.

I get "value is too long" for a few records on a few tables. Everything else seems to be in reasonably good shape.

I read somewhere that the length issue is related to a few mono-byte characters getting translated to multi-byte hence I need to increase the size of my columns with problems.

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch11charsetmig.htm#g1011430

I'll be doing just that:
1) Letting the import/export service do the charset translation.
2) I'll run an import for the metadata only,
3) then alter the tables with the issues,
4) and afterwards import the data.

I'll be using the EM console to do that because, well, my PL/SQL skills suck. 🙂

Anything extremely wrong with that approach? Any tips/advice you might think of?

Thanks in advance.

Best Answer

I had the same issue with a migration from 9i to 11g. I decided that I did not want to take a chance that Oracle's character set conversion would do something I did not expect. Managers don't care about character sets, they just want to know that the data was migrated without any problems. I kept the WE8MSWIN1252 character set and there were no problems.

However, if you had to have a UTF8 character set, and did not want to take any chances this is how I would do it.

Requirements: a virtual machine in a test environment, to make the install and testing painless

  • start on the original 10g database in a test environment.
  • start a new instance of 11g on a virtual machine in a test environment with the old character set
  • import your data using a data pump or the old imp/exp.
  • test the web application to see what happens
  • if successful take a snapshot of the machine so you can revert to this
  • establish what columns need to be larger. Usually these are LONG or possibly large VARCHAR2 fields
  • increase the size of the columns so conversion is not required
  • if you are able set up another Oracle 11 with the new character set and use the data pump to bring the data over. If this is not feasible then do an export of your data and bring the virtual machine back to before the Oracle install. Then install Oracle 11 g with the new character set and import the data. Ideally no conversion will occur if you have identified all the tables.
  • test the web application

Why go to all this extra work? This solution breaks your project into two issues, the upgrade and the character set. Upgrading databases is not to be considered lightly. Many times it is not the upgrade that is the problem but peripheral problems like interactions with applications or other databases. Custom PL/SQL code should have no problems but you never know.

If there is a problem people will be looking to you to explain how you were diligent and careful not how you saved a day or two.

Related Question