上個月底發布了一篇使用 OGG 21c 遇到的幾個問題之后，居然在新的周一后又發現了一個與 OGG 相關的問題，那是什么問題呢？且聽小哥我慢慢道來。說來也簡單就是使用 OGG21c 在中間機配置好了遠程捕獲 EXTRACT 進程，將數據導入到目標端 19c 后，源端捕獲進程有很大的延遲，按照這個延遲來看，本地的歸檔日志也都刪除了，可這個捕獲進程呢沒有任何報錯，延遲一直在增大。
info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT3 96:40:04 00:00:06
配置過程簡單如下：
--重建前的步驟
dblogin USERIDALIAS source_11g
--先強行 kill 掉進程
kill EXT3
delete ext3
--從數據庫注冊里去掉 ext3
unregister 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.
--添加 ext3
ADD EXTRACT ext3 INTEGRATED TRANLOG, BEGIN NOW
--注冊 ext3 trail 文件
add exttrail /home/oracle/ogg21c/dirdat/T4, extract ext3, megabytes 1024
--編輯 ext3 參數
edit params ext3
EXTRACT ext3
USERIDALIAS source_11g
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG 99
TRANLOGOPTIONS NOUSENATIVEOBJSUPPORT
EXTTRAIL /home/oracle/ogg21c/dirdat/T4
TABLE 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 ext3
Sending 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 RUNNING
Checkpoint Lag 00:28:35 (updated 00:00:06 ago)
Process ID 18534
Log 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: 35499
Redo RBA: 3025424
Status: Running
--其他常用命令
info ext3,showch
info ext3,detail
send ext3 showtrans
send extract ext3 status
send 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 9999
SELECT 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") AS
select "OWNER","OBJECT_NAME","SUPPORT_MODE" from DBA_XSTREAM_OUT_SUPPORT_MODE;
SELECT * FROM dba_dependencies where name='DBA_GOLDENGATE_SUPPORT_MODE';
發現視圖 dba_goldengate_support_mode 基于視圖 DBA_XSTREAM_OUT_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 lspatches
29141201;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.zip
1、首先確保 27734982 補丁已經安裝
opatch lsinv | grep 27734982
Sub-patch 27734982; "Database Patch Set Update : 11.2.0.4.180717 (27734982)"
2、確保 Opatch 版本大于 11.2.0.3.5
opatch version
OPatch Version: 11.2.0.3.21
3、確保 $PATH 定義有以下可執行文件:make, ar, ld 和 nm。
這些可執行文件的位置取決于您的操作系統。在許多操作系統中，它們位于 /usr/ccs/bin 目錄中。
4、補丁沖突檢測
當 OPatch 啟動時，它會驗證補丁，并確保與 ORACLE_HOME 中已經安裝的軟件沒有沖突。OPatch 將沖突分為以下類型:
與已應用到 ORACLE_HOME 的補丁沖突，該補丁是您試圖應用的補丁的子集—在這種情況下，請繼續安裝補丁，因為新補丁包含 ORACLE_HOME 中現有補丁的所有補丁。在安裝新補丁之前，將自動回滾子集補丁。
cd 21281961
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.21
Copyright (c) 2022, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.21
OUI version : 11.2.0.4.0
Log 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 apply
Oracle Interim Patch Installer version 11.2.0.3.21
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.21
OUI version : 11.2.0.4.0
Log 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]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle 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 lsinventory
6、執行 SQL 腳本
$ sqlplus / AS SYSDBA
SQL> @?/sqlpatch/21281961/postinstall.sql
7、補丁回退(出現問題時需要回退)
opatch rollback -id 21281961
$ sqlplus / AS SYSDBA
SQL> @?/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_11g
Successfully 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 RUNNING
Checkpoint Lag 01:25:21 (updated 00:01:55 ago)
Process ID 30433
Log 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 RUNNING
Checkpoint Lag 01:27:21 (updated 00:00:05 ago)
Process ID 30433
Log 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 -A10
create or replace view dba_logstdby_unsupported_table
as
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 ……
打完補丁觀察了兩天也沒有出現延遲問題，但是對于視圖 DBA_GOLDENGATE_SUPPORT_MODE 的查詢是一點效果也沒有，還是很慢很慢。在其他環境和 19c 環境查詢很快就可以出現結果，為何還會這樣呢？有感興趣的小伙伴可以繼續研究研究，今天就先到這里咯。
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_aIts2Qttg
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_LOGSTDBY_UNSUPPORTED_TABLE.html#GUID-2CF061B2-E31B-48FB-8BE2-96FD64851B33
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_GOLDENGATE_SUPPORT_MODE.html
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
