Text box

Please note that the problems that i face may not be the same that exist on your environment so please test before applying the same steps i followed to solve the problem .

Wednesday 5 September 2012

Migration from character set AR8ISO8859P6 to AL32UTF8 using csscan and csalter

This topic should cover a simple example for migrating data from  AR8ISO8859P6 to AL32UTF8 using data pump.

The steps involved in character set conversion are listed below.
  • Export data
  • CLUSTER_DATABASE=FALSE (*RAC Only*)
  • SHUTDOWN IMMEDIATE
  • STARTUP
  • CSSCAN
  • SHUTDOWN IMMEDIATE
  • STARTUP RESTRICT
  • CSALTER
  • CLUSTER_DATABASE=TRUE (*RAC Only*)
  • SHUTDOWN IMMEDIATE
  • STARTUP
  • Import data

[oracle@radbstage1 oracleup3]$ csscan FULL=Y FROMCHAR=AR8ISO8859P6 TOCHAR=AL32UTF8 ARRAY=1024000 LOG=charcheck CAPTURE=Y PROCESS=4











Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Sep 5 12:07:14 2012







Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.











Username: sys/password as sysdba







Connected to:



Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production



With the Partitioning, Oracle Label Security, OLAP, Data Mining,



Oracle Database Vault and Real Application Testing options







Enumerating tables to scan...







. process 1 scanning SYSTEM.AUD$[AAAOQsAABAAAOZYAAA]



. process 2 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]]



...................



. process 1 scanning SYSMAN.MGMT_TYPE_PROPERTIES[AAAOg9AACAAAF5wAAA]Message 9999 not found;  product=CSMIG; facility=CSS







. process 3 scanning SYSMAN.MGMT_ARU_OUI_COMPONENTS[AAAOljAACAAAGBIAAA]



. ..................



. process 1 scanning SYS.DUAL[AAAAB0AABAAAAOgAAA]Message 9999 not found;  product=CSMIG; facility=CSS







. process 4 scanning SYS.TRIGGERJAVAC$[AAAACoAABAAAAU4AAA]]



. ....................................



. process 4 scanning SYSMAN.MGMT_TASK_QTABLE[AAAOYKAACAAAFY4AAA]Message 9999 not found;  product=CSMIG; facility=CSS







. process 2 scanning SYSMAN.MGMT_CATEGORIES[AAAOg2AACAAAF64AAA]A]



......................................



. process 2 scanning EXFSYS.RLM$VALIDPRIVS







Creating Database Scan Summary Report...







Creating Individual Exception Report...







Scanner terminated successfully.

The CSSCAN generate 3 files charcheck.txt, charcheck.err and charcheck.out.
Checking the contents of the charcheck.txt showed that the Application user data has some data inside tables that needs conversion.
You must backup those data first then drop those tables.I my case i exported the schemas then droped the schemas containing those data.
;;; 
Export: Release 11.2.0.3.0 - Production on Wed Sep 5 10:01:57 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=schemas-backup-5-9-2012.dmp logfile=schemas-backup-5-9-2012.log schemas=APPADMTST,RFETECMUCADM,RFETECMUCAPP,RFETEPRTADM,RFETEPRTAPP,RFETEUCADM,RFETEUCAPP,RFETEUCDAT,RTETECMUCADM,RTETECMUCAPP,RTETEPRTADM,RTETEPRTAPP,RTETEUCADM,RTETEUCAPP,RTETEUCDAT 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.327 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "RTETEUCADM"."SCH_T_FILE_PART"              149.8 MB  604267 rows
. . exported "RTETEUCADM"."IM_L_TRANSACTION_BLOCK"       124.2 MB 1599134 rows
............................................
. . exported "RTETEUCDAT"."ROAM_TAP_AGG":"DEF"               0 KB       0 rows
. . exported "RTETEUCDAT"."SUBS_BSCS":"DEF"                  0 KB       0 rows
. . exported "RTETEUCDAT"."SUBS_BSCS_AGG":"DEF"              0 KB       0 rows
. . exported "RTETEUCDAT"."TAP_OUT_IOT"                      0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /oracle/oracle11/product/11.2.0/dbhome_1/rdbms/log/schemas-backup-5-9-2012.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:05:00

The contents of the data dictionary showed that some data dictionary needs to be converted :
Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                    18,427,017              412                0                0
CHAR                            16,903                0                0                0
LONG                           204,199                0                0                0
CLOB                         1,336,770           31,521                0                0
VARRAY                          20,577                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                       20,005,466           31,933                0                0
Total in percentage             99.841%           0.159%           0.000%           0.000%

As you can see the re 412 VARCHAR2 are convertible and regarding the 31,521 CLOB.The "Convertible"  CLOB in   Data Dictionary  will be handled by Csalter.
No manual action is required on CLOB "Convertible"   because CSALTER will handle it.

Now we should drop the schemas containing the data under User / Application Data that need action before using Csalte or the csalter will not work.

After backing data/database and dropping this data you can check if you have done your job well by running the below query:

conn / AS sysdba
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' UserColumns
FROM csmig.csmv$errors z
WHERE z.owner_name NOT IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
)
ORDER BY UserColumns
/

to check the data dictionary tables that need manual interaction when using csalter that generated the 412 varchar2 above use this query:

conn / AS sysdba
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' NotHandledDataDictColumns
FROM csmig.csmv$errors z
WHERE z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
) minus
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' DataDictConvCLob
FROM csmig.csmv$errors z
WHERE z.error_type ='CONVERTIBLE'
AND z.column_type = 'CLOB'
AND z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
)
ORDER BY NotHandledDataDictColumns
/

In my case it showed that the error in table  SYS.WRI$_*  and Typically this is seen in SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.

Refering to note:

This is being solved by applying the below steps:

To clear EPVALUE of SYS.WRI$_OPTSTAT_HISTGRM_HISTORY execute:

Do not update or truncate SYS.WRI$_OPTSTAT_HISTGRM_HISTORY itself

Conn / as sysdba
-- check the current retention of stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;
-- now disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
-- remove all stats
DECLARE


ts  TIMESTAMP WITH TIME ZONE;


BEGIN
SELECT SYSTIMESTAMP INTO ts FROM dual;
dbms_stats.purge_stats(ts);
END;


-- check there are no more left
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
-- this will clear SYS.WRI$_OPTSTAT_HISTGRM_HISTORY(EPVALUE )

-- now run the csscan again to see the new fixes we did and then run csalter.
exec dbms_stats.alter_stats_history_retention(31);

Running the csscan showed that no data dictionary tables need conversion and the csalter successfully worked :
The content of charcheck.txt after running csscan and csalter:
[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                    16,275,600                0                0                0
CHAR                            16,860                0                0                0
LONG                           204,199                0                0                0
CLOB                         1,243,848           31,585                0                0
VARRAY                          20,577                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                       17,761,084           31,585                0                0
Total in percentage             99.822%           0.178%           0.000%           0.000%

The data dictionary can be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                             0                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
CLOB                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                                0                0                0                0
Total in percentage              0.000%           0.000%           0.000%           0.000%

Running CSALTER script:

[oracle@radbstage1 oracleup3]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 5 12:09:24 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

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
1 row in table SYS.RULE$ is converted
2778 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
2555 rows in table SYS.WRH$_SQLTEXT are converted
1788 rows in table SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS are converted
20551 rows in table SYS.WRI$_ADV_SQLT_PLANS are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
21 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
10072 rows in table SYS.WRI$_ADV_RATIONALE are converted
130 rows in table SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS are converted
5591 rows in table SYS.WRI$_ADV_OBJECTS are converted
31 rows in table SYSMAN.MGMT_IP_SQL_STATEMENTS are converted
3326 rows in table SYS.WRI$_ADV_ACTIONS are converted
171 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
179 rows in table SYS.METASTYLESHEET are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
5301 rows in table SYS.WRI$_ADV_SQLT_PLAN_STATS are converted
64548 rows in table SYS.WRH$_SQL_PLAN are converted
164 rows in table SYS.WRI$_DBU_FEATURE_USAGE 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.


Restart the database and import data back :

;;; 
Import: Release 11.2.0.3.0 - Production on Wed Sep 5 13:01:12 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=schemas-backup-5-9-2012.dmp logfile=schemas-backup-import-5-9-2012.log schemas=APPADMTST,RFETECMUCADM,RFETECMUCAPP,RFETEPRTADM,RFETEPRTAPP,RFETEUCADM,RFETEUCAPP,RFETEUCDAT,RTETECMUCADM,RTETECMUCAPP,RTETEPRTADM,RTETEPRTAPP,RTETEUCADM,RTETEUCAPP,RTETEUCDAT 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RTETEUCADM"."SCH_T_FILE_PART"              149.8 MB  604267 rows
...........................................
. . imported "RTETEUCDAT"."SUBS_BSCS_AGG":"DEF"              0 KB       0 rows
. . imported "RTETEUCDAT"."TAP_OUT_IOT"                      0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed  at 13:07:53



End of the steps.Hope this helps.
Mohamed ELAzab


Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)