精进不休丨存储过程代码超过百万行的 Oracle 数据库迁移，MogDB 这样就搞定了？...

云和恩墨

于 2024-08-12 17:20:13 发布

阅读量60

点赞数

文章标签：数据库 oracle

原文链接：https://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650637532&idx=1&sn=a9c37262cc6a93d8972254f114ba4186&chksm=bfc8294e4b93f38b3882846e3347a988e4463f85be99971f0e5c0e4f7d2722a8a2f9cb9dafc8&scene=126&sessionid=0

版权

我们知道，数据库国产化改造有几个比较大的难点，其中最让人头疼的莫过于存储过程之类的对象了。前不久部门同事参与了一个关键客户的国产数据库PoC工作，从反馈回来的迁移报告看，这算得上是较为复杂的一个去“O” 的国产化改造项目之一，因此我在这里跟大家做个分享。

90+%自动化迁移成功率

首先让我们来看看 MogDB MTK报告，这里我截图并打码分享一下部分数据截图：

大家可以看到，这个 Oracle 数据库还是非常复杂的，有2747个存储过程。通过 MogDB MTK工具进行迁移之后，有188个存储过程迁移失败，也就是说这188个存储目前暂时无法通过 MogDB MTK工具来实现代码的自动转换。

实际上我们可以看到，这188个中有37个存储过程在源库就是异常状态，那么去掉这37个，实际上迁移失败的可理解为只有188-37=151个存储过程。

那么151/(2747-37)=5.5%，也就是说我们通过 MogDB MTK工具基本上实现了该数据库存储过程94.5%的自动化迁移改造。大家也可以理解为这个版本的 MogDB 对于该用户数据库来讲，存储过程的兼容度在94.5%。

同时我们可以发现这个库除了存储过程之外，还有206个函数，其中源库失效的有5个，一共还有13个函数迁移失败了。这样大致算一下，函数的迁移成功率大约是93.6%。

此外大家也可以看到对于视图的迁移改造，MogDB MTK也能很好地完成代码自动化改写，成功比例也在90%以上。

对于引用到的一些自定义函数，MogDB 暂不支持的话，我们后续通过代码改写基本上都可以实现100%的国产化改造，这里分享一下本案例中的改写部分内容。

MogDB 自定义函数改写

++原Oracle函数
CREATE OR REPLACE TYPE UI_FI.TYPE_LIST_AGG AS OBJECT

(
    TOTAL VARCHAR2(4000),
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPE_LIST_AGG) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPE_LIST_AGG, VALUE IN VARCHAR2)
        RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN TYPE_LIST_AGG,
                                           RETURNVALUE OUT VARCHAR2,
                                           FLAGS       IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPE_LIST_AGG, CTX2 IN TYPE_LIST_AGG)
        RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY UI_FI.TYPE_LIST_AGG IS
    
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPE_LIST_AGG) RETURN NUMBER IS
    BEGIN
        SCTX := TYPE_LIST_AGG(NULL);
        RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPE_LIST_AGG, VALUE IN VARCHAR2)
        RETURN NUMBER IS
    BEGIN
        SELF.TOTAL := SELF.TOTAL || VALUE;
        RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN TYPE_LIST_AGG,
                                           RETURNVALUE OUT VARCHAR2,
                                           FLAGS       IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURNVALUE := SUBSTR(SELF.TOTAL, 2);
        RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPE_LIST_AGG, CTX2 IN TYPE_LIST_AGG)
        RETURN NUMBER IS
    BEGIN
        SELF.TOTAL := SELF.TOTAL || CTX2.TOTAL;
        RETURN ODCICONST.SUCCESS;
    END;
END;
/

CREATE OR REPLACE FUNCTION UI_FI.F_CHANGE_COL_TO_ROW(INPUT VARCHAR2) RETURN VARCHAR2
    PARALLEL_ENABLE
    AGGREGATE USING UI_FI.TYPE_LIST_AGG;
/   

++ MogDB 改写的函数

CREATE OR REPLACE FUNCTION ui_fi.F_CHANGE_COL_TO_ROW_state_func (results VARCHAR2, val VARCHAR2)
        RETURNS VARCHAR2
        LANGUAGE sql COST 50 IMMUTABLE
        AS $$ select results || val; $$;

CREATE OR REPLACE FUNCTION ui_fi.F_CHANGE_COL_TO_ROW_final_func (results VARCHAR2)
        RETURNS VARCHAR2
        LANGUAGE sql COST 111 IMMUTABLE
        AS $$ select substr(results, 2)::VARCHAR2; $$;

CREATE AGGREGATE ui_fi.F_CHANGE_COL_TO_ROW(VARCHAR2)
        (
          sfunc = ui_fi.F_CHANGE_COL_TO_ROW_state_func,
          stype = VARCHAR2,
          initcond = '',
          FINALFUNC = ui_fi.F_CHANGE_COL_TO_ROW_final_func
        );

MogDB pipeline函数改写

这里我们完全仿照了客户生产环境代码的相关功能，给一个测试用例，具体生产代码就不展示了。

++ Oracle端

create TABLE t_test_pipeline(c1 varchar2(20),c2 number);

create type ty_test_pipeline as object(c1 varchar2(20),c2 number);
/
create type tyt_test_pipeline as table of ty_test_pipeline;
/

create or replace function f_test_pipeline(i number) 
return tyt_test_pipeline --table of 类型
PIPELINED is
 PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
 TYPE CUR1 IS REF CURSOR;
 MYCURSOR      CUR1;
 SQL_1 VARCHAR2(4000);
 SQL_2 VARCHAR2(4000);
 OUT_REC       ty_test_pipeline := ty_test_pipeline(NULL,NULL);--构造空对象
begin 
 EXECUTE IMMEDIATE 'TRUNCATE TABLE t_test_pipeline'; --有truncate table 注意ddl 自动commit的行为
insert into t_test_pipeline values ('a',1);
insert into t_test_pipeline values ('b',2);
insert into t_test_pipeline values ('c',2);--模拟两行以上数据
insert into t_test_pipeline values ('d',2);
commit;  --有commit
SQL_1:='SELECT * FROM t_test_pipeline WHERE C2=1';
SQL_2:='SELECT * FROM t_test_pipeline WHERE C2=2';

if i =1 then  --判断逻辑，打开不同的游标
 OPEN MYCURSOR FOR SQL_1;
 ELSE
 OPEN MYCURSOR FOR SQL_2;
end if;

 LOOP
        FETCH MYCURSOR
            INTO OUT_REC.C1,
                 OUT_REC.C2;
        EXIT WHEN MYCURSOR%NOTFOUND;
        PIPE ROW(OUT_REC);
    END LOOP;
    CLOSE MYCURSOR;
    RETURN;
end;
/

--调用
SELECT * FROM TABLE(f_test_pipeline(1));

SELECT * FROM TABLE(f_test_pipeline(2));

++ MogDB 改写后

create TABLE t_test_pipeline(c1 varchar2(20),c2 number);

create type ty_test_pipeline as object(c1 varchar2(20),c2 number);
/
create type tyt_test_pipeline as table of ty_test_pipeline;
/
create or replace function f_test_pipeline(i number) 
return  tyt_test_pipeline --table of 类型
 is
 PRAGMA AUTONOMOUS_TRANSACTION; --自治事务
 TYPE CUR1 IS REF CURSOR;
 MYCURSOR      CUR1;
 SQL_1 VARCHAR2(4000);
 SQL_2 VARCHAR2(4000);
 OUT_TABLE tyt_test_pipeline;
begin 
 EXECUTE IMMEDIATE 'TRUNCATE TABLE t_test_pipeline'; --有truncate table 注意ddl 自动commit的行为
insert into t_test_pipeline values ('a',1);
insert into t_test_pipeline values ('b',1);
insert into t_test_pipeline values ('c',2);--模拟两行以上数据
insert into t_test_pipeline values ('d',2);
commit;  --有commit
SQL_1:='SELECT * FROM t_test_pipeline WHERE C2=1';
SQL_2:='SELECT * FROM t_test_pipeline WHERE C2=2';

if i =1 then  --判断逻辑，打开不同的游标
 OPEN MYCURSOR FOR SQL_1;
 ELSE
 OPEN MYCURSOR FOR SQL_2;
end if;

 fetch MYCURSOR bulk collect into OUT_TABLE;--将循环fetch改成了一次性fetch
 CLOSE MYCURSOR;
return OUT_TABLE;
end;
/
--调用
SELECT * FROM TABLE(f_test_pipeline(1));

SELECT * FROM TABLE(f_test_pipeline(2));

关于 MogDB MTK迁移工具

MTK全称为Database Migration Toolkit，是一个可以将 Oracle、DB2、MySQL、openGauss、SQL Server、Informix 数据库的数据结构、全量数据高速导入到 MogDB 的工具。最新版本同时支持对于 Oracle、MySQL、DB2 数据库中存储过程、函数、触发器等程序段的 MogDB 兼容性改写和导入。

多数据库类型支持

支持 Oracle、DB2、SQL Server、MySQL、Informix、PostgreSQL 到 MogDB 数据库的迁移。
支持将数据库内容导出成可执行的SQL脚本（源数据库内容迁移到文本）。

迁移性能调整

支持调整数据迁移过程中的批量查询、批量插入大小等细粒度参数，来调整数据迁移的性能。
支持数据迁移时的多并发，并行和数据分片。

结构和数据分离

支持迁移对象结构和数据；也支持仅迁移结构或者仅迁移数据（在结构已经迁移完之后）。
支持表级和Schema级的迁移范围限定，允许指定Schema下全部对象或者某些对象进行迁移。
支持迁移过程中的Schema重映射，也就是支持将对象从源Schema迁移到目标端的不同名Schema下。

程序迁移

支持 Oracle、MySQL、DB2 到 MogDB 或 openGauss 的存储过程、函数、触发器、包迁移并对语法进行改写。

迁移场景

源数据库 目标数据库
Oracle       MogDB
Oracle       openGauss
Oracle       MySQL
Oracle       PostgreSQL
MySQL        MogDB
MySQL        openGauss
MySQL        PostgreSQL
DB2          MogDB
DB2          openGauss
DB2          MySQL
DB2          PostgreSQL
SqlServer    MogDB
SqlServer    openGauss
SqlServer    MySQL
SqlServer    PostgreSQL
PostgreSQL   MogDB
PostgreSQL   openGauss
Informix     MogDB
Informix     openGauss

实际上本案例在我们所服务的客户群体中并不算最复杂的，之前我所经手的某案例比这个要复杂得多，有超过3000个存储过程，存储过程代码行数之和超过300万行。我们基于这些超复杂的真实应用场景，不断打磨产品能力，在即将发布的 MogDB 5.2版本中将进一步大幅提升 Oracle 兼容性，同时支持百万级存储过程的并发支撑能力，大家拭目以待吧！

关于作者

李真旭，网名Roger，前Oracle ACE，拥有十多年的Oracle运维管理使用经验，参与过众多运营商、金融客户的大型数据库交付项目，具有丰富的运维管理经验；对数据库管理运行机制、锁机制、优化机制等具有深入理解，擅长数据库的performance tunning、troubleshooting以及异常恢复，帮助众多中大型客户解决了无数疑难问题，累计恢复的数据总量超过1个PB。

END

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。

自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库云管和数据智能分析等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。