Oracle's Goldengate exception handling

Exception handling One (Exception table common type)

New Exception Handling table

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 Add exception Handling

Reperror (DEFAULT, EXCEPTION)

Reperror (Default2,discard)---abend according to demand

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"));

--only chf.a_t_1 tables are processed in the instance

Exception handling two (exception table needs customization)

New table (normal table and exception table)

--Normal table

CREATE TABLE Fei_1_1 (ID number, name VARCHAR2 (1000));

--Anomaly Table

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)

);

Exception handlers

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")

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

, 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"));

Exception Handling III (wildcard character mapexception)

New Exception Table

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)

);

Exception handlers

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"));

Processing instructions:

The Reperror parameter controls how the Replicat process responds to errors that occur during the mapping process

The default parameter represents a global error type, that is, to remove all errors except those that are explicitly specified

The DEFAULT2 parameter represents all errors in the exception part of all map mappings that are not defined when a default error is exception.

Exceptionsonly can only be used to determine table exception handling

Mapexception table exception Handling that can be used with wildcard characters

Author: 51cto Oracle small-mixed son