Oracle中部分不兼容对象迁移到OceanBase的三种处理方式

2024-03-29 12:07:45 来源/作者：这里教程网 /

1、背景介绍2、Oracle 中 LOB 类数据迁移到 OB 时的处理逻辑2.1 找到 Oracle 中 LOB 数据最大长度2.1.1 构建包含LOB类型的数据表2.1.2 创建造数据存储过程2.1.3 查询该表中 CLOB 和 BLOB 字段的最大值2.2 获取整个数据库中 LOB 字段值较大的清单3、Oracle 中 disable 约束在 OMS 迁移过程中的处理逻辑3.1 问题分析3.2 问题结论3.3 约束校验时提前排除 DISABLE 的约束4、Oracle 中分区表迁移到 OB 后，带有的自动分区属性丢失4.1 问题分析4.2 问题结论4.3 如何找出 Oracle 中自动分区的表4.3.1 Oracle 侧模拟自动分区4.3.2 统计 Oracle 侧有哪些表是自动分区的表总结关于 SQLE

1、背景介绍

在进行国产化改造过程中，我们需要将 Oracle 数据库迁移到 OceanBase（Oracle 模式）数据库，虽然 OceanBase 对于 Oracle 兼容性已经足够好，但依旧还有一些特殊语法或对象需要单独处理，下面是遇到的一些不完全兼容对象的处理逻辑。

2、Oracle 中 LOB 类数据迁移到 OB 时的处理逻辑

Oracle 中 CLOB 和 BLOB 类型均可达到 4G 大小（以 Oracle 11.2 为例），而 OceanBase 数据库当前版本（3.2.3.x）所支持的大对象数据类型的信息如下表所示：

类型BLOBCLOB长度变长变长自定义长度上限（字符）48MB48MB字符集BINARY与租户字符集一致

考虑到从 Oracle 迁移到 OceanBase，如果涉及 LOB 类字段，可能会存在当 LOB 数据大于 48M 时数据丢失的问题，需要提前发现这类数据并进行处理。

2.1 找到 Oracle 中 LOB 数据最大长度

我们可以构建一个实验生成 CLOB 及 BLOB 类型数据，使用 Oracle 自带的 DBMS_LOB 包获取对应类型的最大值。

2.1.1 构建包含LOB类型的数据表

CREATE TABLE t_lob( c_ID NUMBER, c_clob CLOB, c_blob BLOB );

2.1.2 创建造数据存储过程

随机插入 100 条记录到 t_lob 表。

CREATE OR REPLACE PROCEDURE insert_random_lob_data AS BEGIN DECLARE l_random_string VARCHAR2(10000); l_random_blob BLOB; BEGIN FOR i IN 1..100 LOOP l_random_string := dbms_random.string('U', dbms_random.value(1, 10000)); dbms_lob.createtemporary(l_random_blob, TRUE); dbms_lob.writeappend(l_random_blob, LENGTH(l_random_string), utl_raw.cast_to_raw(l_random_string)); INSERT INTO t_lob(c_ID, c_clob, c_blob) VALUES(i, l_random_string, l_random_blob); dbms_lob.freetemporary(l_random_blob); END LOOP; COMMIT; END; END; /

2.1.3 查询该表中 CLOB 和 BLOB 字段的最大值

SELECT MAX(DBMS_LOB.GETLENGTH(C_CLOB)) AS LONGEST_CLOB, MAX(DBMS_LOB.GETLENGTH(C_BLOB)) AS LONGEST_BLOB FROM T_LOB;

2.2 获取整个数据库中 LOB 字段值较大的清单

排除了系统用户，获取 LOB 字段清单后再基于清单中的 LOB 字段单独分析其最大值。

SELECT COL.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME, COL.DATA_TYPE, COL.AVG_COL_LEN, COL.CHAR_LENGTH, TAB.NUM_ROWS FROM DBA_TABLES TAB, DBA_TAB_COLUMNS COL WHERE TAB.OWNER = COL.OWNER AND TAB.TABLE_NAME = COL.TABLE_NAME AND COL.DATA_TYPE IN ('CLOB', 'BLOB') AND COL.OWNER NOT IN ('SYS', 'SYSTEM') AND COL.OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN') AND COL.TABLE_NAME NOT LIKE 'BIN%';

3、Oracle 中 disable 约束在 OMS 迁移过程中的处理逻辑

在对 Oracle 中的约束类非表对象做一致性校验时，发现部分约束在 OMS 迁移完成后丢失了，需要分析其 OMS 丢失的原因。

3.1 问题分析

从 OMS 界面中获取 DDL 的语句可以看到有 2 个 WARN，且类型是 DISCARD，表示 OMS 判断其是 DISABLE 状态的约束，直接选择了舍弃掉。

-- [WARN] [DISCARD] CONSTRAINT "PK_T_PARTKEY_IS_PK" PRIMARY KEY ("CRT_DTTM") DISABLE NOVALIDATE -> [NULL] -- [WARN] [DISCARD] CHECK ("ACT_ID" IS NOT NULL) DISABLE NOVALIDATE -> [NULL] CREATE TABLE "T_PARTKEY_IS_PK" ( "ACT_ID" NUMBER(10,0), "SRT_ID" NUMBER(10,0), "SRT_ORIGNAL_ID" NUMBER(10,0), "CRT_DTTM" DATE, "LASTUPT_DTTM" DATE )

3.2 问题结论

Oracle 侧处于 DISABLE 状态的约束通过 OMS 迁移时会被舍弃，不会在 OB 侧创建，在对约束对象比对时，需要额外注意 Oracle 端约束的 status 是否处于 DISABLE 状态，本身对业务和功能没有影响。

3.3 约束校验时提前排除 DISABLE 的约束

可以通过以下语句观测源端 Oracle 约束状态。

-- 手工将T_PARTKEY_IS_PK表的约束都disable ALTER TABLE ZHENXING.T_PARTKEY_IS_PK DISABLE NOVALIDATE CONSTRAINT PK_T_PARTKEY_IS_PK; ALTER TABLE ZHENXING.T_PARTKEY_IS_PK DISABLE CONSTRAINT SYS_C0011109; SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME, STATUS FROM DBA_CONSTRAINTS WHERE OWNER = 'ZHENXING' AND TABLE_NAME = 'T_PARTKEY_IS_PK';

4、Oracle 中分区表迁移到 OB 后，带有的自动分区属性丢失

自动分区属性是 Oracle 11g 的特性，可以用 INTERVAL 语法基于天、月、年做自动分区创建。在通过 OMS 迁移到 OB 后，发现自动分区属性丢失了，会导致当分区未自动创建时导致新增数据没法写入分区表，导致报错。

4.1 问题分析

从 OMS 界面中获取 DDL 的语句可以看到有 1 个 WARN，且类型是 DISCARD，表示 OMS 判断其不完全兼容，直接选择了舍弃掉。

-- OMS 迁移表结构时记录的WARN信息，表示自动分区属性由于不兼容会自动DISCARD舍弃 [WARN] [DISCARD] INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) -> [NULL]

4.2 问题结论

所以在 Oracle 迁移到 OB 前，需要把 Oracle 端存在自动分区属性的表提前找出，避免由于迁移到 OB 后分区为未自动创建导致的数据无法插入的报错，并且找出这类分区后，先在 Oracle 端创建足够的多分区，避免迁移过程中源端分区数增加导致比对不一致的情况。并记录清单告知业务开发待后续用其他方式定期生成新分区。

4.3 如何找出 Oracle 中自动分区的表

4.3.1 Oracle 侧模拟自动分区

-- 创建基于天的自动分区表 SQL> create table interval_sales ( prod_id number(6), time_id date) partition by range (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (partition p1 values less than (to_date('2015-01-01','yyyy-mm-dd'))); -- 查询当前分区，默认生成了1个定义好的分区 SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'INTERVAL_SALES'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVAL_SALES P1 -- 插入数据（不在默认分区内） SQL> INSERT INTO INTERVAL_SALES VALUES(001, TO_DATE('2015-02-01', 'yyyy-mm-dd')); -- 自动生成了新分区 TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVAL_SALES P1 INTERVAL_SALES SYS_P221 -- 单独查看该分区数据（验证数据确实存在新分区） SQL> SELECT * FROM INTERVAL_SALES PARTITION(SYS_P221); PROD_ID TIME_ID ---------- --------- 1 01-FEB-15

4.3.2 统计 Oracle 侧有哪些表是自动分区的表

/* PARTITION_COUNT: Number of partitions in the table. For interval partitioned tables, the value of this column is always 1048575. */ SELECT T1.OWNER, T1.TABLE_NAME, T1.INTERVAL, T1.PARTITIONING_TYPE, T1.PARTITION_COUNT, T1.SUBPARTITIONING_TYPE AS SUB_TYPE, T1.SUBPARTITIONING_KEY_COUNT SUB_COUNT, T1.STATUS FROM DBA_PART_TABLES T1 WHERE 1 = 1 AND TABLE_NAME NOT LIKE 'BIN%' AND (INTERVAL IS NOT NULL OR PARTITION_COUNT = 1048575);