To avoid this, CPAT recommends two possible action plans, one, provision a target with the same character set as the source thereby ensuring no conversions/truncations during migration and thus no possible data loss. This is the easiest, pain-free option favored by most. However, there may also be the need to migrate to and take advantage of the Oracle-recommended AL32UTF8 Unicode character set for numerous advantages including; the support of all global languages i.e rare Chinese or Korean characters, with supplemental objects such as emojis. And this is where DMU comes in with the second recommended action plan where you can scan the schemas to be migrated giving you the capability to analyze any possible convertibility issues before the migration and thus avoiding errors such as the ones shown above. 

So where can we get DMU and how can we use it in this scenario? 

Oracle's Database Migration Assistant for Unicode (DMU) is available for download here. Please read the instructions and pay attention to the pre-requisites mentioned including applying database patches where required. Patches are only necessary for DMU 2.1, however in this scenario, I'm using DMU 23.1 so we will skip this part. 

Before you install DMU, on the database to be migrated, you have to install the SYS.DBMS_DUMA_INTERNAL PL/SQL package which is an interface between the DMU client and the DMU functionality in the database kernel. Using SQL*PLus, log into the database as SYSDBA and execute the following script: ?/rdbms/admin/prvtdumi.plb. Failure to do this and you will hit an error when installing DMU. In a multi-tenant environment, run this on the pluggable database (PDB) to be migrated as shown below. 

This blog post, will go directly into the process of how to use Oracle's Database Migration Assistant for Unicode (DMU) to migrate Oracle Databases from legacy character encoding to Unicode. DMU is Oracle's officially recommended solution for these types of character set migrations and replaces the legacy Character Set Scanner (CSSCAN) which was a command-line utility used to assess any potential data loss or truncation issues of migrating an Oracle database to a new character set. For more information on characters sets, their types, regions etc, I'll post links below. 

So, you're migrating your database from an Oracle 19c Database with the character set WE8ISO8859P15 to an Oracle Autonomous database on OCI with the default Oracle recommended AL32UTF8 unicode character set. In preparation for the impending migration, you run Oracle's Cloud Pre-Migration Advisor Tool (CPAT), which flags this as a potential problem as shown below; 

As an Action Required check against the target Autonomous Database, CPAT raises the NLS Character Set flag which basically identifies the difference in character sets between the source and target databases pointing out that there may be an expansion of character values beyond the column length or loss of invalid character codes. If ignored, you may get ORA-128999 errors during the import process as shown below;

With the necessary patches and package installed, you are now ready to install DMU. Download DMU here. Before you install, make sure you have the Java SE Development Kit in version 11 which you can download here. Even though DMU can be run remotely on a different machine, say your laptop, it is highly recommended to run it locally on the same host as the database server to be migrated for faster performance and reducing network overhead. For this simple migration exercise, I will use my Windows laptop. After downloading the files, you can extract this on any directory preserving the directory structure.

You can then start DMU on Windows by double clicking on the dmuW64.exe executable in the dmu folder for 64 bit or dmuW32.exe for 32 bit Windows system. You can create a shortcut for subsequent quick access.  The first time you run DMU, it will ask for the path to your JDK Install directory, point it to your JDK 11 install base. Below is the DMU splash page.

You next create a connection to the database using its TNS details, similar to what you would use on SQL Developer. You must use a users with SYSDBA role. Test the connection.

The first time you connect to a database using DMU, you will be prompted to create a repository. This repository manages the information necessary for each migration step and includes items such as objects to be processed, details on data that had an error flagged etc, and the migration progress.. DMU cannot work until a repository has been configured. The Repository Configuration Wizard is launched for each new connection for a database without a repository. Click next. 

Select the target character set. Oracle recommends AL32UTF8. Select it and click next. 

Select the tablespace in which the repository will be installed. Oracle recommends that you create a separate, empty tablespace to be used for the DMU repository. The default is the SYSAUX cable. Click Finish and it takes a few minutes to create the repository. 

Once the repository is installed, you are now ready to start the migration process. This is divided into four phases of which installing the repository is the first. Phase 2 is scanning the database, and step 3 resolving the migration issues identified in step 2. After resolving those issues, you can then convert the database in phase four. DMU displays two panes, one for the actual migration phase and the next the overall progress (status) of the migration, capturing all the phases as you can see below. This shows phase 1 complete which is what we did in the immediate steps above, installing the repository. 

To complete phase 2, we have to scan the database. The connection settings are derived from the database connection created earlier. Enter the assumed database character set which could be the same as the current database character set. Also specify the language of the data stored in the database and click apply. 

Below is the full picture, showing all the four phases. DMU 23.1  supports the migration of pluggable databases (PDBs). To convert a PDB using the DMU in a container database (CDB), the root container's character set must be the same as the migration target character set (AL32UTF8 or UTF8). When this requirement is not met, you can still use the DMU for scanning and cleansing PDBs, but the conversion operation is disallowed. This is why our step four is grayed out with the related error message. 

To complete phase 2, we have to scan the database entering parameters such as ...