4 Performing Basic DMU Tasks

This chapter shows the step-by-step use of the Database Migration Assistant for Unicode (DMU) in a few typical scenarios of the character set migration process.

If you follow the examples presented here, you can then use the tool to migrate your database. Most of the steps in this chapter are common throughout all scenarios.

4.1 Initializing the Database

Initializing a database for use with the DMU requires several installation and creation tasks.

4.1.1 Installing Required Patches

For your database release to work with the DMU, you may need to install database patches.

Patches are available at https://support.oracle.com.

To identify required patches and install them:

  1. Refer to the latest DMU release notes to determine whether database patches are required for your database release to work with the DMU.
  2. Use the opatch utility to check whether the required patches are already installed.
  3. Set the ORACLE_HOME environment variable to the Oracle home directory of the database, change the current directory to the OPatch subdirectory of the Oracle home, and then issue the following statement:
    opatch lsinventory –patch
    
  4. If the opatch utility identifies the lack of a valid oraInst.loc file, then locate the file in the Oracle home of the database and pass its location in the -invPtrLoc parameter, as shown in the following example:
    opatch lsinventory –patch –invPtrLoc ../oraInst.loc
    

    The output from the opatch utility will resemble the following:

    Invoking OPatch 10.2.0.5.0
     
     
    Oracle interim Patch Installer version 10.2.0.5.0
    Copyright (c) 2005, Oracle Corporation.  All rights reserved..
     
     
    Oracle Home       : C:\oracle\product\10.2.0\db_1
    Central Inventory : C:\Program Files\Oracle\Inventory
       from           : n/a
    OPatch version    : 10.2.0.5.0
    OUI version       : 10.2.0.5.0
    OUI location      : C:\oracle\product\10.2.0\db_1\oui
    Log file location : C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch2010-05-17_22-50-28PM.log
     
    Lsinventory Output file location : C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\lsinv\lsinventory2010-05-17_22-50-28PM.txt
     
    --------------------------------------------------------------------------
     
    Interim patches (2) :
     
    Patch  5556081      : applied on Mon Feb 08 15:50:52 CET 2010
       Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
       Bugs fixed:
         5556081
     
    Patch  5557962      : applied on Mon Feb 08 15:50:45 CET 2010
       Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
       Bugs fixed:
         4269423, 5557962, 5528974
     
    --------------------------------------------------------------------------
    
    OPatch succeeded.
    
  5. Check whether the required database patches are listed among the interim patches.
  6. If you discover missing patches, then download them from the My Oracle Support site and install them.
  7. Review the documentation that is part of the patches for installation instructions.

4.1.2 Installing Supporting Packages

If your database has not been initialized for the DMU, then it will not contain the PL/SQL supporting packages needed to access dedicated database kernel functions.

Perform the following steps if installation documentation from any installed database patches requires them.

To install supporting packages:

  1. Connect to the database server host as an operating system user who can run the SQL*Plus utility from the Oracle home directory of the database.
  2. Start the SQL*Plus utility and connect as a user with the SYSDBA privilege. For example, on Linux or Microsoft Windows, enter the following command:
    sqlplus / as sysdba
    
  3. After you have logged in, execute the following command:
    SQL> @?/rdbms/admin/prvtdumi.plb
    

    The output from the script should resemble the following:

    Library created.
     
    Package created.
     
    No errors.
     
    Package body created.
     
    No errors.
    

4.1.3 Creating a Tablespace for the DMU Repository

For ease of maintenance and to avoid fragmentation of other production tablespaces, Oracle recommends that you use a separate tablespace for the database objects forming the DMU repository.

To create a tablespace:

  1. Log in to the database as a user with the SYSDBA privilege.
  2. To determine the initial size of the tablespace, run the following SQL query:
    
    SELECT CEIL((t.cnt*300+c.cnt*1000)/1048576)||' MB' "Initial Size"
    FROM   (SELECT COUNT(*) cnt FROM SYS.TAB$) t,
           (SELECT COUNT(*) cnt 
            FROM   SYS.COL 
            WHERE  OBJ# IN (SELECT OBJ# FROM SYS.TAB$)
            AND    BITAND(property,65536)=0
            AND    TYPE# IN (1,8,58,96,112)
            AND    CHARSETFORM=1) c
    

    The size of the tablespace could grow significantly if you collect a lot of rowids for use by the Cleansing Editor or if you apply the "Convert only updatable rows" conversion method to many tables with large numbers of convertible cells. Oracle recommends that the tablespace be locally managed with the default extent allocation policy and with the autoextension feature enabled.

  3. Create a tablespace for the DMU repository, as explained in Oracle Database 2 Day DBA.

4.1.4 Creating a Database Connection

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

4.2 Refreshing, Reinstalling, Upgrading, and Uninstalling the DMU Repository

After the DMU repository is installed, it is automatically refreshed at predefined points in the workflow to account for any objects containing character data that may have been added to, altered in, or removed from the database. You can also refresh, reinstall, upgrade, or uninstall the repository by selecting the appropriate option from the Migration menu.

4.2.1 Refreshing the DMU Repository

You can manually refresh the DMU repository to immediately include any recent changes to the database objects.

To refresh the DMU repository:

  1. In the Migration menu or the context menu of the database node in the Navigator pane, select Refresh DMU Repository.
    The message about the successful refresh of the DMU repository is displayed once the refresh operation is completed.

4.2.2 Reinstalling the DMU Repository

You can explicitly reinstall the DMU repository.

To reinstall the DMU repository:

  1. In the Migration or Validation menu, select Configure DMU Repository.
    Figure 4-1 appears.

    Figure 4-1 Repository Configuration Wizard - Reinstallation

    Description of Figure 4-1 follows
    Description of "Figure 4-1 Repository Configuration Wizard - Reinstallation"
  2. Choose either migration mode or validation mode, and then click Next.
    Figure 4-2 appears.

    Figure 4-2 Repository Configuration Wizard - Reinstallation/Archiving

    Description of Figure 4-2 follows
    Description of "Figure 4-2 Repository Configuration Wizard - Reinstallation/Archiving"
  3. Select the target character set for migration. Ensure that "Archive the existing repository" is selected, and then click Next.
    Figure 4-3 appears.

    Figure 4-3 Repository Configuration Wizard - Select Tablespace

    Description of Figure 4-3 follows
    Description of "Figure 4-3 Repository Configuration Wizard - Select Tablespace"
  4. Click Finish.
    The DMU repository is reinstalled.

4.2.3 Upgrading the DMU Repository to 19.1

When connecting DMU 19.1 to a database whose repository was installed by the DMU 2.2 or 2.1.1 software, the Repository Configuration Wizard enables you to upgrade the repository to DMU 19.1.

You must have a valid DMU 19.1 repository installed before you can perform migration tasks with the DMU 19.1 software.

To upgrade the repository from DMU version 2.2 or 2.1.1 to version 19.1:

  1. From the Migration or Validation menu, select Configure DMU Repository.

    The Select Task page of the Repository Configuration Wizard is displayed.

  2. On the Select Task page, select the following options:

    • For the Migration mode, select Upgrade an older version of the repository in migration mode.

    • For the Validation mode, select Upgrade an older version of the repository in validation mode.

  3. Click Next.

    A progress bar showing the progress of the DMU repository upgrade process is displayed.

    After successful completion of the DMU repository upgrade process, a dialog box containing the following message is displayed:

    Repository has been upgraded successfully.

4.2.4 Uninstalling the DMU Repository

You can uninstall the DMU repository.

To remove the DMU repository:

  1. From the Migration menu or the context menu of the database node in the Navigator pane, select Configure DMU Repository.
    A dialog box appears providing you the option to uninstall the repository.
  2. Select Uninstall the repository, and then click Next.
    The DMU repository is uninstalled.

4.3 Scanning the Database

In the scanning step, you analyze problem causes and choose a cleansing strategy to resolve data issues.

Scanning performs the following steps:

  1. Reading character values from the database

  2. Converting these values to the target character set

  3. Counting how many values meet the following criteria:

    • Change in conversion

    • Do not fit into their columns

    • Do not fit into their data types

    • Contain valid character codes

The scan phase also calculates additional statistics, such as the maximum post-conversion length of values in a column. The DMU stores the calculated counts and statistics in the DMU repository as scan results. The character values resulting from the test conversion are themselves discarded. They are not stored permanently in the database.

Before a database can be converted to Unicode, the DMU must analyze the character data in the VARCHAR2, CHAR, LONG, and CLOB table columns to assess if any issues could prevent the conversion from finishing successfully without causing data corruption. The DMU analyzes the data by converting character column values in the database from their declared character set to the target Unicode character set, and checking each value to determine if any of the following conditions is true:

  • The conversion result differs from the original value.

  • The conversion result fits into the length limit of its column.

  • The conversion result fits into its data type.

  • The conversion result does not contain any replacement characters, that is, each converted source character code is valid in the declared character set of the column.

4.3.1 Setting Database Properties

Before scanning the database, provide the DMU with the language and character set information that you collected when preparing for migration.

To set database properties when connecting to the database:

  1. Connect to a database.

    The Database Properties tab is automatically opened in the client pane of the DMU window. The title of the tab is the name of the connection.

    Figure 4-4 Database Properties Tab - General

    Description of Figure 4-4 follows
    Description of "Figure 4-4 Database Properties Tab - General"

To set database properties in the Navigator pane:

  1. In the Navigator pane, right-click the database node and select Properties from the context menu.

  2. From the left sidebar of the tab, choose the General subtab.

  3. On the subtab, set the Assumed Database Character Set property to the identified real character set of data in the database.

    If your database is used in a correct character set configuration, as opposed to the pass-through configuration, this property should remain equal to the Current Database Character Set property.

See Also:

4.3.2 Scanning the Database with the Scan Wizard

Use the Scan Wizard to initiate the scan.

The scan results indicate whether data needs cleansing before it can be converted permanently without data loss. Also, the DMU uses the scan results to select the most effective conversion method for each table.

The following elements of the DMU user interface enable you to control the scanning process and view the scan results:

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.

The source character set used for the test conversion of column values is the assumed character set of the column. See "Cleansing the Data".

To scan the database using the Scan Wizard:

  1. To open the Scan Wizard, select Scan Database from the Migration menu or Scan Database, Scan Schema, Scan Table, or Scan Column from the context menu of the corresponding object node in the Navigator pane.
    The Scan Wizard is displayed, as in Figure 4-5.

    Figure 4-5 Scan Wizard - Welcome

    Description of Figure 4-5 follows
    Description of "Figure 4-5 Scan Wizard - Welcome"

    The DMU might also open the Scan Wizard after you start the conversion process from the Conversion Details tab. The DMU opens the wizard if one or more tables in the database are to be converted using the conversion method "Update only convertible rows". The additional scan collects rowid addresses that this conversion method requires. The Scan Wizard opened during the conversion phase has a different first page, as shown in Figure 4-6. It also lacks the object selection page. See "Converting the Database" for more information about the conversion phase.

    Figure 4-6 Scan Wizard - Welcome: Rowid Collection

    Description of Figure 4-6 follows
    Description of "Figure 4-6 Scan Wizard - Welcome: Rowid Collection"
  2. Click Next to skip the Welcome page.
  3. The second page of the wizard lets you set general parameters for the scanning process, as shown in Figure 4-7.

    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.

    Figure 4-7 Scan Wizard - Set Parameters

    Description of Figure 4-7 follows
    Description of "Figure 4-7 Scan Wizard - Set Parameters"

    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.

    The three radio buttons let you define the initial rowid collection level for tables selected for this scan. This initial level can be subsequently changed on the Scan Details page of the wizard, as described in point 5 below. The option "Collect rowids only according to 'Rowids to Collect' table property" tells the DMU to use the value of the table property 'Rowids to Collect' as the initial rowid collection level for each table. See "Table Properties: Scanning" for more details about this property.

    The option "Collect also rowids for Update Convertible Rows conversion method" can be selected to let the DMU pre-collect rowid addresses for the "Update only convertible rows" conversion method. The option causes the initial rowid collection level to be set to "All to Convert" for all selected tables that have been assigned this conversion method and to the value of the property Rowids to Collect for all other selected tables. Usually, the rowids for this conversion method are collected by the already mentioned additional scan that starts automatically at the beginning of the conversion phase.

    Pre-collection of rowids removes the need for this additional scan, shortening the required downtime window and enabling the database to be closed for business for a shorter time. However, if any additional convertible rows are added to a table after the last scan that pre-collected rowids for the table has been performed, these rows will not be converted during the conversion phase and will become incorrectly encoded data after the migration. Therefore, use the pre-collection feature sparingly, only on large tables confirmed to have static contents. The option "Collect also rowids for Update Convertible Rows conversion method" has no effect during the first scan of a table as no conversion method has been assigned yet to the table.

    The last option, "Do not collect rowids", enables you to switch off rowid collection during the current scan. This may be useful if you just want to count the number of convertible cells and cells with convertibility issues, and you do not plan to use the filtering option of the Cleansing Editor on any of the scanned tables. The rowid collection cannot be switched off for a few special data dictionary tables in the SYS schema, which have their property "Rowids to Collect" fixed to "All to Convert".

    In the Scan Wizard that the DMU opens in the conversion phase for collection of rowids, the Scan Parameters page changes as shown in Figure 4-8. The check box "Collect rowids for Update Convertible Rows conversion method" is automatically selected and the additional parameter Maximum Age is present on the page. This parameter enables you to skip rescanning of tables for which rowids have already been pre-collected in the recent Maximum Age hours. It also enables you to discard pre-collected rowids that you consider too old.

    Figure 4-8 Scan Wizard - Set Parameters: Rowid Collection

    Description of Figure 4-8 follows
    Description of "Figure 4-8 Scan Wizard - Set Parameters: Rowid Collection"

    See "Converting the Database" for more information about the conversion process.

    Click Next to accept the scan parameters and open the Object Selection page.

  4. For the first scan, you usually select all tables in the database by opening the Scan Wizard from the Migration menu. For subsequent scans, you can select a subset of database objects for which you want the scan results to be refreshed, for example, because they have been invalidated by cleansing actions, or because you want recent DML activity on the objects to be analyzed in the context of possibly introduced conversion issues.

    If you want only invalidated scan results to be recalculated, you can select all columns in the database, by selecting the database node, and then click Exclude Scanned. When this option is selected, the DMU automatically deselects all columns that already have valid scan results. Now, you can manually select additional tables and columns for which you want existing scan results to be refreshed.

    If the "Collect rowids for Update Convertible Rows conversion method" option is selected, the Exclude Scanned option does not exclude tables that do not have required rowids collected by previous scans.

    Oracle strongly recommends that you refresh all scan results in the database before you start the conversion, preferably in the downtime window, when no new data can be added to the database. This ensures that all data requiring conversion is accounted for.

    Figure 4-9 Scan Wizard - Select Scan Objects

    Description of Figure 4-9 follows
    Description of "Figure 4-9 Scan Wizard - Select Scan Objects"

    Click Next to accept the object selection and open the Scan Details page. It might take a while to display the page, because, first, the wizard has to prepare a scanning plan for all the selected objects.

  5. On the Scan Details page, shown in Figure 4-10, you can verify that all objects you wanted to be scanned are included. You can also set the rowid collection level in the drop-down lists that can be opened by selecting fields in the Rowids to Collect column. The choices for the rowid collection level are None (no collection is performed), All to Convert (collects rowids for all data that is not classified as needing no conversion), and With Issues (collects rowids for data with conversion issues only). Finding data that requires cleansing using the filtering and search features of the Cleansing Editor will be faster if rowids for problematic data cells are collected during scanning. See Using the DMU to Cleanse Data for more information about the Cleansing Editor.

    The rowid collection level set in the Scan Wizard is valid only for the current scan. It does not modify the table property "Rowids to Collect" and it does not persist across invocations of the Scan Wizard.

    The DMU automatically handles splitting of large tables into chunks for parallel scanning by multiple scanning processes. However, if necessary, you can prevent a table from being split by deselecting the corresponding check box in the Split column.

    Clicking Finish will start the scan.

    Figure 4-10 Scan Wizard - Scan Details

    Description of Figure 4-10 follows
    Description of "Figure 4-10 Scan Wizard - Scan Details"

4.3.3 Monitoring the Progress of a Scan

You can use the Scan Progress tab to monitor the progress of the scanning task.

When the scanning starts, the DMU automatically opens the Scan Progress tab. The tab is shown in Figure 4-11.

The Scan Progress tab contains a grid that displays scan information for all tables in the scanned set. The columns of the tab grid are as follows:

  • Name

    The column shows the name of the table that is described by the corresponding row of the grid. The icon to the left of the name shows the scan status. A green check mark shows that the table has already been scanned. A green sprocket-like icon marks tables that are currently being scanned. A clock marks tables that are waiting to be scanned.

    If a table is split into multiple chunks, you can click the plus sign to the left of the status icon to show information for all chunks to which the table has been split.

    Note:

    The meaning of the green check mark icon differs between the Scan Progress tab and the Navigator pane/Scan Report tab (see "Overview of the Database Scan Report"). On the Scan Progress tab, the icon shows that a table has been successfully scanned, but without indicating if it contains any problematic data. In the Navigator pane or on a Scan Report tab, the icon means that a table, a schema or database has been scanned and also that its contents does not have any issues that might prevent successful conversion to Unicode.

  • Split

    The number shown is the number of chunks to which the corresponding table has been split. The column is empty in grid rows showing chunk information.

  • Size

    The column shows the size of the corresponding table or table chunk. The topmost row of the grid shows the added size of all tables in the scanned set.

  • Min ROWID

    For chunks, the column shows the rowid of the first row in the chunk. It is empty for tables.

  • Max ROWID

    For chunks, the column shows the rowid of the last row in the chunk. It is empty for tables.

  • Thread ID

    The number shown in this column is the ID of a particular thread that is scanning or that has scanned the corresponding table or table chunk.

  • Start Time

    The column shows the time when scanning of the corresponding table or table chunk started. If a table has been split, its row shows the earliest (minimum) start time for all its chunks. The topmost row of the grid shows the start time of the whole scanning task.

  • End Time

    The column shows the time when the scanning of the corresponding table or table chunk finished. If a table has been split, its row shows the latest (maximum) end time for all its chunks. The topmost row of the grid shows the end time of the whole scanning task.

  • Progress

    The column displays a progress bar and a percentage that indicate how much of the corresponding table or table chunk has already been scanned. The topmost row of the grid shows the overall progress of the scanning task.

    If a database error is encountered during the scanning of a table, the grid row corresponding to the table displays a prefix of the reported error message in this column. You can click the prefix to open a dialog box with the full message text.

The DMU scrolls down the tab grid automatically to bring new grid rows into view when the corresponding tables are assigned to scanning threads. You can suspend the automatic scrolling by clicking the Scroll Lock icon in the top right-hand corner of the Scan Progress tab. This enables you to monitor the overall progress of the scanning by looking at the progress indicators in the topmost row of the grid. To resume automatic scrolling, click the Scroll Lock icon again.

You can suspend the scanning process by clicking Stop on the bottom of the Scan Progress tab. The button will change to Continue. Click Continue to resume scanning. See "Database Scan Report: Stopping the Scan" for more information.

After the scan finishes, the DMU shows an information dialog box. You can now analyze the scan results on the Database Scan Report tab.

4.3.4 Viewing the Database Scan Report

After the scan completes, view the most current scan results on the Scanning subtabs of the Properties tabs, or more conveniently on the Database Scan Report tab.

Depending on what the scan results are, you should follow one of the migration scenarios described in "Cleansing Scenario 1: A Database with No Issues", "Cleansing Scenario 2: Cleansing Expansion Issues", or "Cleansing Scenario 3: Cleansing Invalid Representation Issues".

To view the Database Scan Report:

  1. Open the Database Scan Report tab with scan results for the whole database by selecting Database Scan Report from the Migration menu.

    You can open a Database Scan Report tab for tables in a single schema, for a single table, or for a single column, by selecting Scan Report from the context menu of the corresponding schema, table, or column node in the Navigator pane.

    A Database Scan Report tab for the whole database is shown in Figure 4-12.

4.3.5 Overview of the Database Scan Report

The Database Scan Report tab contains a toolbar on the top and a result grid underneath. You can use three methods to locate results of interest in the Report, and also export the Report.

Figure 4-12 Database Scan Report

Description of Figure 4-12 follows
Description of "Figure 4-12 Database Scan Report"
4.3.5.1 Database Scan Report: Result Grid

The result grid is a user interface item, in form of a tree table, that displays scan results and, optionally, other properties of database objects.

The first column of the grid contains a database object tree that has the same appearance as the tree on the Navigator pane (see "Introduction to the DMU User Interface"). The root of the tree is the database, schema, table, or column for which the Database Scan Report has been opened. Each node of the tree is associated with a row of the grid. This row shows various properties of the database object corresponding to the node.

The status icon to the left of the name of the node shows the convertibility status for the database object described by the node. The meaning of icons is defined in Table 2-1.

A node can be expanded and collapsed to show or hide results of its child nodes by clicking the plus icon to the left of the node. You can expand all descendants of a node at once by selecting the row containing the node and clicking Expand All, the second icon on the toolbar (see Table 2-1). You can collapse all descendants by selecting the node and clicking Collapse All, the third icon on the toolbar.

When the Database Scan Report tab is opened, the columns displayed in the grid by default are: Need No Change (Scheduled), Need Conversion (Scheduled), Invalid Binary Representation (Scheduled), Over Column Limit (Scheduled), and Over Type Limit (Scheduled). The values shown in these columns are equal to scan results shown under the heading "Including Effects of Scheduled Cleansing" on the Scanning subtab of the Properties tab for the corresponding node. Those scan results are described in Viewing and Setting Object Properties in the DMU.

By clicking on Customize Report, the fourth icon on the toolbar, you can open the Customize Scan Report dialog box. In this dialog box, you can select further property columns to display along the default ones. You can also hide the default columns. The available columns correspond to properties described in Viewing and Setting Object Properties in the DMU. The columns Need No Change, Need Conversion, Invalid Binary Representation, Over Column Limit, and Over Type Limit, without the suffix "(Scheduled)", correspond to scan results presented on Scanning subtabs under the heading "Current Data".

If a property displayed in the Database Scan Report grid column is valid only for certain types of nodes, the grid column displays no text in rows corresponding to other types of nodes. For example, the Conversion Method property is valid only for tables. Grid rows for the database, schemas, and columns do not show anything in the Conversion Method column, if it is added to the Database Scan Report tab.

If you right-click a row in the Database Scan Report grid, you can select Scan, Cleansing Editor, or Properties from the context menu. The Scan menu item opens the Scan Wizard in which only the object described by the current grid row is selected for scanning. This way you can quickly instruct the DMU to refresh scan results for this object. Cleansing Editor opens the Cleansing Editor tab for the selected table or table column. See Using the DMU to Cleanse Data for the description of the Cleansing Editor tab. Clicking Properties opens the Properties tab of the object described by the selected grid row (see Viewing and Setting Object Properties in the DMU).

The context menu for data dictionary tables contains the Data Viewer instead of the Cleansing Editor. The Data Viewer tab, opened by clicking Data Viewer, is a read-only version of the Cleansing Editor.

4.3.5.2 Database Scan Report: Navigating by Status Icons

If you expand Database Scan Report nodes marked with a yellow triangle, you can locate columns that contain data with convertibility issues.

The triangle icon signals that columns among node descendants have convertibility issues.

  1. Expand a node marked with a triangle icon.

  2. Search its children to locate child nodes marked with the same icon.

  3. Repeat the preceding steps until you reach the column nodes.

Column nodes marked with a yellow triangle describe columns that must be cleansed. Nonzero counts in the grid columns Invalid Binary Representation (Scheduled), Over Column Limit (Scheduled), and Over Type Limit (Scheduled) show how many issues of each type have been found in the given database column. Counters are displayed as links that you can click to directly open the Cleansing Editor or the Data Viewer tab to take a closer look at the issues.

4.3.5.3 Database Scan Report: Filtering

The filtering feature of the Database Scan Report tab performs useful actions such as displaying only objects with valid scan results and displaying objects without valid scan results.

You can open the drop-down filter list on the toolbar of the tab, as shown in Figure 4-13, and select the type of results to include in the report.

Figure 4-13 Database Scan Report: Filtering

Description of Figure 4-13 follows
Description of "Figure 4-13 Database Scan Report: Filtering"

The available filters are:

  • All

    Switches the filtering off. All objects are displayed.

  • Scanned

    Displays only objects with valid scan results.

  • Scan Failed

    Displays objects that could not be scanned, because a database error was reported.

  • Not Scanned

    Displays objects without valid scan results. An object might have no valid scan results because it has never been scanned, its scan results have been invalidated by a cleansing action or a DDL, or the last scan of the object failed.

  • Requiring No Conversion

    Displays objects that have valid scan results and contain only changeless data that requires no conversion.

  • Requiring Conversion, Without Issues

    Displays objects that have valid scan results and contain a mixture of changeless data that requires no conversion and convertible data that requires conversion. No issues are expected during conversion of these objects.

  • Blocking Conversion

    Displays objects with unresolved convertibility issues that must be acted upon before the database can be successfully converted.

  • With Invalid Representation

    Displays objects that have valid scan results and contain some data with invalid binary representation, that is, with character codes that are not valid in the declared column character set.

  • Exceed Dataytpe Limit

    Displays objects that have valid scan results and contain some data that exceeds its data type limit after conversion.

  • Exceed Column Limit

    Displays objects that have valid scan results and contain some data that exceeds its column limit after conversion.

  • With Length Issues

    Displays objects that have valid scan results and contain some data that exceeds its column limit or its data type limit after conversion.

  • Not Changeless

    Displays objects that have valid scan results and contain some data that will require conversion. The data may or may not have convertibility issues. That is, it may belong to any of the categories: "Need Conversion", "Invalid Representation", "Over Column Limit", or "Over Type Limit".

  • With Some Issues

    Displays objects that have valid scan results and contain data with any of the possible problems for which the DMU searches. For data dictionary schemas, this includes convertible data in columns that the DMU cannot convert.

After a filter is selected, all nodes and associated grid rows for database columns whose scan results do not fulfill the filtering condition are hidden. If all children of a node are hidden, the node is hidden as well. The exception is the root node of the report, which is never hidden.

Filtering is useless and thus disabled in Database Scan Report tabs opened for a single database column.

You can also rescan only the filtered objects in the scan report by selecting the first icon of the toolbar, which is the Rescan Filtered Objects button. As an example, if the the "Exceed Column Limit" filter is set in the scan report, then the Rescan Filtered Objects button will select all tables with "Over Column Limit" issues for the rescan.

4.3.5.4 Database Scan Report: Searching

The search feature in the Database Scan Report tab makes analyzing scan results easier in large scan reports.

Initiate a search by clicking one of the following toolbar buttons:

  • Find

    The fifth button on the toolbar opens a dialog box that enables you specify the search criteria for objects.

  • Find Previous

    The sixth button on the toolbar highlights the previous instance of the originally searched item.

  • Find Next

    The seventh button on the toolbar highlights the next instance of the originally searched item.

Selecting Find displays a dialog box such as that shown in Figure 4-14.

Figure 4-14 Database Scan Report: Searching

Description of Figure 4-14 follows
Description of "Figure 4-14 Database Scan Report: Searching"

You can specify the following search criteria in the dialog box to describe objects that you want located:

  • Name

    Specify a string that should be contained in the name of the object (case-insensitive).

  • Scan Status

    Specify the scan status of the object.

  • Object Type

    Specify if you want to search among schemas, tables, or columns. Only one type can be selected.

  • Include effects of scheduled cleansing

    Select this option if the results for which to search should include effects of the scheduled cleansing action. If the option is selected, the compared results are those displayed in grid columns with the phrase "(Scheduled)" in heading.

  • Requiring No Conversion

    Select the comparison operator and the value to compare with the Need No Change results.

  • Requiring Conversion

    Select the comparison operator and the value to compare with the Need Conversion results.

  • With Invalid Representation

    Select the comparison operator and the value to compare with the Invalid Binary Representation results.

  • Exceeding Column Limit

    Select the comparison operator and the value to compare with the Over Column Limit results.

  • Exceeding Data Type Limit

    Select the comparison operator and the value to compare with the Over Type Limit results.

After you select OK, the grid row for the first schema, table, or column, as selected in Object Type, that fulfills all the specified criteria is highlighted in the scan report. You can select the Find Previous or the Find Next toolbar button to highlight the previous or the next row of the same type that fulfills the criteria.

4.3.5.5 Database Scan Report: Exporting to HTML

To save the scan results for future reference, export them to an HTML file.

Select Export as HTML, the last button on the toolbar. This will open the Export Scan Report dialog box, as shown in Figure 4-15.

Specify the name and directory (folder) for the HTML file. By choosing one of the options in the Export Type group, specify if you want to export, correspondingly, any one of the following:

  • Only grid rows for columns and their parent objects that require conversion or have data with convertibility issues

  • The entire content of the scan report

  • Only those rows and columns of the result grid that are visible on the report tab (not hidden or collapsed)

Click Export to create the HTML report.

4.3.5.6 Database Scan Report: Stopping the Scan

Clicking Stop ends the scan and generates a warning. Select Yes to end immediately, No to stop after the current process finishes, or Cancel to resume scanning.

4.3.6 Generating the Problem Data Report

A Problem Data report shows details of objects that contain data with convertibility issues. DMU can generate a Problem Data Report as an .xls spreadsheet.

To generate the Problem Data report:

  1. From the Migration menu, select Problem Data Report.
    The Welcome page of Problem Data Report Wizard is displayed.
  2. Click Next.
    The Select Problem Data page is displayed showing all the database objects that have convertibility issues.
  3. Select the database objects whose details you want to see in the Problem Data report.
  4. Click Next. The Generate Report page is displayed.
  5. Enter the values for the following fields on the Generate Report page:
    • Preview Data Length: Number of bytes of data that you want to see in the Problem Data report for the data cells having convertibility issues.

    • Report File Path: Name of the Problem Data report file along with its directory path. Problem Data report file has .xls extension.

    Note:

    The default Problem Data report file name is in the format ProblemDataReport_ProjectName_yyyyMMddHHmmss.xls. The default directory path is the value specified for the Log Directory field in the Preference panel. You can either use the default values provided by DMU or specify any other values for Problem Data report file name and its directory path.

  6. Click Finish to generate the Problem Data report.

The following figure shows a sample Problem Data report.

Figure 4-17 Sample Problem Data Report

Description of Figure 4-17 follows
Description of "Figure 4-17 Sample Problem Data Report"

Note:

  • For the invalid representation and over size limit data, the problem characters are highlighted in the Cell Data and Hexadecimal Value columns in the Problem Data report.

  • DMU does not support converting ANYDATA or ANYDATASET to Unicode format and considers it as problem data. This type of data is shown in the Unsupported Convertible Data tab.

4.3.7 Scanning the PL/SQL Code

After scanning the database using the Scan Wizard, you can use the PL/SQL Code Scan Wizard to scan the PL/SQL code in your application.

4.3.7.1 About PL/SQL Code Scans

The PL/SQL Code Scan Wizard can identify several conversion-related issues.

Specifically, scanning can identify any of the following types of Unicode conversion related issues or coding patterns:

  • Variable length needs expansion due to planned column length expansion

  • Variable length shorter than the column length

  • Variable length shorter than the column data

  • Variable length semantics different from the column length semantics

  • SQL function operating on character data not in the database character set

  • Non-ASCII identifier name exceeding maximum length

After the PL/SQL code has been scanned, you can view the scan summary by generating the following two reports:

  • PL/SQL Code Scan Report

  • PL/SQL Problem Code Report

Note:

The PL/SQL Code Scan Wizard can be used only in the Migration mode before doing database conversion. The PL/SQL Code Scan Wizard is disabled in the Validation mode.

4.3.7.2 Prerequisites for Scanning the PL/SQL Code

Before scanning application code using the PL/SQL Code Scan Wizard, you must meet several prerequisites, including the database version, repository availability, and so on.

The prerequisites are as follows:

  • Oracle database version is 12.2 or later.

  • The DMU repository is installed in the database in the Migration mode.

  • The DMU is connected to the database.

  • The PL/Scope identifier data for the following PL/SQL objects is already collected, in the following order:

    Note:

    PL/Scope is a compiler-driven tool that collects data about identifiers in a PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment), and the location of each usage in the source code. See Oracle Database Development Guide for more information about PL/Scope.

    • STANDARD and DBMS_STANDARD packages

      The STANDARD and DBMS_STANDARD packages declare and define Oracle Database base types, such as VARCHAR2 and NUMBER. The PL/Scope identifier data for these packages is always available in Oracle Database 12.2 and later. So, you do not have to explicitly collect this data.

    • Oracle supplied packages containing Unicode migration unsafe procedures and functions

      The following Oracle supplied packages contain Unicode migration unsafe procedures and functions – UTL_RAW, UTL_I18N, and UTL_FILE.

      The DMU automatically collects this data when you use PL/SQL Code Scan. So, you do not have to explicitly collect this data.

    • Your application’s PL/SQL code

      You must explicitly collect the PL/Scope identifier data for your application’s PL/SQL code. To achieve this, set the PL/SQL compilation parameter PLSCOPE_SETTINGS to IDENTIFIERS:ALL as shown below, and then recompile your PL/SQL code.

      SQL> ALTER SYSTEM SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
4.3.7.3 Scanning the PL/SQL Code with the PL/SQL Code Scan Wizard

You can scan the PL/SQL code in your application using the PL/SQL Code Scan Wizard.

To scan the PL/SQL code using the PL/SQL Code Scan Wizard:

  1. In the Tools menu, click Scan PL/SQL Code.... The Welcome page is displayed.

    Figure 4-18 PL/SQL Code Scan Wizard - Welcome Page

    Description of Figure 4-18 follows
    Description of "Figure 4-18 PL/SQL Code Scan Wizard - Welcome Page"
  2. Click Next. The Select Scan Objects page is displayed.

    Figure 4-19 PL/SQL Code Scan Wizard - Select Scan Objects Page

    Description of Figure 4-19 follows
    Description of "Figure 4-19 PL/SQL Code Scan Wizard - Select Scan Objects Page"
  3. On the Object Selection page, select the PL/SQL objects that you want to scan.

  4. Click Next. The Scan Details page is displayed, showing the list of PL/SQL objects that will be scanned.

    Figure 4-20 PL/SQL Code Scan Wizard - Scan Details Page

    Description of Figure 4-20 follows
    Description of "Figure 4-20 PL/SQL Code Scan Wizard - Scan Details Page"
  5. Click Finish. The Scan Progress panel is displayed, showing the progress of the PL/SQL code scan operation.

    Figure 4-21 PL/SQL Code Scan Progress Panel

    Description of Figure 4-21 follows
    Description of "Figure 4-21 PL/SQL Code Scan Progress Panel"

    If you want to cancel the scan operation, click Abort.

  6. After the PL/SQL code scan is complete, the Code Scan completion dialog is displayed.

    Figure 4-22 Code Scan Completion Dialog

    Description of Figure 4-22 follows
    Description of "Figure 4-22 Code Scan Completion Dialog"
4.3.7.4 Viewing the PL/SQL Code Scan Report

After the PL/SQL code scan has completed, you can view the summary of the scan using the PL/SQL code scan report.

To view the PL/SQL code scan report:

  1. In the Tools menu, click PL/SQL Code Scan Report…. The PL/SQL code scan report is displayed.

    Figure 4-23 PL/SQL Code Scan Report

    Description of Figure 4-23 follows
    Description of "Figure 4-23 PL/SQL Code Scan Report"

    The PL/SQL code scan report shows the number of errors in PL/SQL code in a specific schema, package, procedure, and function.

    You can also export this report to an HTML file by clicking the export icon This is a screenshot of the export scan report icon. Its details are described in the current line. present in the top section of the report. When you click the export icon, the Export Code Scan Report dialog is displayed as shown below.

    Figure 4-24 Export Code Scan Report

    Description of Figure 4-24 follows
    Description of "Figure 4-24 Export Code Scan Report"

    Here, you can specify the file name and directory path to store the report. You can also specify the type of information that you would like to to include in the exported file, that is, to include all the scanned objects, or all the scanned objects having issues, or only the information that is currently shown in the report.

    Click Export to export the report to an HTML file.

  2. In the PL/SQL code scan report, when you click the error count number link for a specific PL/SQL object, the Code Viewer panel for the PL/SQL object is displayed.

    The Code Viewer panel highlights the code having issues in the PL/SQL object, and displays the descriptions of the issues below the code. By default, all types of issues are highlighted in the PL/SQL code.

    The foreground and background colors used for highlighting the PL/SQL code having issues can be configured using the Code Viewer page of the Preferences dialog box, as shown in the following figure. You can open the Preferences dialog box by selecting Preferences from the Tools menu.

    Figure 4-26 Code Viewer Preferences Page

    Description of Figure 4-26 follows
    Description of "Figure 4-26 Code Viewer Preferences Page"
  3. If you want a specific issue type to be highlighted in the PL/SQL code, select the required issue type from the Pattern Filter list that is present in the top section of the Code Viewer panel.

    Figure 4-27 Pattern Filter List

    Description of Figure 4-27 follows
    Description of "Figure 4-27 Pattern Filter List"
4.3.7.5 Viewing the PL/SQL Problem Code Report

After the code scan completes, use the Export PL/SQL Problem Code Wizard to generate an Excel report describing problems in the code.

To view the PL/SQL problem code report:

  1. In the Tools menu, click PL/SQL Problem Code Report….

    The Welcome page appears.

    Figure 4-28 Export PL/SQL Problem Code Wizard - Welcome Page

    Description of Figure 4-28 follows
    Description of "Figure 4-28 Export PL/SQL Problem Code Wizard - Welcome Page"
  2. Click Next.

    The Select Objects page is displayed, showing the PL/SQL objects having issues.

    Figure 4-29 Export PL/SQL Problem Code Wizard - Select Objects Page

    Description of Figure 4-29 follows
    Description of "Figure 4-29 Export PL/SQL Problem Code Wizard - Select Objects Page"
  3. On the Select Objects page, select the PL/SQL objects that you want to include in the problem report.

  4. Click Next.

    The Generate Report page is displayed.

    Figure 4-30 Export PL/SQL Problem Code Wizard - Generate Report Page

    Description of Figure 4-30 follows
    Description of "Figure 4-30 Export PL/SQL Problem Code Wizard - Generate Report Page"
  5. On the Generate Report page, you can specify file name and directory path for storing the report as an Excel file.

    The default PL/SQL problem code report file name is in the format ProblemCodeReport_ProjectName_yyyyMMddHHmmss.xls, where ProjectName is the database connection name that you have set in the DMU GUI, and yyyyMMddHHmmss is the current timestamp generated by combining the values of year, month, day, hour, minutes, and seconds. For example, ProblemCodeReport_Oracle_DB_20180818150947.xls. Here, Oracle_DB is the project name and 20180818150947 is the timestamp.

    The default directory for storing the report is the value specified for the Log Directory field on the Log page of the Preferences dialog box. The Preferences dialog box can be opened by selecting Preferences from the Tools menu.

    You can either use the default values or specify any other values for the PL/SQL problem code report file name and its directory path.

  6. Click Finish.

    A progress bar showing the progress of generating the PL/SQL problem code report is displayed. After the report has been generated successfully, a dialog box showing its name along with its directory path is displayed.

    Figure 4-31 Report Generation Complete Dialog

    Description of Figure 4-31 follows
    Description of "Figure 4-31 Report Generation Complete Dialog"

    A PL/SQL problem code report file is an Excel file containing multiple worksheets, where each worksheet contains PL/SQL code issues related to a specific type of issue or coding pattern as shown in the following sample report.

    Figure 4-32 Sample PL/SQL Problem Code Report Excel File

    Description of Figure 4-32 follows
    Description of "Figure 4-32 Sample PL/SQL Problem Code Report Excel File"

Note:

A problem code report file has a limit of 65536 rows. If a problem code report file crosses 65536 rows, then one more problem code report file is created with the additional rows added to it. When more than one problem code report files are created, incremental integer numbers are suffixed to their file names.

For example:

ProblemCodeReport_ProjectName_yyyyMMddHHmmss_1.xls
ProblemCodeReport_ProjectName_yyyyMMddHHmmss_2.xls
...
ProblemCodeReport_ProjectName_yyyyMMddHHmmss_n.xls

4.3.8 Scanning the Database with the DMU Command-Line Scanner (DMU-CLS)

The DMU Command-line Scanner (DMU-CLS) is a command-line tool for scanning the database. It provides the same functionality as the GUI scan.

The DMU-CLS scans a database to check its character data and provides information about the data readiness for its migration to a Unicode database or about any problem data that is currently present in the existing Unicode database. After scanning a database, the DMU-CLS can provide its analysis results in the form of a scan report and a problem data report.

4.3.8.1 Prerequisites for Using the DMU-CLS

Before you scan an Oracle database using DMU-CLS, you must meet prerequisites such as the database version, installation of required packages, and so on.

The following are the prerequisites:

  • The Oracle database version must be 11.2.0.4 or later.

  • The PL/SQL package SYS.DBMS_DUMA_INTERNAL must be installed in the Oracle database.

  • All the other DMU connections must be closed.

  • The database user using the DMU-CLS must have the SYSDBA role.

See Also:

"Managing a DMU Repository Using the DMU-CLS" to learn more about installing the DMU repository

4.3.8.2 Starting the DMU-CLS

Start the DMU-CLS using the dmucls command, specifying parameters on the command line or using a command file.

The command syntax depends on your platform::

  • On a Windows system:

    c:\> dmucls.bat parameters
  • On a Linux or UNIX system:

    $ sh dmucls.sh parameters

In the preceding examples, parameters are optional command-line parameters. You can also store the dmucls command parameters in a text file called the parameter file. You can then specify the parameter file name as the only parameter to the dmucls command instead of specifying all the individual parameters on a command-line.

The examples in this document are provided for Linux and UNIX systems.

Note:

  • You can see the list of all the dmucls command parameters and their descriptions using the following command:

    $ sh dmucls.sh -h

  • The dmucls command parameters and their values must be specified as key-value pairs on the command line. For example:

    $ sh dmucls.sh param1=value1 param2=value2

  • For the parameters having multiple values, the values must be separated by a comma (,). For example:

    $ sh dmucls.sh param1=value1,value2

    Any additional spaces between two comma-separated values are ignored.

  • The dmucls command parameters and their values are case-insensitive, except the values for the parameters CONNECTION, PARFILE, REPORT_NAME, and REPORT_DIR.

The following examples show some sample dmucls commands:

Example 1:

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=scan SCHEMAS=hr,oe REPORT_NAME=scan_report

Here, the DMU-CLS connects to the database having the database connection name db_connection_1 that is configured using the DMU GUI and scans the schemas hr and oe, and stores the scan result in a report file having the name scan_report.

Example 2:

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=INSTALL_MIGRATION_REPO REPO_TABLESPACE=sysaux REPO_TARGET_CHARSET=UTF8

Here, the DMU-CLS connects to the database having the connection name db_connection_name and installs the DMU repository having the target database character set of UTF8 in the tablespace sysaux.

Note:

If the value for REPO_TARGET_CHARSET is not specified, then the default value of AL32UTF8 is used as the target database character set for the DMU repository. Oracle recommends using AL32UTF8 as the target database character set.

See Also:

4.3.8.3 DMU-CLS Parameters

You can specify several parameters using dmucls, such as CONNECTION, OPERATION, and SCHEMAS.

The following table lists all the dmucls command parameters and their descriptions.

Note:

The dmucls command parameters and their values are case-insensitive, except the values for the parameters CONNECTION, PARFILE, REPORT_NAME, and REPORT_DIR.

Table 4-1 List of dmucls Command Parameters

Parameter Description

CONNECTION

Oracle database connection name already configured in the DMU using the DMU GUI. This is one of the three ways in which a database connection information can be specified to the dmucls command. The other two ways to specify a database connection information to the dmucls command are the Easy Connect format and the Connect Descriptor format, which can be specified only using the CONNECT_TO parameter.

Example:

CONNECTION=db_connection_1

In the preceding example, db_connection_1 is the Oracle database connection name that is already configured in the DMU using the DMU GUI.

Note:

  • If the Basic type of database connection is configured, the DMU-CLS will prompt you to enter the database administrator password, if it is not already stored in the DMU.

  • If the SSH type of database connection is configured and if key file is not used, then the DMU-CLS will prompt you to enter password for the SSH server user. If key file is used and a passphrase is set for the key, then the DMU-CLS will prompt you to enter the passphrase.

  • The Easy Connect format and the Connect Descriptor format cannot be used to create a database connection of SSH type. To create a database connection of SSH type, you must first configure it in the DMU GUI and then reference it in the CONNECTION parameter.

CONNECT_TO

Oracle database connection string in one of the following formats – Easy Connect format or Connect Descriptor format. Use one of these database connection string formats to connect to a database when the database connection name is not already configured in the DMU using the DMU GUI.

  • Easy Connect format:

    username@[//]host:port/service_name[ as sysdba]

  • Connect Descriptor format:

    username@(description=(address=(protocol=tcp)(host=host)(port=port))(connect_data=(service_name=service_name)))[ as sysdba]

Here,

username is the Oracle database user name having the SYSDBA role.

host is the Oracle database server DNS name or IP address.

port is the Oracle database server port number.

service_name is the service name registered with the database listener for the Oracle database server.

[//] is optional and is used for URL-style host name values.

[ as sysdba] is optional as DMU-CLS implicitly assumes that the database user connecting to the Oracle database server already has the SYSDBA role.

Examples:

  • Easy Connect format examples:

    CONNECT_TO=sys@server1.us.example.com:1521/orcl
    
    CONNECT_TO=sys@192.168.40.2:1521/orcl
    
    
  • Connect Descriptor format examples:

    CONNECT_TO=sys@(description=(address=(protocol=tcp)(host=server1.us.example.com)(port=1521))(connect_data=(service_name=sales.us.example.com)))
    
    CONNECT_TO=sys@(description=(address=(protocol=tcp)(host=192.168.40.2)(port=1521))(connect_data=(service_name=sales.us.example.com)))
    
    

Note:

  • The DMU-CLS will prompt you to enter the database administrator password each time you run the dmucls command with the CONNECT_TO parameter.

  • The Easy Connect format and the Connect Descriptor format cannot be used to create a database connection of SSH type. To create a database connection of SSH type, you must first configure it in the DMU GUI and then reference it in the CONNECTION parameter.

BUFFER

Memory size in bytes to allocate for each database server session for scanning a table.

You can specify any value from 1 byte to 9999999999 bytes (10 GB). The default value is the value stored in the DMU repository, if available; otherwise it is set to 1024000 bytes (1 MB).

Example:

BUFFER=5120000

PROCESSES

Number of concurrent scanning process threads to use for scanning the database. Note that each thread opens a separate database connection.

You can specify any value from 1 to 9999. The default value is the value stored in the DMU repository, if available; otherwise it is set to the number of CPUs present on the database server.

Example:

PROCESSES=4

OPERATION

The DMU-CLS operation to perform.

You can specify one of the following operations:

  • SCAN: Scan the database and generate a scan report by default. If you do not want to generate a scan report by default after completion of the database scan, then specify the additional parameter SCAN_REPORT_TYPE=NONE.

  • INSTALL_MIGRATION_REPO: Install the DMU repository in the Migration mode.

  • INSTALL_VALIDATION_REPO: Install the DMU repository in the Validation mode.

  • UPGRADE_REPO: Upgrade the DMU repository.

  • UNINSTALL_REPO: Uninstall the DMU repository.

  • SCAN_REPORT: Generate a scan report without scanning the database and by using the earlier database scan results that are already stored in the DMU repository.

  • PROBLEM_DATA_REPORT: Generate a problem data report.

The default operation is SCAN.

Example:

OPERATION=INSTALL_MIGRATION_REPO

SCHEMAS

Schemas to include in the:

  • database scan or

  • scan report or

  • problem data report

Example:

SCHEMAS=hr,oe

TABLES

Tables to include in the:

  • database scan or

  • scan report or

  • problem data report

Note that the tables must be specified in the form of a full identifier, that is, schema_name.table_name.

Example:

TABLES=hr.employees,hr.departments

COLUMNS

Columns to include in the:

  • database scan or

  • scan report or

  • problem data report

Note that the columns must be specified in the form of a full identifier, that is, schema_name.table_name.column_name.

Example:

COLUMNS=hr.employees.first_name,hr.employees.last_name

EXCLUDE

Tables to exclude from the:

  • database scan or

  • scan report

This parameter cannot be used for the problem data report.

Note that the tables must be specified in the form of a full identifier, that is, schema_name.table_name.

Example:

EXCLUDE=hr.locations,hr.countries

EXCLUDE_SCANNED

Option to exclude database objects with valid scan results and necessary log information. You can specify one of the following values:

  • TRUE: Exclude already scanned database objects for the current database scan.

  • FALSE: Include already scanned database objects for the current database scan.

The default value is FALSE.

Example:

EXCLUDE_SCANNED=TRUE

ROWIDS_COLLECTION

Rowid collection method for scanning tables. You can specify one of the following values:

  • TABLE_SETTING: Collect rowids only according to Rowids to Collect table property.

  • FOR_CONVERSION: Collect also rowids for Update only Convertible Rows conversion method.

    Note that you can specify this option only in the Migration scan mode and not in the Validation scan mode.

  • NONE: Do not collect rowids.

    Note that rowid collection cannot be disabled for a few special data dictionary tables in the SYS schema for which the Rowids to Collect property is always set to All to Convert.

The default value is TABLE_SETTING.

Example:

ROWIDS_COLLECTION=FOR_CONVERSION

See Also:

The information provided in step 3 of the section "Scanning the Database with the Scan Wizard" for additional details about the rowid collection methods.

REPORT_NAME

File name to use for:

  • a scan report (OPERATION=SCAN or OPERATION=SCAN_REPORT) or

  • a problem data report (OPERATION=PROBLEM_DATA_REPORT)

You need not specify any file extension for the report file name as DMU-CLS always uses the file extension of .html for a scan report file and .xls for a problem data report file by default.

If you do not specify this parameter, then the default report file name used is:

  • For a scan report:

    When the CONNECTION parameter is used to connect to the database, the default scan report file name format is:

    ScanReport_<db_connection_name>_<datetime>.html

    When the CONNECT_TO parameter is used to connect to the database, the default scan report file name format is:

    ScanReport_<db_name_identifier_in_db_service_name>_<datetime>.html

  • For a problem data report:

    When the CONNECTION parameter is used to connect to the database, the default problem data report file name format is:

    ProblemDataReport_<db_connection_name>_<datetime>.xls

    When the CONNECT_TO parameter is used to connect to the database, the default problem data report file name format is:

    ProblemDataReport_<db_name_identifier_in_db_service_name>_<datetime>.xls

Example:

REPORT_NAME=scan_report

See Also:

REPORT_DIR

Directory in which a scan report or a problem data report file needs to be stored. You can specify either an absolute directory path or a directory path that is relative to the directory from which the dmucls command is run.

If you do not specify this parameter, then the default directory used for storing a report file is the DMU log directory:

  • On a Windows system:

    %APPDATA%\DMU\log

  • On a Linux and a UNIX system:

    $HOME/.dmu/log

Example:

REPORT_DIR=/usr/dmu/reports

SCAN_REPORT_TYPE

Scan report type to use for generating a scan report. You can specify one of the following report types:

  • NONE: Do not generate a scan report by default after the database scan is completed.

  • ALL: Include all the scanned columns in the scan report, including the columns that do not need conversion.

  • NON_CHANGELESS: Include only those scanned columns in the scan report that need conversion.

The default scan report type is NON_CHANGELESS.

Example:

SCAN_REPORT_TYPE=ALL

PROBLEM_DATA_LENGTH

Number of bytes of data that you want to see in a problem data report for the data having convertibility issues.

You can specify any value from 1 byte to 16383 bytes (16 KB). The default value is 30 bytes.

Example:

PROBLEM_DATA_LENGTH=100

REPO_TABLESPACE

Tablespace in which the DMU repository needs to be installed in either the Migration mode or the Validation mode.

Example:

REPO_TABLESPACE=SYSAUX

REPO_TARGET_CHARSET

Unicode character set to use for the DMU repository, that is, for the target database. You can specify the value of either AL32UTF8 or UTF8. The default value is AL32UTF8.

Example:

REPO_TARGET_CHARSET=AL32UTF8

ASSUMED_CHARSET

Assumed character set of the database to be scanned, that is, character set of the source database.

If this parameter is not specified, then the assumed character set value stored in the DMU repository is used. If the DMU repository also does not contain the assumed character set value, then the value of the NLS_CHARACTERSET parameter present in the NLS_DATABASE_PARAMETERS table of the source database is used.

If the value specified by you for the ASSUMED_CHARSET parameter is different from the one that is already stored in the DMU repository, the DMU-CLS replaces the existing assumed character set value stored in the DMU repository with the new value specified by you and invalidates the existing scan results present in the DMU repository.

Note:

  • If the database to be scanned is configured to use the correct character set, then the assumed character set should be the same as the database character set. However, in a pass-through configuration, as described in "Invalid Binary Storage Representation of Data", all or almost all character columns might store data in the character set of client workstations, which might be different from the declared database character set. In such a case, after you identify the real character set of the database contents, specify it as the value for the ASSUMED_CHARSET parameter to let the DMU know how to correctly interpret the data.

  • You should specify this parameter only when you exactly know the difference between the assumed database character set and the actual character set of the data stored in the database.

  • You can reset the assumed character set value by setting it to NONE.

Example:

ASSUMED_CHARSET=WE8ISO8859P1

See Also:

"Setting the Assumed Character Set"

PARFILE

Parameter file to use. Specify this parameter when you want to use the parameters and their values stored in a parameter file instead of entering them on a command-line. You can specify either an absolute file path or a file path that is relative to the directory from which the dmucls command is run.

Example:

PARFILE=/usr/dmu/params/scan_params.txt

See Also:

"DMU-CLS Parameter File"

4.3.8.3.1 Specifying Special Characters in the dmucls Command on the Command-line

Most operating systems and command-line interpreters or shells do not interpret special characters. Multiple methods exist for specifying a special character in a parameter value of a dmucls command.

For example, in C shell and Bourne shell of UNIX, the character ? is used for matching any single character and the character i is used for matching any number of characters. A whitespace is considered as a special character in most of the operating systems.

If you specify a special character in a parameter value of a dmucls command as it is, then DMU-CLS displays an error. There are multiple methods of specifying a special character in a parameter value of a dmucls command, so that the DMU-CLS interprets it literally by ignoring the special meaning given to that character in the operating system. In general, the parameter value containing a special character is enclosed in double quotes or single quotes, or the special character is prefixed with an escape character, such as a backslash (\) in a UNIX or a Linux system, or a double quote in a Windows system.

The following are various methods of specifying special characters in parameter values in a dmucls command on the command-line.

  • If a database object name (a schema name, a table name, or a column name) contains a special character, such as a whitespace, a dot (.), or a comma (,), then one of the following methods can be used to specify it on the command-line:

    • the database object name must be enclosed in escaped double quotes and an appropriate escape character must be prefixed to the special character.

      For example:

      • On a UNIX or a Linux system

        TABLES=hr.\"temp\ table\"
      • On a Windows system

        TABLES=hr.\"temp" table\"
    • (only for a UNIX or a Linux system) the whole parameter value must be enclosed in single quotes with the database object name enclosed in double quotes.

      For example:

      TABLES='hr."temp table"'
    • (only for a Windows system) the database object name must be enclosed in escaped double quotes, which additionally must be enclosed in double quotes.

      For example:

      TABLES=hr."""temp table"""
      TABLES=hr."\"temp table\""
      
    • (only for a Windows system) the database object name must be enclosed in escaped double quotes, and additionally the whole parameter value must be enclosed in double quotes.

      For example:

      TABLES="hr.""temp table"""
      TABLES="hr.\"temp table\""
  • If a parameter value contains a special character for the parameters CONNECTION, PARFILE, REPORT_NAME, REPORT_DIR, or CONNECT_TO, then one of the following methods can be used to specify it on the command-line:

    • the parameter value must be enclosed in double quotes.

      For example:

      CONNECTION="db 122"
      CONNECT_TO="user name@host:port/service"
      REPORT_NAME="scan report"
    • an escape character must be prefixed to the special character.

      For example:

      • On a UNIX or a Linux system

        CONNECTION=db\ 122
        CONNECT_TO=user\ name@host:port/service
        REPORT_NAME=scan\ report
      • On a Windows system

        CONNECTION=db" 122
        CONNECT_TO=user" name@host:port/service
        REPORT_NAME=scan" report
    • (only for a UNIX or a Linux system) the whole parameter value must be enclosed in single quotes.

      For example:

      CONNECTION='db 122'
      CONNECT_TO='user name@host:port/service'
      REPORT_NAME='scan report'
  • In the parameter value for CONNECT_TO, if the user name contains the at symbol (@), then you can use one of the following methods to specify it on the command-line:

    • The user name must be enclosed in escaped double quotes.

      For example:

      CONNECT_TO=\"user@name\"@host:port/service
    • The user name must be enclosed in double quotes and the whole parameter value must be enclosed in single quotes (only for a UNIX or a Linux system).

      For example:

      CONNECT_TO='"user@name"@host:port/service'

See Also:

"Specifying Special Characters in Parameter Values in a Parameter File" for the methods of specifying special characters in parameter values in a parameter file

4.3.8.4 DMU-CLS Scopes for Database Scan and Report Generation

The DMU-CLS supports different scopes for scanning a database as well as for generating reports, such as a scan report and a problem data report.

Table 4-2 DMU-CLS Scopes for Database Scan and Report Generation

Scope The dmucls Command Parameter

Database-level scope

  • For database scan: Scan a whole database.

  • For report generation: Generate a report containing data for the whole database.

This is the default scope.

None of these parameters is specified: SCHEMAS or TABLES or COLUMNS.

Note:

Additionally, you can specify the EXCLUDE parameter to exclude certain tables from a database scan or from a scan report. The EXCLUDE parameter cannot be used for a problem data report.

EXCLUDE=schema1.table1, schema2.table2, ...

Schema-level scope

  • For database scan: Scan the specified schemas only.

  • For report generation: Generate a report containing data for the specified schemas only.

SCHEMAS=schema1, schema2, ...

Note:

Additionally, you can specify the EXCLUDE parameter to exclude certain tables from a database scan or from a scan report. The EXCLUDE parameter cannot be used for a problem data report.

EXCLUDE=schema1.table1, schema2.table2, ...

Table-level scope

  • For database scan: Scan the specified tables only.

  • For report generation: Generate a report containing data for the specified tables only.

TABLES=schema1.table1, schema1.table2, ...

Column-level scope

  • For database scan: Scan the specified columns only.

  • For report generation: Generate a report containing data for the specified columns only.

COLUMNS=schema1.table1.column1, schema2.table2.column2, ...

Note:

You must specify only one scope in the dmucls command, otherwise the DMU-CLS reports an error.

4.3.8.5 DMU-CLS Scan Modes

The DMU-CLS automatically picks a scan mode based on the mode of installation of the DMU repository.

Table 4-3 DMU-CLS Scan Modes

Scan Mode Description

Migration scan mode

The Migration scan mode is always used for scanning a database when the DMU repository is installed in the Migration mode.

Validation scan mode

The Validation scan mode is always used for scanning a database when the DMU repository is installed in the Validation mode.

4.3.8.6 DMU-CLS Execution Statuses

The DMU-CLS returns a status code after execution.

Table 4-4 DMU-CLS Execution Statuses

Status Code Description

0

Operation successfully completed.

1

Operation failed.

2

In the Validation mode: Scan successfully completed. Invalid data found.

In the Migration mode: Scan successfully completed. Invalid, over column limit, or over datatype limit data found.

3

New DMU connection to the database is not allowed. Another DMU GUI process or DMU-CLS process is already connected to the database.

4

Failed to connect to the database.

4.3.8.7 DMU-CLS Operations Examples

This topic demonstrates typical use cases for DMU-CLS.

The following are the examples of various operations that can be performed using the DMU-CLS.

  • The following example shows how to scan a schema for collecting rowids using the Update Convertible Rows conversion method.

    $ sh dmucls.sh CONNECTION=db_connection_1 SCHEMAS=hr ROWIDS_COLLECTION=FOR_CONVERSION
  • The following example shows how to scan multiple tables present in different schemas.

    $ sh dmucls.sh CONNECTION=db_connection_1 TABLES=hr.employees,oe.orders
  • The following example shows how to scan all the tables in the HR schema, except tables employees and departments.

    $ sh dmucls.sh CONNECTION=db_connection_1 SCHEMAS=hr EXCLUDE=hr.employees,hr.departments
  • The following example shows how to scan multiple columns present in different tables and in different schemas.

    $ sh dmucls.sh CONNECTION=db_connection_1 COLUMNS=hr.employees.lastname,oe.orders.order_mode
4.3.8.8 DMU-CLS Parameter File

You can store all the command-line parameters of the dmucls command in a text file called the parameter file.

You can specify any name and extension for a parameter file. You can then specify this parameter file name as the only parameter to the dmucls command as shown below instead of specifying all the individual parameters on a command-line.

$ sh dmucls.sh PARFILE=scan_params.txt

In the preceding example, scan_params.txt is the DMU-CLS parameter file containing command-line parameters. You can specify either the absolute file path or the relative file path for a parameter file. When no file path is specified, the parameter file is assumed to be stored in the same directory where the dmucls command is being executed.

Note:

If a parameter file name or path contains a whitespace character, then the whole file path should be enclosed in double quotes. For example:

$ sh dmucls.sh PARFILE="/usr/dmu/params/scan params.txt"

The following example shows the contents of a sample parameter file:

CONNECTION=db_connection_1
SCHEMAS=hr,sales
OPERATION=scan
BUFFER=4096000
PROCESSES=2 

Specifying this parameter file as a parameter to the dmucls command is equivalent to:

$ sh dmucls.sh CONNECTION=db_connection_1 SCHEMAS=hr,sales OPERATION=scan BUFFER=4096000 PROCESSES=2

In a parameter file, each parameter and its value must be specified as a key-value pair in a single separate line. For multivalue parameters, the multiple values must be separated by a comma (,). Additional whitespaces between two comma-separated values are ignored. All parameter names and their values are case-insensitive, except the values for the parameters CONNECTION, PARFILE, REPORT_NAME, and REPORT_DIR.

You can add a comment anywhere in a parameter file as a single separate line with the pound character (#) in the beginning of the line. For example:

# Scan HR schema
SCHEMAS=hr
4.3.8.8.1 Specifying Special Characters in Parameter Values in a Parameter File

Specifying special characters in parameter values in a parameter file is similar to specifying special characters in parameter values in the dmucls command on the command line.

One difference is that in a parameter file, you need not use escape characters as prefixes for the double quotes and special characters. Also, there is no need to use the single quotes enclosing the parameter values on UNIX and Linux systems.

For example, in a parameter file, you can provide the following parameter value:

TABLES=hr."temp table"

Without using a parameter file, however, you must specify this parameter value on a command line as follows:

  • On a UNIX or Linux system:

    TABLES=hr.\"temp\ table\"
    
    or
    
    TABLES='hr."temp table"'
  • On a Windows system:

    tables="hr.""temp table"""
    
    or
    
    tables=hr."""temp table"""
    
    or
    
    tables="hr.\"temp table\""
    
    or
    
    tables=hr."\"temp table\""
    
    or
    
    tables=hr.\"temp" table\"

The following are some examples of specifying certain special characters in parameter values in a parameter file.

  • Specifying at symbol (@) in the user name for the CONNECT_TO parameter value:

    CONNECT_TO="user@name"@host:port/service
  • Specifying a whitespace in a parameter value:

    CONNECTION=db 122
    CONNECT_TO=user name@host:port/service
    REPORT_NAME=scan report

Note:

Oracle recommends that you use a parameter file for specifying DMU-CLS parameters rather than specifying them on a command-line, so as to reduce the complexity of handling special characters in parameter values.

See Also:

"Specifying Special Characters in the dmucls Command on the Command-line" for the methods of specifying special characters in parameter values in the dmucls command on the command-line

4.3.8.9 Managing a DMU Repository Using the DMU-CLS

Before using the DMU-CLS, you must install a DMU repository using either the DMU-CLS or the DMU GUI. This section describes the DMU-CLS technique.

The following are the dmucls command parameters for managing a DMU repository.

Table 4-5 The dmucls Command Parameters for Managing a DMU Repository

Parameter Description

OPERATION

The following are the OPERATION parameter values for managing a DMU repository:

  • INSTALL_MIGRATION_REPO: Install the DMU repository in the Migration mode.

  • INSTALL_VALIDATION_REPO: Install the DMU repository in the Validation mode.

  • UPGRADE_REPO: Upgrade the DMU repository.

  • UNINSTALL_REPO: Uninstall the DMU repository.

REPO_TABLESPACE

The tablespace in which the DMU repository needs to be installed in either the Migration mode or the Validation mode. You must specify this parameter when installing the DMU repository.

REPO_TARGET_CHARSET

The Unicode character set to use for the DMU repository, that is, for the target database. You must specify this parameter when installing the DMU repository.

The value for this parameter can be either AL32UTF8 or UTF8. The default value for this parameter is AL32UTF8.

Note:

Unlike the DMU GUI, the DMU-CLS does not support the functionality of recovering the DMU repository. If you use the DMU-CLS and connect to a DMU repository that indicates the need for recovery, the DMU-CLS does not perform the recovery task, but continues execution of the specified DMU-CLS operation.

Example 4-1 Installing DMU Repository Using the DMU-CLS

The following example shows how to install a DMU repository in the Migration mode having the Unicode character set of AL32UTF8.

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=INSTALL_MIGRATION_REPO REPO_TABLESPACE=sysaux REPO_TARGET_CHARSET=AL32UTF8

The following example shows how to upgrade a DMU repository.

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=UPGRADE_REPO

The following example shows how to uninstall a DMU repository.

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=UNINSTALL_REPO

See Also:

"Installing the DMU Repository" for information about installing a DMU repository using the DMU GUI

4.3.8.10 DMU-CLS Scan Report

The DMU-CLS generates a scan report by default after completing a database scan (OPERATION=SCAN). You can also generate a report without scanning a database by using the earlier scan result stored in the DMU repository (OPERATION=SCAN_REPORT).

The following are the dmucls command parameters for generating a scan report.

Table 4-6 The dmucls Command Parameters for Generating a Scan Report

Parameters Description

OPERATION

Specify one of the following operation parameter values.

  • SCAN: Generate a scan report by default after scanning a database. If you do not want to generate a scan report by default after completion of the database scan, then specify the additional parameter SCAN_REPORT_TYPE=NONE.

  • SCAN_REPORT: Generate a scan report without scanning a database and by using the earlier database scan result that is already stored in the DMU repository.

SCHEMAS or

TABLES or

COLUMNS

Specify one of the following scopes to determine what scan results to include in the scan report.

  • Database-level scope (none of these parameters specified: SCHEMA, TABLES or COLUMNS): Include the scan results of the whole database in the scan report.

  • Schema-level scope (parameter SCHEMAS): Include the scan results of the specified schemas only in the scan report.

  • Table-level scope (parameter TABLES): Include the scan results of the specified tables only in the scan report.

  • Column-level scope (parameter COLUMNS): Include the scan results of the specified columns only in the scan report.

EXCLUDE

Specify this parameter if you want to exclude certain tables from the scan report.

This parameter can be used only for the database-level scope and the schema-level scope of scan report generation.

REPORT_NAME and

REPORT_DIR

Specify the file name and the directory path for the scan report. These parameters are optional for generating a scan report.

Note:

  • You need not specify any file extension for a scan report as it is always set to .html by default.

  • If a scan report file name or its directory path contains spaces, then it must be enclosed in double quotes.

  • If you do not specify a file name for a scan report, then the default file name used is:

    • When the CONNECTION parameter is used to connect to the database:

      ScanReport_<db_connection_name>_<datetime>.html

      Example of default scan report file name where the Oracle database connection name is db_connection_1:

      ScanReport_db_connection_1_20170615012127.html
    • When the CONNECT_TO parameter is used to connect to the database:

      ScanReport_<db_name_identifier_in_db_service_name>_<datetime>.html

      Example of default scan report file name where the Oracle database service name is sales.us.example.com:

      ScanReport_sales_20170615012127.html
  • If you do not specify a directory for a scan report, then the default directory used is the DMU log directory:

    On a Windows system:

    %APPDATA%\DMU\log

    On a Linux and a UNIX system:

    $HOME/.dmu/log

SCAN_REPORT_TYPE

Specify one of the following scan report types.

  • NONE: Do not generate a scan report by default after the database scan is completed.

  • ALL: Include all the scanned columns in the scan report, including the columns that do not need conversion.

  • NON_CHANGELESS: Include only those scanned columns in the scan report that need conversion.

The default scan report type is NON_CHANGELESS.

The following example shows how to generate a scan report using the dmucls command.

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=SCAN TABLES=hr.employees,hr.departments REPORT_NAME=scan_report REPORT_DIR=/usr/dmu/reports SCAN_REPORT_TYPE=ALL
4.3.8.11 DMU-CLS Problem Data Report

The DMU-CLS generates a problem data report as an .xls spreadsheet. The report shows details of database objects with convertibility issues.

The following are the dmucls command parameters for generating a problem data report.

Table 4-7 The dmucls Command Parameters for Generating a Problem Data Report

Parameters Description

OPERATION

Specify the operation parameter value of PROBLEM_DATA_REPORT.

SCHEMAS or

TABLES or

COLUMNS

Specify one of the following scopes to determine what data having convertibility issues to include in the problem data report.

  • Database-level scope (none of these three parameters specified: SCHEMA, TABLES or COLUMNS): Include the data having convertibility issues in the whole database in the problem data report.

  • Schema-level scope (parameter SCHEMAS): Include the data having convertibility issues in the specified schemas only in the problem data report.

  • Table-level scope (parameter TABLES): Include the data having convertibility issues in the specified tables only in the problem data report.

  • Column-level scope (parameter COLUMNS): Include the data having convertibility issues in the specified columns only in the problem data report.

REPORT_NAME and

REPORT_DIR

Specify the file name and the directory path for the problem data report. These parameters are optional for generating a problem data report.

Note:

  • You should not specify any file extension for a problem data report as it is always set to .xls by default.

  • If a problem data report file name or its directory path contains spaces, then it must be enclosed in double quotes.

  • If a problem data report size crosses a certain limit, the DMU breaks the problem data report into multiple files having incremental numbers appended to their file names.

    For example, problem_data_report_1.xls, problem_data_report_2.xls, and so on.

  • If you do not specify a file name for a problem data report, then the default file name used is:

    • When the CONNECTION parameter is used to connect to the database:

      ProblemDataReport_<db_connection_name>_<datetime>.xls

      Example of a default problem data report file name where the Oracle database connection name is db_connection_1:

      ProblemDataReport_db_connection_1_20170615012127.xls
    • When the CONNECT_TO parameter is used to connect to the database:

      ProblemDataReport_<db_name_identifier_in_db_service_name>_<datetime>.xls

      Example of a default problem data report file name where the Oracle database service name is sales.us.example.com:

      ProblemDataReport_sales_20170615012127.xls
  • If you do not specify a directory for a problem data report, then the default directory used is the DMU GUI log directory:

    On a Windows system:

    %APPDATA%\DMU\log

    On a Linux and a UNIX system:

    $HOME/.dmu/log

PROBLEM_DATA_LENGTH

Specify the number of bytes of data that you want to see in the problem data report for the data having convertibility issues.

You can specify any value from 1 byte to 16383 bytes. The default value is 30 bytes.

The following example shows how to generate a problem data report using the dmucls command.

$ sh dmucls.sh CONNECTION=db_connection_1 OPERATION=PROBLEM_DATA_REPORT TABLES=hr.employees,hr.departments REPORT_NAME=problem_data_report REPORT_DIR=/usr/dmu/reports PROBLEM_DATA_LENGTH=200
4.3.8.12 DMU-CLS Error Messages

DMU provides error message reporting.

The following are the various error messages that can be displayed by the DMU-CLS.

DMU-60000 failed to parse parameter <parameter name>

Cause: The parameter was specified in an invalid format or value.
Action: Refer to the documentation for the correct syntax.

DMU-60001 missing parameter <parameter name>

Cause: The parameter with no default value was required at that time but not specified.
Action: Specify the required parameter either in parameter file or command line.

DMU-60002 multiple operation scopes requested: <operation scopes>

Cause: The user specified multiple operation scopes at once.
Action: Check the parameters and remove the redundant ones.

DMU-60003 unknown parameter name <parameter name>

Cause: The user specified parameter name is incorrect.
Action: Check the parameter name.

DMU-60004 failed to find parameter file

Cause: The parameter file was not found for the given path.
Action: Check the parameter PARFILE to use the correct path.

DMU-60005 failed to parse parameter file

Cause: The parameter file was written in an invalid format.
Action: Refer to the documentation for the correct syntax.

DMU-60006 multiple values not allowed for parameter <parameter name>

Cause: The parameter was specified for multiple times.
Action: Check the parameters and remove the redundant ones.

DMU-60007 failed to find DMU connection name <connection name>

Cause: The DMU connection name couldn’t be found.
Action: Check the connection name.

DMU-60008 multiple database connections specified

Cause: User specified the parameters CONNECTION and CONNECT_TO at the same time.
Action: Check the parameters and remove one of the connections.

DMU-60009 couldn’t perform operation <operation name> for repository status <status>

Cause: The current repository status is illegal for the specified operation.
Action: Check the repository status and parameters.

DMU-60010 invalid identifier for parameter <parameter name>

Cause: The specified database identifier is incorrect for the parameter.
Action: Check the parameters and database identifiers.

DMU-60011 couldn’t find database object <object name>

Cause: The database object could not be found in the database.
Action: Check the identifier of the database object.

DMU-60012 ‘FOR_CONVERSION’ is not a valid option for scanning in validation mode

Cause: User specified rowids_collection=for_conversion when scanning database in validation mode.
Action: Check the parameter and change its value.

DMU-60013 couldn’t find data with issues within the specified scope

Cause: There is no problem data (either not scanned or scanned but no problem data found) within the specified scope when trying to export Problem Data report.
Action: Check the identifier of the database object, or try scan first.

DMU-60014 unknown character set name <character set name>

Cause: The specified character set name is not a valid Oracle character set name.
Action: Check the character set name.

DMU-60015 <parameter value> is not a valid option for operation <operation>

Cause: The specified parameter value is invalid for the operation.
Action: Check the parameter values.

4.4 Cleansing the Data

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.

See Also:

4.5 Converting the Database

After all issues in the database have been resolved, you can convert your database character data.

The Migration Status tab must resemble what is shown in Figure 4-33.

Figure 4-33 Migration Status Tab - No Unresolved Issues

Description of Figure 4-33 follows
Description of "Figure 4-33 Migration Status Tab - No Unresolved Issues"

To convert the database:

  1. To begin the conversion process, select Convert Database from the Migration menu or the context menu of the database node in the Navigator pane.
    The DMU generates a conversion plan, which is a list of SQL statements to run in the conversion phase, and shows it on the Conversion Details tab in the client pane of the DMU window, as shown in Figure 4-34. The Conversion Steps area on the tab lists the successive steps of the conversion process. When you click a step in the list, the Step Details area shows the list of SQL statements to be executed as part of the step. For a more detailed explanation of this tab's capabilities, see "Figure 4-34".

    Figure 4-34 Conversion Details Tab

    Description of Figure 4-34 follows
    Description of "Figure 4-34 Conversion Details Tab"
  2. Click Edit Database Conversion Parameters to display the Converting subtab of the Database Properties tab.
    See "Viewing and Setting Database Properties" for a description of parameters that you can set on this subtab.
  3. Click Edit Table Conversion Plan to show the conversion plan details for application tables.
    The upper part of the displayed dialog box contains a list of all tables that will be processed during the conversion. When you select a table from the list, the lower part shows the SQL statements that will be used to process the table and its dependent objects. You can customize this list by setting the properties Conversion Method, Target Tablespace, Preserve LONG Position, and Parallel Execution. See "Viewing and Setting Table Properties" for a description of these parameters.

    Figure 4-35 Edit Table Conversion Plan Details: Example 1

    Description of Figure 4-35 follows
    Description of "Figure 4-35 Edit Table Conversion Plan Details: Example 1"
  4. Click the Convert button on the Conversion Details tab to start the conversion process.
    The DMU might display warnings about unexpected sessions present in the database and about the age of the oldest scan results. Review the warnings as described below. Accept the warning messages to continue.

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';

If the column V$SESSION.PROGRAM in a row returned by the above query contains the name of the Oracle Database executable, in an operating system dependent format, followed by "(Jnnn)", where nnn are three decimal digits, for example "ORACLE.EXE (J000)", then the user session described by the row has been created by the database server itself to execute a job submitted through the PL/SQL package DBMS_JOB or DBMS_SCHEDULER. You can query the Data Dictionary views DBA_JOBS_RUNNING and DBA_SCHEDULER_RUNNING_JOBS to identify jobs currently running in the database. Before continuing with conversion, ensure that all the jobs have finished their work and that their sessions have disappeared from V$SESSION.

To prevent further jobs from starting, issue the following SQL statement:

ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY

You can stop an already running DBMS_SCHEDULER job using the procedure DBMS_SCHEDULER.STOP_JOB. You cannot stop a DBMS_JOB job unless you shut down the database in immediate mode.

If you accept the warning message about unexpected sessions without disconnecting the sessions, the conversion will start but the step ALTER DATABASE CHARACTER SET TO target_character_set will fail reporting "ORA-12721: operation cannot execute when other sessions are active". At this moment, you can still disconnect the offending sessions and resume the conversion as described in the next section.

The warning message about the oldest scan result in the database lets you estimate the staleness of scan results. The DMU does not monitor DML changes to table data and it does not know if there are any data convertibility issues or any columns requiring conversion introduced into the database beyond those identified in the existing scan results. If the scan results are too old, you risk that some recently added data will remain unconverted or will be converted incorrectly. Oracle recommends that you perform a full database scan just before starting conversion, already in the migration downtime window, after all applications have been shut down and no new data comes into the database. If the database is very large and a full database scan takes long time, you may identify all large tables that are known to always contain only changeless data and deselect them in the Scan Wizard.

After you accept all warnings, if there are tables to be converted using the conversion method "Update only convertible rows" in the database, the DMU opens the Scan Wizard to rescan those tables. This is to ensure that the required rowid information for the tables is available and up-to-date. If you have just performed a full database scan in the downtime window or you have scanned all affected tables individually with the "All to Convert" rowid collection level, according to an time-optimized schedule, you may prefer to accept existing rowid information for the tables. Use the "Maximum age" parameter on the Scan Parameters page of the Scan Wizard to tell the DMU that it should accept available rowid information that is not older than the specified value in hours. After the scan has finished, the conversion process starts.

When the conversion starts, the Conversion Details tab changes its form and begins to display progress information for the conversion process. The link Edit Table Conversion Plan becomes View Table Conversion Progress. Clicking View Table Conversion Progress opens a table conversion progress dialog box, where you can view the detailed conversion progress of application tables.

When the conversion is completed, the DMU displays a confirmation message. If there is an error caused by any of the conversion SQL statements, it is signaled by a red icon next to the name of the step on the Conversion Steps list and next to the affected SQL statement in the Step Details list. Error details are displayed at the bottom of the Conversion Details tab, after you click the failed statement.

4.5.1 Conversion Details Tab

The Conversion Details tab works in two modes: planning and monitoring.

Immediately after you open the tab by selecting Convert Database, the tab works in the planning mode. The tab contains a list of SQL statements that the DMU plans to execute to convert the database to the target character set. The statements are grouped into steps listed in the Conversion Steps area. When you click a conversion step, the DMU displays the SQL statements associated with the step in the Step Details area. If a plus icon is shown to the left of a SQL statement, you can click the icon to expand the area to show the full text of the statement.

Figure 4-36 Conversion Details Tab: Planning Mode

Description of Figure 4-36 follows
Description of "Figure 4-36 Conversion Details Tab: Planning Mode"

If you click the link Edit Database Conversion Parameters, the Converting subtab of the Database Properties tab is opened in a dialog box. See "Column Properties: Converting" for a description of available parameters.

If you click the link Edit Table Conversion Plan, the DMU opens the Edit Table Conversion Plan Details dialog box, described in "Edit Table Conversion Plan Details Dialog".

When you are ready to start conversion, click Convert at the bottom of the tab. The Conversion Details tab switches to monitoring mode, as shown in Figure 4-37.

Figure 4-37 Conversion Details Tab: Monitoring Mode

Description of Figure 4-37 follows
Description of "Figure 4-37 Conversion Details Tab: Monitoring Mode"

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).

In the monitoring mode, the link Edit Table Conversion Plan changes to View Table Conversion Progress. The link opens the View Table Conversion Progression dialog box in the monitoring mode.

The green check mark marks the steps and the SQL statements that have already been successfully executed. The animated circle icon marks the currently executed step, while the sprocket-like icon marks the currently executed SQL statement.

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, as shown in Figure 4-38. The conversion process is suspended.

Figure 4-38 Conversion Errors Tab

Description of Figure 4-38 follows
Description of "Figure 4-38 Conversion Errors Tab"

If you click the failing statement, the reported database error messages are displayed in the Statement Execution Status field. You can tell the DMU to retry or skip the statement by selecting the corresponding option from the drop-down list in the Action column of the SQL Statement area. Use the Skip option very cautiously because leaving a statement out of the conversion process could leave the database in an inconsistent state. After you select how to proceed with the failing statement, click Continue to resume the conversion process.

When the conversion process finishes, the DMU displays a confirmation dialog box. Status icons are removed from the Navigator pane. You can now reinstall the repository in Unicode Validation Mode by selecting Configure DMU Repository from the Migration menu.

4.5.2 Edit Table Conversion Plan Details Dialog

The Edit Table Conversion Plan Details dialog box enables you to modify conversion parameters for individual tables.

The dialog box is shown in Figure 4-39. To modify a parameter for a table, click the corresponding cell in the upper grid of the dialog box. The cell can then be edited, if the conversion method of the table allows this. When you click a row of a table in the upper grid of the dialog box, the lower grid displays the SQL statements associated with conversion of the table.

Figure 4-39 Edit Table Conversion Plan Details: Example 2

Description of Figure 4-39 follows
Description of "Figure 4-39 Edit Table Conversion Plan Details: Example 2"

The conversion parameters that you can set for an individual table are:

  • Conversion Method

  • Target Tablespace

  • Preserve LONG Position

  • Parallel Execution

The first three parameters are described in "Table Properties: Converting". The Parallel Execution parameter specifies if parallel hints are added to the SQL statements for the given table to use the Parallel DDL and Parallel DML features of the database.

The search toolbar on the top of the dialog box enables you to quickly locate tables with a particular substring in their names. Enter the substring into the search box and press ENTER. If the substring is found in some names, the arrow icons are enabled to let you navigate between those names. If you click the marker pen icon, the matching tables are highlighted.

When you are ready, click OK to accept parameter changes and close the dialog box. Click Apply if you want to accept the changes but leave the dialog box open. When Apply or OK is clicked, the SQL statements for the changed tables are regenerated.

The Edit Table Conversion Plan Details dialog box can also be opened in monitoring mode during the conversion process by clicking the link View Table Conversion Progress. In the monitoring mode, the upper grid of the dialog box gets two additional columns: Elapsed Time and Progress, which show the time used to convert individual tables and progress bars reporting the percentage of the already processed table data. The lower grid also gets two additional columns: Elapsed Time and Action, which show the time used to execute individual SQL statements and an action to select if a statement fails, either Retry or Skip.

4.5.3 Stopping a Conversion

You can stop a conversion by clicking Stop. A dialogue box lets you stop the conversion process immediately (Yes), stop after the current running process finishes (No), or resume the conversion (Cancel).

Figure 4-40 Stopping a Conversion

Description of Figure 4-40 follows
Description of "Figure 4-40 Stopping a Conversion"

4.6 Validating Data as Unicode

The DMU can validate the contents of an existing AL32UTF8 or UTF8 database, which might have been converted in the past or initially created in the Unicode character set. In either case, you can use the DMU to check the current data.

You can use the DMU with databases already converted to AL32UTF8 or UTF8, or with new databases in these character sets, to verify that all data is indeed in the declared database character set. Because the processes to validate and migrate data differ, the DMU user interface differs for the two usage types as well.

The type of the user interface presented by the DMU is decided at the repository installation time. If the current database character set is neither AL32UTF8 nor UTF8, then the DMU activates the migration mode automatically. It activates the validation mode, if the current database character set is AL32UTF8. If the current database character set is UTF8, then the first page of the Repository Configuration Wizard (see "Installing the DMU Repository") gives you the choice between migration to AL32UTF8, which is the recommended Unicode character set, and activation of the validation mode. To activate the validation mode after you have migrated your database to Unicode, reinstall the DMU repository.

You can perform the validation of current character data by performing a scan, and see if any problems occur in the Database Scan report. The report shows which table columns contain sequences of bytes that do not form valid UTF-8 character codes. If any invalid data is reported, you can view the data and analyze where the problem is in the GUI. The DMU also offers you the means to correct the data in the Cleansing Editor. You can repeat the process of scanning and fixing the issues until no more invalid data is reported.

Fixing the data should usually be accompanied by fixing an application configuration problem that caused the invalid data to be stored in the database. Client configuration issues, usually a pass-through configuration one, are the most common reason for incorrectly encoded character data being stored in an AL32UTF8 or UTF8 database. You should periodically scan your AL32UTF8 and UTF8 databases in validation mode to discover any encoding issues as early as possible.

4.6.1 Introduction to the User Interface in Validation Mode

The DMU user interfaces in validation and migration modes are similar, but have some noteworthy differences.

The main differences are as follows:

  • The Migration Status tab becomes the Validation Status tab, shown in Figure 4-41.

  • The Migration menu becomes the Validation menu.

  • The Convert Database item in the Migration menu becomes the Convert Invalid Columns item in the Validation menu – the item invokes the procedure to convert columns from their assumed character set to the database character set.

  • The Database Scan Report shows only the database object name, scan results for current database contents and the Assumed Character Set property. Other report columns are not available.

    Figure 4-41 Validation Status Tab

    Description of Figure 4-41 follows
    Description of "Figure 4-41 Validation Status Tab"
  • Context menus in the Cleansing Editor tab do not have options to open Schedule Column Modification and Schedule Attribute Modification dialog boxes. Only the immediate operations Modify Column and Modify Attribute are possible.

  • The button Show Impact of Scheduled Cleansing is absent from the toolbar of the Cleansing Editor.

  • The Conversion Details tab for the Convert Invalid Columns process is very similar to the user interface for the conversion step of the migration mode, except that fewer conversion steps are present.

  • The Table Conversion Plan dialog box is simplified compared to the migration mode. It shows the tables and columns to be converted and the associated SQL statements but it does not allow you to set any table-level conversion parameters. Also, this version of the dialog box does not show conversion progress. You can monitor conversion progress only on the Conversion Details tab.

  • You can find any remaining issues preventing the conversion of the invalid columns under the "Status" field of step 3 on the Validation Status Panel. The reported issues need to be reviewed and resolved until the status indicates there are no more unresolved convertibility issues, at which point you can proceed to convert the columns tagged with assumed character sets to Unicode.

4.6.2 Validating Data

To validate data, you must scan the database, cleanse the data, and then convert incorrectly encoded data.

To validate data as Unicode:

  1. Install the repository

    The DMU repository is installed in the validation mode, as described previously. After installation, the repository can be used many times in repeated application of the validation process. The repository must be reinstalled only if you upgrade the database or the DMU.

  2. Scan the database

    The whole database or selected schemas, tables, or columns are scanned to look for illegal codes. Scanning of the whole database is highly recommended but you can choose to scan only a subset of objects if the database is too large to be scanned regularly without affecting production work and a preceding migration process revealed that only a small set of tables actually contains non-ASCII data.

    If the scan result shows only columns with data not requiring conversion, the contents of the database are correct and this part of the validation process is finished.

    The value of the property "Report U+FFFD as an invalid character" on the Scanning sub-tab of the Database Properties tab determines how the DMU interprets the Unicode default replacement character U+FFFD (the byte sequence 0xEF 0xBF 0xBD in AL32UTF8 and UTF8). If the property value is "Yes", the character is treated as invalid data. This is the default behavior, because the presence of this character in data usually indicates that the data is the result of character set conversion of some input that was not properly tagged with its real character set. If you use the character U+FFFD for some internal processing purposes and you do not want the DMU to report it as invalid, change the property value to "No".

    You initiate scanning and view the results as they were in the migration mode. See "Scanning the Database" for more details.

  3. Cleanse the data

    If the scan results show any columns with data having invalid binary representation, you must diagnose the source of this data and fix the associated application or configuration problem. See "Cleansing Scenario 3: Cleansing Invalid Representation Issues" for information about possible reasons for data having invalid binary representation.

    You have two options to cleanse invalid representation issues: migrating a column to a binary data type, if its contents is binary, or converting it in the database character set, if it is encoded in some other character set. The first step to convert column data is to tag it with its proper character set by setting the column's Assumed Character Set property. Refer to "Cleansing Scenario 3: Cleansing Invalid Representation Issues" and "Setting the Assumed Character Set" for more information.

    If none of the columns has its Assumed Character Set property changed, the scan results never show any data length issues. However, if you change this property for a column and rescan the column, cells exceeding column limit or data type limit might be reported. You must resolve the length issues before you proceed to the next step. You can lengthen columns or migrate them to another data type in immediate mode, the same way you do this in migration mode. See Using the DMU to Cleanse Data and in particular "Modifying Columns" for more information.

    All cleansing actions are immediate in validation mode. Setting the Assumed Character Set property is immediate in that the new value immediately affects scan results and data display, but the character set conversion is not performed at the time of setting. The character set conversion must be performed in a separate conversion step. After the Assumed Character Set of a column has been set properly, the column is reported in scanning as not having invalid binary representation issues. Go to the Validation Status tab to see if the conversion step is needed to convert any column from its assumed character set to the database character set. Before the conversion step is performed, the database is not yet clean.

    Cleansing actions invalidate scan results of affected tables. Rescan the tables to confirm that the cleansing actions were successful. If you have set the Assumed Character Set property of a column, rescanning will tell you if column data can be converted from the assumed character set to the database character set without truncation issues.

  4. Convert incorrectly encoded data

    If any column had its Assumed Character Set property set during cleansing, this step should be executed to convert content of the column from the assumed character set to the database character set. In this step, all columns marked with an Assumed Character Set different from the database character set are physically converted to the database character set. The conversion is performed with SQL UPDATE statements. You start the process by selecting Convert Invalid Columns from the Validation menu or from the context menu of the database node in the Navigator pane, and then by clicking Convert on the displayed Conversion Details tab.

    After the column content is converted, the Assumed Character Set property is reset to the database character set. You can rescan the converted columns to confirm that the conversion was successful.

Reapply the validation process regularly, but at least after each change to the configuration of your system, for example, upgrade of an application or the database, addition of a new application, or addition of new application users, especially from a new country. Let some time pass between the configuration change and the scanning so that there is high probability that some scanned data has been stored in the database already in the new configuration.

If the database character set is UTF8, the DMU repository is installed in the validation mode and if you decide to migrate the database to AL32UTF8, you must uninstall the repository and install it again, selecting the migration mode.

4.7 Creating a Diagnostic Package

To help resolve any problems that occurred while using the DMU, you can create a diagnostic package for subsequent analysis.

This package is a jar file that contains a project log file, a scan report, and an Oracle Data Pump dump file containing current and any archived DMU repository tables.

To create a diagnostic package:

  1. From the Tools menu, click Create Diagnostic Package.
    Figure 4-42 is displayed.

    Figure 4-42 Diagnostic Package Wizard

    Description of Figure 4-42 follows
    Description of "Figure 4-42 Diagnostic Package Wizard"
  2. Click Next.
    Figure 4-43 is displayed.

    Figure 4-43 Diagnostic Package Wizard - Package Content

    Description of Figure 4-43 follows
    Description of "Figure 4-43 Diagnostic Package Wizard - Package Content"
  3. Click Next. Figure 4-44 is displayed.

    Figure 4-44 Diagnostic Package Wizard - Package File

    Description of Figure 4-44 follows
    Description of "Figure 4-44 Diagnostic Package Wizard - Package File"
  4. After clicking Finish, the diagnostic jar file is created in the path chosen.