Migrate data from an Oracle tenant of an OceanBase database to an Oracle database

Background information

In the DTS console, you can create a data migration task to migrate data from an Oracle tenant of an OceanBase database to an Oracle database. You can use schema migration, full migration, and incremental synchronization to seamlessly migrate historical and incremental data from the source database to the Oracle database.

DTS supports consolidating table data from multiple OceanBase database Oracle tenants into a single table in an Oracle database. This process requires only full migration and incremental synchronization, not schema migration. The limits for this consolidation feature are as follows:

• For full migration and incremental synchronization, all columns in the source table must also exist in the destination table. If this requirement is not met, DTS reports an error.

• The source table must have a primary key column.

• The destination table can have columns that do not exist in the source table.

Prerequisites

• Data Transmission Service has been granted access permissions to cloud resources. For more information, see Authorize a migration role for Data Transmission Service.

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

• You have created a database user dedicated to the data migration task in both the Oracle tenant of the source OceanBase database and the destination Oracle database. You have also granted the required permissions to this user. For more information, see Create a database user.

• To migrate tables without primary keys, you must create the __OCEANBASE_INNER_DRC_USER user in the corresponding tenant and grant it permissions before you execute a data migration task. For more information, see Create the __OCEANBASE_INNER_DRC_USER user.

Limits

• Operational limits on the source database

  Do not perform DDL operations that change the database or table structure during the schema migration and full migration phases. Otherwise, the data migration task may be interrupted.

• DTS supports Oracle Database versions 10G, 11G, 12C, 18C, and 19C. Versions 12C and later include Container Databases (CDBs) and Pluggable Databases (PDBs).

• DTS does not support incremental data migration for tables where all columns are of LOB types (BLOB, CLOB, or NCLOB).

• DTS does not support migrating non-template subpartitioned tables from an OceanBase database Oracle tenant to an Oracle database.

• DTS does not support expression-based indexes.

• DTS supports multi-table consolidation only for tables with primary keys.

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

• DTS does not support triggers on the destination database. The presence of triggers may cause the data migration to fail.

• Data source identifiers and user accounts are globally unique within the DTS system.

• Oracle Database 11G and earlier versions do not support creating database objects with names longer than 30 bytes. When migrating data from an OceanBase database Oracle tenant to an Oracle database, do not create database objects in the source that exceed this limit.

• DTS does not support migrating spatial indexes. This includes existing spatial indexes and new spatial indexes added during the incremental phase (including INSERT USER_SDO_GEOM_METADATA).

• The following limits apply when writing GIS data to an Oracle database:

  • Data with an SRID of 0, 4327, or 4328 cannot be written.

  • Data such as SDO_POINT_TYPE(0.00001, 0.00001, 0), which is converted to scientific notation after using get_wkt, cannot be written.

  • Only coordinate data within the range of -2147483647 to 2147483647 can be written.

  • When using the 4326 coordinate system, ensure that longitude and latitude values are within their valid ranges. The longitude range is -180 to 180, and the latitude range is -90 to 90. Values outside these ranges will cause data inconsistency.

  • Data with too many decimal places may not be written successfully. This is because data read in scientific notation cannot be written.

    Scientific notation for read data is based on the following two definitions:

    • scale: The number of decimal places in a value. For example, the scale of 123.45 is 2, and the scale of 0.000001 is 6.

    • precision: The precision of a value, which is the number of digits after the first non-zero digit. For example, the precision of 123.45 is 5, and the precision of 0.000001 is 1.

    If -scale + (precision + 1) < -2 for a data value, the value is converted to base-10 scientific notation.

    For example, for 0.00001, the scale is 5 and the precision is 1. Therefore, -5 + (1 + 1) = -3, which is less than -2. The value is converted to 1e-5. For the exact value, refer to the result of the sdo_geometry.get_wkt() method or SDO_UTIL.TO_WKTGEOMETRY.

  • Very large or very small values may not be written successfully. Numbers greater than the maximum value of an INT (32-bit integer, 2147483647) or less than the minimum value of an INT (32-bit integer, -2147483647) may fail to be written.

Notes

• For reverse incremental synchronization from an OceanBase database Oracle tenant to an Oracle database, note the following:

  • When performing incremental synchronization for an Oracle database, the size of a single archived object should be less than 2 GB.

  • Retain Oracle database archived objects for more than two days. Otherwise, if archived objects are unavailable when you try to recover from events such as a sudden increase in archiving volume, recovery will not be possible.

  • Incremental log parsing for Oracle databases supports a maximum of 5 TB per day.

  • For reverse incremental synchronization from an OceanBase database Oracle tenant to Oracle Database 12C or later, DTS does not support migrating database objects (such as schemas, tables, and columns) with names longer than 30 bytes. To migrate such objects, contact technical support.

  • DTS does not support certain UPDATE commands in Oracle databases. The following example shows an unsupported UPDATE command.

    UPDATE TABLE_NAME SET KEY=KEY+1;

    In the example, TABLE_NAME is the table name, and KEY is a column of the NUMERIC type defined as the primary key.

  • For tables that have no primary key and contain LOB type fields, reverse incremental synchronization can cause Data Quality issues.

• For OceanBase Database V4.x, we strongly recommend that you enable archived logs. After you enable archived logs, if Clogs are reclaimed, DTS can still perform incremental synchronization by consuming the archived logs.

• If the source character set is UTF-8, use a compatible character set for the destination (such as UTF-8 or UTF-16) to prevent issues such as garbled text caused by character set incompatibility.

• Asynchronous clocks between nodes, or between client terminals and servers, can cause inaccurate latency measurements for incremental synchronization or reverse incremental synchronization.

  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.

• When the character sets of the Oracle database and the OceanBase database Oracle tenant differ, the field length policy is selected based on the specific data migration task scenario.

• When migrating a table without a primary key, a hidden column is added to the destination table for the migration. However, if the destination is an Oracle database version earlier than 12C, a non-hidden column is added instead.

• If a source table contains a field named OMS_PK_INCRMT, the incremental synchronization task will be interrupted and cannot be recovered.

• 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 index and pseudo-columns from the destination database. If you do not delete them, data cannot be written, and new pseudo-columns will be generated when importing data downstream, causing conflicts with the pseudo-columns in the source database.

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

• In a database and table consolidation scenario:

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

  • Create the table structure on the destination manually. If you use DTS to create it, skip any failed objects during the schema migration step.

• Check the objects in the recycle bin of the OceanBase database Oracle tenant. If there are more than 100 objects, internal table queries may time out. Clear the objects in the recycle bin.

  • Check if the recycle bin is enabled.

    SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

  • Check the number of objects in the recycle bin.

    SELECT count(*) FROM RECYCLEBIN;

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

• In a data migration task where the source is an OceanBase database and DDL synchronization is enabled, if a database or table in the source is renamed (RENAME), restart the task to prevent data loss during incremental synchronization.

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

  If you configure Full Migration and Incremental Synchronization when creating a data migration task, DTS requires that local incremental logs in the source database be retained for at least 7 days. Otherwise, the data migration task may fail or even cause data inconsistency between the source and destination because DTS cannot obtain the incremental logs.

• If the source or destination contains table objects whose names differ only in case, the migration result may not be as expected due to case-insensitivity at the source or destination.

Supported source and destination instance types

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

Data type mapping

Create a data migration task

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

   For more information, see Purchase a data migration task.

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

3. On the Select Source and Target page, configure the parameters.

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

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

5. Select the migration types for the task.

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

   

   Migration Type	Description
   Schema Migration	After the schema migration task starts, DTS migrates the definitions of database 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 historical data from the source tables to the corresponding tables in the destination database. If you select Full Migration, collect the statistics information of the OceanBase database Oracle tenant before you migrate data.
   Incremental Synchronization	After the incremental synchronization task starts, DTS synchronizes data changes (additions, modifications, or deletions) from the source database to the corresponding tables in the destination database. Incremental Synchronization includes DML Synchronization and DDL Synchronization, and you can customize the configuration based on your requirements. For more information, see Customize DDL and DML. 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 in the source database. If the DDL operation is to add a 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 is synchronized to the destination and is nearly in sync with the source, DTS automatically starts a full data verification task for the configured source and destination tables. If you select Full Data Verification, we recommend that before the full data verification starts, you collect statistics for the Oracle tenant of the OceanBase database and use GATHER_SCHEMA_STATS or GATHER_TABLE_STATS statements to collect statistics for the Oracle database. If you select Incremental Synchronization and do not select all DML operations under the DML Synchronization option, Data Transmission Service does not support full data verification in this scenario.
   Reverse Incremental Synchronization	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.

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

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

   • Full Migration

     These parameters are displayed only if you select One-way Synchronization > Full Migration on the Select Migration Types page.

     

     Parameter	Description
     Read Concurrency	This parameter specifies the number of concurrent threads for reading data from the source during full migration. The maximum is 512. A high concurrency can put excessive pressure on the source and affect your business.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during full migration. The maximum is 512. A high concurrency can put excessive pressure on the destination and affect your business.
     Full Migration Rate Limit	You can enable the full migration rate limit as needed. If enabled, set the RPS (maximum number of rows migrated to the destination per second) and BPS (maximum volume of data migrated to the destination per second). Note The RPS and BPS settings here are for rate limiting only. The actual performance of full migration depends on factors such as the source, destination, and instance specifications.
     Policy for Existing Records in Destination Table	The policies include Ignore and Stop Migration: Select Ignore: If the destination table contains data and a conflict occurs between the original data and the data being written, DTS logs the conflicting data and keeps the original data unchanged. Important If you select Ignore, full data verification will pull data in IN mode. This mode cannot verify scenarios where the destination has data that the source does not, and verification performance will be degraded to some extent. Select the default value Stop Migration: If the destination table contains data, full migration will report an error and stop. Handle the data in the destination table before continuing the migration. Important If you click Resume after an error, DTS will ignore this setting and continue migrating the table data. Proceed with caution.
     Allow Postponed Index Creation	You can choose whether to create indexes after the full data migration is complete. Postponing index creation can reduce the time required for full migration. For precautions when choosing this option, see the notes below this table. Important This parameter is displayed only if you select both Schema Migration and Full Migration on the Select Migration Types page. Only non-unique indexes support postponed creation.

     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	When you migrate data from an Oracle tenant of an OceanBase database to an Oracle database, you need to configure whether to add a hidden column to tables without a non-empty unique key. For more information, see Hidden column mechanism of Data Migration Service.
     Source Table Object Storage Class	This parameter is displayed only when the source OceanBase database is V4.3.0 or later and you select Reverse Incremental > DDL Synchronization on the Select Migration Type page. The storage classes for source table objects include Default, Row Store, Column Store, and Mixed 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 other options based on the parameter settings of the destination instance. This means that during reverse incremental synchronization, new table objects created by incremental DDL operations are written to the corresponding structure based on the configured storage class.

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

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

• Create an Oracle data source

• Configure and modify matching rules

• View the details of a data migration task