Data migration between OceanBase databases

Background information

You can create a data migration task in the DTS console to migrate data between OceanBase databases of the same tenant type. You can use schema migration, full migration, and incremental synchronization to seamlessly migrate existing and incremental data from the source database to the destination database.

Prerequisites

• DTS has the required permissions to access cloud resources. For more information, see Grant permissions to a DTS migration role.

• A database user for the data migration task has been created in both the source and destination OceanBase databases. The required permissions have been granted to this user. For more information, see Create a database user.

• The corresponding schema has been created in the destination OceanBase database.

Limits

• Source database operation limits

  Do not perform DDL operations that change the database or table schema during the schema migration and full migration phases. This can interrupt the data migration task.

• DTS only supports data migration between OceanBase databases of the same tenant type.

  This means you can migrate data from an OceanBase database MySQL tenant to another OceanBase database MySQL tenant, or from an OceanBase database Oracle tenant to another OceanBase database Oracle tenant.

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

• DTS does not support triggers on the destination database. If triggers exist, the data migration task may fail.

Notes

• If the source character set is UTF-8, use a compatible character set for the destination, such as UTF-8 or UTF-16. This prevents issues such as garbled text at the destination due to character set incompatibility.

• If the clocks are not synchronized between nodes, or between your computer and the server, the latency time for incremental synchronization or reverse incremental synchronization may be inaccurate.

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

• Confirm that the migration precision for column types such as DECIMAL, FLOAT, or DOUBLE meets your expectations. If the precision of the destination field type is less than that of the source, data truncation may occur. This leads to data inconsistency between the source and destination.

• If DDL synchronization is not enabled and you change a unique index on the destination, you must restart the data migration task. Otherwise, data inconsistency may occur.

• If forward switchover is not enabled for the data migration task, delete the corresponding unique indexes and pseudo-columns from the destination database. Otherwise, data cannot be written. When data is imported to the downstream database, new pseudo-columns are generated that conflict with the pseudo-columns in the source database.

  If forward switchover is enabled, DTS automatically deletes hidden columns and unique indexes based on the task type. For more information, see Hidden column mechanism in Data Transmission Service.

• In a database and table aggregation scenario:

  • Use matching rules to map the relationship between the source and destination.

  • You must manually create the table schemas at the destination. If you use DTS to create them, skip any failed objects during the schema migration step.

• In a data migration task where the source is an OceanBase database and DDL synchronization is enabled, if a RENAME operation occurs on a source database or table, restart the task. This prevents data loss during incremental synchronization.

• If you configure only Incremental Synchronization when creating the task, DTS requires the source database to retain local incremental logs for more than 48 hours.

  If you configure Full Migration and Incremental Synchronization, DTS requires the source database to retain local incremental logs for at least 7 days. Otherwise, DTS may fail to obtain the incremental logs, causing the task to fail or even leading to data inconsistency.

• If the source or destination contains table objects whose names differ only in case, the migration result may not be as expected. This can happen if the source or destination is case-insensitive.

• Notes for OceanBase database MySQL tenants only:

  • For data migration between OceanBase database MySQL tenants, the destination parameter lower_case_table_names is set to `1` by default. Destination database objects are created in lowercase.

  • If the table schemas of the source and destination are not identical, data inconsistency may occur. Known scenarios include the following:

    • When you manually create a table schema that is outside the supported scope of DTS, implicit conversion issues may arise. This can cause column type mismatches between the source and destination.

    • If the data length at the destination is smaller than at the source, data truncation may occur. This leads to data inconsistency.

• Notes for OceanBase database Oracle tenants only:

  • If you skip the "Source-Primary Database-Database ROW_MOVEMENT Check" precheck item during migration and synchronize a table with ROW_MOVEMENT set to enable, data inconsistency will occur.

Supported source and destination instance types

In the following table, OceanBase database MySQL tenant is abbreviated as OB_MySQL, and OceanBase database Oracle tenant is abbreviated as OB_Oracle.

Procedure

1. (Optional) To migrate tables without primary keys, create a user in the corresponding tenant.

   For more information about how to create a user, see Create a database user. To migrate tables with primary keys, skip this step.

   • If the source is an OceanBase database MySQL tenant, create the __oceanbase_inner_drc_user user.

   • If the source is an OceanBase database Oracle tenant, create the __OCEANBASE_INNER_DRC_USER user.

2. Log on to the OceanBase Management Console and purchase a data migration task.

   For more information, see Purchase a data migration task.

3. On the Data Transmission > Data Migration page, click Configure for your new data migration task.

   

   To reference an existing task configuration, you can click Reference Configuration. For more information, see Reference a data migration task configuration.

4. On the Select Source and Destination page, configure the parameters.

   Parameter	Description
   Migration Task Name	The name can contain letters, digits, and Chinese characters. It cannot contain spaces and must be no more than 64 characters long.
   Source	If you have already created an OceanBase data source, select it from the drop-down list. If not, click Create Data Source in the drop-down list to create one in the dialog box that appears on the right. For more information about the parameters, see Create an OceanBase data source.
   Destination	If you have already created an OceanBase data source, select it from the drop-down list. If not, click Create Data Source in the drop-down list to create one in the dialog box that appears on the right.
   Tags (Optional)	Click the text box and select a tag from the drop-down list. You can also click Manage Tags to create, modify, or delete tags. For more information, see Manage data migration tasks using tags.

5. Click Next. On the Select Migration Type page, set Synchronization Topology to One-way Synchronization.

   Data Transmission Service supports One-way Synchronization and Two-way Synchronization. This topic describes the operations for a one-way synchronization task. For more information about two-way synchronization, see Configure a two-way synchronization task.

6. Select the migration types for the task.

   When the synchronization topology is one-way, the supported migration types include Schema Migration, Full Migration, Incremental Synchronization, Full Verification, and Reverse Incremental Synchronization.

   

   Migration Type	Description
   Schema Migration	After the schema migration task starts, DTS migrates the definitions of data objects (such as tables, indexes, constraints, comments, and views) from the source database to the destination database. Temporary tables are automatically filtered out.
   Full Migration	After the full migration task starts, DTS migrates the existing data from the source tables to the corresponding tables in the destination database. If you select Full Migration, collect statistics from the source OceanBase database before you migrate the data.
   Incremental Synchronization	After the incremental synchronization task starts, DTS synchronizes data changes (inserts, updates, or deletes) from the source database to the corresponding tables in the destination database. Incremental Synchronization includes DML Synchronization and DDL Synchronization, which you can configure as needed. For more information, see Customize DDL and DML synchronization. The limits of Incremental Synchronization are as follows: If you select DDL Synchronization, the data migration task may be interrupted if a DDL operation that is not supported by DTS occurs on the source database. If the DDL operation is to add a new column, set the column's attribute to NULL. Otherwise, the data migration task may be interrupted.
   Full Verification	After full migration is complete and the incremental data at the destination has mostly caught up with the source, DTS automatically starts a full data verification task. This task verifies the data in the configured source tables against the destination tables. If you select Full Verification, collect statistics from both the source and destination OceanBase databases before the verification starts. If you select Incremental Synchronization but do not select all DML types for the DML Synchronization option, DTS does not support full data verification in this scenario.
   Reverse incremental	After the reverse incremental synchronization task starts, it can synchronize data changes from the destination database back to the source database in real time after a service switchover. Reverse incremental synchronization typically reuses the configuration of incremental synchronization, but you can also customize the configuration as needed.

7. (Optional) Click Next.

   If you select schema migration or incremental synchronization with a self-managed OceanBase database in a VPC as the source, or if you select reverse incremental synchronization with a self-managed OceanBase database in a VPC as the destination, you must provide additional information such as the username, password, oblogproxy IP address, and oblogproxy port. If you are migrating a table without a unique key, you must enter the password for _OCEANBASE_INNER_DRC_USER for the OceanBase data source.

   If the source or destination data source is not configured with the required parameters, the Additional Data Source Information dialog box appears when you create the task, prompting you to configure them. For more information about the parameters, see Create an OceanBase data source.

8. Click Next. On the Select Migration Objects page, select the objects for the data migration task.

   You can select migration objects using two methods: Specify Objects and Matching Rules. This topic describes how to select migration objects using the Specify Objects method. For more information about configuring matching rules, see Configure and modify matching rules.

   Important

   • The names of the tables to be migrated and the columns in these tables cannot contain Chinese characters.

   • If a database name or table name contains the "$$" characters, the creation of the data migration task may fail.

   • If you select DDL Synchronization in the Select Migration Type step, we recommend using matching rules to select migration objects. This ensures that all new objects that match the rules are synchronized. If you select migration objects individually, new or renamed objects will not be synchronized.

   

   1. In the Select Migration Objects section, select Specify Objects.

   2. In the Select Migration Scope area, in the Source Objects list, select the objects to migrate. You can select tables and views from one or more databases to migrate.

   3. Click > to add them to the Destination Objects list.

   Data Transmission Service lets you import objects from text files. It also lets you rename destination objects, set row filters, view column information, and remove a single object or all objects.

   Note

   When you select migration objects using the Matching Rules method, the matching rule syntax overrides the renaming capability, and the Actions section is limited to setting filter conditions. For more information, see Configure and modify matching rules.

   Operation	Description
   Import Object	In the list on the right of the selection area, click Import Object in the upper-right corner. In the dialog box that appears, click OK. Important The import operation overwrites previous selections. Proceed with caution. In the Import Migration Objects dialog box, import the objects to be migrated. You can import a CSV file to rename database tables, set row filtering conditions, and perform other operations. For more information, see Download and import migration object configurations. Click Check Validity. After you import the migration objects, check their validity first. Column field mapping is not currently supported. After the check is passed, click OK.
   Rename	Data Transmission Service lets you rename migration objects. For more information, see Rename database and table objects.
   Settings	Data Transmission Service supports row filtering using WHERE conditions. For more information, see Filter data with SQL conditions. You can also view the column information of migration objects in the View Columns area.
   Remove/Remove All	Data Transmission Service lets you remove one or more objects that have been temporarily selected for the destination during data mapping. Remove a single migration object In the list on the right of the selection area, hover over the target object and click the displayed Remove button to remove the migration object. Remove all migration objects In the list on the right of the selection area, click Remove All in the upper-right corner. In the dialog box that appears, click OK to remove all migration objects.

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

   • Full Migration

     On the Select Migration Type page, the following parameters appear only when you select One-way Synchronization > Full Migration.

     

     Parameter	Description
     Read Concurrency	This parameter specifies the number of concurrent threads for reading data from the source during full migration. The maximum value is 512. A high degree of concurrency may put excessive pressure on the source and affect your services.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during full migration. The maximum value is 512. A high degree of concurrency may put excessive pressure on the destination and affect your services.
     Full Migration Rate Limit	You can decide whether to enable the full migration rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be migrated to the destination per second during full migration) and BPS (the maximum volume of data that can be migrated to the destination per second during full migration). Note The RPS and BPS settings here only serve as a rate-limiting capability. The actual performance of full migration is affected by factors such as the source, destination, and instance specifications.
     Processing Policy for Existing Records in Destination Table Objects	The processing policy includes Ignore and Stop Migration: Select Ignore: If a destination table already contains data and a conflict occurs between the data being written and the existing data, Data Transmission Service logs the conflicting data and keeps the existing data unchanged. Important If you select Ignore, full data verification uses the IN pattern to pull data. This process cannot verify scenarios where data exists in the destination but not in the source, and verification performance is degraded to a certain degree. If you select the default value Stop Migration, the full migration fails with an error if the destination table contains data. You must process the data in the destination table before you can restart the migration. Important If an error occurs and you click Resume, Data Transmission Service will ignore this configuration option and continue to migrate table data. Proceed with caution.
     Is deferred indexing supported?	You can set whether to allow index creation after full data migration is complete. The postponed index creation feature can shorten the time required for full migration. For notes on selecting postponed index creation, see the description below the table. Important On the Select Migration Type page, this parameter is displayed only if you select both Schema Migration and Full Migration. Only non-unique indexes support postponed creation. When creating an index, if the destination OceanBase database encounters the following errors, Data Transmission Service will ignore them and assume the index was created successfully, without attempting to create it again. OceanBase database in MySQL-compatible mode reports a Duplicate key name error. OceanBase database in Oracle-compatible mode reports a name is already used by an existing object error. If the destination is an OceanBase database and you select Allow, configure the following settings: Concurrency for a single index DDL statement: A higher degree of parallelism consumes more resources and results in a faster migration speed. Maximum number of concurrent index DDL statements: The maximum number of postponed index DDL statements that the system can call at the same time.

     If you allow postponed index creation, we recommend that you adjust the following business tenant parameters using a command-line tool according to the hardware of the OceanBase database and the current service traffic.

     // File memory buffer limit
     alter system set _temporary_file_io_area_size = '10' tenant = 'xxx'; 
     // V4.x Disable throttling
     alter system set sys_bkgd_net_percentage = 100;  

   • Incremental Synchronization

     The following parameters appear only when you select One-way Synchronization > Incremental Synchronization on the Select Migration Type page.

     

     Parameter	Description
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during incremental synchronization. The maximum value is 512. A high degree of concurrency may put excessive pressure on the destination and affect your services.
     Incremental Synchronization Rate Limit	You can decide whether to enable the incremental synchronization rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be synchronized to the destination per second during incremental synchronization) and BPS (the maximum volume of data that can be synchronized to the destination per second during incremental synchronization). Note The RPS and BPS settings here only serve as a rate-limiting capability. The actual performance of incremental synchronization is affected by factors such as the source, destination, and instance specifications.
     Incremental Synchronization Start Offset	If you select Full Migration, this parameter is not displayed. If you select Incremental Synchronization but not Full Migration, specify a point in time from which to start migrating data. The default value is the current system time. For more information, see Configure an incremental synchronization offset.

   • Reverse Incremental

     On the Select Migration Type page, the parameters for this area are displayed only if you select One-way Synchronization > Reverse Incremental. The configuration parameters for Reverse Incremental default to Reuse Incremental Synchronization Configuration.

     

     You can also choose not to reuse the incremental synchronization configuration and configure it manually.

     Parameter	Description
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the source during reverse incremental synchronization. The maximum value is 512. A high degree of concurrency may put excessive pressure on the source and affect your services.
     Reverse Incremental Rate Limit	You can decide whether to enable the reverse incremental rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be synchronized to the source per second during reverse incremental synchronization) and BPS (the maximum volume of data that can be synchronized to the source per second during reverse incremental synchronization). Note The RPS and BPS settings here only serve as a rate-limiting capability. The actual performance of reverse incremental synchronization is affected by factors such as the source, destination, and instance specifications.
     Incremental Synchronization Start Offset	If you select Full Migration, this parameter is not displayed. If you do not select Full Migration but select Incremental Synchronization, the setting defaults to the forward switch (if any) and cannot be modified.

   • Advanced Options

     

     Parameter	Description
     Add Hidden Column for Tables Without Non-null Unique Keys	For data migration between OceanBase databases, you need to configure whether to add a hidden column for tables that do not have a non-null unique key. For more information, see Hidden column mechanism in Data Transmission Service.
     Destination Table Storage Class	This parameter appears only if the destination OceanBase database is v4.3.0 or later and you select Schema Migration or Incremental Synchronization > DDL Synchronization on the Select Migration Type page. The destination table storage classes include Default, Row Store, Column Store, and Row and Column Store. This setting determines the storage class of destination table objects during schema migration or incremental synchronization. For more information, see default_table_store_format. Note The Default option automatically adapts to other options based on the destination parameter configuration. Table objects from schema migration or new table objects from incremental DDL are written with the corresponding structure based on the set storage class.
     Source Table Storage Class	This parameter appears only if the source OceanBase database is v4.3.0 or later and you select Reverse Incremental Synchronization > DDL Synchronization on the Select Migration Type page. The source table storage classes include Default, Row Store, Column Store, and Row and Column Store. This setting determines the storage class of source table objects during reverse incremental synchronization. For more information, see default_table_store_format. Note The Default option automatically adapts to other options based on the destination parameter configuration. New table objects from incremental DDL during reverse incremental synchronization are written with the corresponding structure based on the set storage class.

10. Click Precheck to precheck the data migration task.

    In the Precheck step, Data Transmission Service checks whether items meet the requirements, such as the read and write permissions of the database user and the network connectivity of the database. You can start the data migration task only after all check items pass. If the precheck fails:

    • You can identify and troubleshoot the problem, and then run the precheck again until it succeeds.

    • You can also click Skip in the Actions column for a failed precheck item. A dialog box appears that describes the impact of skipping this operation. To proceed, click OK.

11. After the precheck passes, click Start Task.

    If you do not want to start the task immediately, click Save. You can then start the task manually from the Data Migration Task List page or using batch operations. For more information about batch operations, see Batch Operations on Data Migration Tasks.

    Data Transmission Service lets you modify migration objects and their row filtering conditions while a data migration task is running. For more information, see View and modify migration objects and their filter conditions. After the data migration task starts, it performs the migration steps sequentially based on the selected migration types. For more information, see View migration details.

References

• Create an OceanBase data source

• Configure and modify matching rules

• View the details of a data migration task