Database characterset change

[suhasd75]

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

[PAVB]

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.

[suhasd75]

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

[PAVB]

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?

[suhasd75]

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