Oracle Database Migration Assistant for Unicode (DMU) is a next-generation migration tool that streamlines the entire migration process with an intuitive GUI that minimizes the DBA's manual workload and decision-making. It helps ensure all migration issues are addressed beforehand and the conversion of data is carried out correctly and efficiently.
Latest version of Oracle DMU 19.1 can be downloaded from this link,
https://www.oracle.com/database/technologies/appdev/migration-assistant-unicode.html
(or)
From MOS download the patch 30149496 -->Use this for Production databases
Oracle DMU is a free Oracle Database tool. It is distributed via OTN and via My Oracle Support (MOS). Both download packages are identical but the OTN download is made available under the OTN Developer License, which allows you to evaluate the tool, while the MOS download is a Program Update under the database support contract and permits you to migrate production databases covered by a valid support contract on any level that entitles to Program Updates.
Important Pointers for Migrating Character set Native to Unicode
Follow this document from MOS if you are converting EBS database to Unicode that also contains the steps for post conversion steps of configuring the application tier files as well,
Globalization Guide for Oracle Applications Release 12 (Doc ID 393861.1)
Note: EBS does not allow column expansion, data truncation, nor CHAR semantics. Any approaches related to these are not applicable on EBS.
After conversion to the target character set, the cells classified as Over Column Limit (Scheduled) will no longer fit into their columns unless the columns are lengthened. The cells classified as Over Type Limit (Scheduled) will become longer than the limit of their data types.
The migration happens in-place and reduces the effort and downtime required for data conversion, comparing to conventional export and import migration methods.
Under one of the following conditions, the Import and Export utilities should be used instead of DMU:
•The character set migration process is combined with database upgrade to a higher release and/or migration to another platform.
•A lot of tables in the database are fragmented and the character set migration process is an opportunity to perform de-fragmentation and clean-up.
Tips For and Known Issues With The Database Migration Assistant for Unicode (DMU) Tool version (Doc ID 2018250.1) - Follow all this first to clear max issues that we might face.
1. This zip file downloaded from MOS (patch 30149496) contains the Oracle Database Migration Assistant for Unicode (DMU) release 19.1 client package.
Download and installed 64-bit JDK 1.8.0 latest version on a machine with good configuration.
2. Launch the tool, d:\>cd e:\DMU19.1\dmu -->d:\DMU19.1\dmu>dmuW64.exe
The interface between the DMU client and the DMU functionality in the database kernel is the PL/SQL package SYS.DBMS_DUMA_INTERNAL. This package is not installed by default. After any required database patches have been installed, start an SQL*Plus session in the Oracle Home of your database, log in with SYSDBA credentials, and run the script ?/rdbms/admin/prvtdumi.plb to create this package SYS.DBMS_DUMA_INTERNAL.
To avoid DMU Database Scan Report Failing With OutOfMemory Error (Doc ID 1928231.1)
Always use 64bit JDK and then Increase the JVM maximum memory like 10-20G from the window Tools > Preferences > Memory Settings > Max JVM Memory
3. When you connect for the first time to a database, you will be prompted to install a DMU migration repository. Install the repository in the new tablespace created above. The DMU does not function without a repository. It's a good idea to create a separate tablespace for the repository, rather than using the SYSAUX tablespace.
4. The process is as below,
•Enumeration - auto-identification of database objects containing textual data that requires conversion
•Scanning - comprehensive assessment of migration feasibility and discovery of potential data issues
•Cleansing - sophisticated tool set for iterative data analysis and cleansing to ensure data safety
•Conversion - automated in-place data conversion to minimize time and space requirements
5. Important pointers during SCANNING phase:
The Number of Scanning Processes is the number of threads that the DMU spawns to perform the scanning process. Each thread opens a separate database connection. The DMU assigns a set of tables to each thread to scan independently. Larger tables might be split into chunks and scanned by multiple threads. Smaller tables are scanned entirely by one thread. The default value for this parameter is the value of the CPU_COUNT initialization parameter of the database. You can increase the parameter to see if the scanning time decreases, leveraging the parallel processing of CPUs and disks of the database server, or you can decrease the parameter to limit the negative impact that the scanning process might have on the performance of a busy production database.
You can monitor the scanning progress in the "Scan Progress" tab in DMU . It displays a progress bar and a percentage that indicate how much of the corresponding table or table chunk has already been scanned.
You can monitor the table-level conversion progress in the "View Table Conversion Progress" link on the Conversion Progress tab in DMU. It will display the completion percentage for each table based on the status from the V$SESSION_LONGOPS view along with the execution status of individual SQL statements.
What scan option should I choose for the first scan ever of a database?
For the first scan done on a big database then one might choose "Do not collect rowids" as scan option in the second screen of the scan wizard.
This will reduce the time needed for the scan and limit the amount of space needed for the DMU repository while still giving an overall overview of the dataset condition.
If you then notice that there is a lot of "Invalid Representation" as scan result for example you then know there is some work to do /data to check.
If you have already done conversion of databases with similar datasets (Q&A db or so based on the prod db and you are now converting the prod db) or the database is not really huge use the default "Collect rowids only according to 'Rowids to Collect' table property" option.
The DMU might collect during scanning the rowid addresses of column cells that contain convertible values or values with expansion or invalid binary representation issues.
CLOB columns are not scanned or even listed in the Navigator pane in a multibyte database. Any multibyte database, including databases in AL32UTF8 and UTF8 character sets, store CLOB values in the same internal storage character set (Unicode UTF-16), and so CLOB values need no conversion when the database character set is migrated from one multibyte character set to another.
https://docs.oracle.com/en/database/oracle/dmu/19.1/dumag/ch4_basic_tasks.html#GUID-8B5BA47D-7BB1-401C-A2AA-7CF34F0D7229
Above link tells about values for ‘Scan Buffer Size’ and ‘Number of Scanning Processes’.
The parameter Scan Buffer Size controls the size in bytes of a buffer that the DMU allocates in each database server session to scan a table. The default value is 1000 kilobytes. The total buffer space used in a single scan is the number of scanning processes times the value of this property. Increasing the value of the property could speed up scanning, but only for as long as the allocated buffer memory fits into the available RAM on the database server.
Ignore below External Tables error while scanning.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
04001: error opening file /tmp/inbound/exttab1.log
ORA-06512: at "SYS.DBMS_DUMA_INTERNAL", line 8
ORA-06512: at "SYS.DBMS_DUMA_INTERNAL", line 43
ORA-06512: at line 1
Refer to this document for any scan and cleaning data related errors and respective action to be taken
https://docs.oracle.com/cd/E89575_01/DUMAG/ch4_basic_tasks.htm#DUMAG-GUID-C9DCB0E2-01DD-4B05-9EBE-9B0A2C91232E
At this stage cleanising is not mentioned in section 3.1 of MOS document
Globalization Guide for Oracle Applications Release 12 (Doc ID 393861.1)
Hence data should be fixed and then do the conversion while cleaning of data not supported for Oracle EBS.
You must resolve data issues identified during scanning before you can convert the database. Cleansing actions are actions that can take to resolve convertibility issues.
The DMU does not allow the conversion process to start until all issues have been resolved or explicitly marked as ignorable.
Below link details on cleansing the data.
https://docs.oracle.com/cd/E89575_01/DUMAG/ch6_cleansing_data.htm#DUMAG1465
6. Important pointers during CONVERSION phase:
After scanning check the Database Properties: Scanning tab and click on "Estimate Tablespace Extension" button to check the estimated data expansion and check if there is enough space in the tablespaces and on OS level to do the conversion.
We suggest to add enough space upfront (before conversion) to the table spaces , this will be faster than letting them autoextent
Identify any (local) batch jobs or check the database jobs to see if there are any load or so done , if so then stop those at least during the conversion phase.
If you do not stop the listener then make sure everyone is aware to NOT connect during the conversion phase.
Stop the OEM agent also during the conversion phase.
The Migration Status tab must show that there are no issues as below only then we should proceed with Conversion.
STOP ALL APPLICATION SERVICES
RESTART THE DATABASE ONCE
The SQL statement ALTER DATABASE CHARACTER SET, which the DMU uses in the conversion phase, succeeds only if the session executing the statement is the only user session logged into the database. Therefore, before starting the conversion, the DMU warns you about any user sessions logged into the database that are not opened by the DMU itself. You may use the following SQL statement in SQL*Plus or SQL Developer to find out the details of the offending sessions:
SELECT sid, serial#, username, status,
osuser, machine, process, program
FROM v$session
WHERE username IS NOT NULL
AND program <> 'Database Migration Assistant for Unicode';
Note:
After the database is successfully converted to Unicode, you must restart the database instance to synchronize any in-memory character set metadata information with the new Unicode database character set.
In the monitoring mode, the Convert button is replaced with a Stop button. If you click Stop, a warning dialog is displayed. In this dialog, you have the options of aborting the conversion process immediately (Yes), stopping the conversion process after the current running process finishes (No), or returning to the conversion operation (Cancel).
If the database reports an error during the conversion process, the currently executed step and the statement that caused the error are marked with the red X error icon
7. Verification
select NLS_LANGUAGE from apps.FND_LANGUAGES where INSTALLED_FLAG in ('B','I');
NLS_LANGUAGE
AMERICAN
SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';
select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)
order by CHARACTERSET, TYPES_USED_IN;
CHARACTERSET TYPES_USED_IN
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 LONG
AL32UTF8 VARCHAR2
Follow below MOS document if you are converting EBS database to Unicode that also contains the steps for post conversion steps of configuring the application tier files as well,
Globalization Guide for Oracle Applications Release 12 (Doc ID 393861.1)
