February 14, 2013
1.
Install
csscan Utility
The first step is to install the csscan utility in the database.
Follow note
745809.1 for this.
Please
run the following script in database:
SQL> @?/rdbms/admin/csminst.sql
This script will create the CSMIG user account in the database and
the script creates the user and assigns a default tablespace of SYSTEM. A
number of CSM$ tables are created in the CSMIG schema.
2.
Running
the csscan utility
The csscan will do a full table scan of all the tables in the
database and this could cause a performance impact. So run the csscan at
an appropriate time period.
$ export
LD_LIBRARY_PATH=$ORACLE_HOME/lib $ csscan
\”sys/xxxxx as sysdba\” FULL=Y FROMCHAR=AL32UTF8 TOCHAR=WE8MSWIN1252
LOG=win1252 CAPTURE=Y ARRAY=1000000 PROCESS=2
csscan will produce three files with the . err, .txt and .out
extensions and the name of each will depend on the value used for the
LOG parameter.
- The .out file is basically a log of all the scan activity performed .
- The .txt file is the most important file as it has the Database Scan
Summary Report
- The .err file will list all the ‘exception’ rows – rows with lossy
data or truncated data or convertible data.
The Conversion Summary
section
The Conversion Summary section of the .txt file is the most important
section of the Scan Summary Report and has 4 columns showing the
possible status of the data
- Changeless: All the characters use the same codepoints in the new
characterset as compared to the old characterset and no action needs to
be taken. The csalter or ALTER DATABASE CHARACTERSET command will handle
that for us.
- Convertible: The data is valid, but the characters will change to a
different code point in the new characterset. In this case for any
application data reported as convertible, we need to export the data,
truncate the tables or delete the data, change the characterset using
csalter or ALTER DATABASE and then import after characterset has been
changed.
- Truncation: In case of going from a single byte characterset to a
multiple byte characterset, the data will grow and we will need to
increase the column size in this case. In this case, the .err file will
tell us by how much the data is going to grow post conversion and we
need to accordingly modify the column sizes of the affected tables.
- Lossy: If we see data in the Lossy column, then it means that the
either the data is not a valid code point for the current characterset,
or the proposed target characterset does not define that data. If we see
data listed as lossy, we CANNOT use Export/Import to change
characterset, but need to use either ALTER DATABASE CHARACTERSET command
for 8i and 9i and csalter for 10g and upwards.
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 10,538,740,813 0 0 0
CHAR 922,484,552 0 0 0
LONG 34,823 0 0 0
CLOB 76,562,599 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 11,537,822,787 0 0 0
3.
Performing
the actual characterset conversion.
Shutdown and startup the database in restricted mode. Run the
csalter.plb which will prompt us to enter a confirmation.
Successful Execution:
SQL> @?/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
20 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
737 rows in table SYS.WRH$_SQLTEXT are converted
167 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
121 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
21134 rows in table SYS.WRH$_SQL_PLAN are converted
1 row in table SYS.RULE$ is converted
34 rows in table SYS.WRI$_ADV_OBJECTS are converted
178 rows in table SYS.METASTYLESHEET are converted
4971 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
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.
Failed Execution
SQL> @?/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…
Unrecognized convertible data found in scanner result
PL/SQL procedure successfully
completed.
Checking or Converting phase did
not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully
completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
This fails to perform the characterset Conversion.
4.
Alternative
Method
Shutdown the database and start it in restricted mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area
5277921280 bytes
Fixed Size
2213096 bytes
Variable Size
3556772632 bytes
Database Buffers
1677721600 bytes
Redo Buffers
41213952 bytes
Database mounted.
Database opened.
Perform the character set
conversion:
SQL> select name from
v$database;
NAME
———
DEV
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252 ;
Database altered.
SQL> select value from
NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;
VALUE
—————————————-
WE8MSWIN1252
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area
5277921280 bytes
Fixed Size
2213096 bytes
Variable Size
3556772632 bytes
Database Buffers
1677721600 bytes
Redo Buffers
41213952 bytes
Database mounted.
Database opened.
SQL> select value from NLS_DATABASE_PARAMETERS where
parameter=’NLS_CHARACTERSET’;
VALUE
—————————————-
WE8MSWIN1252
SQL> exit
5.
Run
the following commands
SQL>@$ORACLE_HOME/rdbms/admin/catalog
SQL>@$ORACLE_HOME/rdbms/admin/catproc
Reload the datapump utility by executing the below scripts:
SQL>@$ORACLE_HOME/rdbms/admin/catmet2.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
