Scribd
Upload
146 views7 pages

CSSCAN for Character Set Migration

The document outlines the steps for migrating a database character set from WE8MSWIN1252 to AL32UTF8 using CSSCAN and CSALTER. Key steps include backing up the database, purging the recycle bin, checking for invalid objects, running the CSSCAN utility, performing the final conversion, and verifying the character set change. Additional considerations for RAC databases and error handling during the migration process are also discussed.

Uploaded by

cfmnizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
146 views7 pages

CSSCAN for Character Set Migration

The document outlines the steps for migrating a database character set from WE8MSWIN1252 to AL32UTF8 using CSSCAN and CSALTER. Key steps include backing up the database, purging the recycle bin, checking for invalid objects, running the CSSCAN utility, performing the final conversion, and verifying the character set change. Additional considerations for RAC databases and error handling during the migration process are also discussed.

Uploaded by

cfmnizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 7

Character Set Migration using CSSCAN and CSALTER (WE8MSWIN1252 --> AL32UTF8)

============================================================================

Summary:

1. Backup
2. Purge Recycle Bin
3. Invalid objects
4. Orphaned Datapump master tables
5. CLUSTER_DATABASE=FALSE
6. CSMINST Schema
7. CSSCAN
8. Final Conversion
9. CLUSTER_DATABASE=TRUE
10. Bounce The Database
11. Verification
12. Compile INVALID OBJECT
13. Gather Stats
14. References

1. Backup
----------

Take full backup of database

2. Purge Recycle Bin


---------------------

SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE FROM dba_recyclebin ORDER BY 1,2


PURGE DBA_RECYCLEBIN

Note: It might be a good idea to simply disable the recyclebin until the change is
done by setting the recyclebin='OFF' parameter

3. Invalid objects
------------------
set lines 1000
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status ='INVALID';

If there are any invalid objects run utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql
If there are any left after running utlrp.sql then please manually resolve / drop
the invalid objects.

4. Orphaned Datapump master tables


----------------------------------
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50
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;

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)

5. CLUSTER_DATABASE=FALSE
-------------------------

In the case of RAC databases, the CLUSTER_DATABASE parameter should be set to


FALSE, then all RAC instances stoped and only one restarted.

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; -- RAC Only


SHUTDOWN IMMEDIATE;
STARTUP;

6. CSMINST Schema
-----------------

The character set migration utility schema is installed by running the


"$ORACLE_HOME/rdbms/admin/csminst.sql" script in SQL*Plus
as the SYS user. Once the schema is present, the character set scanner should work
normally.
If the character set migration utility schema is not installed on your database,
scanner will not perform successfully.

sql>@$ORACLE_HOME/rdbms/admin/csminst.sql

7. CSSCAN
---------

--> View the Chrachter set


select value from NLS_DATABASE_PARAMETERS where
parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252

--> CSSCAN utility

csscan "'sys/oracle@trgt AS SYSDBA'" FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=AL32UTF8


LOG=PROD_1252_TO_1252_beforeconvert ARRAY=1024000 CAPTURE=Y process=4

--> last run


csscan "'sys/oracle@trgt AS SYSDBA'" FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=AL32UTF8
LOG=PROD_1252_TO_1252_convert ARRAY=1024000 CAPTURE=Y

--> Progress

SET pages 1000


SELECT target,
TO_CHAR(start_time,'HH24:MI:SS - DD-MM-YY'),
time_remaining,
sofar,
totalwork,
sid,
serial#,
opname
FROM v$session_longops
WHERE sid IN
(SELECT sid FROM v$session WHERE upper(program) LIKE 'CSSCAN%'
)
AND sofar < totalwork
ORDER BY start_time;

8. Final Conversion
--------------------

Once the scan has completed successfully, the database should be opened in
restricted mode so you can run the "$ORACLE_HOME/rdbms/admin/csalter.plb"
script as the SYS user.
Note – changing the characterset is not dependant on the volume of data in the
tables,
but depends on the number of columns and how many tables have the ‘exception’ data
found out by running the csscan.

-->STARTUP RESTRICT
SHUTDOWN IMMEDIATE
STARTUP RESTRICT

@$ORACLE_HOME/rdbms/admin/csalter.plb

9. CLUSTER_DATABASE=TRUE
------------------------
Once the conversion is complete, you must restart the instance. In the case of RAC
databases, the CLUSTER_DATABASE parameter should be set back to TRUE.

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; -- RAC Only


SHUTDOWN IMMEDIATE;
STARTUP;

10. Bounce The Database


-----------------------

sql> startup force

11. Verification
----------------

--> Before Activity

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

--> After Activity

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
-----------------------------------------
AL32UTF8

--> You can do an additional check after the change of the NLS_CHARACTERSET

select distinct(nls_charset_name(charsetid)) CHARACTERSET,


decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2,
'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN'))
TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96,
112)
order by CHARACTERSET, TYPES_USED_IN

this should give 7 rows, one for each datatype and 2 charactersets: the
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET.

CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 LONG
AL32UTF8 VARCHAR2

7 rows selected.

12. Compile INVALID OBJECT


--------------------------

set lines 1000


SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE status ='INVALID';

If there are any invalid objects run utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql

13. Gather Stats


-----------------

Sqlplus / as sysdba
-- where x is number of cpu cores
EXEC DBMS_STATS.GATHER_DATABASE_STATS(DEGREE=> '4')

14. References
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g
(Doc ID 260192.1)
https://oracle-base.com/articles/10g/character-set-migration
http://gavinsoorma.com/2011/07/changing-the-database-characterset-from-us7ascii-to-
we8mswin1252/

---------
Alternative Method
==================

--> Before Activity:

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
WE8MSWIN1252 CHAR
WE8MSWIN1252 CLOB
WE8MSWIN1252 LONG
WE8MSWIN1252 VARCHAR2

Shutdown the database and start it in restricted mode.

startup restrict;

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

select distinct(nls_charset_name(charsetid)) CHARACTERSET,


decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)
order by CHARACTERSET, TYPES_USED_IN;
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 LONG
AL32UTF8 VARCHAR2

7 rows selected.

startup force;

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

5. Run the following commands


SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

Reload the datapump utility by executing the below scripts:

SQL>@$ORACLE_HOME/rdbms/admin/catmet2.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

=============================== Error Faced ======================

-- IF
"ORA-12712: NEW CHARACTER SET MUST BE SUPERSET OF OLD CHARACHTER SET"

--> update sys.propos set value$='AL32UTF8' where name='NLS_CHARACTERSET';


commit;

"ORA-12721:OPERATION CANNOT EXECUTE WHEN OTHER SESSIONS ARE ACTIVE"


alter system enable restricted session;

You might also like