|
How
can I check that my character set conversion process doesn't cause any data loss or data corruption?
|
More Resources by
Google: |
|
|
|
|
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
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.
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.
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.
Good
Luck!
|