"Intuition
will tell the thinking mind where to look next."
-Jonas
Salk (1914-1995)
|
How can I check that my
character set conversion process doesn't cause any data loss or data
corruption?
In order to check that your
conversion doesn't cause any data loss or create any data corruption,
use the character set scanner (csscan) utility to identify a potential
problem, or corrupted data.
Check and download if
necessary the latest version of csscan on technet
<http://technet.oracle.com/software/tech/globalization/content.html>
for your platform.
The following steps will
help you to install and run the “csscan” utility and then show the
process to convert from one type of database character set to another.
Use
of the csscan utility:
1. Make sure that the
following file exist in your server.
$ORACLE_HOME/rdbms/admin/csminst.sql
$ORACLE_HOME/bin/csscan
2. Make sure that you have
the most updated version if not replace them with the downloaded
version.
3. Run the csminst.sql
script to create the csmig user with its tables.
# sqlplus /nolog
SQL> connect system/…@...
SQL> start $ORACLE_HOME/rdbms/admin/csminst.sql
4. Once the csminst.sql
script finished, it will disconnect from sqlplus automatically.
5. Check the csminst.log
file for errors.
Now you should be able to
run the scanner utility against the database.
6. Type the following: #
csscan help=y
You see the following:
dbtest: /u01/app/oracle $csscan
help=y
Character Set Scanner:
Release 8.1.7.0.0 - Production on Tue Jul 29 2003
(c) Copyright 2000 Oracle
Corporation. All rights reserved.
You can let Scanner prompt
you for parameters by entering the CSSCAN
command followed by your
username/password:
Example: CSSCAN
SYSTEM/MANAGER
Or, you can control how
Scanner runs by entering the CSSCAN command
followed by various
parameters. To specify parameters, you use keywords:
Example: CSSCAN
SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=102400 PROCESS=3
Keyword Default Prompt
Description
---------- ------- ------
-------------------------------------------------
USERID yes
username/password
FULL N yes scan entire
database
USER yes user name of the
table to scan
TABLE yes table name to
scan
TOCHAR yes new database
character set name
FROMCHAR current database
character set name
TONCHAR new NCHAR character
set name
FROMNCHAR current NCHAR
character set name
ARRAY 10240 yes size of
array fetch buffer
PROCESS 1 yes number of
scan process
MAXBLOCKS split table if
larger than MAXBLOCKS
CAPTURE N capture
convertible data
SUPPRESS suppress error log
by N per table
FEEDBACK feedback progress
every N rows
BOUNDARIES list of column
size boundaries for summary report
LASTRPT N generate report
of the last database scan
LOG scan base name of log
files
PARFILE parameter file name
HELP N show help screen
(this screen)
---------- ------- ------
-------------------------------------------------
Scanner terminated
successfully.
dbtest: /u01/app/oracle $
7. Run the csscan utility:
# cd $ORACLE_HOME/bin
# ORACLE_SID=<your
SID>; export ORACLE_SID
# csscan system/manager
FULL=y FROMCHAR=UTF8 TOCHAR=US7ASCII ARRAY=10240
8. Check the following
files:
# more scan.err
# more scan.out
# more scan.txt
9. Once you are convinced
that you don't have any problem or exception, do the following:
a. Make sure that you
have your database recent exported dump file, structure and a copy
of init file.
# exp parfile=xxx
SQL> alter database
backup controlfile to trace;
SQL> host cp $ORACLE_BASE/admin/<database
name>/pfile/init<SID>.ora
$ORACLE_HOME/init<SID>.ora
(Keep the copy of init
file so when you recreate the database again use the same
configuration).
b. Make sure that your
database is in restricted mode.
SQL> alter system
enable restricted session;
c. Make sure no one is
using the database tables.
SQL> select username
from v$session;
d. Use the dbassist
utility to delete the database.
# dbassist (and delete
the database with character set for example UTF8).
e. Use the dbassist
utility to recreate a new database with the same <SID> and
database name with different character set (EX: US7ASCII).
f. Check and modify the
new parameter file to match with the old database configuration.
g. Shutdown and startup
the database.
h. Import the database
back with the FULL=y option.
|