Hey DBAs! Run this query on your database:

select value as character_set
  from nls_database_parameters
 where parameter = 'NLS_CHARACTERSET';

CHARACTER_SET
________________
AL32UTF8

If you see anything other than AL32UTF8 as the answer to the above query, then you should be building a plan to get from whatever character set your database is currently running to Unicode (AL32UTF8). While this can seem daunting, Oracle actually provides a great tool to assist with the process called the Oracle Database Migration Assistant for Unicode (DMU).

DMU will show you if your database will have any issues when you migrate from your legacy character set to Unicode. As you can see from the splash screen above “Oracle recommends using Unicode as the database character set for maximum compatibility and extensibility“. Oracle strongly suggests that all new databases should be created using AL32UTF8 and that legacy databases created in the past should be migrated to AL32UTF8.

A great time to think about a character set upgrade is during a database upgrade. If you are planning on a 19c upgrade (and we should really be there already today, but, of course, “should” and “are” are two different words for a reason) or potentially even 23c when Oracle releases it soon, and your current character set isn’t AL32UTF8 then now is the time to plan the migration!

Here’s a real-life DMU story:

A customer of mine had a database that had been created on Solaris in the late ’80s and they had used whatever the default character set was at the time, which is really what everyone was doing back then. In 2013 we decided to upgrade the database to 12.1 and change the character set during the upgrade. The fear was that this was going to be a big problem and we came up with all kinds of plans on how to fix the various issues (data cleansing, adjusting column widths on tables, etc.) However, after talking about all the potential issues, we actually ran the DMU and it showed us that the problem was much smaller than we thought. We only had about 30 rows of data that could have an issue during the migration!

After checking with the business, we just went into the 30 rows and cleaned up the data. A few “do not”s got changed to “don’t”, saving a single character which was all we needed.

Today, I talk to folks about using DMU to check their databases before they decide that “the problem is too big”. DMU will sometimes show that the problem either doesn’t exist, or it’s very small.

As an aside:

Today I did an install of DMU on a server using the download of DMU from Oracle Support. After extracting the zip file on my Oracle Linux box into /opt/oracle/dmu, I noticed that the dmu.sh script was read-only. A simple chmod u+x on the dmu.sh script enabled me to start it up without any issues.

[oracle@databaseserver DB:dbacon /opt/oracle/dmu]
$ ./dmu.sh
-bash: ./dmu.sh: Permission denied

[oracle@databaseserver DB:dbacon /opt/oracle/dmu]
$ chmod u+x dmu.sh

[oracle@databaseserver DB:dbacon /opt/oracle/dmu]
$ ./dmu.sh

   Database Migration Assistant for Unicode
 Copyright (c) 2011, 2019, Oracle and/or its affiliates. All rights reserved.