Schema migration mechanisms

OceanBase Migration Service (OMS) will provide a schema migration mechanism based on the actual situation of the data migration project.

Background

When you advance to the forward switchover step in a data migration project, OMS will automatically drop the hidden columns and unique indexes based on the type of the data migration project.

• When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, OMS will add hidden columns to all tables in the destination database, and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped during the forward switchover step.

• For data migration projects of the types listed in the following table except for the migration from an Oracle database to an Oracle tenant of OceanBase Database, to migrate tables without a unique key, OMS will add a hidden column to tables in the destination database and create a unique index based on the hidden column. The corresponding hidden column and index will be automatically deleted in the forward switchover step.

If you do not advance to the forward switchover step in a data migration project, you must manually drop the hidden columns and unique indexes based on the type of the data migration project as guided by this topic.

Prerequisites

• "{partition_col_0}" or {partition_col_0} indicates a table partitioning key column. Multiple such fields may exist. Separate multiple fields with commas (,), Example: "{partition_col_0}", "{partition_col_1}","{partition_col_2}" or `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`.

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

Data migration from an Oracle database to an Oracle tenant of OceanBase Database

When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, OMS will add hidden columns and create unique indexes for the tables, regardless of whether they have primary keys.

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

  // Partitioning key columns are 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 partitioning key column 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");
  

Data migration between MySQL tenants of OceanBase Database

When you migrate data between MySQL tenants of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

Notice

In active-active disaster recovery scenarios, you can migrate only tables with unique keys.

You can execute the following statement to check whether a table has a unique key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no unique key.

SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA = {schema} AND TABLE_NAME = {table} GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING count(1) = count(IF(upper(nullable) != 'YES' and NON_UNIQUE = 0, 1, NULL));

• Add columns.

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

• Add unique indexes.

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`OMS_PK_INCRMT`, `{partition_col_0}`, `{partition_col_1}`) LOCAL;
  // No partitioning key column 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 hidden columns.

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

Data migration between Oracle tenants of OceanBase Database

When you migrate data between Oracle tenants of OceanBase Database, OMS will add hidden columns and create unique indexes for tables without primary keys.

Notice

In active-active disaster recovery scenarios, you can migrate only tables with unique keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• Add hidden columns.

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

• Add unique indexes.

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT", "{partition_col_0}", "{partition_col_1}") LOCAL;
  // No partitioning key column 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 an Oracle tenant of OceanBase Database to an Oracle database

When you migrate data from an Oracle tenant of OceanBase Database to an Oracle database, OMS will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

• Add hidden columns.

  // Oracle Database 12C or later
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
  // Oracle Database earlier than 12C
  ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;
  

• Add unique indexes.

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT", "{partition_col_0}", "{partition_col_1}") LOCAL;
  // No partitioning key column 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 an Oracle database to a MySQL tenant of OceanBase Database

When you migrate data from an Oracle database to a MySQL tenant of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

You can execute the following statement to check whether a table has a unique key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no unique key.

select 1 from ALL_CONS_COLUMNS DCC join ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER join ALL_TAB_COLUMNS DTC on DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME where DCC.owner = {schema} AND DCC.TABLE_NAME = {table} and DC.constraint_type in ('U', 'P') group by DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME having count(*) = count(CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END);

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

  // Partitioning key columns are available.
  CREATE UNIQUE INDEX `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 partitioning key column is available.
  CREATE UNIQUE INDEX `UK_{table}_OMS_ROWID` ON `{schema}`.`{table}` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`);
  

• Drop unique indexes.

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

• Drop hidden 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`;