To be supported by the DMU, a database must meet several requirements, including release version, type of character set, open mode, and so on.

The requirements are as follows:

• The release of Oracle Database must be 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1, or later. Check the latest version of the DMU release notes for any additional patches that must be installed in the database. The release notes also list further database releases that are supported with appropriate patches.

• The database character set must be ASCII-based. For example, databases running on the EBCDIC-based platforms IBM z/OS and Fujitsu BS2000 are not supported.

• The SYS.DBMS_DUMA_INTERNAL package must be installed in the database.

  This package is available as part of the database installation. You must create it manually by running the script ?/rdbms/admin/prvtdumi.plb from the Oracle home. You must log in as SYSDBA to run the script.

• You must disable Oracle Database Vault before starting the migration. The DMU has not been certified to work with Database Vault enabled.

• The database must be opened in read/write mode.

Additional requirements pertain to databases that the DMU should convert. Without meeting these requirements, you can still use the DMU for scanning and cleansing the database. These requirements are as follows:

• All database objects, including objects created by standard PL/SQL packages, such as DBMS_RULE, DBMS_DATA_MINING, or DBMS_WM, must be named using only standard characters from the ASCII character set. Similarly, expressions of CHECK constraints, virtual columns and other database features must be specified only using those characters. In other words, the data dictionary of the database cannot contain non-ASCII characters. However, there is some character data in the data dictionary in which non-ASCII characters are allowed.

  See "Data Dictionary Tables That Are Converted" for more details.

• No OLAP analytical workspaces, other than predefined system workspaces and certain predefined Oracle Applications workspaces, can exist in the database.

• No flashback data archives can exist in the database.

• No data to be converted can reside in a read-only or offline tablespace.

• Neither cluster key columns nor partitioning key columns can be defined with character length semantics.

• No convertible data can be present in tables in the recycle bin.

• No convertible data can be present in a reference partitioning key column.

This release of the DMU 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 release of the DMU also supports the migration of Oracle E-Business Suite and PeopleSoft databases to Unicode. The migration workflow is mostly the same as that of a regular database instance. Note that you should not perform any data cleansing actions that require modifying the database schema of Oracle E-Business Suite or PeopleSoft applications because such modifications will not be supported by Oracle. In addition, this release of the DMU does not support migrating databases for PeopleSoft Application versions older than 9.0 or PeopleTools versions older than 8.48.

The Java SE Development Kit (JDK) version 8 or version 11 must be available on the system running the DMU.

When it is first started, the DMU asks for the location of the Java Runtime executable file. Depending on the platform, this file is either java.exe or java.

From the Java SE download page on http://www.oracle.com, you can download the JDK for Linux, Solaris, and Microsoft Windows operating systems for Intel x86, Intel x64, Intel Itanium, and SPARC processors. Look for the installation instructions for your platform on the relevant Oracle Technology Network documentation page at https://www.oracle.com/java/technologies/downloads/.

For other platforms, such as Hewlett-Packard HP-UX or IBM AIX, visit the vendor's website.

Before you start the migration, be aware of the DMU security considerations listed in this section.

• Protect DMU software integrity

  In this release, the DMU requires that you connect to a database with the SYSDBA privilege. This highest privilege is needed to analyze and convert all data in the database. But this also means that once a connection is established, the program—if maliciously modified—could read and change all database content, create database users and grant privileges to them, and so on.

  To ensure that this power is not abused, the DMU software files must be modifiable only by users who are also authorized to connect as SYSDBA. This implies that all files in the DMU installation directory must be owned and writable only by an operating system user to which only the trusted administrators can connect. If writable by a malicious user, the DMU program code in JAR files, the repository maintenance SQL scripts, or configuration files could be abused to inject malicious SQL statements.

  Unless you install the DMU on a host to which only authorized users have access, you must protect the DMU installation and configuration files using the operating system privileges. After you have uncompressed the archive file with the DMU installation, ensure that all uncompressed files and directories are writable only by you and other authorized operating system users. The DMU does not have an installer that can automatically set file permissions.

• Protect connection details

  If you select the Save Password check box when creating a database connection, the password you specify is saved in an obfuscated form in a password file named cwallet.sso in your user directory. Because obfuscation is a reversible operation, use this feature only for passwords to test databases with no production data or only if the DMU is installed on a very well-protected host. Ensure that the password file is readable only by you.

  The cwallet.sso file is in the $HOME/.dmu/ directory on Linux and UNIX systems, and in the %APPDATA%\DMU\ directory on Microsoft Windows systems.

• Protect DMU repository objects

  To avoid unauthorized access to potentially sensitive metadata and functionality, do not grant any privileges for the DMU repository database objects to any database users. This guideline applies to any object with a name starting with DUM$ or DBMS_DUMA.

• Avoid information leakage

  To avoid unauthorized access to potentially sensitive information, ensure that the DMU configuration, log, and report files are readable and modifiable only by authorized operating system users. By default, these files are in the $HOME/.dmu/ directory on Linux and UNIX systems, and in the %APPDATA%\DMU\ directory on Microsoft Windows systems. However, DMU users can often select a file path for generated logs and reports.

• Protect operating system files

  The DMU lets its users specify file paths to access specific files. In a remote terminal setup where a DMU user does not have full shell access to the shared host, this functionality could be abused to read or overwrite sensitive files. The host's operating system administrator should protect all sensitive operating system files from inadvertent or malicious overwriting. This precaution is not specific to the DMU.

Before you start the migration, collect information such as whether your database is supported, the target character set, which languages can be stored in the character set, and so on.

To help you set up the DMU properly, answer the following questions about your database and its use by applications:

• Is your database supported by the DMU?

  Verify that the version of your database software is supported by the DMU as described in "Overview of Database Requirements". The platform on which the database is installed must be ASCII-based (that is, not IBM z/OS or Fujitsu BS2000). Your database must not have Oracle Database Vault installed.

• What is the target character set?

  You have to decide if the target character set of the migration is AL32UTF8 or UTF8. Oracle recommends AL32UTF8, which is a proper implementation of the UTF-8 encoding form of the Unicode Standard. The DMU supports migration to the older character set UTF8 for databases that must support database client software based on Oracle 8i Client libraries, or that must support applications certified with UTF8 but not with AL32UTF8, for example, Oracle Applications Release 11i. The UTF8 character set, despite its name, is an implementation of the CESU-8 compatibility encoding form of the Unicode Standard. CESU-8 is very similar to UTF-8 except for the way the supplementary characters are stored (that is, characters with Unicode code points U+010000 and higher). Oracle does not generally update the definition of UTF8 to synchronize it with the newest versions of the Unicode Standard, while Oracle does update the definition of AL32UTF8.

• Which languages and character sets can be stored in the database?

  The very nature of character set encodings and the very nature of character storage in an Oracle database make it impossible to automatically and precisely recognize what language in what character set is represented by a given sequence of bytes in a column value. The DMU scanning process can tell if there are bytes in a character value that are invalid in the declared character set of this value, but not more. If almost all bytes of the declared character set are assigned to single-byte codes of some characters, such as the case of CL8MSWIN1251, which defines all bytes except 0x98, then virtually any sequence of bytes declared as encoded in this character set will appear as valid to the scanning process. Therefore, even if an application uses the pass-through configuration to store data in the database that it interprets in a character set different from the database character set (see "Invalid Binary Storage Representation of Data") the DMU might not see any invalid codes and it might not signal the mismatch between the real (application) character set of the data and the declared database character set. Hence, it is very important that you research, for example, by asking application developers and administrators, what languages or character sets are most probably stored in your database. This will help you to manually analyze the contents of the database, if necessary, to supplement the automatic analysis by the DMU.

• Which columns contain data in foreign languages?

  If your database contains data mainly in one language and only a few table columns might contain other languages, for example foreign customer names and street addresses, a list of all such columns facilitates the manual search for invalid data stored in the pass-through configuration, if the existence of such data becomes probable after analysis of the information gathered in the previous list item.

• Which character columns might contain binary data?

  Some applications might connect to your database in the pass-through configuration and use character columns to store data that is binary in nature, such as text encrypted without using the Transparent Data Encryption feature of the database, images, text in a binary format of a word processor, and so on. Obtain a list of those applications from application developers or administrators to further help you analyze the contents of your database.

  See "Cleansing Scenario 3: Cleansing Invalid Representation Issues" for information about resolving invalid binary representation issues using the collected information.

• What is the real character set of your database?

  Determine if the character data in your database is actually to be interpreted in a particular common character set of the clients connecting to database and not in the declared database character set. To improve the effectiveness and accuracy of the analysis that you perform with the help of the DMU to verify that the database can be converted to Unicode.

  One way to determine if the real character set of your database differs from the declared database character set is to look for the following characteristics:

  • The database (declared) character set is US7ASCII, or WE8ISO8859P1, or WE8ISO8859P15.

  • The client character set, declared in the NLS_LANG client setting, is the same as the database character set. If the NLS_LANG is not specified at all, the client character set defaults to US7ASCII. NLS_LANG affects only C or C++ clients connecting through an OCI API. Java clients connecting through a JDBC API are always UTF8 clients.

  • All database client software runs on the Microsoft Windows platform and works in one of the Windows character sets, which are also known as ANSI Code Pages. This character set depends on the language version of Windows: WE8MSWIN1252 – US and Western European versions, EE8MSWIN1250 – Central European versions (for example, Polish), CL8MSWIN1251 – Cyrillic versions (for example, Russian), AR8MSWIN1256 – Arabic version, and so on.

  This Microsoft Windows character set common to all clients is the real database character set that was to be identified.

  If your database is used in a correct character set configuration, that is, the NLS_LANG setting always correctly corresponds to the real character set of the database clients, the real character set of the database is its declared character set.

• What is the connection information for your database?

  To migrate a database with the DMU, you must have the SYSDBA privilege and the required connection credentials (user name and password). Starting in Oracle Database 12c, to perform scanning and cleansing operations on a PDB, you can connect as a user who has the SYSDBA privilege in the local PDB. However, to convert a PDB to Unicode, you must connect as the SYS user.

  You need to know the host name or IP address of the database server, the port number on which the database listener listens for connection requests, and the service name of the database. For performance reasons, Oracle recommends that you connect to a service that is configured for dedicated connections, that it, not using the a shared server configuration.

After you have collected the preceding information, you can install the DMU.

Oracle recommends that you run the DMU on the database host or on a workstation connected to the database host with a fast and reliable local area network.

1. To install the DMU, uncompress the downloaded archive file to any directory on the host on which you want to run the DMU.
2. After you have uncompressed the archive file, ensure that the DMU files are writable only to you and other authorized operating system users. This is very important because unprivileged users with access to the DMU host could modify the DMU files to make the DMU execute arbitrary SQL statements when the DMU is later started with SYSDBA credentials. Such SQL statements could compromise database security.
3. After you have installed the DMU, initialize the database for the migration process with the DMU.

You must create a connection to the database that you want to analyze or migrate.

1. In the Navigator pane, right-click the Databases node to open the context menu. Alternatively, open the File menu, and then select New Database Connection.

   The Create Database Connection dialog box appears.

   Figure 2-1 Creating a Database Connection - Basic Connection Type

   
   Description of "Figure 2-1 Creating a Database Connection - Basic Connection Type"

   In this dialog, the connection type of Basic is selected by default.

   If you select the connection type of SSH in this dialog, then the dialog shows additional fields to connect to the database securely using the Secure Shell (SSH) protocol:

   Figure 2-2 Creating a Database Connection - SSH Connection Type

   
   Description of "Figure 2-2 Creating a Database Connection - SSH Connection Type"
2. Enter the following information to define a connection:

   • Connection Name

     Give a descriptive name to the connection so that you can easily identify to which database the DMU is connected.

   • User Name

     Specify a database user name to use for authentication with the database. In this release, DMU requires the user to have the SYSDBA privilege.

   • Password

     Specify the password for the database user.

   • Save Password

     Select this check box if you want to save the password on the local system, so that you are not prompted for password each time you open the database connection.

     Note:

     If you select the Save Password check box, then the password will be saved along with other connection details in an obfuscated form in a configuration file in your user directory. Because obfuscation is a reversible operation, use this feature only for passwords to test databases with no production data or only on very well protected hosts. Ensure that the configuration files storing your preferences and connection information are readable only by you. On Linux and UNIX systems, the configuration files are in the $HOME/.dmu/ directory. On Microsoft Windows systems, the configuration files are in the %APPDATA%\DMU\ directory. Otherwise, you risk compromising the security of your database. If you deselect the Save Password check box, you will be prompted for the password each time you open the connection.

   • Connection Type

     Specify one of these two connection types to connect to the database – Basic or SSH.

   • Role

     In this DMU release, only the SYSDBA role is supported.

   • DB Host Name

     Specify the DNS name or the IP address of the database host.

   • DB Port

     Specify the TCP/IP port on which the Net Services (TNS) listener listens for database connection requests.

   • Service Name

     Specify the service name of the database instance to which you want to connect.

   • If you select the connection type of SSH, then the following additional fields are displayed:

     • SSH Host Name

       Specify the DNS name or the IP address of the SSH server.

     • SSH Port

       Specify the port for the SSH server.

     • SSH User Name

       Specify the SSH server user name.

     • Forwarded Local Port

       Specify local port of the system where the DMU application is running.

     • Use Key File

       Select this option to connect to the SSH server using key file authorization. If you select this option, then specify the key file with the complete directory path.

       Note:

       • The supported key types are: DSA, RSA, and ECDSA.

       • The supported key formats are: OpenSSH and Putty.

       • For the key file validation rules, refer to the documentation for the Apache Mina SSHD API org.apache.sshd.common.config.keys.KeyUtils.validateStrictKeyFilePermissions().

The DMU repository manages the information necessary for each step of the migration.

1. If you connect to a database for the first time, the DMU automatically prompts you to install the repository. If you are not prompted to install the repository, you can install it by right-clicking the database you want to use, and selecting Configure DMU Repository. You can also select Configure DMU Repository from the Migration menu. In all of these cases, the Repository Configuration Wizard appears. Figure 2-3 shows how it appears.

   Figure 2-3 Repository Configuration Wizard - Select Task

   
   Description of "Figure 2-3 Repository Configuration Wizard - Select Task"
2. On the first page of the wizard, the only choice available is Install the repository in migration mode. After selecting this, click Next.

   After you click Next, the second page of the Repository Configuration Wizard is shown. Figure 2-4 is an example of this page.

   Figure 2-4 Repository Configuration Wizard - Task Details

   
   Description of "Figure 2-4 Repository Configuration Wizard - Task Details"
3. Here, you select the target character set for the migration. You can choose between AL32UTF8, the normally recommended character set, and UTF8, which might be required for compatibility with certain applications, such as Oracle Applications 11i. After selecting the character set, click Next. Figure 2-5 appears.

   Figure 2-5 Repository Configuration Wizard - Select Tablespace

   
   Description of "Figure 2-5 Repository Configuration Wizard - Select Tablespace"
4. On the third page, you can select the tablespace in which you want to install the DMU repository. The default tablespace is SYSAUX.

   Oracle recommends that you create a separate, empty tablespace to be used for the DMU repository. This helps to avoid fragmentation of the production tablespaces. Refer to "Creating a Tablespace for the DMU Repository" for more information.

   Click Finish to install the repository.

After a database connection has been established, you can follow the progress of the migration by looking at the Migration Status tab.

1. Click the Migration Status tab in the Client pane.
2. If you have modified the database outside of the DMU to resolve some of the convertibility issues, click Retest to let the DMU recheck the database and update the status information.
3. To reopen the tab at any time, select Migration Status Panel from the Migration menu.

In this step, the DMU scans tables for problems, assessing the feasibility of migrating the data to Unicode.

The most common types of problems are with your data. Issues include values expanding during conversion beyond column or data type limits, data in a mislabeled character set, or binary data stored in character data types.

During scanning, the DMU reads specified character columns and performs a test conversion of each column value to the target character set, AL32UTF8 or UTF8. Depending on the result of this conversion, the DMU classifies data as follows:

• Needs no conversion

  The data is fine, because the binary representation of the data does not change in the conversion.

• Needs conversion

  The data must be converted, because the binary representation of the data does change, but no other data issues have been found.

• Invalid binary representation

  The binary representation of the data is invalid under the current database character set. If you do the conversion in this state, the resulting data will usually not make sense to applications and users.

• Exceeds column limit

  The data will not fit into a column after migration.

• Exceeds data type limit

  The data will exceed a data type limit after migration.

Each column value is assigned to only one of the preceding categories. Values that have invalid binary representation are classified only as such even if their lengths exceed column or data type limit after conversion. As conversion of invalid character codes usually yields the default replacement character, which has a three-byte representation in AL32UTF8 and UTF8, the length expansion issues are not rare among values with invalid representation. Because the DMU let you ignore invalid representation issues and force conversion of a column, you should be aware that forcefully converted values with invalid binary representation may be additionally truncated. You can compare the value of the Maximum Post-Conversion Length property of the column with the column and data type length limits to see if the truncation will take place.

This release of the DMU supports Oracle Database 12c extended data type limit where the maximum size limit for VARCHAR2 has been increased to 32767 bytes. The extended type limit guarantees that VARCHAR2 data created in earlier releases up to the original type limit of 4000 bytes can be migrated to Unicode on Database 12.1 with no over data type limit expansion exceptions.

After you have run a scan, the DMU creates a Database Scan Report, which can be found under the Migration drop-down menu. This scan report shows the statistics for the current data under each of the preceding categories. The counts of values are presented for each character data type column, and also summed up at the table, schema, and database levels. The scan report enables you to filter and customize the output, for example, so that only columns with selected potential problems such as exceeding the column limit are displayed.

In addition, you can use the report to iteratively go through the steps of taking the data that is not clean, reviewing this data in the Cleansing Editor, fixing the data, either immediately or at your convenience later, and rescanning to verify that the data is now clean. While scanning the data, you can generate an index check report, which can be found on the Indexes subtab of the Database Scan Report. The index check report lists the indexes defined on character columns whose key lengths will exceed the maximum allowed key size due to column modifications made with the Cleansing Editor or the increased post-conversion data size of the columns. This enables you to fix any problematic indexes before proceeding to the data conversion phase.

You can perform scans on the following objects:

• All tables in the database

• All tables in the data dictionary of a database

• All application schema tables in a database

• All tables in an application schema

• A table column in an application schema

• An arbitrary set of application schema tables and columns in a database

As mentioned in "Overview of Character Set Migration Considerations", certain data issues cannot be discovered automatically. For example, the DMU does not analyze character data in binary data types. Also, some single-byte character sets define almost all byte values as valid codes. For example, the only byte value that is not a valid CL8MSWIN1251 character code is 0x98. Therefore, if the incorrectly stored data happens not to contain any of the undefined byte values, then the test conversion during a scan might not show invalid binary representation problems. Even if the database scan reports no issues, collect and analyze information about use of the database by applications and try to identify these types of hidden problems before attempting the database conversion. Creating a test copy of the database, migrating it to Unicode, and thoroughly testing it with your applications is also a way to discover many problems.

In this step, the DMU cleans the data in tables based on identified issues.

You can define cleansing actions for immediate or for delayed execution (immediate versus scheduled cleansing mode). You can perform certain types of cleansing in a production environment with no side effects to applications. Other cleansing operations involve metadata changes and require that you adapt applications to these changes. The scheduled mode enables you to define a cleansing action at any time but delay its execution to the conversion phase of the migration process. This is usually the most convenient moment to introduce metadata changes because the database is not used in production and new application versions can be easily deployed at the same time.

Immediate changes to metadata are defined in the Modify Column and Modify Attribute dialog boxes. Scheduled changes are defined in the Schedule Column Modification and Schedule Attribute Modification dialog boxes. All these dialog boxes can be invoked from the context menu of a column or attribute in the Cleansing Editor. Immediate editing changes of user data are performed directly in the Cleansing Editor.

In the immediate mode, any change entered is performed immediately after an appropriate Save button is selected. In this case, all SQL statements are issued to the database and the transaction is committed.

In the scheduled mode, clicking Save puts the cleansing action into the DMU repository. The corresponding statements are executed during the conversion step. You can change the scheduled action or remove it, provided the conversion step has not yet been started, by reopening the Schedule Column Modification or Schedule Attribute Modification dialog box and selecting No Modification.

You apply the editing changes performed in the Cleansing Editor on table data to the database by clicking Save on the Cleansing Editor tab. You can revert the changes that have not yet been saved by clicking the Revert button.

The Cleansing Editor can also be used to set the assumed character set of a column. This property tells the DMU to interpret the contents of the column in a character set different from the database character set. The selected character set is applied to the test conversion during scanning, actual conversion occurs in the conversion step, and the set is used to interpret data for display in the Cleansing Editor. If column data in the Cleansing Editor is not legible, the character set of the column might be selected incorrectly.

After cleansing, rescan the database to verify that your changes have successfully handled the potential problems. This is an iterative process where you scan and cleanse until there are no more conversion issues before you proceed to the conversion phase.

When you select Convert Database in the Migration menu, the DMU first executes a conversion feasibility test.

This test checks that:

• All database convertibility requirements listed in "Overview of Database Requirements" are met

• All data in the database has been scanned and has valid scan results

  Cleansing actions might invalidate scan results, so cleansed tables might need to be rescanned.

• No data in the database has binary representation or length issues

If the test succeeds, the DMU presents a plan for conversion. This plan shows all SQL statements that will be executed to convert the database, including statements to handle auxiliary objects, such as indexes, constraints, and triggers. You can customize the plan as desired. There are various database-level and table-level options that can be used to influence the way DMU converts the database. For example, you can set the conversion method for a given table or the number of processes participating in the conversion step. The conversion plan is generated and displayed in the Conversion Progress tab. You can then modify the available options by clicking Edit Table Conversion Plan or Edit Database Conversion Parameters.

This step converts the data into Unicode.

You accept the conversion plan and initiate the conversion by clicking the Convert button on the Conversion Progress tab. The DMU repeats the conversion feasibility test to ensure that no issues have been introduced while you worked on the conversion plan. It verifies also that no other sessions are connected to the database and that the database is mounted in exclusive mode. Then, the DMU begins to migrate the data, executing the statements from the conversion plan.

You can monitor the conversion progress on the Conversion Progress tab.

As an overview, the process the DMU uses to convert data is:

1. Put the database into restricted mode.

2. Disable various job queue processes.

3. Drop or disable selected indexes.

4. Disable selected triggers and constraints.

5. For tables assigned the "Convert using Data Pump" conversion method, export the tables to a database directory using Oracle Data Pump.

6. Convert the data in user tables (except blockchain and immutable tables) and in selected data dictionary tables to Unicode.

7. Convert CLOB columns in the data dictionary.

8. Issue the ALTER DATABASE CHARACTER SET statement, and convert the data in blockchain and immutable tables to Unicode.

9. For tables assigned the "Convert using Data Pump" conversion method, import the data into the tables using Oracle Data Pump.

10. Enable triggers and constraints; and re-create indexes and constraints.

11. Restore the database instance parameters.

The conversion of a table is performed by updating its columns with an UPDATE statement, by converting the columns while re-creating the table using the CREATE TABLE AS SELECT statement, or by exporting and importing the table with Oracle Data Pump. The re-creation of a table is faster than an update if most of the table rows must be converted. Oracle Data Pump export and import may be faster than the other conversion methods if the table is large and contains CLOB data.

After the conversion has finished, the DMU will re-create or re-enable any objects that were dropped or disabled earlier. You can check that no errors have been generated by looking at the Log pane.