Database characterset change
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Database characterset change

  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66

    Database characterset change

    Hello,

    Oracle 10.2.0.3 on Linux 4 64-bit; DB size 170G

    I am looking at ways to change a database's characterset from WE8ISO8859P1 to AL32UTF8.

    I went through a few Metalink documents and ran the CSSCAN tool as well.
    The tool reported several convertible, truncation and Lossy data.

    My question is this: which is the best method for characterset migration?

    1. Create new DB with AL32UTF8 characterset and import only the schema(s) affected? (after increasing truncation table columns and exporting the schema(s)

    2. Create new DB with AL32UTF8 characterset and use transportable tablespace?

    If anyone hs actually done the migration, I would appreciate them sharing their experience.

    Thanks!
    Suhas

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would...
    1- Precreate database as AL32UTF8
    2- Precreate tables in affected schema
    3- Export schema from WE8ISO8859P1 database
    4- Import from_schema into_schema into AL32UTF8 with ignore=y option
    5- Check object counts between affected schemas of both databases.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Thanks very much, Pablo!

    The database is quite big and export/import would take too long. Even standby databases will not work in this case.
    Time is of essence. I do not have great computing resources that can import a 600G database within 2 days (max).

    Transportable TBS: dont they help at all? forgive my ignorance.

    I have CSSCAN output of all cases: Convertible, Lossy and Truncation

    Thanks!
    Suhas

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    As far as I remember one of the limitations of Transportable Tablespaces is that both source and target database must have the same characterset - which in this case defeats the purpose.

    Please somebody correct me if I'm wrong.

    By the way, how your 170 Gig database ended up sized at 600 Gig?
    Last edited by PAVB; 11-02-2010 at 01:36 PM. Reason: Typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Hello Pablo,

    I have a few databases and two are around 170g and another is 600g (this also needs a characterset change, btw and I am more concerned in this case as the downtime window is very less). Sorry for the confusion!

    And we cannot even use Standbys since they also need to have the same characterset :(

    And yes, TTS is out of question.

    So... exp/imp is the only option, I suppose.

    Thanks very much for the nice tips!

    Anyone else who have successfully done characterset migration are requested to share their experience.

    Thanks & Regards,
    Suhas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width