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

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.
SQL> 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.

I had to work on this problem as in a new development database, someone accidently changed the charaterset and then we started getting all sorts of issues. Now lets find out which tables & columns are having problems:
For CHAR,VARCHAR2 and CLOB columns: I have mentioned the old or problem character set in the below query, you are 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; 2 3

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 my 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> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> sho 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;
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
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;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 926941184 bytes
Fixed Size 1222648 bytes
Variable Size 276826120 bytes
Database Buffers 641728512 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

Need to restart the database twice.

Check the database to see if there are any problems:
SQL> SQL> SQL> 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;

no rows selected

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=2;
System altered.

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.