How TO: Check for Charater Set Conversion Problems & Fix Them  

Posted by Mawahid

Character set conversion was done without using the csscan to verify if its possible.

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

Character set was converted from WE8ISO8859P1 to AL32UTF8.

Helpful metalink note: 286964.1

Below query is used to see if there is a mismatch in character set in the database.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

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

So we can see that the above query also returns the old character set along with the new character set. It should ideally not show any record of the old character.

Here you can see that there are 2 different character sets returned for VARCHAR2, CLOB & CHAR data, which indicates a mixup in the database character set setup, which is the cause for this PLS-553 error.

Now lets find out which tables & columns are having problems:

For CHAR,VARCHAR2 and CLOB columns:

The old or problem character set in the below query, replace it with the one you are facing problem with.

SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1')
order by OWNER, TABLE_NAME, COLUMN_NAME;

OWNER TABLE_NAME COLUMN_NAME
---------- -------------------- --------------------
TEST TEST_TABLE1 CODE
TEST TEST_TABLE1 CREATETIME
TEST TEST_TABLE1 LASTMODIFIEDTIME
TEST TEST_TABLE1 TYPEID
TEST TEST_TABLE2 ACTIONTYPE
.
.
.
49692 rows selected.

In this case almost all schemas in the database are affected including sys and system.

For Nchar, Nvarchar2 and Nclob:

SQL> set lines 199
SQL> col owner format a10
SQL> col table_name format a20
SQL> col column_name format a20
SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='2')
order by OWNER, TABLE_NAME, COLUMN_NAME;

no rows selected

Oracle note suggests that if the character set conversion has happened between a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc, then there will be data loss for clob columns which display the old character set. So it is best to take a full back of the database, preferably using the tradional export utility.

Check the following things:

SQL> show parameter CLUSTER_DATABASE

NAME                                   TYPE                      VALUE
------------------------------------ ----------- ------------------------------
cluster_database                             boolean               FALSE
cluster_database_instances       integer                  1

SQL> show parameter PARALLEL_SERVER

NAME                                                   TYPE                      VALUE
------------------------------------ ----------- ------------------------------
parallel_server                                  boolean               FALSE
parallel_server_instances            integer                  1

The parameters CLUSTER_DATABASE & parallel_server should be false.

Save the values for;

SQL> show parameter JOB_QUEUE_PROCESSES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
SQL> show parameter AQ_TM_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 2

To start the fix:

Shutdown listener, make sure there are not connections to the database.

SQL> shutdown immediate;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> COL VALUE NEW_VALUE CHARSET

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

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

SQL> COL VALUE NEW_VALUE NCHARSET

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
old 1: ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET
new 1: ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
Database altered.

SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
old 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET
new 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16
Database altered.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> SHUTDOWN IMMEDIATE;

SQL> startup

Need to restart the database twice.

Check the database to see if there are any problems:

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

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

6 rows selected.


SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1') order by OWNER, TABLE_NAME, COLUMN_NAME;


SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=2;

This procedure doesn't fix any data, it cannot get back the lost data due to character set changes. That will need to be restored from backup.

All it does is fixes the references the tables are having to the old character set. Hence this is just the solution to fix the data dictionary not the data, or the loss that has happened.

This entry was posted on Monday, April 11, 2011 at 10:48 AM . You can follow any responses to this entry through the comments feed .

0 comments

Post a Comment