ORACLE10g the database character set conversion problems - a failure experience

Jan 6 posted in Oracle (RSS), 288 views.

Question:

The database character set has been modified, by the zhs16gbk been modified US7ASCII, so can not correctly display Chinese, the following procedure is to modify the character set to come back.

select 'hello' from dual;

'??'

----

??

The other, the character set query:

SELECT NAME, VALUE $ FROM SYS.PROPS $ WHERE NAME LIKE '% NLS%';

SELECT * FROM V $ NLS_PARAMETERS;

Environment:

select * from v $ version;

BANNER

-------------------------------------------------- ------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL / SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

Theoretical basis for:

- From the ORACLE DOC in the query to the following:

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

Do a full backup of the database, because the CSALTER script cannot be rolled back.

Start up the database.

Run the Database Character Set Scanner utility.

CSSCAN / AS SYSDBA FULL = Y. ..

Run the CSALTER script.

@ @ CSALTER.PLB

SHUTDOWN IMMEDIATE; - or SHUTDOWN NORMAL;

STARTUP;

Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm # CEGHDCJF

Experiment:

1 shutdown;

SQL> shutdown immediate

Database has been closed.

The database has been uninstalled.

ORACLE instance shut down.

2 because it is their own testing library, do not do a Full Backup;

3 Use CSSCAN check the character set conversion will not lead to data loss

CSSCAN \ "sys / orcl @ orcl10 AS SYSDBA \" FULL = Y TOCHAR = zhs16gbk ARRAY = 102400 PROCESS = 3

D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ BIN> CSSCAN \ "sys / orcl @ orcl10 AS SYSDBA \" FULL = Y FULL = y TOCHAR = zhs16gbk ARRAY = 102400 PROCESS = 3

Character Set Scanner v2.1: Release 10.2.0.0.0 - Production on Saturday, January 2 11:03:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

* Problems

Solution: Create a data dictionary

@ $ Oracle_home \ rdbms \ admin \ csminst.sql

Continue to:

CSSCAN system / orcl FULL = y TOCHAR = zhs16gbk ARRAY = 102400 PROCESS = 3

In the implementation of the directory (for example, in this experiment: D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ BIN>) generated scan.txt scan.err scan.out three files, you can check the character set conversion will take to the problem.

@ @ CSALTER.PLB

@ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ rdbms \ admin \ csalter.plb

SQL> @ D: \ WORK_SOFT \ app \ product \ 10.2.0 \ db_1 \ rdbms \ admin \ csalter.plb

0-line has been created.

Function has been created.

Function has been created.

The process has been created.

This script will update the content of the Oracle Data Dictionary.

Please ensure you have a full backup before initiating this procedure.

Would you like to proceed (Y / N)? Y

Original value 6: if (UPPER ( '& conf') <> 'Y') then

The new value 6: if (UPPER ( 'y') <> 'Y') then

Checking data validility ...

Exceptional data found in scanner result

PL / SQL procedure successfully completed.

Checking or Converting phrase did not finish successfully

No database (national) character set will be altered

CSALTER finished unsuccessfully.

PL / SQL procedure successfully completed.

0 rows deleted.

Function has been deleted.

Function has been deleted.

The process has been deleted.

Note: You can see from the implementation of the process, the character set conversion process is not successfully implemented, from the reasons is because CSSCAN checking out to see some of the conversion problems caused by data loss.

Conclusion:

Own this experiment did not succeed, at present they do not know how to resolve those BLOB field (mostly SYSMAN in the table).

First, this problem does not get entangled, decided to rebuild the database.

Last:

Accompanied by two reference documentation (CSSCAN use):

http://www.ningoo.net/html/2007/using_csscan_before_character_set_conversion.html

http://www.oracle-base.com/articles/10g/CharacterSetMigration.php

rn rn rn rn rn rn rn