Migrate data from OceanBase Database in Oracle compatible mode to an Oracle database

You can create a data migration task to seamlessly migrate the existing business data and incremental data from the source database to an Oracle database through schema migration, full migration, and incremental synchronization.

Background information

Data migration supports consolidating table data from multiple OceanBase databases in Oracle compatible mode into a single table in an Oracle database. This process does not require schema migration; only full migration and incremental synchronization are needed. The usage limitations for this consolidation and synchronization feature are as follows:

• For full migration and incremental synchronization, the target must have all columns that the source has. If this requirement is not met, data migration will report an error.

• Primary key columns must exist in the source table.

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

Prerequisites

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

• You have created the cluster and tenant for the target OceanBase database. For details, see Create an instance and Create a tenant.

• You have created a dedicated database user for the data migration task on both the source and target sides and granted them the relevant privileges. For details, see User privileges.

Usage limitations

• Only users with the project role of Project Owner, Project Admin, or Data Services Admin can create new data migration tasks.

• Limitations on operations on the source database

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

• Currently, data migration supports Oracle Database versions 10G, 11G, 12C, 18C, and 19C. Versions 12C and later include Container Database (CDB) and Pluggable Database (PDB). It also supports OceanBase Database Oracle compatible mode V2.x, V3.x, and V4.x.

• The data migration service does not support incremental data migration for tables where all columns are of LOB type (BLOB/CLOB/NCLOB).

• The data migration service does not support migrating non-template secondary partitioned tables from OceanBase Database Oracle compatible mode to an Oracle database.

• The data migration service currently does not support expression-based indexes.

• The data migration service only supports multi-table consolidation for tables with primary keys.

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

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

• Data source identifiers and user accounts must be globally unique within the data migration service system.​

• Oracle Database 11G and earlier versions do not support creating database objects exceeding 30 bytes. Note that when migrating data from OceanBase Database Oracle compatible mode to an Oracle database, you cannot create database objects larger than this limit at the source.

Considerations

• For OceanBase Database V4.x, it is strongly recommended to enable log archiving. After enabling log archiving, if clogs are reclaimed, the data migration service can still achieve incremental synchronization by consuming the archived logs.

• 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 text at the target caused by character set incompatibility.

• Clock skew between nodes or between the client and the server can 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, it may cause actual latency.

• The strategy for determining field length varies depending on the specific circumstances of the data migration task, whether the Oracle character set is supported, and whether OceanBase Database is in Oracle compatible mode.

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

• If DDL synchronization is not enabled, you must restart the data migration task after modifying a unique index on the target side. Otherwise, data inconsistency may occur.

• Please check the objects in the recycle bin of OceanBase Database in Oracle compatible mode. When the number of objects exceeds 100, internal table queries may time out. Please clean up objects in the recycle bin.

• Please check the objects in the recycle bin of the Oracle database. When the number of objects exceeds 100, internal table queries may time out. Please clean up objects in the recycle bin.

  • Check whether 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;
    

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

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

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

• If you perform incremental synchronization from an OceanBase database as the source and the database table objects contain UDT columns, the incremental synchronization task may fail because it does not support UDT columns.

Supported source and target instance types

Data type mappings

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 data source, click Quick Fill next to Source and select the target data source from the drop-down list. After selection, the configurations for the source section 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 area.

      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 Provider	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud.
      Database Type	Select OceanBase Oracle Compatible for the source.
      Instance Type	Includes Dedicated (Transactional) and Self-managed Database. Currently, only data migration from a Dedicated (Transactional) instance to an Oracle database is supported.
      Region	Select the region where the source database is located.
      Instance	The ID or name of the instance where the tenant in Oracle compatible mode of OceanBase Database resides. You can view the ID or name of the target instance on the Instances* page. Note When the cloud provider is Alibaba Cloud, cross-account authorization for Alibaba Cloud root account instances is also supported. For details, see Alibaba Cloud Account Authorization. </main
      Tenant	The ID or name of the tenant in Oracle compatible mode of 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.
      Database Account	The username of the user in the tenant of OceanBase Database in Oracle compatible mode 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 data source, click Quick Fill next to Target and select the target data source from the drop-down list. After selection, the configurations for the target section 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 area.

      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 Provider	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud. You can choose the same cloud provider as the source or migrate data across different cloud providers. Note The cross-cloud migration feature is not currently enabled by default. To use this feature, contact OceanBase Cloud Technical Support.
      Database Type	Select Oracle for the target.
      Instance Type	When the cloud provider is AWS, the instance types supported are RDS Oracle and Self-managed Oracle. When the cloud provider is Huawei Cloud or Google Cloud, the instance type supported is Self-managed Oracle.
      Region	Select the region where the target database is located.
      Connection Type	Includes Endpoint and Public IP. If you choose the Endpoint connection method, you must first add the account ID displayed on the page to the allowlist of your Endpoint service to allow connections from this account to the Endpoint service. For details, see Select Private Network Connection. If the Cloud Vendor is set to AWS, and you selected Require for the Require Acceptance to Use Endpoint parameter when creating the Endpoint service, the Data Migration Service will prompt you to enter the AWS console to perform an Accept endpoint connection request operation for the received Connections request upon first accessing the private connection. If the cloud provider is Google Cloud, please add the authorized project to Published Services. After authorization, manual authorization is no longer required during data source test connections. If you choose the Public IP connection method, you must first add the data source IP address displayed on the page to the allowlist of the Oracle database instance to ensure connectivity. For details, see Select Public Network Connection.</main id="notice" type='explain'> Note The data source IP address to be added to the allowlist is displayed only after you select the regions for both the source and target.
      Connection Information	If Connection Type is set to Endpoint, enter the name of the Endpoint service. If Connection Type is set to Public IP, enter the IP address and port number of the database host.
      Service Name	The service name of the Oracle database.
      Database Account	The username of the Oracle database user used for data migration.
      Password	The password of the database user.

4. Click Test and Continue.

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

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

      Data migration supports One-way Sync and Two-way Sync. This topic describes one-way synchronization. For details about two-way synchronization, see Configure a two-way synchronization task.

   2. In the Select Type & Objects step, select the migration type for the current data migration task.

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

      Parameter	Description
      Schema Migration	Schema migration requires defining character set mappings yourself. Data migration only replicates the source database's data (schema) to the target database without affecting the source data (schema).
      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 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, which you can configure as needed. 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 using either Specify Objects or Match by Rule.

      

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

      • When Specify Objects is selected, data migration supports Table-level and Database-level. Database table migration supports selecting one or more tables or views within a database as migration objects, while full database migration supports selecting an entire database as the migration object. If database table migration is selected for a database, that database cannot support full database migration. If full database migration is selected for a database, that database cannot support database table migration.

        After selecting 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 allows you to rename target objects, filter rows, view column information, and remove one 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 migration type Incremental Synchronization - DDL Synchronization, newly added tables at the source can be synchronized to the target.

        Operation	Description
        Import Objects	In the list on the right side 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 filtering rows 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 during data mapping. Remove a single migration object In the list on the right side of the selection area, click the Remove icon next to the target object to remove it. Remove all migration objects In the list on the right side 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

     The following parameters are displayed only if you have selected One-way Sync > Full Migration in the Select Type & Objects step.

     Parameter	Description
     Read Concurrency Configuration	This parameter specifies the number of concurrent reads from the source during full migration. The maximum limit is 512. A high concurrency value may cause excessive load on the source and affect business operations.
     Write Concurrency Configuration	This parameter specifies the number of concurrent writes to the target during full migration. The maximum limit is 512. A high concurrency value 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 source read RPS (the maximum number of rows that can be read from the source per second during full migration), source read BPS (the maximum traffic that can be read from the source per second during full migration), target write RPS (the maximum number of rows that can be written to the target per second during full migration), and target write BPS (the maximum traffic that can be written to the target per second during full migration). Note The RPS and BPS values 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.
     Processing Strategy When Target Table Has Records	Valid values: Stop Migration and Ignore. If you select Stop Migration, an error is returned when full migration attempts to migrate data to the target table. In this case, you must handle the data in the target table before you can continue the migration. Notice If you click Resume after an error occurs, data migration will ignore this configuration option and continue migrating table data. Proceed with caution. If you select Ignore, when the target table has 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.
     Whether to Allow Post-indexing	Post-indexing 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. Notice This option is available only if you have selected both Schema Migration and Full Migration on the Select Migration Type page. Only non-unique key indexes support post-creation.

     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 your OceanBase Database and the current business traffic.

     // File memory buffer size
     ALTER SYSTEM SET _temporary_file_io_area_size = '10' tenant = 'xxx';
     // Disable throttling for OceanBase Database V4.x
     ALTER SYSTEM SET sys_bkgd_net_percentage = 100;
     

   • Incremental synchronization

     The following parameters are displayed only if you select One-way Sync > Incremental Synchronization in the Select Type & Objects step.

     Parameter	Description
     Write Concurrent Configuration	This parameter specifies the number of concurrent writes to the target during incremental synchronization, with a maximum limit of 512. Excessively high concurrency may overwhelm the target and impact your business.
     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 target write RPS (the maximum number of data rows that can be synchronized to the target per second during incremental synchronization) and target write BPS (the maximum traffic that can be synchronized to the target per second during incremental synchronization). Note The RPS and BPS settings 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 chose Incremental Synchronization, 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.

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

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

   • You can troubleshoot and resolve the issue, then re-execute the pre-check until it passes.

   • You can also click Skip in the Actions column of the failed pre-check item. A dialog box appears, explaining the specific impact of skipping this operation. After confirming 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 if 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 task execution. For details, see View and modify migration objects. After a data migration task starts, it is executed sequentially according to the selected migration type. For details, see the "View migration details" section in View the details of a data migration task.