Migrate data from an Oracle tenant of OceanBase Database to an Oracle database by using OMS

OceanBase Migration Service (OMS) is a service that supports data interaction between OceanBase Database and homogeneous or heterogeneous data sources. It supports online data migration and real-time synchronization of incremental data. OMS provides a visualized and centralized management platform. You can migrate data in real time with simple configurations.

This topic describes how to migrate all data from an Oracle tenant of OceanBase Database to an Oracle database by using OMS V4.0.2.

Prerequisites

• You have deployed OMS V4.0.2. For more information about how to deploy OMS, see Deployment types.
• You have an Oracle tenant of OceanBase Database to serve as the source data source for data migration. For more information about user tenants, see User tenants.
• You have a MySQL database to serve as the destination data source for data migration.

Procedure

1. Configure the source and destination data sources.
2. Create data sources.
3. Create a full data migration project and start the project.
4. View the data migration status.

Step 1: Configure the source and destination data sources

The following table describes information about the test environment.

Step 1.1: Configure the source Oracle tenant of OceanBase Database

1. Create test data in the source Oracle tenant of OceanBase Database.

   Example:

   1. Create a schema in a source database named test_ob_to_oracle.

      obclient [SYS]> CREATE USER test_ob_to_oracle IDENTIFIED BY "******";
      

   2. Create a source table named test_ob_to_oracle.tbl1 and add test data to it.

      Create a table named test_ob_to_oracle.tbl1:

      obclient [SYS]> CREATE TABLE test_ob_to_oracle.tbl1(col1 NUMBER(10) PRIMARY KEY, col2 VARCHAR2(20));
      

      Insert the test data:

      obclient [SYS]> INSERT INTO test_ob_to_oracle.tbl1 VALUES(1,'China'),(2,'Taiwan'),(3,'Hong Kong'),(4,'Macao'),(5,'North Korea');
      

      Commit the test data:

      obclient [SYS]> COMMIT;
      

      View the test data:

      obclient [SYS]> SELECT * FROM test_ob_to_oracle.tbl1;
      

      Return result:

      +------+-------------+
      | COL1 | COL2        |
      +------+-------------+
      |    1 | China       |
      |    2 | Taiwan      |
      |    3 | Hong Kong   |
      |    4 | Macao       |
      |    5 | North Korea |
      +------+-------------+
      5 rows in set
      

2. Create a migration user for the source database and grant the required privileges to the user.

   In Oracle mode of OceanBase Database V2.2.70 and later, the migration user must have the privileges of a database administrator (DBA).

   Example:

   1. Create a migration user named user001 for the source database.

      obclient [SYS]> CREATE USER user001 IDENTIFIED BY "******";
      

   2. Grant the required privileges to the user.

      obclient [SYS]> GRANT DBA TO user001;
      

Step 1.2: Configure the destination Oracle database

1. Create a destination schema and table.

   Example:

   1. Create a schema in a destination database named test_ob_to_oracle_backup.

      SQL> CREATE USER test_ob_to_oracle_backup IDENTIFIED BY "******";
      

   2. Create a destination table named test_ob_to_oracle_backup.tbl1_backup.

      SQL> CREATE TABLE test_ob_to_oracle_backup.tbl1_backup(col1 NUMBER(10) PRIMARY KEY, col2 VARCHAR2(20));
      

2. Create a migration user for the destination data source and grant the required privileges to the user.

   The migration user must have the RESOURCE, CREATE SESSION, ALTER SESSION, SELECT ANY TABLE, and SELECT ANY DICTIONARY privileges.

   Grant the DELETE, INSERT, and UPDATE privileges on the database objects to be migrated to the migration user.

   Example:

   1. Create a migration user named user001_backup for the destination data source.

      SQL> CREATE USER user001_backup IDENTIFIED BY "******";
      

   2. Grant the required privileges to the user.

      SQL> GRANT RESOURCE,CREATE SESSION,ALTER SESSION,SELECT ANY TABLE,SELECT ANY DICTIONARY TO user001_backup;
      

      SQL> GRANT DELETE,INSERT,UPDATE ON test_ob_to_oracle_backup.tbl1_backup TO user001_backup;
      

3. Create a user named drc_user and grant privileges to the user.

   You must create a user in the sys tenant of OceanBase Database, which is used by OMS for authentication when it consumes clogs from an OceanBase database. You select this user in the Advanced Options section when you add the OceanBase data source.

   Example:

   obclient [(none)]> CREATE USER drc_user001 IDENTIFIED BY '******';
   

   Grant the drc_user user the SELECT privilege in the sys tenant:

   obclient [(none)]> GRANT SELECT ON *.* TO drc_user001;
   Query OK, 0 rows affected
   

Step 2: Create data sources

1. Log on to the OMS console.

2. On the Data Source Management page, click New Data Source.

3. Create a physical OceanBase data source as the source data source of the migration project.

   Configure the following parameters.

   Parameter	Description
   Data Source Type	Select OceanBase and Physical Data Source.
   Data Source Identifier	We recommend that you set it to a combination of digits and letters. It must not contain any spaces and cannot exceed 32 characters in length.
   Region	Select the region where the data source resides from the drop-down list. The region is the value that you set for the cm_region parameter when you deploy OMS. Notice This parameter is displayed only when multiple regions are available. Make sure that the mappings between the data source and the region are consistent. Otherwise, the migration and synchronization performance can be poor.
   Tenant Type	Select Oracle. The type of the OceanBase Database tenant. Valid values: Oracle and MySQL.
   OCP Cluster (Optional)	The OceanBase Cloud Platform (OCP) cluster associated with the data source. Select an OCP cluster from the drop-down list. Note Check whether the data source is managed in the selected OCP cluster. This parameter ensures that OMS obtains the incremental data of the OceanBase database. If you do not set this parameter, you cannot select Incremental Migration, Incremental Synchronization, or Reverse Incremental Migration. If the OCP cluster is not available in the list, click Add OCP Cluster and configure the parameters on the Add Associated OCP Cluster page.
   Connection Mode	Valid values: Manual Input and Command-line Connection String. Manual Input: Enter the public IP address and port number of the OceanBase database. Command-line Connection String: Enter a command-line connection string. This option is optional.
   Host IP Address	The IP address of the host where the database is located.
   Port	The port number of the host where the database is located.
   Tenant Name	The name of the OceanBase Database tenant.
   Cluster Name	The name of the cluster to which the OceanBase Database tenant belongs.
   Database Username	The username of the OceanBase database user for data migration or synchronization. We recommend that you create a dedicated database user for data migration or synchronization.
   Database Password	The password of the database user.
   Schema Name (Optional)	The name of the schema.
   Remarks (Optional)	Additional information about the data source.

   Advanced Options: In the New Data Source dialog box, click Show next to Advanced Options to set the parameters as needed.

   Parameter	Description
   Username	Optional. The user reads the incremental logs of OceanBase Database. You must create the user in the sys tenant.
   Password	Optional. The password of the drc_user user.
   Configurl	Optional. The actual IP address of the server that runs OceanBase Database. If you selected OCP Cluster, this parameter is not displayed. In this case, OMS obtains the configurl from the OCP cluster to which the server belongs. If you do not specify OCP Cluster, you can specify the Username and Password parameters, and then click Search in OBServer next to the ConfigUrl field to obtain the IP address from the corresponding OBServer node. If the query fails, you can run the show parameters like 'obconfig_url' command in the sys tenant of OceanBase Database. The returned value is the configurl. If OCP is not deployed, you must deploy the web service config server to access the OceanBase metadata.
   _OCEANBASE_INNER_DRC_USER Password	Optional. The admin user is used to migrate tables without unique keys in OceanBase Database. Notice You must create this user in the tenant to which the current data source belongs. This parameter is not displayed if you create a data source of OceanBase Database V4.0.0. In addition, data migration from a MySQL database to a MySQL tenant of OceanBase Database is supported only for OceanBase Database V4.0.0.

   • When you create a project to migrate or synchronize data from a physical OceanBase data source, you must perform the following operations:

     • For schema migration, schema synchronization, or incremental synchronization, specify the username and password. If you do not specify OCP Cluster, you must specify Configurl.
     • When you migrate a table without a unique key, you must enter the password of the _OCEANBASE_INNER_DRC_USER user.

   • When you create a project to migrate data to a physical OceanBase data source, you must specify the username and password for reverse incremental migration. If you do not specify OCP Cluster, you must specify Configurl.

   After you configure the parameters, click Test Connection to verify the network connection between OMS and the data source, as well as the validity of the username and password. After the connection test succeeds, click OK.

4. Create an Oracle data source as the destination data source of the migration project.

   Configure the following parameters.

   Parameter	Description
   Data Source Type	Select Oracle.
   Data Source Identifier	We recommend that you set it to a combination of digits and letters. It must not contain any spaces and cannot exceed 32 characters in length.
   Region	Select the region where the data source resides from the drop-down list. The region is the value that you set for the cm_region parameter when you deploy OMS. Notice This parameter is displayed only when multiple regions are available. Make sure that the mappings between the data source and the region are consistent. Otherwise, the migration and synchronization performance can be poor.
   Database Attributes	Select Primary Database. Valid values: Primary Database, Primary Database + Standby Database, and Standby Database. Note If you select Primary Database + Standby Database or Standby Database, you must specify the Active Data Guard (ADG) mode for the Oracle database. The ADG mode is specified for Oracle databases of a version later than 11g by default.
   Host IP Address	The IP address of the host where the database is located. You must specify the IP address of the physical server that hosts the Relational Database Service (RDS). Do not enter the IP address of any middleware.
   Port	The port number of the host where the database is located.
   Database Username	The name of the Oracle database user for data migration or synchronization. We recommend that you create a dedicated database user for the migration or synchronization project.
   Database Password	The password of the database user.
   Service Name	The service name of the Oracle database.
   Schema Name (Optional)	The schema name of the Oracle database.
   Remarks (Optional)	Additional information about the data source.

   After you configure the parameters, click Test Connection to verify the network connection between OMS and the data source, as well as the validity of the username and password. After the connection test succeeds, click OK.

Step 3: Create a full data migration project and start the project

1. On the Data Migration page, click Create Migration Project in the upper-right corner.

2. On the Select Source and Destination page, configure the following parameters.

   Parameter	Description
   Migration Project Name	We recommend that you set it to a combination of digits and letters. It must not contain any spaces and cannot exceed 64 characters in length.
   Tag (Optional)	Click the field and select a target tag from the drop-down list. You can also click Manage Tags to create, modify, and delete tags.
   Source	Select the created Oracle data source from the drop-down list.
   Destination	Select the data source created for an Oracle tenant of OceanBase Database from the drop-down list. Note The source cannot be an Oracle tenant of OceanBase Database V4.0.0.

   After you configure the parameters, click Next.

3. On the Select Migration Type page, configure the following parameters.

   Options for Migration Type are Schema Migration, Full Migration, Incremental Synchronization, Full Verification, and Reverse Incremental Migration.

   Migration type	Description
   Schema Migration	The definitions of data objects, such as tables, indexes, constraints, comments, and views, are migrated from the source database to the destination database. Temporary tables are automatically filtered out.
   Full Migration	If you select Full Migration, we recommend that you collect the statistics of the Oracle tenant of OceanBase Database before the data migration.
   Incremental Synchronization	Options for Incremental Synchronization are DML Synchronization and DDL Synchronization. You can select the operations as needed. If the source and destination databases use different character sets, OMS does not support modifications of schema fields. Incremental Synchronization has the following limitations: If you select DDL Synchronization, when you perform a DDL operation that cannot be synchronized by OMS in the source database, data migration may fail. If the DDL operation creates a new column, we recommend that you set the attribute of the column to Null. Otherwise, data migration may be interrupted.
   Full Verification	If you select Full Verification, we recommend that you collect the statistics of the Oracle tenant of OceanBase Database and use the GATHER_SCHEMA_STATS or GATHER_TABLE_STATS statement to collect the statistics of the Oracle database before the full verification. If you selected Incremental Synchronization but did not select all DML statements in DML Synchronization, OMS does not support full data verification in this case.
   Reverse Incremental Migration	For Oracle 12c or later, when you add or change a column, the table name and column name cannot exceed 30 bytes in length. If you want the database to support table names and column names of more than 30 bytes in length, specify the ENABLE_GOLDENGATE_REPLICATION parameter as the SYS user in the Oracle database, and set deliver2store.logminer.need_check_object_length=false for Oracle Store. Set ENABLE_GOLDENGATE_REPLICATION as follows: For a Real Application Cluster (RAC) environment, set this parameter for each node. If the Oracle database is in Active Data Guard (ADG) mode, set this parameter in the ADG source database. ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=true SCOPE=BOTH; Query ENABLE_GOLDENGATE_REPLICATION as follows. SELECT K.KSPPINM,V.KSPPSTVL FROM SYS.X$KSPPI K,SYS.X$KSPPSV V WHERE K.INDX=V.INDX AND UPPER(K.KSPPINM) = 'ENABLE_GOLDENGATE_REPLICATION'; You cannot select Reverse Incremental Migration in the following cases: Multi-table aggregation and synchronization is enabled. Multiple source schemas map to the same destination schema.

   Select Full Migration, specify the parameters, and then click Next.

4. On the Select Migration Objects page, select the migration objects and migration scope.

   You can select Specify Objects or Match Rules to specify the migration objects.

   If you select Specify Objects, select the objects to be migrated on the left and click > to add them to the list on the right. You can select tables and views of one or more databases as the migration objects.

   Notice

   • The names of tables to be migrated, as well as the names of columns in the tables, must not contain Chinese characters.
   • If the database or table name contains a double dollar sign ($$), you cannot create the migration project.

   When you migrate data from an Oracle database to a MySQL tenant of OceanBase Database, OMS allows you to import objects through text, rename object names, set row filters, view column information, and remove one or all objects to be migrated.

   Operation	Steps
   Import Objects	In the list on the right of the Specify Migration Scope section, click Import Objects in the upper-right corner. In the dialog box that appears, click OK. Notice This operation overwrites previous selections. Proceed with caution. In the Import Objects dialog box, import the objects to be migrated. You can import CSV files to rename databases/tables and set row filtering conditions. Click Validate. After the validation succeeds, click OK.
   Rename	OMS allows you to rename migration objects. Note When you rename an object in the Oracle tenant of OceanBase Database, the object name must be in uppercase.
   Settings	OMS allows you to use the WHERE clause to filter data by row. You can also view column information about the migration objects in the View Columns section.
   Remove/Remove All	During data mapping, OMS allows you to remove one or more selected objects to be migrated to the destination. Remove a single migration object: In the list on the right of the Specify Migration Scope section, move the pointer over the object that you want to remove, and then click Remove. Remove all migration objects: In the list on the right of the Specify Migration Scope section, click Remove All in the upper-right corner. In the dialog box that appears, click OK.

   After you configure the parameters, click Next.

5. On the Migration Options page, configure the parameters for full migration.

   Parameter	Description
   Concurrency for Full Migration	The value can be Smooth, Normal, or Fast. The number of resources to be consumed by a full data migration task varies based on the migration performance. You can define the concurrency for full migration by modifying the configurations of the Full-Import component. Note You can set this option only when Full Migration is selected on the Select Migration Type page.
   Whether to Allow Destination Table to Be Not Empty During Full Migration	If destination tables are allowed to be not empty during full migration, full verification is performed in IN mode. Note You can set this option only when Full Migration is selected on the Select Migration Type page.

   After you configure the parameters, click Precheck.

6. On the Precheck page, handle Failed check items.

   During the precheck, OMS checks the read and write privileges of the database users and the network connections of the databases. The data migration project can be started only after it passes all check items. If an error is returned during the precheck:

   • You can identify and troubleshoot the problem and then perform the precheck again.
   • You can also click Skip in the Actions column of a failed precheck item. In the dialog box that appears, you can view the prompt for the consequences of the operation and click OK.

   After the precheck succeeds, click Start Project.

   Note

   If you do not need to start the project now, click Save to go to the details page of the data migration project. You can start the project later as needed.

7. On the Migration Details page, click Start Forward Switchover.

8. Wait for the task to complete.

Step 4: View the data migration status

Log on to the Oracle database to check whether the data has been migrated.

View the data in the test_ob_to_oracle_backup.tbl1_backup table.

SQL> SELECT * FROM test_ob_to_oracle_backup.tbl1_backup;

Return result:

      COL1 COL2
---------- --------------------
         1 China
         2 Taiwan
         3 Hong Kong
         4 Macao
         5 North Korea

More information

• Migrate data from an Oracle tenant of OceanBase Database to an Oracle database
• What is OMS?
• Create a database user
• Create a physical OceanBase data source
• Create an Oracle data source