Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Any specific reason why you're changing the charset at all? (And please don't cross-post. You should have flagged your original question for moderator attention to get it migrated here.) –  Mat Oct 13 '12 at 6:54
    
I was (wrongly?) under the impression that we8mswin1252 was no longer recommended under 11, did I get it wrong? Appologies about my missbehaibour, didn't know you could move post between SE sites. Thanks for letting me know. –  frenetix Oct 13 '12 at 13:18
    
No longer recommended doesn't mean unsupported. Changing the character set could have an impact on the applications that use the database if they're not expecting it/not setting up their environment properly. (That being said, your question is perfectly valid, and I'm afraid I don't know enough to answer it. I was just thinking that knowing why you wanted to do that might help more knowledgeable people giving you more relevant help.) –  Mat Oct 13 '12 at 13:20
    
Thanks again. Having reviewed the fields that need changing, and knowing the 1 web app which connects to this db, I don't foresee any complications in that regard. So I'm willing to trade a bit of complications now, if it means ease of mind for the future, if it's worth it. I don't have a clue if it is, though. :) –  frenetix Oct 13 '12 at 13:31

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks, this looks a lot safer than my approach. Now I'm beginning to question the need to change the charset... it certainly hasn't been requested, and I only brought this up because it was set by default, and after I started reading some posts I began to think it was the wisest... but as they say, if ain't broken, don't fix it :) –  frenetix Oct 14 '12 at 3:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.