Oracle Database Character set Change from AL32UTF8 to WE8MSWIN1252
Character set Change from AL32UTF8 to WE8MSWIN1252
I tested below procedure successfully in Test Environment .
Case 1:
Simple alter statement does not work in this case..
SYS@S2411S > ALTER DATABASE CHARACTER SET WE8MSWIN1252;
ALTER DATABASE CHARACTER SET WE8MSWIN1252
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
Case 2:
Steps to Change CHARACTER SET*****************************
Step 1: Install CSSCANcd $ORACLE_HOME/rdbms/admin
sqlplus “/as sysdba”
@csminst.sql
Find log file for sample result for this execution.
*********************
Step 2: Run csscan from command line.
csscan sys/Welcome123 fromchar=al32utf8 tochar=we8mswin1252 full=y
[oracle@s24-oratl15 admin]$ csscan \"sys as sysdba\" full=y fromchar=al32utf8 tochar=we8mswin1252
Character Set Scanner v2.2 : Release 11.2.0.4.0 - Production on Tue Apr 19 15:54:13 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..): 1 > 2
Enumerating tables to scan...
It will Scan all tables
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
It will Generate 3 files
-rw-r--r-- 1 oracle oinstall 16853 Apr 19 16:32 scan.txt
-rw-r--r-- 1 oracle oinstall 1357 Apr 19 16:32 scan.err
-rw-r--r-- 1 oracle oinstall 99826 Apr 19 16:32 scan.out
Verify scan.txt
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 6,938,112 0 0 0
CHAR 2,879 0 0 0
LONG 252,878 0 0 0
CLOB 108,353 13,194 0 0
VARRAY 49,809 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 7,352,031 13,194 0 0
Total in percentage 99.821% 0.179% 0.000% 0.000%
Verify scan.err
Below should be blank.
[Data Dictionary individual exceptions]
[Application data individual exceptions]
If we see any lossy or truncation values other than 0 .. we need to resolve those first and rerun ccscan.
*****************************
Note: I got SYS.REG$ with lossy data and followed Doc ID 1490394.1 to resolve
Transcatonal LossySYS.REG$ 0 0 1
For your Database issue may be with different table .. we need to make sure no lossy data and they should be convertible then proceed for next steps
****************************
Take Full Database Backup
Make sure below parameter values should be 0SYS@S2411S > sho parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SYS@S2411S > alter system set job_queue_processes=0;
System altered.
SYS@S2411S > sho parameter aq_tm_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
SYS@S2411S > alter system set aq_tm_processes=0;
System altered.
SYS@S2411S > sho parameter aq_tm_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
Stop Listener
Shut immediate
Startup restrict
Cd $ORACLE_HOME/rdbms/admin
Sqlplus “/as sysdba”
@csalter.plb
Result:
3 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
130 rows in table SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS are converted
9994 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
12 rows in table APEX_030200.WWV_FLOW_BUTTON_TEMPLATES are converted
1 row in table SYS.WRI$_ADV_OBJECTS is converted
1 row in table SYS.RULE$ is converted
7416 rows in table APEX_030200.WWV_FLOW_PAGE_PLUGS are converted
1 row in table SYS.RADM_FPTM_LOB$ is converted
179 rows in table SYS.METASTYLESHEET are converted
21 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
5 rows in table MDSYS.SDO_XML_SCHEMAS are converted
64 rows in table APEX_030200.WWV_FLOW_TEMPLATES are converted
1490 rows in table SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
166 rows in table APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES are converted
1754 rows in table APEX_030200.WWV_FLOW_STEPS are converted
2238 rows in table APEX_030200.WWV_FLOW_STEP_PROCESSING are converted
39 rows in table APEX_030200.WWV_FLOW_SHORTCUTS are converted
11 rows in table APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS are converted
5 rows in table APEX_030200.WWV_FLOW_FLASH_CHART_SERIES are converted
3406 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
10 rows in table APEX_030200.WWV_FLOW_BANNER are converted
44 rows in table APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR are converted
54 rows in table APEX_030200.WWV_FLOW_ROW_TEMPLATES are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
30 rows in table APEX_030200.WWV_FLOW_WORKSHEETS are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
105 rows in table APEX_030200.WWV_FLOW_LIST_TEMPLATES are converted
176 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
31 rows in table SYSMAN.MGMT_IP_SQL_STATEMENTS are converted
176 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
45 rows in table APEX_030200.WWV_FLOW_PROCESSING 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.
***********************
Now restart databases and reset below parameters to previous values .
job_queue_processes
aq_tm_processes
Start Listener ..
Hope it helps..
Thanks
Anil Vejendla
Comments
Post a Comment