1-Installing and configuring CSSCAN in 10g and 11g
cd $ORACLE_HOME/rdbms/admin
set oracle_sid=
sqlplus /nolog
SQL>conn / as sysdba
SQL>set TERMOUT ON
SQL>set ECHO ON
SQL>spool csminst.log
SQL> START csminst.sql
2-Take full export of database as well other backups (RMAN or Hot)
(Do not to use expdp )
3-Invalid objects.
SQL> Select owner, object_name, object_type, status from dba_objects where status ='INVALID';
4-Orphaned Datapump master tables (10g and up)
SELECT o.status, o.object_id, o.object_type,
o.owner'.'object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
5-Drop default schemas
For example : The 'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT' users are by default sample schema's.
6-Check the Source database for "Lossy" (invalid code points in the source character set).
Note: Always run Csscan connecting with a 'sysdba' connection/user,do not use "system" or "cmsig" user.
csscan FULL=Y FROMCHAR=current_charaterset TOCHAR=target_charaterset LOG=log_file_name CAPTURE=N ARRAY=1000000 PROCESS=2
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2
This will create 3 files :
dbcheck.out a log of the output of csscan
dbcheck.txt a Database Scan Summary Report
dbcheck.err contains the rowid's of the Lossy rows reported in dbcheck.txt (if any).
If all the data in the database is stored correctly at the moment then there should only be "Changeless" data reported in dbcheck.txt.
If this is the case please go to point 5).
7-Check for "Convertible" and "Truncation" data when going to AL32UTF8
csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2
This will create 3 files :
toutf8.out a log of the output of csscan
toutf8.txt the Database Scan Summary Report
toutf8.err contains the rowid's of the Convertible and Lossy rows reported in toutf8.txt
8-Objects in the recyclebin
Sqlplus>PURGE DBA_RECYCLEBIN;
9-Steps needed to use Alter Database Character Set / Csalter:
For 10g and up:
a. Export all the "Convertible" data (tables) ( no tables belong to sys schemas) ( NLS_LANG is set to the current database character set)
b. Truncate the exported tables of point a
c. Run csscan again with the syntax of point 5) to verify you only have "convertible" CLOB in the data dictionary and all other data is "changeless".
d. If this is now correct then proceed to step 10), otherwise do the same again for the rows you've missed out.
e. Import the exported data again.
10-Running Csalter/Alter Database Character Set
For 10g and up
shutdown
startup restrict
SPOOL Nswitch.log
@@?/rdbms/admin/csalter.plb
-- Csalter will aks confirmation - do not copy paste the whole actions on one time
-- sample Csalter output:
-- 3 rows 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
-- old 6: if (UPPER('&conf') <> 'Y') then
-- New 6: if (UPPER('y') <> 'Y') then
-- Checking data validility...
-- begin converting system objects
-- PL/SQL procedure successfully completed.
-- Alter the database character set...
-- CSALTER operation completed, please restart database
-- PL/SQL procedure successfully completed.
...
-- Procedure dropped.
shutdown
startup
11-Check the character set
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
12 - Reload the data pump packages after a change to AL32UTF8 in 10g and up.
If you use 10g or up then the datapump packages need to be reloaded after a conversion to AL32UTF8. In order to do this run the following scripts from $ORACLE_HOME/rdbms/admin in sqlplus connected as "/ AS SYSDBA":
For 10.2.X and higher:
catnodp.sql
catdph.sql
catdpb.sql
For 10.1.X:
catnodp.sql
catdp.sql
13- Possible error
---------------
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
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
Unrecognized convertible date 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.
4 rows deleted.
---------------
Check the log file toutf8.err 's "[Application data individual exceptions]"
Export all the tables coming under above session and truncate them
Re-run CSSAN command and check again “Application data individual exceptions”
csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2
=================More info================
Note:745809.1 Installing and configuring CSSCAN in 10g and 11g
Note:225912.1 Changing the Database Character Set - a short overview
Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)
Note:258904.1 Convertible data in data dictionary: Workarounds when changing character set
