GG Extract Report Shows "Purging Transaction", WARNING OGG-00723, OGG-00715,

最新推荐文章于 2023-03-03 15:09:38 发布

翻译最新推荐文章于 2023-03-03 15:09:38 发布 · 1.7k 阅读

ogg 专栏收录该内容

27 篇文章

订阅专栏

本文探讨了Oracle GoldenGate (OGG) 在处理XA事务时出现的数据丢失问题，特别是警告OGG-00723和OGG-00715。分析了可能的原因，包括分布式事务和流线化日志读取代码，并提供了升级到OGG 11.2版本并启用集成抽取作为解决方案之一的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

客户某套ogg总是莫名丢数，检查各个配置文件，均配置正确。

查看discard文件和rpt文件发现警告WARNING OGG-00723, OGG-00715，此时可以确定是源端的抽取漏抽数据。

查询mos发现文章1458472.1有比较清楚的解释。

Applies to: 
Oracle GoldenGate - Version 11.1.1.0.0 and later
Information in this document applies to any platform.

Symptoms
Extract report shows following messages:

2012-03-05 09:14:54  WARNING OGG-00723  Oracle GoldenGate Capture for Oracle, ext_bi.prm:  Record with class# 34, slt# 4, at seqno 77324,
rba 32995856 SCN 27.2929925862 (118894042854) has secondary transaction ID that is duplicate of existing open uncommitted transaction.

2012-03-05 09:14:54  WARNING OGG-00715  Oracle GoldenGate Capture for Oracle, ext_bi.prm:  [Thread #1] Purging transaction
(transaction id: 9.4.26367119, start time: 2012-03-05 09:11:35, start seqno: 77323, start RBA: 154945040).

2012-03-05 09:14:54  WARNING OGG-00712  Oracle GoldenGate Capture for Oracle, ext_bi.prm:  Updating I/O checkpoint after purging
orphaned transactions on thread 1 with current position (Seq#: 77324, RBA: 32998564). s

 

Cause
1) This is due to the presence of XA (distributed transactions).

报错一致，官方给出的原因也是很明确，XA

This can be verified by running the following query:
下面语句就是测试你的数据库是否是XA
sql> spool output.txt              

set headin off
select /*+ ORDERED */
'-------- Found Global XID -----------------------'||'
Current Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA
from x$k2gte g;
set headin off
select /*+ ORDERED */
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||'
'||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||'
'||'Ascii GTXID='||g.K2GTITID_ORA ||'
'||'Branch= '||g.K2GTIBID ||'
Local TX Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||'
'||'KTUXESTA='|| x.KTUXESTA ||'
'||'KTUXEDFL='|| x.KTUXECFL
from x$k2gte g, x$ktcxb t, X$KTUXE x
where g.K2GTDXCB =t.ktcxbxba and
x.KTUXEUSN = t.KXIDUSN(+) and
x.KTUXESLT = t.kXIDSLT(+) and
x.KTUXESQN =t.kXIDSQN(+);

set headin on

sql> spool off

Extract/ Classic Extract encountered a shutdown of an instance, and is purging any transactions that are open on that instance.
Extract/ Classic Extract does not have the capability to handle XA transactions on RAC. It may purge XA transactions that would eventually
commit. Note the first warning (secondary  transaction ID that is duplicate of existing open uncommitted transaction) suggests that we have encountered
XA on RAC.

There can be data loss (precisely in such cases where an instance with an XA branch crashes and the crashed XA txn is recovered in another instance).

2) This can also be caused due to streamlined dblogreader code which was added in OGG version 11.2.1.0.7 and it was disabled in OGG version 11.2.1.0.32

Solution
1) Upgrade to OGG v11.2 and configure "Integrated Extract" which supports XA transactions.

How To Upgrade From Goldengate Classic Extract To Integrated Extract (Doc ID 1484313.1)

For environments using Classic Extract, the following can be used as a work-around

_clusterwide_global_transactions=false 

1. What is the impact of setting "_clusterwide_global_transactions"=false 

This will ensure that ALL branches of XA goes to the same Oracle instance. However if the instance fails the whole XA transaction will roll back.
This will only impact incoming distributed transactions.

2. For existing connections will it be transparent ?

It is not transparent. We still have to setup services to accept XA connections.

From the Oracle 10.2 doc:
For services that you are going to use for distributed transaction processing, create the service using Enterprise Manager, DBCA, or SRVCTL and
define only one instance as the preferred instance. You can have as many AVAILABLE instances as you want. For example, the following SRVCTL command
creates a singleton service for database crm, xa_01.service.us.oracle.com, whose preferred instance is RAC01:

eg., srvctl add service -d crm -s xa_01.service.us.oracle.com -r RAC01 -a RAC02, RAC03

For incoming transactions we will have to set up the services as explained above. Then mark the service for distributed transaction processing by
setting the DTP parameter to TRUE; the default is FALSE. Enterprise Manager enables you to set this parameter on the Cluster Managed Database Services: Create
Service or Modify Service page. You can also use the DBMS_SERVICE package to modify the DTP property of the singleton service as follows:

eg., EXECUTE DBMS_SERVICE.MODIFY_SERVICE(service_name=>'xa_01.service.us.oracle.com', DTP=>TRUE);

If, for example, RAC01 which provides service XA_01 fails, then the singleton service that it provided fails over to one of the other instances, such as
RAC02 or RAC03.

Additional details on XA transactions are available in following document

http://www.oracle.com/technetwork/products/clustering/overview/distributed-transactions-and-xa-163941.pdf

2) If using OGG versions 11.2.1.0.7 through 11.2.1.0.31 and 12.1.2.0.0 through 12.1.2.1.4 add TRANLOGOPTIONS _DISABLESTREAMLINEDDBLOGREADER to the extract to fix the issue with streamlined
dblogreader code. Starting with OGG versions 11.2.1.0.32 and 12.1.2.1.5 streamlined db logreader is disabled by default.

可以使用脚本验证spoolout.txt处的脚本，输入之后，如果有结果就是XA，如果没有结果（no rows select）就不是XA。--注意要多运行几次

关于在XA模式下如何保证抽取不丢数，参看mos上的另一篇文章：ogg将普通模式转换成集成模式