Data migration between different compatibility modes of OceanBase Database

This topic describes how to migrate data from the MySQL-compatible mode of OceanBase Database to the Oracle-compatible mode, and how to migrate data from the Oracle-compatible mode of OceanBase Database to the MySQL-compatible mode.

Prerequisites

• You have created the corresponding schema in the target OceanBase database.

• You have created clusters and tenants in both the source and target OceanBase databases. For more information, see Create an instance and Create a tenant.

• You have created a dedicated database user for data migration in OceanBase Database and granted it the relevant privileges. For more information, see Data migration user privileges.

Limitations

• Only users with the project roles of Project Owner, Project Admin, or Data Services Admin can create a data migration task.

• Limitations on the source database

  Do not perform DDL operations that modify database or table structures during schema migration or full migration. Otherwise, the data migration task may be interrupted.

• Currently, data migration supports OceanBase Database (including MySQL and Oracle compatibility modes) V2.x, V3.x, and V4.x.

• Data migration only supports migrating objects whose database, table, and column names are in ASCII and do not contain special characters (including .|"'`()=;/& and line breaks).

• When the target is a database, data migration does not support triggers (Triggers) on the target side. If triggers exist, data migration may fail.

Considerations

• If the source character set is UTF-8, it is recommended to use a character set compatible with the source at the target (for example, UTF-8, UTF-16, etc.) to avoid issues such as garbled characters at the target due to character set incompatibility.

• Clock desynchronization between nodes, or between the client terminal and the server, may cause inaccurate latency reporting during incremental synchronization.

  For example, if a clock is ahead of standard time, the reported latency may be negative. If a clock is behind standard time, it may cause a positive latency.

• If DDL synchronization is disabled, you must restart the data migration task if you modify a unique index at the target. Otherwise, data inconsistency may occur.

• Verify whether the migration precision for column types such as DECIMAL, FLOAT, and DATETIME meets your expectations. If the precision of the target field type is lower than that of the source field type, truncation may occur, leading to data inconsistency between the source and target.

• In a data migration task where the source is an OceanBase database and DDL synchronization is enabled, if a rename (RENAME) operation occurs on a source database table, it is recommended to restart the task to prevent data loss during incremental synchronization.

• If table objects differing only in case exist at the source or target, the data synchronization result may not meet expectations due to case-insensitivity at the source or target.

• Case sensitivity notes:

  • When synchronizing data from an OceanBase Database MySQL-compatible mode tenant to an OceanBase Database Oracle-compatible mode tenant, table names, field names, and other identifiers at the target are uniformly converted to uppercase.

  • When synchronizing data from an OceanBase Database Oracle-compatible mode tenant to an OceanBase Database MySQL-compatible mode tenant, table names, field names, and other identifiers at the target are uniformly converted to lowercase.

• If you configure only Incremental Synchronization when creating a data migration task, data migration requires that local incremental logs in the source database be retained for more than 48 hours.

  If you configure both Full Migration and Incremental Synchronization when creating a data migration task, data migration requires that local incremental logs in the source database be retained for at least 7 days. Otherwise, the data migration task may fail due to inability to obtain incremental logs, potentially leading to data inconsistency between the source and target.

• When migrating data from an OceanBase Database MySQL-compatible mode to an OceanBase Database Oracle-compatible mode, if a UNIQUE KEY column allows NULL values, data loss may occur. When multiple NULL values are synchronized from an OceanBase Database MySQL-compatible mode to an OceanBase Database Oracle-compatible mode, only the first NULL value is successfully inserted; subsequent NULL values are discarded due to UNIQUE KEY constraint violations.

• If you perform incremental synchronization with an OceanBase database as the source and the database table objects contain UDT columns, the incremental synchronization task may fail due to the lack of support for UDT columns.

Supported source and target instance types

Data type mappings

Data type mappings from OB_MySQL to OB_Oracle

Data type mappings from OB_Oracle to OB_MySQL

The data type mappings in the following table are based on OceanBase Database Oracle mode V4.2.0 as the reference version.

Procedure

1. Create a data migration task.

   

   1. Log in to the OceanBase Cloud console.

   2. In the left-side navigation pane, click Data Services > Data Migration.

   3. On the Data Migration page, click the Data Migration tab.

   4. On the Data Migration tab, click Create Task in the upper-right corner.

2. In the Edit Task Name text box, enter a custom name for the migration task.

   It is recommended to use a combination of Chinese characters, numbers, and letters. The name must not contain spaces and cannot exceed 64 characters in length.

3. On the Configure Source & Target page, configure the parameters.

   1. In the Source section, configure the parameters.

      If you need to reference an existing and saved data source, you can click Quick Fill next to Source and select the target data source from the drop-down list. After selection, the configurations for the source area will be automatically populated. If you want to save the current configuration as a new data source, click the Save icon in the upper-right corner of the Source section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page to view and manage data sources. This page provides unified management for different types of data sources. For more information, see the documentation in the Data Sources module.

      Parameter	Description
      Cloud Vendor	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud.
      Region	Select the region where the source database is located.
      Database Type	Select the source type as OceanBase MySQL Compatible or OceanBase Oracle Compatible based on your actual situation.
      Instance Type	Currently supports Cluster Instance (Transactional).
      Instance	The ID or name of the instance where the OceanBase database is located. You can view the ID or name of the target instance on the Instances page. Note When the cloud vendor is Alibaba Cloud, cross-account authorization for Alibaba Cloud root account instances is also supported. For details, see Alibaba Cloud Account Authorization.
      Tenant	The ID or name of the tenant in the OceanBase database. You can expand the target instance on the Instances page to view the ID or name of the target tenant under that instance.
      Account	The username of the OceanBase database user used for data migration.
      Password	The password of the database user.

   2. In the Target section, configure the parameters.

      If you need to reference an existing and saved data source, you can click Quick Fill next to Target and select the target data source from the drop-down list. After selection, the configurations for the target area will be automatically populated. If you want to save the current configuration as a new data source, click the Save icon in the upper-right corner of the Target section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page to view and manage data sources. This page provides unified management for different types of data sources. For more information, see the documentation in the Data Sources module.

      Parameter	Description
      Cloud Vendor	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud. You can choose the same cloud vendor as the source or perform data migration across vendors. Note The cross-cloud vendor data migration feature is currently not available by default. To use this feature, contact OceanBase Cloud Technical Support.
      Region	Select the region where the target database is located.
      Database Type	Select the target database type OceanBase MySQL Compatible or OceanBase Oracle Compatible based on the source database type.
      Instance Type	Currently supports Cluster Instance (Transactional).
      Instance	The ID or name of the instance where the OceanBase database is located. You can view the ID or name of the target instance on the Instances page.
      Tenant	The ID or name of the tenant in OceanBase Database. On the Instances page, you can expand the target instance to view the ID or name of the target tenant.
      Account	The username of the user in OceanBase Database used for data migration.
      Password	The password of the database user.

4. Click Test and Continue.

5. On the Select Type and Objects page, configure the parameters.

   1. In the Sync Topology section, select One-way Sync.

      Notice

      Data migration between different compatibility modes of OceanBase Database currently only supports one-way synchronization.

   2. In the Migration Type section, select the migration type for the current data migration task.

      The Migration Type includes Schema Migration, Full Migration, and Incremental Synchronization.

      Parameter	Description
      Schema Migration	Schema migration requires you to define the character set mapping relationship. Data migration only copies the data (schema) from the source database to the target database without affecting the source data (schema). Schema migration from OceanBase Database in Oracle compatible mode to OceanBase Database in MySQL compatible mode supports automatically creating databases that do not exist at the target.
      Full Migration	After a full migration task starts, the data migration service migrates the existing data from the source database tables to the corresponding tables in the target database.
      Incremental Synchronization	After an incremental synchronization task starts, data migration synchronizes the changed data (inserts, modifications, or deletions) from the source database to the corresponding tables in the target database. Incremental Synchronization includes DML Synchronization and DDL Synchronization. You can configure them according to your needs. For details, see Configure DML/DDL synchronization.

   3. In the Select Migration Objects section, configure the method for selecting migration objects.

      You can select migration objects by using either Specify Objects or Match by Rule.

      

   4. In the Select Migration Scope section, select the objects to migrate.

      • When you select Specify Objects, data migration supports Table-level and Database-level. Database table migration supports selecting one or more tables or views from a database as migration objects. Full database migration supports selecting an entire database as migration objects. If you select database table migration for a database, that database does not support full database migration. If you select full database migration for a database, that database does not support database table migration.

        After you select Table-level or Database-level, select the objects you want to migrate on the left and click > to add them to the list on the right.

        Data migration supports importing objects via text, and you can rename target objects, set row filters, view column information, and remove single or all migration objects.

        

        Note

        When you select Full Database Migration:

        • The list on the right only displays database names; it does not support displaying specific objects.

        • If you select the Incremental Synchronization-DDL Synchronization migration type, newly added tables at the source can be synchronized to the target.

        Operation	Description
        Import Objects	In the right-side list of the selection area, click Import Object in the upper-right corner. For more information, see Import migration objects.
        Rename	Data migration supports renaming migration objects. For more information, see Rename a database or table.
        Row Filter	Data migration supports row filtering using WHERE conditions. For more information, see Filter data with SQL conditions. You can also view the column information of a migration object in the View Column section.
        Remove/Clear All	Data migration supports removing one or more temporarily selected objects to the target during data mapping. Remove a single migration object In the right-side list of the selection area, click the Remove icon next to the target object to remove it. Remove all migration objects In the right-side list of the selection area, click Clear All in the upper-right corner. In the dialog box, click OK to remove all migration objects.

      • Select Match by Rule. For more information, see Configure database-to-database matching rules.

6. Click Next. On the Migration Options page, configure the parameters.

   *Full Migration

    On the **<UI-TERM key="oms-cloud.Migration.constants.ChooseTypes">Select Type and Object</UI-TERM>** page, the following parameters are displayed only when you select **<UI-TERM key="oms-cloud.components.SyncTopology.constant.OneWaySynchronization">One-way Sync</UI-TERM>** > **<UI-TERM key="oms-cloud.New.components.MigrationObjectForm.FullMigration">Full Migration</UI-TERM>**.
   
    ![migration114-en](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/oms/oceanbase-cloud/migration114-en.png)
   
    | Parameter | Description |
    |--------------------|--------------------|
    | Read Concurrency Configuration | This parameter specifies the number of concurrent reads from the source during full migration. The maximum value is 512. A high concurrency may cause excessive load on the source and affect business operations.|
    | Write Concurrency Configuration | This parameter specifies the number of concurrent data writes to the target during full migration. The maximum value is 512. A high concurrency may cause excessive load on the target and affect business operations.|
    | Whether to Limit Full Migration Rate | You can decide whether to enable the full migration rate limit based on your actual needs. If enabled, set the RPS (the maximum number of data rows that can be migrated to the target per second during full migration) and BPS (the maximum amount of data that can be migrated to the target per second during full migration).<main id="notice" type='explain'><h4>Note</h4><p>The RPS and BPS set here are only for rate limiting. The actual achievable performance during full migration is subject to factors such as the source, target, and instance specifications.</p></main>|
    | Processing Strategy When Target Table Has Records | The processing strategies include **<UI-TERM key="oms-cloud.components.MigrationParamsConfig.MigrationParamsDetail.NotAllowed.ignore">Stop Migration</UI-TERM>** and **<UI-TERM key="oms-cloud.components.PreCheck.PreCheckTable.Ignore">Ignore</UI-TERM>**:<ul><li>If you select **<UI-TERM key="oms-cloud.components.MigrationParamsConfig.MigrationParamsDetail.NotAllowed.ignore">Stop Migration</UI-TERM>**, an error is returned during full migration if the target table contains data. In this case, you must handle the data in the target table before you can continue the migration.<main id="notice" type='notice'><h4>Notice</h4><p>If you click Resume after an error occurs, data migration will ignore this configuration option and continue migrating table data. Proceed with caution.</p></main></li><li>If you select **<UI-TERM key="src.page.WorkBench.components.SQLPage.InlineChat.3B042B6D">Ignore</UI-TERM>**, when the target table contains data, if the original data conflicts with the data to be written, data migration logs the conflicting data and retains the original data unchanged during write operations.</li> </ul>|
    | Whether to Allow Post Indexing | The post-indexing feature can shorten the duration of full migration. You can specify whether to allow indexes to be created after full data migration is completed. For considerations on selecting post-indexing, see the description below the table.<main id="notice" type='notice'><h4>Notice</h4><ul><li><p>This option is available only if you have selected both <b>Schema Migration</b> and <b>Full Migration</b> on the <b>Select Migration Type</b> page.</p><li>Only non-unique key indexes support post-creation.</li></ul></main>|
   
    If post-indexing is allowed, we recommend that you adjust the following business tenant parameters using the command-line client tool based on the hardware conditions of OceanBase Database and the current business traffic.
   
    ```SQL
    // File memory buffer size
    ALTER SYSTEM SET _temporary_file_io_area_size = '10' tenant = 'xxx';
    // Disable rate limiting for OceanBase Database V4.x
    ALTER SYSTEM SET sys_bkgd_net_percentage = 100;
    ```
   

   • Incremental Synchronization

     The following parameters are displayed only when you select One-way Sync > Incremental Synchronization on the Select Type and Object page.

     

     Parameter	Description
     Write Concurrency Configuration	This parameter specifies the number of concurrent data writes to the target during incremental synchronization. The maximum value is 512. A high concurrency may cause excessive load on the target and affect business operations.
     Whether to Limit Incremental Migration Rate	You can decide whether to enable the incremental synchronization rate limit based on your actual needs. If enabled, set the RPS (the maximum number of data rows that can be synchronized to the target per second during incremental synchronization) and BPS (the maximum amount of data that can be synchronized to the target per second during incremental synchronization). Note The RPS and BPS set here are only for rate limiting. The actual achievable performance during incremental synchronization is subject to factors such as the source, target, and instance specifications.
     Incremental Synchronization Start Timestamp	If you selected Full Migration as the migration type, this parameter is not displayed. If you did not select Full Migration but selected Incremental Synchronization as the migration type, specify a point in time after which data should be migrated. The default is the current system time. For details, see Set an incremental synchronization timestamp.

   • Advanced Options

     These parameters are displayed only if the target OceanBase Database is V4.3.0 or later, and you have selected Schema Migration or Incremental Synchronization > DDL Synchronization on the Select Type & Objects page.

     

     The target table object storage types include Default, Row Storage, Column Storage, and Hybrid Row-Column Storage. This configuration determines the storage type for table objects during schema migration or incremental synchronization.

     Note

     The Default option adapts other options based on the target parameters. It ensures that table objects migrated during schema migration or newly added tables during incremental DDL operations are written with the corresponding structure based on the specified storage type.

7. Click Next to have the system pre-check the data migration task.

   During the Pre-check step, data migration checks whether the database user's read/write permissions, network connectivity, and other requirements are met. The migration task can only start after all checks pass. If a pre-check error occurs:

   • After you troubleshoot and resolve the issues, you can re-execute the pre-check until it passes.

   • You can also click Skip in the Actions column of the failed pre-check items. A dialog box will pop up, explaining the specific impact of skipping the operation. After confirming that you can skip, click OK in the dialog box.

8. After the pre-check passes, click Purchase to go to the Purchase Data Migration Instance page for purchase.

   After successful purchase, you can start the data migration task. For purchase details, see Purchase a data migration instance. If you do not need to purchase a data migration instance immediately, click Save to go to the data migration task details page, where you can manually purchase it later as needed.

   You can click Configure Validation Task in the upper-right corner of the data migration details page to compare data differences between the source and target databases. For details, see Create a data verification task.

   The data migration service supports modifying migration objects during the execution of a data migration task. For details, see View and modify migration objects. After a data migration task is started, it is executed sequentially based on the selected migration type. For details, see the content in the "View migration details" section of View the details of a data migration task.