1、背景介绍
在进行国产化改造过程中,我们需要将 Oracle 数据库迁移到 OceanBase(Oracle 模式)数据库,虽然 OceanBase 对于 Oracle 兼容性已经足够好,但依旧还有一些特殊语法或对象需要单独处理,下面是遇到的一些不完全兼容对象的处理逻辑。
2、Oracle 中 LOB 类数据迁移到 OB 时的处理逻辑
Oracle 中 CLOB 和 BLOB 类型均可达到 4G 大小(以 Oracle 11.2 为例),而 OceanBase 数据库当前版本(3.2.3.x)所支持的大对象数据类型的信息如下表所示:
类型 | BLOB | CLOB |
---|---|---|
长度 | 变长 | 变长 |
自定义长度上限(字符) | 48MB | 48MB |
字符集 | 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);
总结
以上总结分析了 3 种 Oracle 对象和 OB 对象不兼容时的处理方法和提前统计发现的操作方式,在迁移前提前发现这类问题能有效避免在迁移过程中报错的问题。
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。