1、生产环境：
Linux系统版本：OracleLinux7.9
Oracle软件版本：11.2.0.4 RAC
Oracle GoldenGate版本：11.2.1.0.0
2、故障描述：
ogg抽取进程ABENDING,查看$GGATE_HOME/ggserr.log日志发现报错如下：
2022-01-26 15:04:08 ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, e_chouqu.prm:
OCI Error getting OCI_ATTR_NAME for UDT SYS.ANYDATA (status = 24328-ORA-24328: illegal attribute value), SQL<Not available>.
2022-01-26 15:04:08 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, e_chouqu.prm: PROCESS ABENDING.
报错信息中只是描述了非法的属性值 SYS.ANYDATA导致SQL不可用
3、问题处理：
1、查询$GGATE_HOME/dirrpt目录下抽取进程日志获取更详细的报错信息，日志信息如下：
2022-01-26 15:04:08 INFO OGG-01057 Recovery completed for all targets.
Wildcard TABLE resolved (entry u_ws.*):
TABLE "u_ws"."SCHEDULER$_JOB_ARG";
Source Context :
SourceModule : [ggdb.ora.sess]
SourceID : [/scratch/mmar/view_storage/mmar_17707241/oggcore/OpenSys/src/gglib/ggdbora/ocisess.c]
SourceFunction : [oci_try]
SourceLine : [651]
ThreadBacktrace : [26] elements
: [/acfs/userspace/ggate/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7fc5fd0f860e]]
: [/acfs/userspace/ggate/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7fc5fd0f135c]]
: [/acfs/userspace/ggate/libgglog.so(_MSG_ERR_ORACLE_OCI_ERROR_WITH_DESC_SQL(CSourceContext*, int, char const*, char const*, char const*, CMessageFactory::MessageDisposition)+0x4e) [0x7fc5fd0e07c0]]
: [/acfs/userspace/ggate/extract(OCISESS_context_def::oci_try(int, char const*, ...)+0x463) [0x66a333]]
: [/acfs/userspace/ggate/extract() [0x66d282]]
: [/acfs/userspace/ggate/extract() [0x66e0b5]]
: [/acfs/userspace/ggate/extract(OCIUDT_get_udt_def(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CDBObjName<(DBObjType)13> const&, int, DDLPropertyBuffer*, short)+0x2ea) [0x66edf6]]
: [/acfs/userspace/ggate/extract(gl_get_col_specs(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, col_def*, ggs::gglib::ggapp::CDBObjName<(DBObjType)13> const&, short, char const*, long, long, short, short, short*, int*, int*, int, DDLP
ropertyBuffer*, short, char*)+0x5ad) [0x65741d]] : [/acfs/userspace/ggate/extract() [0x658005]]
: [/acfs/userspace/ggate/extract(gl_get_query_def(file_def const*, ggs::gglib::ggunicode::UString const&, short, char*)+0x41) [0x65a721]]
: [/acfs/userspace/ggate/extract(gl_get_odbc_table_def(file_def*, short, char*)+0xdb) [0x65c71b]]
: [/acfs/userspace/ggate/extract(get_extract_source_def(file_def*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&)+0x53) [0x573103]]
: [/acfs/userspace/ggate/extract(add_source_file(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, unsigned int*, unsigned int, recopts_t*, ggs::gglib::ggunicode::UString const&, int*, int, wc_def*, ggs::gglib::ggapp::CQualDBObjName<(DBOb
jType)1>&, int)+0x340) [0x55a430]] : [/acfs/userspace/ggate/extract(get_map_entry(ggs::gglib::ggunicode::UString const&, int, wc_def*, int)+0x57b) [0x55b7ab]]
: [/acfs/userspace/ggate/extract(wc_def::resolve_wc_entry(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, int, unsigned int*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>*, int)+0x1af) [0x5f04ff]]
: [/acfs/userspace/ggate/extract(WILDCARD_check_table(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const*, int, unsigned int*, int, unsigned int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>*, int)+0x12c) [0x5f08ac]]
: [/acfs/userspace/ggate/extract(REDOORA_lookup_table(file_def**, log_context_t*, int, bool, SparseArray<bool, int, SparseArrayEntry<bool, int>, 1>*)+0x233) [0x6a6263]]
: [/acfs/userspace/ggate/extract() [0x6d3c42]]
: [/acfs/userspace/ggate/extract(REDOORAOUT_get_committed_record(objectpool*, cache_object*, log_context_t*, chkpt_context_t*, short, int, int, int*, char*, int*, int*, int*, char*, short*, int*, char*)+0xdef) [0x6d92df]]
: [/acfs/userspace/ggate/extract(REDO_read_transaction_record(objectpool*, cache_object*, int, char*, int*, long*, char*, short*, int*, int*, int*, char*)+0x146) [0x6a5e76]]
: [/acfs/userspace/ggate/extract(RTC_read(char*, int*, long*, char*, short*, int, char*)+0x281) [0x7353f1]]
: [/acfs/userspace/ggate/extract(READ_EXTRACT_RECORD(chkpt_context_t*, short, char*, __std_rec_hdr*, int*, int*, long*, short, short, char*, short*, char*, short*, long*, char*)+0xcc) [0x4f7fcc]]
: [/acfs/userspace/ggate/extract(process_extract_loop()+0x625) [0x563de5]]
: [/acfs/userspace/ggate/extract(main+0x5e6) [0x5765d6]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x7fc5f8f1ed1d]]
: [/acfs/userspace/ggate/extract(__gxx_personality_v0+0x39a) [0x4efd9a]]
2022-01-26 15:04:08 ERROR OGG-00665 OCI Error getting OCI_ATTR_NAME for UDT SYS.ANYDATA (status = 24328-ORA-24328: illegal attribute value), SQL<Not available>.
通过抽取进程e_chouqu的rpt报告可以发现，在捕获"u_ws"."SCHEDULER$_JOB_ARG"表的数据时产生了(ORA-24328: illegal attribute value)报错
3、查看"u_ws"."SCHEDULER$_JOB_ARG"表结构
SQL> desc u_ws.SCHEDULER$_JOB_ARG;
Name Type Nullable Default Comments
JOB_NAME VARCHAR2(30) Y
ARG_NAME VARCHAR2(30) Y
ARG_POSITION NUMBER Y
VALUE SYS.ANYDATA Y
FLAGS NUMBER Y
ENABLED VARCHAR2(1) Y
SQL>
可以看到 “VALUE” 字段的数据类型为：“SYS.ANYDATA”，这就是日志中的报错所在。
原因：
通过在MOS查询到在Oracle GoldenGate - 版本 11.1.1.1.2 及更高版本的ogg中，并不支持ANYDATA的数据类型，导致抽取进程无法捕获表中的数据。
处理：
因为ogg并不支持该种数据类型的表，所以只能将其进行屏蔽处理，在抽取进程配置文件中添加TABLEEXCLUDE参数，例如：
TABLEEXCLUDE <schema.table_name>
特别注意： TABLEEXCLUDE 参数必须放在所有 TABLE 和 SEQUENCE 语句之前
参阅官方文档ID:1437156.1
|