Oracle GoldenGate is a pillar of reliability and efficiency in real-time data replication. However, even the most robust systems encounter challenges, and one such hurdle is the OGG-04036 error—an Extract failure attributed to a sequence out-of-order issue. In this post, we’ll dissect the complexities of this error code, unravel its root causes, and equip you with strategies to address and mitigate its impact on your replication workflows effectively.
The OGG-04036 error code signifies that the GoldenGate Extract process has encountered transactions with sequence numbers that deviate from the expected order. GoldenGate relies on the sequential arrangement of transactions to ensure data consistency and integrity. When transactions arrive out of order, it disrupts the replication process, triggering the OGG-04036 error and halting Extract operations.
Several factors, such as network latency, concurrent transactions, database configuration changes, timezone discrepancies, and database platform variations, are responsible for the root cause of this issue.
2023-01-03T07:48:55.972+0900 ERROR OGG-04036 Oracle GoldenGate Capture for MySQL, epmmct2.prm: Positioning sequence ID is out of order, old sequence ID is 000000000000000003090:000004294960709, new sequence ID is 000000000000000003090:000000000010480.
The Extract on MySQL fails due to the transaction overspill in the binary log. Due to large transactions, the Binary log file grows exponentially from its default size of 257 MB (max_binlog_size) to 4.1GB. The golden gate fails to process the binary log if the binary log size grows beyond 3GB. This bug in Goldengate Oracle support will provide a patch on top of the October 2022 patch set. Until then, please follow the below workaround.
- he Error from the process report file or the ggserr.log
- Check the binlog number and the offset number on which the extract is stuck currently as below
Log Number <==> Mysql binlog number
Record Offset <==> Mysql binlog offset number
GGSCI (ORA-X1) 10> info EPMMCT2
EXTRACT EPMMCT2 Last Started 2023-01-04 08:38 Status ABENDED
Checkpoint Lag 01:03:54 (updated 00:10:19 ago)
VAM Read Checkpoint 2023-01-04 07:34:32.000000
Log Number: 3106
Record Offset: 4246683596
3. Login to the MySQL master node and check the corresponding binlog size
mysql> show variables like 'log_bin_basename';
+------------------+--------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------+
| log_bin_basename | /MYSQL/dbaas-mysql-bcp/binlog/binlog |
+------------------+--------------------------------------+
1 row in set (0.00 sec)
Dump the binary log to the text file as mysqlbinlog –base64-output=decode-rows –verbose –read-from-remote-server binlog.003090 > /MYSQL/dbaas-mysql-bcp/backup/binlog3090.txt
Move the Extract to the next binlog manually as below.
ALTER EXTRACT EPMSHP2 VAM, LOGNUM 3091, LOGPOS 0
ALTER EXTRACT EPMMCT2 VAM, LOGNUM 3091, LOGPOS 0
ALTER EXTRACT EPMMYNUM VAM, LOGNUM 3091, LOGPOS 0
Get the primary key values from BINLOG3090.TXT and share it to the application team to repair the data correction.
Finally validate the golden gate processes are up and running.
