如何获取 Oracle 数据库中和 OceanBase 数据库不兼容的 DATE 数据

迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户时，不兼容的 DATE 类型数据容易触发 OMS 的异常。本文为您介绍发生该问题的原因和解决方法。

适用版本

适用于 OceanBase 迁移服务（OceanBase Migration Service，OMS）全部版本。

问题描述

DATE 是 Oracle 数据库中的常用数据类型。迁移 Oracle 数据库的数据至 OceanBase 数据库 Oracle 租户时，公元前日期数据以及其它违反 Oracle 数据库格式要求的数据将不同程度影响数据的迁移。

问题原因

​目前 OceanBase 数据库 Oracle 租户不支持公元前的数据记录，仅能与开发商协调寻找解决方案。例如，将其修改为公元后的数据、修改字段类型等。

解决方法

对于违反 Oracle 数据库格式的数据，OMS 在全量迁移期间会自动将其转换为合法的数据，在增量同步期间则会触发 ORA- 报错导致异常。此时，您只能通过 skipErrorCode 参数临时规避，并在割接时补录数据。 ​ 本文提供一种方法，直接从 Oracle 数据库查询 OceanBase 数据库 Oracle 租户不兼容/触发异常的 DATE 类型记录。

OceanBase 数据库 Oracle 租户目前仅支持取值范围为 0001-01-01 00:00:00~9999-12-31 23:59:59 之间的记录，暂不支持公元前的记录。因此，在数据割接中，仅能通过修改数据，或者修改目标端字段类型等方法进行处理。 ​ 除此以外，由于 Oracle 数据库本身的缺陷，源端数据库中的 DATE 类型记录可能是乱码。该部分记录在通过 OMS 增量同步至 OceanBase 数据库 Oracle 租户的过程中，将触发不同的 ORA- 报错，进而导致进程的异常。

典型的异常数据及其触发的告警示例如下。

ORA-报错
 0000-00-00 00:00:00
ORA-1841: (full) year must be between -4713 and +9999, and not be 0
 2099-12-00 00:00:00
ORA-1847: day of month must be between 1 and last day of monthORA-1847: day of month must be between 1 and last day of month
 2007-11-21 24:00:00
ORA-1850: hour must be between 0 and 23
 2013-09-31 00:00:00
ORA-1861: literal does not match format string
-0975-05-11 08:21:47
ORA-1858: a non-numeric character was found where a numeric was expected

对于该类问题，为了保证 OMS 增量进程的正常同步，您可以跳过错误码来临时规避，以确保进程的正常运行。但该操作将导致数据丢失的问题，在正式生产割接时，您需要进行检查和补充。

• 如果是 OMS V3.3.1 及之后版本，您可以设置 JDBCWriter 实时同步组件参数 JDBCWriter.sinkFile.skipErrorCode 的值为需要跳过的错误码。例如，将其设置为 ["1841","1847","1861","1850","1858"]。

• 如果是数据同步或者 OMS V4.0.1 及之后版本，您可以在 更新配置 对话框的 Sink scope 中更新 skipErrorCode 的值为 ["1841","1847","1861","1850","1858"]。

  ​

与增量同步时触发 ORA- 报错不同，OMS 的全量同步自带修复数据逻辑，会将异常的 DATE 记录修复为 OceanBase 数据库兼容的日期。修复前后的示例如下。

由上述修复前后的示例可见，JDBC 在没有 session 符号位的修复逻辑为自动进行加减位运算，以确保各个位置的值均合法（OMS V4.2.0 及之后版本不再使用该逻辑）。0000-00-00 00:00:00 数据变为 0002-11-30 00:00:00，是因为 OceanBase 数据库 Oracle 租户并不支持公元前的数据。

当然，OMS 全量迁移的自动修复逻辑并不能完全解决问题。

• 自动修复会导致月份甚至年份的变更。例如，2013 年的数据变成 2020 年（最极端的示例）。

• 0000-00-00 00:00:00 是占比很大的异常记录，但修复后的 0002-11-30 00:00:00 可能无业务含义。

• OMS 的校验进程仍然为将自动修复后的数据视为不一致。

综合上述情况，从源端 Oracle 数据库找出 OceanBase 数据库 Oracle 租户不兼容或者触发异常的 DATE 类型记录，并协调开发商进行数据修复，才能彻底解决问题。 ​ Oracle 数据库的函数以及查找的语句的参考如下。

• Oracle 数据库的函数

  CREATE OR REPLACE FUNCTION "OMSDBA"."IS_INVALID_DATE_FOR_OMS" (i_date date) return varchar2
  IS
  o_date date;
  begin
      o_date:=to_date(to_char(i_date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
      if i_date < to_date('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  then
          return 'TRUE';
      end if;
      return 'FALSE';
  exception
      when others then
      return 'TRUE';
  end IS_INVALID_DATE_FOR_OMS;
  /
  

• Oracle 数据库查找乱码数据的参考 SQL 脚本

  SET tim ON timing ON echo ON numwidth 30 lin 400 pagesize 50000 trimspool ON
  SELECT 'Spe'||'Record^'||'NGCRM_XX'||'^'||'XXXX_PRODUCT'||'^'||ora_rowid||'^'||
  substr(spe_record,2) spe_record from (
    SELECT /*+parallel(a,32) */ rowid ora_rowid,
      decode(OMSDBA.IS_INVALID_DATE_FOR_OMS("STARTDATE"),'FALSE','',',STARTDATE^DateEr')||
      decode(OMSDBA.IS_INVALID_DATE_FOR_OMS("ENDDATE"),'FALSE','',',ENDDATE^DateEr')||
      '' SPE_RECORD
    FROM "NGCRM_XX"."XXXX_PRODUCT" a)
  WHERE spe_record IS NOT NULL;
  exit