Thanks Tom, but I when I start the migration I need to be sure -

1. That all the currently running transactions are complete
2. And nobody else start any new transaction when the migration is going on.
(We would shut down the web site from where the transactions are done, but still there are background processes and unfinished transactions going on)

Oracle 9.2.0.6

What are the considerations that should be taken into account when migrating a 9.2.0.6 US7ASCII database to UTF8? How much testing is needed? Where is all this documented? I know about the Character Set Scanner utility (csscan). Can you please point me to some docs/notes that explain this in detail?

Thanks

Hm, but UTF8 is a strict superset of US7ASCII, right? In other words, if I run the CSSCAN utility and it tells me that all my data is 7-bit ascii, I can do the ALTER DATABASE CHARACTER SET command.

"each character takes 1,2,3 or more bytes in multibyte character sets)"

But each 7-bit ASCII character in the US7ASCII character set still takes just 1 byte in the AL32UTF8 character set, right?

Thanks

I did the ALTER DATABASE CHARACTER SET AL32UTF8; command on a US7ASCII database and got

12716, 00000, "Cannot ALTER DATABASE CHARACTER SET when CLOB data exists"
// *Cause: CLOB data changes representation to Unicode when converting to a
// multibyte character set and must be migrated
// *Action: Remove CLOB data as listed in the alert file. CLOB data can be migrated
// by methods such as import/export


Not sure how to handle this. Some of my application schemas have CLOB columns, even the data dictionary (sys.snap$, sys.deflob and HTML DB's flows_010600) schemas have a bunch of CLOB columns.

Does this mean I cant use the ALTER DATABASE command to migrate my characterset to AL32UTF8?

Thanks

Metalink has a note on this Note 66320.1

Basically, it acknowledges that the 9i dictionary itself has CLOB columns so the ALTER DATABASE CHARACTER SET command will not work even on a brand new database with no user data.

It describes how to do this.

Basically, do a
select owner,table_name,count(*)
from dba_tab_columns
where data_type='CLOB'
and owner!='SYS'
group by owner,table_name;

Put all those owner.table_name into a exp parfile with tables=(...), export them, truncate them (if truncate complains about foreign keys, use DELETE).

Now the ALTER DATABASE CHARACTER SET works fine.

Then re-import all the tables with ignore=y constraints=n grants=n indexes=n

Worked like a charm for me

If I issue the ALTER DATABASE CHARACTER SET <new character set>; command on the primary database, would it properly change the characterset of my physical standby database?

Thanks

I have one question about CSSCAN when I do conversion from WE8ISO8859P1 to AL32UTF8 unicode character set.

Below is the test case.

create table aaa (c1 varchar2(10));
insert into aaa values ('sys.¿Med');

After csscan the data, get the application exception data as below

------------------------------------------------
[Application data individual exceptions]
User : JACKY
Table : AAA
Column: C1
Type : VARCHAR2(10)
Number of Exceptions : 1
Max Post Conversion Data Size: 9
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAYtgAAEAAAEKPAAA lossy conversion sys.¿Med
------------------ ------------------ ----- ------------------------------

But after use convert method and check it, the data can be converted successfully after use below sql

select case when convert(convert(C1, 'AL32UTF8', 'WE8ISO8859P1') ,'WE8ISO8859P1', 'AL32UTF8') = C1 then 0 else
-999999+111111 END from aaa;

Below is dump of data

1. dump in AL32UTF8 character set
select dump(convert(c1,'AL32UTF8')) from aaa
DUMP(CONVERT(C1,'AL32UTF8'))
----------------------------------------------------
Typ=1 Len=9: 115,121,115,46,194,133,77,101,100
2. dump in WE8ISO8859P1 character set
select dump(c1) from aaa
DUMP(C1)
----------------------------------------------------
Typ=1 Len=8: 115,121,115,46,133,77,101,100
Is report of cssacn not correct?

Thanks

I have changed the character set from WE8ISO8859P1 to WE8MSWIN1252 using cscan/csalter scripts, but the physical standby database character set is still showing as WE8MSWIN1252. The Oralce Support says that recreating the standby database is the only supported way to change the character set on the standby database.

Hi Tom

Having difficulty understanding process in going from
NLS_CHARACTERSET WE8MSWIN1252 to
NLS_CHARACTERSET AL32UTF8

Environment:
Windows 2003 64-bit running oracle 10g 64-bit
windows 2008 r2 64-bit running oracle 11g r2 64-bit

Assumptions:
CScan has been run and all issues have been resolved for conversion

Two scenarios:

1. I need to convert a 10g database in place from WE8MSWIN1252 to AL32UTF8

Can I just run CSALTER on the database and the conversion will happen or do I need to do more ?

2. I need to move/convert the 10g database (WE8MSWIN1252) into an 11g database (AL32UTF8)

Can I just create the 11g database as AL32UTF8 and then export the 10g
schema into the 11g database ?

Thanks for Your Time
Jim

Thanks for your time and input Tom

I will review the document

Jim

Hi Tom

after running Csscan on my database i have "Convertible" data. Any idea on what that means and what would happen if i now run CSALTER ?

Example Csscan output:


[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ----------- ---------------- ---------------- ----------------
VARCHAR2 9,737,307 1,957 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 131,273 127,978 0 0
VARRAY 1,575 0 0 0
--------------------- ----------- ---------------- ---------------- ----------------
Total 9,870,155 129,935 0 0
Total in percentage 98.701% 1.299% 0.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

USER.TABLE Convertible Truncation Lossy
--------------------------------- ---------------- ---------------- ----------------
CDRL.DAUDITMORE 21 0 0
CDRL.DAUDITNEW 118 0 0
CDRL.DAVPROP 11,784 0 0

(rest if report truncated)




Thanks
Jim

Hi Tom


Thanks for the info and your time on this. Will read it. I found this nice document about Migrating Database Character Sets to Unicode by Yan Li. It was also very helpful

http://www.nyoug.org/Presentations/2009/Li_Unicode.pdf

Jim

Hi Tom

I just coompleted a sucessful change to NLS_CHARACTERSET

I went from WE8MSWIN1252 to AL32UTF8
using CSSCAN,CSALTER and DataPump Export/Import

My question is about the NLS_LANG Registry setting
on the server and client side

It currently is set to AMERICAN_AMERICA.WE8MSWIN1252

Do I need to change it on the server and client side
to now reflect AL32UTF8 or can it stay the same ?

Thanks Jim

Thanks again Tom

Appreciate the assist

Jim