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, this is where you scan for convertibility issues, that may cause data loss or corruption.. 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. 

You're taken to the Scan Parameters where you can set the number of scanning processes, similar to the processes that run data pump workers as well as the scan buffer size. These settings are dependent on the resources available to the database and determines how long the overall process takes. Click Next.

When you click apply, the scan parameters are saved and you can then now go to the status page and initiate the scan process in phase two by clicking on the 'Scan all character data in the database to check for conversion issues.' link in the Next Actions area of phase 2. 

This will launch the Scan Wizard with four phases of its own, the first confirming the character set migration selected. We're migrating from WE8ISO8859P1 to AL32UTF8. Click Next. 

In 'Objects Selection', you can select the Schemas to scan. These are separated into Oracle-Maintained and Application Schemas. You can choose to scan all but in this case, we'll focus on the HR schema as stated at the beginning of this exercise. You can further see the scan properties of the schema objects selected as seen in the 2nd image. If this is a rerun, you could chose to exclude objects that have already been scanned. Click Next.

Once the scan is complete, you're presented with the scan details page that summarizes the scan results for the selected schema objects as shown below. Note the change in Rowids to collect value from All to Convert to With Issues for the HR schema. This means collects rowids for data with conversion issues only. Press Finish. 

You will now see the scan progress as shown below. 

The scan process begins and may take a while.

You can also see this progress on the status page. The first image shows status in case you paused or stopped the scan in progress (which is now partial) and the next when scan is in progress. 

In case there's an error that needs your attention, then this will be displayed with a suggest next action. In our case, this can be ignored. In any case, we haven't seen any alert on the HR schema. 

You can also see the scan results in details on the DB page as shown below. 

In my example above, the migration issues identifies three items I need to consider;

• Consider allowing Create Table as Select (CTAS) for tables with row movement disabled by default and not explicitly. This is done on the DB page on the converting tab as shown in fig. 1. Switch from No to Yes and click Apply.

• A suggestion to consider turning off FORCE LOGGING mode (at least for the duration of the conversion to improve performance) which may be at the expense of media recoverability. Fig 2.

• Since my source database is multi-tenant, I start both the Container and Pluggable databases in upgrade mode as advised to ensure conversion of the AUDSYS.AUD$UNIFIED table. Fig 3. 

With the scan complete, the status pane will be updated as shown below and you can now proceed to the next phase of resolving migration issues (if any) detected during the scan. Click on the 'More...' link for a narrative on how to address the issue. 

With all these done, you can now click on the 'Retest' button to confirm that the migration issues identified have been addressed. You can see that I now how the green check mark for status showing I can now go ahead with the character set conversion. The FORCE LOGGING alert is considered as configuration details of the database that might only decrease the performance of the database conversion, but which the DMU cannot change itself because of the possible side effects. It doesn't necessarily stop the conversion and that's why it's marked in yellow and not red as the Audit table. We already addressed it in point 2 above. 

Having addressed all the identified migration issues and with a green check mark for status, you can now start the last phase (four) which is the conversion process. As earlier stated, this is grayed out because we cannot convert the PDB to AL32UTF8 as the CDB isn't on that same character set. This shouldn't be a problem for a non-CDB environment. In Part 2 of this blog, I will have this configuration scenario and complete the last conversion process. 

I hope this blog now makes it clear how to install and configure DMU as well as prepare the database for conversion. See you on the next blog. Cheers.