Hidden column mechanisms

During schema migration or incremental synchronization DDL, OceanBase Migration Service (OMS) provides corresponding mechanisms based on the actual situation of the data migration task.

Background information

To ensure data consistency, OMS will add hidden columns to all tables in the target database and create unique indexes based on the hidden columns during schema migration or incremental synchronization DDL. These columns and indexes will be automatically dropped during the forward switchover step. The following table lists the supported source, target, and table types. For more information about how to query tables with primary keys or non-null unique keys in different data sources, see Query table objects.

For data migration tasks that support adding hidden columns to tables without non-null unique keys, OMS V4.3.0 and later provide the Add Hidden Columns for Tables Without Non-null Unique Keys option in the Migration Options > Advanced Options step. You can choose whether to add hidden columns to ensure the consistency of migration for tables without non-null unique keys.

Data migration from an Oracle database to the Oracle compatible mode of OceanBase Database

• Add hidden columns

  ALTER TABLE "{schema}"."{table}" ADD "OMS_OBJECT_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_RELATIVE_FNO" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_BLOCK_NUMBER" NUMBER INVISIBLE;
  ALTER TABLE "{schema}"."{table}" ADD "OMS_ROW_NUMBER" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partition key is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER", "{partition_col_0}", "{partition_col_1}") LOCAL;
  // No partition key is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");
  

• Drop unique indexes

  DROP INDEX "{schema}"."UK_{table}_OMS_ROWID";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");
  

Migrate data between MySQL-compatible tenants of OceanBase Database

• Add columns

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_PK_INCRMT` BIGINT;
  

• Add unique indexes

  // A partition key is available.
  CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
  // No partition key is available.
  CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`OMS_PK_INCRMT`);
  

• Drop unique indexes

  DROP INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}`;
  

• Drop columns

  ALTER TABLE `{schema}`.`{table}` DROP COLUMN `OMS_PK_INCRMT`;
  

Data migration between Oracle-compatible tenants of OceanBase Database

• Add hidden columns

  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partition key is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partition key is available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");
  

• Drop unique indexes

  DROP INDEX "{schema}"."UK_{table}_OBPK_INCRMT";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";
  

Data migration from the Oracle compatible mode of OceanBase Database to an Oracle database

• Add hidden columns

  // Versions earlier than Oracle 12c
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
  // Oracle 12c and later
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;
  

• Add unique indexes

  // A partition key is available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
  // No partition key is available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT");
  

• Drop unique indexes

  DROP INDEX "{schema}"."{table}";
  

• Drop hidden columns

  ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";
  

Data Migration from Oracle Database to the MySQL compatible mode of OceanBase Database

• Add columns

  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_OBJECT_NUMBER` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_RELATIVE_FNO` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_BLOCK_NUMBER` BIGINT;
  ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_ROW_NUMBER` BIGINT;
  

• Add unique indexes

  // A partition key is available.
  CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`, `{partition_col_0}`, `{partition_col_1}`) LOCAL;
  // No partition key is available.
  CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`);
  

• Drop unique indexes

  DROP INDEX UK_{table}_OMS_ROWID ON {table};
  

• Drop columns

  ALTER TABLE `{schema}`.`{table}`  DROP COLUMN `OMS_OBJECT_NUMBER`, DROP COLUMN `OMS_RELATIVE_FNO`, DROP COLUMN `OMS_BLOCK_NUMBER`, DROP COLUMN `OMS_ROW_NUMBER`;
  

FAQ

How do I handle partitions

• "{partition_col_0}" or `{partition_col_0}` is a partition key of the table. There can be multiple such columns. Separate the names of the columns with commas (,). For example, "{partition_col_0}", "{partition_col_1}","{partition_col_2}" or `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`.

• {schema} and {table} are placeholders for the database name and table name, respectively. {partition_col_0} and {partition_col_1} are placeholders for the partition key names.

How do I set the language for comments

To set the language for comments when OMS creates hidden columns, perform the following steps:

1. Log in to the OMS container.

   docker exec -it ${CONTAINER_NAME} bash
   

   Notice

   CONTAINER_NAME is the name of the container that you created.

2. In the /home/admin/conf/rm/ directory, set the value of the default.language parameter in the application-oms.properties file.

   • If the default.language parameter is not configured or its value is zh-CN, comments on hidden columns in tables without primary keys that are migrated in terms of structure or synchronized incrementally by DDL operations are displayed in Chinese.

   • If the value of the default.language parameter is en-US, comments on hidden columns in tables without primary keys that are migrated in terms of structure or synchronized incrementally by DDL operations are displayed in English.

3. Restart the console.

   supervisorctl restart oms_console
   

4. Synchronize the modification of the default.language value in /home/admin/conf/rm/application-oms_template.properties. Otherwise, if you restart the OMS container, the default.language value in the application-oms.properties file will be reset to the default value.

References

• Migrate data from an Oracle database to the Oracle compatible mode of OceanBase Database

• Migrate data between OceanBase databases of the same tenant type

• Migrate data from the Oracle compatible mode of OceanBase Database to an Oracle database

• Migrate data from an Oracle database to the MySQL compatible mode of OceanBase Database