其他
OGG 21c 远程捕获进程延迟问题处理
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 OGG 21c 远程捕获进程延迟问题处理，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
前面几天发布了一篇使用 OGG 21c 遇到的几个问题之后，居然在新的周一后又发现了一个与 OGG 相关的问题，那是什么问题呢？且听小哥我慢慢道来。说来也简单就是使用 OGG21c 在中间机配置好了远程捕获 EXTRACT 进程，将数据导入到目标端 19c 后，源端捕获进程有很大的延迟，按照这个延迟来看，本地的归档日志也都删除了，可这个捕获进程呢没有任何报错，延迟一直在增大。
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNINGEXTRACT RUNNING EXT3 96:40:04 00:00:06配置过程简单如下
--重建前的步骤dblogin USERIDALIAS source_11g--先强行 kill 掉进程kill EXT3delete ext3--从数据库注册里去掉 ext3unregister extract ext3 database
--登录数据库dblogin USERIDALIAS source_11g--注册 ext3 到数据库中register extract ext3 database
2022-11-07 15:25:33 INFO OGG-02003 Extract group EXT3 successfully registered with database at SCN 13714764296.
--添加 ext3ADD EXTRACT ext3 INTEGRATED TRANLOG, BEGIN NOW
--注册 ext3 trail 文件add exttrail /home/oracle/ogg21c/dirdat/T4, extract ext3, megabytes 1024
--编辑 ext3 参数edit params ext3
EXTRACT ext3USERIDALIAS source_11gDDL INCLUDE MAPPEDDDLOPTIONS REPORTTRANLOGOPTIONS EXCLUDETAG 99TRANLOGOPTIONS NOUSENATIVEOBJSUPPORTEXTTRAIL /home/oracle/ogg21c/dirdat/T4TABLE OPS.*;SEQUENCE OPS.*;然后查看 ggserr 日志无明显报错。view report ext3 查看日志也没有明显的错误。
ggserr.log 日志
2022-11-07T15:31:04.513+0800 INFO OGG-06604 Oracle GoldenGate Capture for Oracle, ext3.prm: Connected to database jieke, CPU info: CPU Count 4, CPU Core Count 4, CPU Socket Count 4.2022-11-07T15:31:04.513+0800 INFO OGG-06618 Oracle GoldenGate Capture for Oracle, ext3.prm: Database jieke Platform: Linux x86 64-bit.2022-11-07T15:31:04.723+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.2022-11-07T15:31:05.546+0800 WARNING OGG-02045 Oracle GoldenGate Capture for Oracle, ext3.prm: Database does not have streams_pool_size initialization parameter configured.2022-11-07T15:31:05.708+0800 INFO OGG-02248 Oracle GoldenGate Capture for Oracle, ext3.prm: Logmining server DDL filtering enabled.2022-11-07T15:31:08.306+0800 INFO OGG-02339 Oracle GoldenGate Capture for Oracle, ext3.prm: Integrated capture successfully attached to logmining server OGG$CAP_EXT3 using 60 second streaming duration.2022-11-07T15:31:08.306+0800 INFO OGG-02089 Oracle GoldenGate Capture for Oracle, ext3.prm: Source redo compatibility version is: 11.2.0.4.0.2022-11-07T15:31:08.306+0800 INFO OGG-15446 Oracle GoldenGate Capture for Oracle, ext3.prm: Extract configured as resource group.2022-11-07T15:31:08.306+0800 INFO OGG-02086 Oracle GoldenGate Capture for Oracle, ext3.prm: Integrated Dictionary will be used.2022-11-07T15:31:08.449+0800 INFO OGG-02710 Oracle GoldenGate Capture for Oracle, ext3.prm: Database metadata information is obtained from source database.2022-11-07T15:31:08.517+0800 INFO OGG-02776 Oracle GoldenGate Capture for Oracle, ext3.prm: Native data capture is enabled for Oracle NUMBER data type.2022-11-07T15:31:08.523+0800 INFO OGG-01971 Oracle GoldenGate Capture for Oracle, ext3.prm: The previous message, 'INFO OGG-02776', repeated 1 times.2022-11-07T15:31:08.523+0800 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, ext3.prm: Extract group EXTRACT EXT3 started.2022-11-07T15:31:08.524+0800 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, ext3.prm: No recovery is required for target file /home/oracle/ogg21c/dirdat/T4000000000, at RBA 0 (file not opened).2022-11-07T15:31:08.524+0800 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, ext3.prm: Output file /home/oracle/ogg21c/dirdat/T4 is using format RELEASE 19.1/21.1.2022-11-07T15:31:51.789+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
view report ext3 的日志
2022-11-07 15:31:05 INFO OGG-02248 Logmining server DDL filtering enabled.2022-11-07 15:31:08 INFO OGG-02339 Integrated capture successfully attached to logmining server OGG$CAP_EXT3 using 60 second streaming duration.2022-11-07 15:31:08 INFO OGG-02089 Source redo compatibility version is: 11.2.0.4.0.2022-11-07 15:31:08 INFO OGG-15446 Extract configured as resource group.2022-11-07 15:31:08 INFO OGG-02086 Integrated Dictionary will be used.2022-11-07 15:31:08 INFO OGG-02710 Database metadata information is obtained from source database.2022-11-07 15:31:08 INFO OGG-02776 Native data capture is enabled for Oracle NUMBER data type.2022-11-07 15:31:08 INFO OGG-01971 The previous message, 'INFO OGG-02776', repeated 1 times.2022-11-07 15:31:08 INFO OGG-01052 No recovery is required for target file /home/oracle/ogg21c/dirdat/T4000000000, at RBA 0 (file not opened).2022-11-07 15:31:08 INFO OGG-01478 Output file /home/oracle/ogg21c/dirdat/T4 is using format RELEASE 19.1/21.1.************************************************************************* Run Time Messages *************************************************************************
--查看统计信息stats ext3Sending STATS request to Extract group EXT3 ...Start of statistics at 2022-11-07 15:52:52.DDL replication statistics (for all trails):
*** Total statistics since extract started *** Operations 0.00 Mapped operations 0.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00
Output to /home/oracle/ogg21c/dirdat/T4:
info ext3,showch
Extract EXT3 Last Started 2022-11-07 15:31 Status RUNNINGCheckpoint Lag 00:28:35 (updated 00:00:06 ago)Process ID 18534Log Read Checkpoint Oracle Integrated Redo Logs 2022-11-07 15:25:40 SCN 3.829909378 (13714811266)Current Checkpoint Detail:Read Checkpoint #1 Oracle Integrated Redo Log
Startup Checkpoint (starting position in the data source): Timestamp: 2022-11-07 15:25:40.000000 SCN: 0.0 (0)
Recovery Checkpoint (position of oldest unprocessed transaction in the data source): Timestamp: 2022-11-07 15:25:40.000000 SCN: 3.829909372 (13714811260)
Current Checkpoint (position of last record read in the data source): Timestamp: 2022-11-07 15:25:40.000000 SCN: 3.829909378 (13714811266)
send ext3 showtrans
Sending SHOWTRANS request to Extract group EXT3 ...------------------------------------------------------------XID: 0.60.26.988124 Items: 0 Extract: EXT3 Redo Thread: 3 Start Time: 2022-11-07:15:25:40 SCN: 3.829909394 (13714811282) Redo Seq: 35499Redo RBA: 3025424 Status: Running
--其他常用命令info ext3,showchinfo ext3,detailsend ext3 showtranssend extract ext3 statussend ext3 showtrans
没有任何报错，这便无从查起，便是很苦恼，删除进程重新添加观察一段时间之后也是这样的延迟，难道是因为数据库是 11g 的原因吗？但记得之前测试环境单机 11g 用的 OGG21c 也是可以的，那么是因为我这环境是 RAC 的问题吗?是因为远程复制吗？苦思不得其解，久久不能找到答案。
终于在昨天看到李老师公众号发布的文章因视图引起集成模式不同步的问题排查过程，于是跟着排查步骤一步一步检查自己的环境。
终于发现我们遇到了同样的问题，查询视图 DBA_GOLDENGATE_SUPPORT_MODE 很慢很慢，这个视图呢是记录的捕获进程对数据库中表的支持级别的信息。
select * from DBA_GOLDENGATE_SUPPORT_MODE where support_mode in ('ID KEY', 'NONE') order by owner,object_name;
查看这个视图的定义
set long 9999SELECT DBMS_METADATA.GET_DDL('VIEW','DBA_GOLDENGATE_SUPPORT_MODE','SYS') DDL_SQL FROM DUAL;
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_GOLDENGATE_SUPPORT_MODE" ("OWNER", "OBJECT_NAME", "SUPPORT_MODE") ASselect "OWNER","OBJECT_NAME","SUPPORT_MODE" from DBA_XSTREAM_OUT_SUPPORT_MODE;
SELECT * FROM dba_dependencies where name='DBA_GOLDENGATE_SUPPORT_MODE';查看视图 DBA_XSTREAM_OUT_SUPPORT_MODE 的定义，比较复杂，由三个 union all 组成，把视图 dba_xstream_out_support_mode 的组成部分分开来查询，发现涉及到 dba_logstdby_unsupported_table 视图的几个部分查询会夯住，count(*) 73 条内容竟然需要 22 分钟多：
SELECT DBMS_METADATA.GET_DDL('VIEW','DBA_XSTREAM_OUT_SUPPORT_MODE','SYS') DDL_SQL FROM DUAL;
select owner, table_name, 'FULL' from dba_logstdby_unsupported_table;
select owner, table_name, 'ID KEY' from dba_logstdby_unsupported_table where table_name not like 'AQ$_%' and (owner, table_name) not in (select owner, queue_table from dba_queue_tables) and (owner, table_name) not in (select owner, table_name from "_DBA_XSTREAM_OUT_ADT_PK_TABLES");DBA_LOGSTDBY_UNSUPPORTED_TABLE displays the data tables that are
not supported by Logical Standby.
该视图显示的是逻辑 DG 备库不支持的表。
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_LOGSTDBY_UNSUPPORTED_TABLE.html#GUID-2CF061B2-E31B-48FB-8BE2-96FD64851B33
目前我的数据库是 四节点 RAC 11.2.0.4 补丁为 190416,那么到底是不是 bug 21281961 呢，死马当活马医咯，先试试吧，首先去 MOS 上下载所需要的对应平台和版本号的补丁。
opatch lspatches29141201;OCW Patch Set Update : 11.2.0.4.190416 (29141201)29141056;Database Patch Set Update : 11.2.0.4.190416 (29141056)下载补丁
Patch 21281961: DBA_LOGSTDBY_UNSUPPORTED VIEW IS UNACCEPTABLY SLOW
因我的数据库打的补丁是 190416 故下载这个补丁时需要找对应的补丁，没有对应的补丁要找小于这个版本的补丁，不能找高于这个版本的补丁。故这里下载 DATABASE PATCH SET UPDATE 11.2.0.4.180717 补丁。
安装补丁
export PATH=${ORACLE_HOME}/bin:${ORACLE_HOME}/OPatch:${PATH}unzip p21281961_11204180717_Generic.zip1、首先确保 27734982 补丁已经安装
opatch lsinv | grep 27734982Sub-patch 27734982; "Database Patch Set Update : 11.2.0.4.180717 (27734982)"opatch version OPatch Version: 11.2.0.3.213、确保 $PATH 定义有以下可执行文件:make, ar, ld 和 nm。
这些可执行文件的位置取决于您的操作系统。在许多操作系统中，它们位于 /usr/ccs/bin 目录中。
4、补丁冲突检测
当 OPatch 启动时，它会验证补丁，并确保与 ORACLE_HOME 中已经安装的软件没有冲突。OPatch 将冲突分为以下类型:
与已应用到 ORACLE_HOME 的补丁冲突，该补丁是您试图应用的补丁的子集—在这种情况下，请继续安装补丁，因为新补丁包含 ORACLE_HOME 中现有补丁的所有补丁。在安装新补丁之前，将自动回滚子集补丁。
cd 21281961opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.21Copyright (c) 2022, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/db_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/db_1/oraInst.locOPatch version : 11.2.0.3.21OUI version : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-11-07_16-23-14PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.5、各个节点应用补丁
cd 21281961
opatch apply
opatch applyOracle Interim Patch Installer version 11.2.0.3.21Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/db_1/oraInst.locOPatch version : 11.2.0.3.21OUI version : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-11-07_16-47-38PM_1.log
Verifying environment and performing prerequisite checks...OPatch continues with these patches: 21281961
Do you want to proceed? [y|n]yUser Responded with: YAll checks passed.Provide your email address to be informed of security issues, install andinitiate Oracle Configuration Manager. Easier for you if you use your MyOracle Support Email address/User Name.Visit http://www.oracle.com/support/policies.html for details.Email address/User Name:
You have not provided an email address for notification of security issues.Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Backing up files...Applying interim patch '21281961' to OH '/u01/app/oracle/product/11.2.0/db_1'
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...Patch 21281961 successfully applied.Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2022-11-07_16-47-38PM_1.log
OPatch succeeded.
opatch lsinventory6、执行 SQL 脚本
$ sqlplus / AS SYSDBASQL> @?/sqlpatch/21281961/postinstall.sql7、补丁回退(出现问题时需要回退)
opatch rollback -id 21281961$ sqlplus / AS SYSDBASQL> @?/sqlpatch/21281961/postdeinstall.sql打完补丁，重启捕获进程 ext3
打完补丁，重启进程后，捕获进程一个多小时的延迟，几秒内瞬间就没有了。
GGSCI (jiekedb as ogg@jieke2) 43> stop ext3
Sending STOP request to Extract group EXT3 ...Request processed.
GGSCI (jiekedb as ogg@jieke2) 44> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EXT3 01:25:21 00:00:03
GGSCI (jiekedb as ogg@jieke2) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EXT3 01:25:21 00:00:28
GGSCI (jiekedb as ogg@jieke2) 47> kill ext3
Sending KILL request to Manager ...Terminated process (18534) for EXTRACT EXT3
GGSCI (jiekedb as ogg@jieke2) 48> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT STOPPED EXT3 01:25:21 00:01:06
GGSCI (jiekedb as ogg@jieke2) 49> exit
GGSCI (jiekedb) 2> dblogin USERIDALIAS source_11gSuccessfully logged into database.
GGSCI (jiekedb as ogg@jieke1) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT STOPPED EXT3 01:25:21 00:01:40
GGSCI (jiekedb as ogg@jieke1) 4> start ext3
Sending START request to Manager ...Extract group EXT3 starting.
GGSCI (jiekedb as ogg@jieke1) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EXT3 01:25:21 00:01:49
GGSCI (jiekedb as ogg@jieke1) 6> info ext3
Extract EXT3 Last Started 2022-11-07 15:31 Status RUNNINGCheckpoint Lag 01:25:21 (updated 00:01:55 ago)Process ID 30433Log Read Checkpoint Oracle Integrated Redo Logs 2022-11-07 15:25:40 SCN 3.829909402 (13714811290)
GGSCI (jiekedb as ogg@jieke1) 7> !info ext3
Extract EXT3 Last Started 2022-11-07 16:53 Status RUNNINGCheckpoint Lag 01:27:21 (updated 00:00:05 ago)Process ID 30433Log Read Checkpoint Oracle Integrated Redo Logs 2022-11-07 15:25:40 SCN 3.829909402 (13714811290)
GGSCI (jiekedb as ogg@jieke1) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EXT3 00:18:43 00:00:00 GGSCI (jiekedb as ogg@jieke1) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING EXTRACT RUNNING EXT3 00:00:04 00:00:05查看这个补丁发现 cat ./21281961/files/sqlpatch/21281961/postinstall.sql
主要是执行了 catlsby.sql 脚本，那么在这个脚本中我们可以发现是有重建 dba_logstdby_unsupported_table
视图的，这个和小麦苗老师说的另一种方案一样可以直接重建视图快速解决问题，但是生产环境如果出现问题还是保守一些以打补丁为好。
exec :scriptFile := ‘?/rdbms/admin/catlsby.sql’;
cat ./21281961/files/rdbms/admin/catlsby.sql | grep dba_logstdby_unsupported_table -A10create or replace view dba_logstdby_unsupported_tableas select owner, name table_name from ( select u.owner, u.name, u.type#, u.obj#, u.current_sby, u.gensby from logstdby_support_tab_10_1 u, (select (CASE d.database_role WHEN 'PRIMARY' THEN p.value ELSE nvl( (select s.redo_compat from system.logstdby$parameters p, system.logmnr_session$ s ……的查询是一点效果也没有，还是很慢很慢。在其他环境和 19c 环境查询很快就可以出现结果，为何还会这样呢真的是这个环境的问题？有感兴趣的小伙伴可以继续研究研究私信我，今天就先到这里咯。
--其他正常 11g 环境select count(*) from DBA_GOLDENGATE_SUPPORT_MODE where support_mode in ('ID KEY', 'NONE') order by owner,object_name;
COUNT(*)----------46
Elapsed: 00:00:05.90参考链接
https://mp.weixin.qq.com/s/sch4-eEvWAcd_aIts2Qttghttps://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_LOGSTDBY_UNSUPPORTED_TABLE.html#GUID-2CF061B2-E31B-48FB-8BE2-96FD64851B33https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_GOLDENGATE_SUPPORT_全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————