Problem Description: I was unable to start the extract, and it failed with the error “unable to identify the log number to start the extract” after the MySQL failover.
Let’s review the extract log for the possible cause for the failure
2023-01-24 14:49:37 INFO OGG-01055 Recovery initialization completed for target file /MYSQL/ggtrail/ESMBAR2/rb000002714, at RBA 2875424
2.
2023-01-24 14:49:37 INFO OGG-01478 Output file /MYSQL/ggtrail/ESMBAR2/rb is using format RELEASE 19.1.
2023-01-24 14:49:37 INFO OGG-01026 Rolling over remote file /MYSQL/ggtrail/ESMBAR2/rb000002714.
2023-01-24 14:49:37 INFO OGG-01053 Recovery completed for target file /MYSQL/ggtrail/ESMBAR2/rb000002715, at RBA 1473.
2023-01-24 14:49:37 INFO OGG-01057 Recovery completed for all targets.
2023-01-24 14:49:37 INFO OGG-00182 VAM API running in single-threaded mode.
2023-01-24 14:49:37 INFO OGG-01513 Positioning to Log Number: 467
Record Offset: 6228343.
INFO !! The supported binlog_format is ROW format only. Events in other formats are ignored.
INFO !! using index file
Source Context :
SourceModule : [ggvam.gen]
SourceID : [../gglib/ggvam/cvamgen.cpp]
SourceMethod : [vamInitialize]
SourceLine : [534]
ThreadBacktrace : [14] elements
: [/MYSQL/gguser/libgglog.so(CMessageContext::AddThreadContext())]
: [/MYSQL/gguser/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
: [/MYSQL/gguser/libgglog.so(_MSG_String_Int32_String(CSourceContext*, int, char const*, int, char const*, CMessag
eFactory::MessageDisposition))]
: [/MYSQL/gguser/extract(com_goldengate_vam::CVamGen::vamInitialize())]
: [/MYSQL/gguser/extract()]
: [/MYSQL/gguser/extract(ggs::er::VAMDataSource::finalizeConfiguration())]
: [/MYSQL/gguser/extract(ggs::gglib::ggapp::ReplicationContext::finalizeConfiguration())]
: [/MYSQL/gguser/extract()]
: [/MYSQL/gguser/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())]
: [/MYSQL/gguser/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadA
rgs*))]
: [/MYSQL/gguser/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/MYSQL/gguser/extract(main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/MYSQL/gguser/extract()]
2023-01-24 14:49:37 ERROR OGG-00146 Call to VAMInitialize returned with error status 600: VAM Client Report <CAUSE OF FAILURE : Invalid
Log number 467 given for positioning
WHEN FAILED : While setting initial position in the binary log file
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
When GoldenGate cannot identify the log number to start the extract, it typically means a mismatch or inconsistency between the GoldenGate trail files and the database logs. This can happen for various reasons, such as Missing or Corrupted Trail Files, Mismatched SCN (System Change Number), Changes to the Database Structure, and Network Latency.
To address this issue, we need to follow a few checks, such as Checking Trail Files, Verifying SCN, Reviewing Database Changes, Network Troubleshooting, and GoldenGate Monitoring.
To reposition Oracle GoldenGate for MySQL to a specific starting point, we need the MySQL log file number and position. I must go to the MySQL database to get the information and check the binlog files. The mysqlbinlog utility can identify DB events and their update position in the binlog files. For example, we can find bin.000003 and offset 120.
To be more precise, if you have the exact binlog coordinates of the new master after the failover, get the log number from MySQL and apply it as below.
GGSCI (ORA-X1) 2> ALTER EXTRACT ERMYSQL VAM, LOGNUM , LOGPOS