DB - Character Set conversion using CSSCAN/CSALTER

Character Set conversion using CSSCAN/CSALTER Master Note: 260192.1 :
------------------------------------------------------------------------------------------------------

1. Installing Csscan in Oracle RDBMS 11.2.0.1

set oracle_sid=<your SID>
sqlplus /nolog
SQL> conn / as sysdba
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> -- note the drop user
SQL> drop user csmig cascade;
SQL> @?/rdbms/admin/csminst.sql

Check the csminst.log for errors. The Csmig user is by default locked.

SQL> alter user csmig identified by csmig account unlock;

User altered.

2. Check current character set

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
OBIQA     READ WRITE

SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET'

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

2.a) Invalid objects.

conn / AS sysdba
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

2.b) Orphaned Datapump master tables (10g and up) – Follow Note 336014.1

2.b) Leftover Temporary tables using CHAR semantics. – Revisit before CSALTER. Can be ignored if exists too.

3. Run CSSCAN to check LOSSY objects

When run without Exclude Option the CSSCAN will report table XDB.XDB$COMPLEX_TYPE as lossy. But this is a 11gR2 Code bug as here . So, please use the exclude option and run as below

csscan \"sys/manager@<sid> as sysdba\" FULL=Y FROMCHAR= WE8MSWIN1252 TOCHAR= WE8MSWIN1252 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2 USER=XDB EXCLUDE=’(XDB.XDB$COMPLEX_TYPE)’

After this there must be no LOSSY data in the Summary Report

4.Run with the target Character set, here UTF8

csscan \"sys/manager@obiqa as sysdba\" FULL=Y TOCHAR=UTF8 LOG=TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2

5.Dealing with "Truncation" data. – We have no Truncation Data to deal with.

6.Dealing with "Convertible" data.

When using Csalter/Alter Database Character Set

6.a) "Convertible" Application Data:

When using Csalter/Alter Database Character Set all User / Application Data "Convertible" data needs to be exported and truncated/deleted. This means ALL data that is listed as "Convertible" in the [Application Data Conversion Summary] summary NEEDS to be exported and truncated.

We have following table in Application Data in Convertible Section.

USER.TABLE

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

APEX_030200.WWV_FLOW_BANNER

APEX_030200.WWV_FLOW_BUTTON_TEMPLATES

APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS

APEX_030200.WWV_FLOW_FLASH_CHART_SERIES

APEX_030200.WWV_FLOW_LIST_TEMPLATES

APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR

APEX_030200.WWV_FLOW_PAGE_PLUGS

APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES

APEX_030200.WWV_FLOW_PROCESSING

APEX_030200.WWV_FLOW_ROW_TEMPLATES

APEX_030200.WWV_FLOW_SHORTCUTS

APEX_030200.WWV_FLOW_STEPS

APEX_030200.WWV_FLOW_STEP_PROCESSING

APEX_030200.WWV_FLOW_TEMPLATES

APEX_030200.WWV_FLOW_WORKSHEETS

DAC.W_ETL_TAG

INFORM.OPB_EXPRESSION

INFORM.OPB_MAPPING

INFORM.OPB_SRC

INFORM.OPB_SRC_FLD

INFORM.OPB_TARG

INFORM.OPB_TARG_FLD

 6.b) "Convertible" Data Dictionary Data:

* For 10g and up you do not need to take action on "convertible" Data Dictionary CLOB data. Convertible CLOB in Data Dictionary objects is handled by Csalter.
Convertible CHAR, VARCHAR2 and LONG listed under the "[Data Dictionary Conversion Summary]" header in the toutf8.txt however do need action.

7. Take COLD backup of Database cp –rp backup of the DB.

cp -rp obiqa obiqa_bak/

8. Take export backup of schemas/tables reported in the TOUTF8.txt

CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/home/oracle/char_conv';

 Export E.g

expdp tables=MGMT_IP_ELEM_DEFAULT_PARAMS,MGMT_IP_REPORT_ELEM_PARAMS,MGMT_IP_SQL_STATEMENTS directory=DATA_PUMP dumpfile=sysman.dmp logfile=sysman.exp.log

 expdp schemas=SIEBEL directory=DATA_PUMP dumpfile=SIEBEL.dmp logfile=SIEBEL.exp.log

 exp file=sys.dmp log=sys.exp.log tables="SYS.WRI\$_OPTSTAT_HISTGRM_HISTORY","SYS.HISTGRM\$" statistics=none grants=y indexes=y buffer=10240000

 expdp schemas=APEX_030200 directory=DATA_PUMP dumpfile=APEX_030200.dmp logfile=APEX_030200.exp.log parallel=4

impdp schemas=SIEBEL directory=DATA_PUMP dumpfile=SIEBEL.dmp logfile=SIEBEL.imp.log parallel=4k

 DAC

expdp tables=W_ETL_TAG directory=DATA_PUMP dumpfile=W_ETL_TAG.dmp logfile=W_ETL_TAG.exp.log

impdp tables=W_ETL_TAG directory=DATA_PUMP dumpfile=W_ETL_TAG.dmp logfile=W_ETL_TAG.imp.log

 INFORM

expdp tables=OPB_EXPRESSION,OPB_MAPPING,OPB_SRC,OPB_SRC_FLD,OPB_TARG,OPB_TARG_FLD directory=DATA_PUMP dumpfile=inform.dmp logfile=inform.exp.log

impdp tables=OPB_EXPRESSION,OPB_MAPPING,OPB_SRC,OPB_SRC_FLD,OPB_TARG,OPB_TARG_FLD directory=DATA_PUMP dumpfile=inform.dmp logfile=inform.exp.log

 9. Run Section 7) of MOS Note Before using Csalter / Alter Database Character Set check the database for:

There must be no mismatch from MOS note.

10. Run CSSCAN again and confirm the requirments

csscan \"sys/manager@obiqa as sysdba\" FULL=Y TOCHAR=UTF8 LOG=TOUTF8FIN CAPTURE=Y ARRAY=1000000 PROCESS=2

In TOUTF8FIN.txt below messages must appear.

The data dictionary can be safely migrated using the CSALTER script

and

All character type application data remain the same in the new character set

Also check, select value from csm$parameters where name='TO_CHARSET_NAME';

Gives the character set you wish to convert to.

11.The Character set Conversion step:

sho parameter CLUSTER_DATABASE
--  if you are using spfile note the
sho parameter job_queue_processes
sho parameter aq_tm_processes
-- (this is Bug 6005344 fixed in 11g )
-- then do

shutdown immediate
startup restrict
SPOOL Nswitch.log

ALTER SYSTEM ENABLE RESTRICTED SESSION;

 @?/rdbms/admin/csalter.plb

>>Alter the database character set...

>>CSALTER operation completed, please restart database

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;
ALTER SYSTEM SET aq_tm_processes=2 SCOPE=BOTH;

shutdown
startup

 12.Verify the character set conversion

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
--------------------------------------------------------------------------------
UTF8

 13.Reload the data pump packages after a change to UTF8 in 10g and up.

• For Oracle version 10.2, 11.1 and 11.2 :

1. Catdph.sql will Re-Install DataPump types and views

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

Note: If XDB is installed the it is required to run "catmetx.sql" script also.

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

2. prvtdtde.plb will Re-Install tde_library packages

SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb 3. Catdpb.sql will Re-Install DataPump packages

SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql 4.Dbmspump.sql will Re-Install DBMS DataPump objects

SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql 5. To recompile  invalid objects, if any

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

SQL> select count(*) from dba_objects where status <> 'VALID';

     COUNT(*)

       ----------

         3

 14 Import the exported data .

14.a) When using Csalter/Alter database to go to AL32UTF8 and there was NO "Truncation" data, only "Convertible" and "Changeless" in the csscan done in point 4:

SQL> alter system set BLANK_TRIMMING=TRUE scope=spfile;

 Bounce the DB

 15. Import back the Exported Data

 Drop the export the Tables/Schemas before start of import

 CREATE OR REPLACE DIRECTORY DATA_PUMP AS '/home/oracle/char_conv';

expdp schemas=APEX_030200 directory=DATA_PUMP dumpfile=APEX_030200.dmp logfile=APEX_030200.exp.log parallel=4

impdp schemas= APEX_030200  directory=DATA_PUMP dumpfile=APEX_030200.dmp logfile=SIEBEL.imp.log parallel=4

 --Better take export of only necessary tables and drop them before import. The schema import is increasing invalids.

 DAC

expdp tables=W_ETL_TAG directory=DATA_PUMP dumpfile=W_ETL_TAG.dmp logfile=W_ETL_TAG.exp.log

impdp tables=W_ETL_TAG directory=DATA_PUMP dumpfile=W_ETL_TAG.dmp logfile=W_ETL_TAG.imp.log

 INFORM

expdp tables=OPB_EXPRESSION,OPB_MAPPING,OPB_SRC,OPB_SRC_FLD,OPB_TARG,OPB_TARG_FLD directory=DATA_PUMP dumpfile=inform.dmp logfile=inform.exp.log

impdp tables=OPB_EXPRESSION,OPB_MAPPING,OPB_SRC,OPB_SRC_FLD,OPB_TARG,OPB_TARG_FLD directory=DATA_PUMP dumpfile=inform.dmp logfile=inform.imp.log

 Import E.g.

 impdp tables=MGMT_IP_ELEM_DEFAULT_PARAMS,MGMT_IP_REPORT_ELEM_PARAMS,MGMT_IP_SQL_STATEMENTS directory=DATA_PUMP dumpfile=sysman.dmp logfile=sysman.imp.log

 impdp schemas=SIEBEL directory=DATA_PUMP dumpfile=SIEBEL.dmp logfile=SIEBEL.imp.log

 imp fromuser=sys touser=sys file=sys.dmp tables="WRI\$_OPTSTAT_HISTGRM_HISTORY","HISTGRM\$" ignore=y log=sys.imp.log