CSSCAN and CSALTER
In this article I will be showing how to perform character set conversion in Oracle 11g. Please note that the process described here is not applicable for Oracle 12c as we have the Data Migration Assistant for Unicode (DMU) to serve the same purpose.
In this scenario I am going to convert my 11g database, named tamaldb, from WE8ISO8859P15 to WE8MSWIN1252.
In order to perform character set conversion using csscan we first need to ensure that the CSIMG user schema is available. We can check this by using the following the following sql command:
SQL> select username from dba_users where username like 'CSM%'; USERNAME ------------------------------ CSMIG In case the schema is not present the create it as follows:
SQL> @?/rdbms/admin/csminst.sql
Once we have ensured that the schema is available we will need to scan the database to specify from which character set and to which character set we want to perform the conversion. Execute the csscan script to perform it:
[oracle@node1 admin]$ csscan \"sys/oracle@tamaldb as sysdba\" full=y log=csslog Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Sat Sep 24 15:55:32 2016 . process 1 scanning CTXSYS.DR$PARALLEL . process 1 scanning CTXSYS.DR$DBO . process 1 scanning CTXSYS.DR$INDEX_CDI_COLUMN . process 1 scanning CTXSYS.DR$SDATA_UPDATE . process 1 scanning EXFSYS.RLM$RULESETSTCODE Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully
Once the script execution has completed it will create the following 3 files with the extension .err , .log and .txt. Of special interest are the .err file and the .txt file which will have details of any object that is not compatible with the conversion process. As for example here it created the following three files:
[oracle@node1 ~]$ ls csslog.err csslog.txt Desktop scan.err scan.txt csslog.out db_11_2_0_3 oradiag_oracle scan.out First taking a look at the csslog.err file. to find if there are any error
[oracle@node1 ~]$ vi csslog.err
[Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name tamaldb Database Version 11.2.0.3.0 Scan type Full database Scan CHAR data? YES Database character set WE8ISO8859P15 FROMCHAR WE8ISO8859P15 TOCHAR WE8MSWIN1252 Scan NCHAR data? NO Array fetch buffer size 1024000 Number of processes 6 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Data Dictionary individual exceptions] [Application data individual exceptions]
Next let's look at the csslog.txt file to find in which schema the incompatible objects are present [oracle@node1 ~]$ vi csslog.txt
Database Scan Summary Report Time Started : 2016-09-24 16:48:27 Time Completed: 2016-09-24 16:48:54 Process ID Time Started Time Completed ---------- -------------------- -------------------- 1 2016-09-24 16:48:27 2016-09-24 16:48:53 2 2016-09-24 16:48:28 2016-09-24 16:48:53 3 2016-09-24 16:48:28 2016-09-24 16:48:53 4 2016-09-24 16:48:27 2016-09-24 16:48:53 5 2016-09-24 16:48:28 2016-09-24 16:48:54 6 2016-09-24 16:48:28 2016-09-24 16:48:53 ---------- -------------------- -------------------- [Database Size] Tablespace Used Free Total Expansion ------------------------- --------------- --------------- --------------- --------------- SYSTEM 698.13M 1.88M 700.00M .00K SYSAUX 508.19M 21.81M 530.00M .00K UNDOTBS1 47.00M 3.00M 50.00M .00K TEMP .00K .00K .00K .00K USERS 1.31M 3.69M 5.00M .00K ------------------------- --------------- --------------- --------------- --------------- Total 1,254.63M 30.38M 1,285.00M .00K The size of the largest CLOB is 1625114 bytes [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name tamaldb Database Version 11.2.0.3.0 Scan type Full database Scan CHAR data? YES Database character set WE8ISO8859P15 FROMCHAR WE8ISO8859P15 TOCHAR WE8MSWIN1252 Scan NCHAR data? NO Array fetch buffer size 1024000 Number of processes 6 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Scan Summary] All character type data in the data dictionary remain the same in the new character set All character type application data remain the same in the new character set [Data Dictionary Conversion Summary] Data Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 3,874,431 0 0 0 CHAR 2,864 0 0 0 LONG 248,720 0 0 0 CLOB 51,588 0 0 0 VARRAY 49,807 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 4,227,410 0 0 0 Total in percentage 100.000% 0.000% 0.000% 0.000% The data dictionary can be safely migrated using the CSALTER script XML CSX Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 702 0 0 0 CHAR 0 0 0 0 LONG 0 0 0 0 CLOB 0 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 702 0 0 0 Total in percentage 100.000% 0.000% 0.000% 0.000% [Application Data Conversion Summary] Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 2,550,581 0 0 0 CHAR 0 0 0 0 LONG 0 0 0 0 CLOB 30,474 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 2,581,055 0 0 0 Total in percentage 100.000% 0.000% 0.000% 0.000% [Distribution of Convertible, Truncated and Lossy Data by Table] Data Dictionary Tables: USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- [Distribution of Convertible, Truncated and Lossy Data by Column] Data Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- [Indexes to be Rebuilt] USER.INDEX on USER.TABLE(COLUMN) ----------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- As we can see there are zero Truncation and Lossy section.
Once all the checking is done next we need to run the csalter.plb command to perform the conversion. Before doing so we need to first shutdown the database and startup the database is restricted mode as follows:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup restrict ORACLE instance started. Total System Global Area 1536602112 bytes Fixed Size 2228624 bytes Variable Size 402656880 bytes Database Buffers 1124073472 bytes Redo Buffers 7643136 bytes Database mounted. Database opened.
Now run the csalter command as follows:
SQL> @$ORACLE_HOME/rdbms/admin/csalter.plb 0 rows created. Function created. Function created. Procedure 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 validity... 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. 0 rows deleted. Function dropped. Function dropped. Procedure dropped.
Once the procedure has completed restart the database to complete the conversion.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup
ORACLE instance started. Total System Global Area 1536602112 bytes Fixed Size 2228624 bytes Variable Size 402656880 bytes Database Buffers 1124073472 bytes Redo Buffers 7643136 bytes Database mounted. Database opened.
Now let's check if the conversion was successful.
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; PARAMETER ------------------------------ VALUE -------------------------------------------------------------------------------- NLS_CHARACTERSET
WE8MSWIN1252
Note: There are certain cases in which you might encounter the error message "Unrecognized convertible data found in scanner result" while running the csalter script. I will cover this error in one of my other blog
