问题描述

早上对KFS同步链路巡检时发现KES到Oracle这条链路中目标端同步服务离线，KFS管控平台中显示批处理中出现错误: ORA-00923: 未找到要求的 FROM 关键字，以下是分析过程。

分析过程

查看同步日志

cd /topsoft/kfs/replicator/flysync/flysync-replicator/log
tail -300f fsrepsvc.log

输出如下：

2025-05-20 17:22:17 |                | [target_oracle_3112 - pool-2-thread-1-q-to-dbms-0] ERROR applier.JdbcApplier Statement failed:    create or replace view BZGSV5_ICIS_ALTERPUNISHINFO as  
 SELECT bg.casealtid AS id,
    wfcf.pendecno,
    (bg.alt)::character varying(1000 char) AS alt,
    bg.altdate,
    cast(dsr.pripid as number(19,0)) AS marprid,
    bg.type AS busitype,
    bg.caseid,
    cast(bg.penauth as number(19,0)) AS penorgid,
    bg.penauth_cn AS penauth,
    bg.s_ext_datatime AS "timestamp"
   FROM ((case_pub_alter bg
     LEFT JOIN case_cf_partyinfo dsr ON (((dsr.caseid)::text = (bg.caseid)::text)))
     LEFT JOIN case_cf_irregpunishinfo wfcf ON (((wfcf.caseid)::text = (bg.caseid)::text)))
  WHERE ((wfcf.pendecissdate)::timestamp without time zone >= (to_date('2014-10-01'::text, 'yyyy-mm-dd'::text))::timestamp without time zone)
UNION ALL
 SELECT qt.casealtid AS id,
    NULL::varchar AS pendecno,
    (qt.alt)::character varying(1000 char) AS alt,
    qt.altdate,
    ( SELECT cast(e_ot_case.pripid as number(19,0))
           FROM e_ot_case
          WHERE (((e_ot_case.caseid)::text = (qt.caseid)::text) AND (ROWNUM = 1))) AS marprid,
    '01'::varchar AS busitype,
    qt.caseid,
    cast(NULL as number(19,0)) AS penorgid,
    qt.penauth,
    qt.s_ext_datatime AS "timestamp"
   FROM e_ot_casalt qt
2025-05-20 17:22:17 |                | [target_oracle_3112 - pool-2-thread-1-q-to-dbms-0] ERROR applier.JdbcApplier This error may be unexpected
com.kingbase.flysync.replicator.applier.ApplierException: java.sql.BatchUpdateException: 批处理中出现错误: ORA-00923: 未找到要求的 FROM 关键字

	at com.kingbase.flysync.replicator.applier.JdbcApplier.applyStatementData(JdbcApplier.java:982)
	at com.kingbase.flysync.replicator.applier.OracleApplier.applyStatementData(OracleApplier.java:1319)
	at com.kingbase.flysync.replicator.applier.JdbcApplier.apply(JdbcApplier.java:2604)
	at com.kingbase.flysync.replicator.applier.ApplierWrapper.apply(ApplierWrapper.java:99)
	at com.kingbase.flysync.replicator.pipeline.SingleThreadStageTask.apply(SingleThreadStageTask.java:963)
	at com.kingbase.flysync.replicator.pipeline.SingleThreadStageTask.runTask(SingleThreadStageTask.java:646)
	at com.kingbase.flysync.replicator.pipeline.SingleThreadStageTask.run(SingleThreadStageTask.java:206)
	at java.lang.Thread.run(Thread.java:750)

查看断点

dsctl -service target_oracle_3112 get -ascmd

输出如下：

[kfs@xzsbk log]$ dsctl -service target_oracle_3112 get -ascmd
dsctl set -seqno 7910 -epoch 0 -event-id "kb:1599460595648:1599470540552" -source-id "kesbzk"

查看断点下一个事务对应的sql语句

语法：kufl -service target_oracle_3112 list -seqno seqno号

kufl -service target_oracle_3112 list -seqno 7911

输出如下：

[kfs@xzsbk kfs]$ kufl -service target_oracle_3112 list -seqno 7911
SEQ# = 7911 / FRAG# = 0 (last frag)
- TIME = 2025-05-16 18:24:41.21
- EPOCH# = 0
- EVENTID = kb:1599460595648:1599473027176
- SOURCEID = kesbzk
- METADATA = [tz_aware=true;dbms_type=kingbase;client_id;all_tables=#UNKNOWN;bidi_unsafe=true;service=source_kesv8r6_3112;shard=#UNKNOWN]
- TYPE = com.kingbase.flysync.replicator.event.ReplDBMSEvent
- OPTIONS = []
 - OPTIONS = [TG_TAG = CREATE VIEW, TABLE = NULL]
 - SQL(0) = 
  - SCHEMA = SJTBK
  - DDL =    create or replace view BZGSV5_ICIS_ALTERPUNISHINFO as  
 SELECT bg.casealtid AS id,
    wfcf.pendecno,
    (bg.alt)::character varying(1000 char) AS alt,
    bg.altdate,
    cast(dsr.pripid as number(19,0)) AS marprid,
    bg.type AS busitype,
    bg.caseid,
    cast(bg.penauth as number(19,0)) AS penorgid,
    bg.penauth_cn AS penauth,
    bg.s_ext_datatime AS "timestamp"
   FROM ((case_pub_alter bg
     LEFT JOIN case_cf_partyinfo dsr ON (((dsr.caseid)::text = (bg.caseid)::text)))
     LEFT JOIN case_cf_irregpunishinfo wfcf ON (((wfcf.caseid)::text = (bg.caseid)::text)))
  WHERE ((wfcf.pendecissdate)::timestamp without time zone >= (to_date('2014-10-01'::text, 'yyyy-mm-dd'::text))::timestamp without time zone)
UNION ALL
 SELECT qt.casealtid AS id,
    NULL::varchar AS pendecno,
    (qt.alt)::character varying(1000 char) AS alt,
    qt.altdate,
    ( SELECT cast(e_ot_case.pripid as number(19,0))
           FROM e_ot_case
          WHERE (((e_ot_case.caseid)::text = (qt.caseid)::text) AND (ROWNUM = 1))) AS marprid,
    '01'::varchar AS busitype,
    qt.caseid,
    cast(NULL as number(19,0)) AS penorgid,
    qt.penauth,
    qt.s_ext_datatime AS "timestamp"
   FROM e_ot_casalt qt

解决办法

目标库中创建表

在目标库中创建一个空表，用于做语法转换用。会把报错的DDL给转换为truncate这张表。

create table  sjtbk.kfs2(id number(10,0));

停止同步服务

fsrepctl offline

--如果有多个服务，指定要停止的服务进行停止
fsrepctl -service  同步服务名称 offline

查看服务状态，确保已经offline

目标端从断点开始purge

kufl -service target_oracle_3112 purge -low 7910 -y

查看purge后是不是已经删除了断点后的kufl

kufl -service target_oracle_3112 index

开启同步服务

fsrepctl online

查看服务状态，确保已经online

禁用掉源端ddlSupport.json中的视图

vi /topsoft/kfs/replicator/filters-config/replaceStatement.csv
#################################################
# ReplaceStatementFilter sample definition file #
#################################################

# CSV format:
#
# originalPattern,newPattern
#

# In case of multiple entries matching the same request, order of preference is
# important. Consider the following example:
#
# SET SESSION AUTHORIZATION "SYSTEM";SET search_path = "$USER",PUBLIC;,
select[\\s\\S]+,truncate table kfs2;
create or replace view[\\s\\S]+,truncate table kfs2;

配置ddl同步配置

将存储过程、视图、触发器、函数等过滤掉。

cd /topsoft/kfs/replicator/filters-config
vi ddl_support.json

将以下参数值都配置成N

  "VIEW": {
    "DEFAULT": {
      "CREATE": "N",
      "DROP": "N",
      "ALTER": "N"
    }
  },
  "PROCEDURE": {
    "DEFAULT": {
      "CREATE": "N",
      "DROP": "N",
      "ALTER": "N"
    }
  },
  "FUNCTION": {
    "DEFAULT": {
      "CREATE": "N",
      "DROP": "N",
      "ALTER": "N"
    }
  },
  "TRIGGER": {
    "DEFAULT": {
      "CREATE": "N",
      "DROP": "con'g'qi

重启同步服务

--重启同步服务
replicator restart

--查看同步服务状态
fsrepctl services