goldengate 异常处理

异常处理一（异常表通用型）
新建异常处理表

create table ogg.exception_log
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

REPLICAT添加异常处理

REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,discard)---abend根据需求
map chf.a_t_1, target chf.a_t_1;
map chf.a_t_1, target ogg.exception_log,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
--实例中只处理chf.a_t_1表

异常处理二(异常表需要定制)
新建表(正常表和异常表)

--正常表
create table fei_1_1(id number , name varchar2(1000));
--异常表
create table ogg.exception_fei_1
    ( id number,
      name varchar2(1000),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

map chf.fei_1, target chf.fei_1_1;
map chf.fei_1, target ogg.exception_fei_1,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP ( USEDEFAULTS
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

异常处理三（通配符MAPEXCEPTION）
新建异常表

create table ogg.exception_fei_all
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

MAP chf.fei_*, TARGET chf.*,
MAPEXCEPTION (TARGET ogg.exception_fei_all,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")));

处理说明：
REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型，即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时，所有MAP映射中未定义Exception部分出现的所有错误
EXCEPTIONSONLY只能用于确定表的异常处理
MAPEXCEPTION可以用于通配符的表异常处理