OGG-05259 The Extract’s recovery checkpoint LSN is not in the cdc.lsn_time_mapping table of database ‘test’.

OGG-05259 The Extract’s recovery checkpoint LSN 00009da1:0057deff:0004 is not in the cdc.lsn_time_mapping table of database ‘test’.

Symptoms:

2019-10-08 14:33:14 INFO OGG-05257 For CDC tuning best practices, please see https://technet.microsoft.com/en-us/library/dd266396%28v=sql.100%29.aspx.

2019-10-08 14:33:14 WARNING OGG-05292 The Oracle GoldenGate capture job is running on database test with NOMANAGECDCCLEANUP. Oracle GoldenGate will not purge CDC enabled tables.

Source Context :

SourceModule : [ggmssqlcdcvam.mssqlcdcreader]

SourceID : [ggmssqlcdcvam/MSSqlCDCReader.cpp]

SourceMethod : [mssqlcdcvam::MSSqlCDCVamPosition::SetStartPosition]

SourceLine : [384]

ThreadBacktrace : [19] elements

: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart)]

: [D:\OGG4MSSQL\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ)]

: [D:\OGG4MSSQL\gglog.dll(?_MSG_Pointer_Pointer@@YAPEAVCMessage@@PEAVCSourceContext@@HPEBX1W4MessageDisposition@CMessageFactory@@@Z)]

: [D:\OGG4MSSQL\extract.exe(ERCALLBACK)]

: [D:\OGG4MSSQL\extract.exe(VAMInitialize)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [D:\OGG4MSSQL\extract.exe(VAMInitialize)]

: [D:\OGG4MSSQL\extract.exe(GGDataBufferGetNextChunk)]

: [D:\OGG4MSSQL\extract.exe(GGDataBufferGetNextChunk)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [D:\OGG4MSSQL\extract.exe(ERCALLBACK)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [D:\OGG4MSSQL\extract.exe(_ggTryDebugHook)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [D:\OGG4MSSQL\extract.exe(_ggTryDebugHook)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart)]

: [C:\Windows\System32\KERNEL32.DLL(BaseThreadInitThunk)]

: [C:\Windows\SYSTEM32\ntdll.dll(RtlUserThreadStart)]

2019-10-08 14:33:14 ERROR OGG-05259 The Extract’s recovery checkpoint LSN 00009da1:0057deff:0004 is not in the cdc.lsn_time_mapping table of database ‘test’. Extract cannot re-position without possible data loss.

2019-10-08 14:33:14 ERROR OGG-01668 PROCESS ABENDING.

Debug

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 216> info ext01 detail

EXTRACT EXT01 Last Started 2019-09-02 15:46 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Process ID 2680

VAM Read Checkpoint 2019-09-25 11:13:08.923333

LSN: 00009da1:0057deff:0004-00009da1:0057deff:0004, Tran: 0000:d31ab4af

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

D:\OGG\dirdat\TR 6 29928748 200 EXTTRAIL

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 226> info ext01 showch

EXTRACT EXT01 Last Started 2019-10-08 14:36 Status ABENDED

Checkpoint Lag 00:00:00 (updated 00:20:06 ago)

VAM Read Checkpoint 2019-09-25 11:13:08.923333

LSN: 00009da1:0057deff:0004-00009da1:0057deff:0004, Tran: 0000:d31ab4af

Current Checkpoint Detail:

Read Checkpoint #1

VAM External Interface

Startup Checkpoint (starting position in the data source):

Timestamp: 2019-09-02 15:46:27.778000

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Timestamp: 2019-09-25 11:13:08.923333

LSN: 00009da1:0057deff:0004-00009da1:0057deff:0004, Tran: 0000:d31ab4af

Current Checkpoint (position of last record read in the data source):

Timestamp: 2019-09-25 11:13:08.923333

LSN: 00009da1:0057deff:0004-00009da1:0057deff:0004, Tran: 0000:d31ab4af

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 6

RBA: 29928748

Timestamp: 2019-10-08 14:16:10.159000

Extract Trail: D:\OGG\dirdat\TR

Seqno Length: 9

Flip Seqno Length: No

Trail Type: EXTTRAIL

Header:

Version = 2

Record Source = A

Type = 8

# Input Checkpoints = 1

# Output Checkpoints = 1

File Information:

Block Size = 2048

Max Blocks = 1000

Record Length = 20480

Current Offset = 0

Configuration:

Data Source = 5

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2019-10-08 14:36:15

Last Update Time = 2019-10-08 14:16:10

Stop Status = A

Last Result = 400

select * from cdc.lsn_time_mapping

0x00009DA2000C34B00001 2019-10-05 01:54:44.710 2019-10-05 01:54:44.710 0x00

0x00009DA2000C36280001 2019-10-05 01:59:49.883 2019-10-05 01:59:49.883 0x00

0x00009DA2000C38C70001 2019-10-05 02:04:50.037 2019-10-05 02:04:50.037 0x00

0x00009DA2000C3A410001 2019-10-05 02:09:55.200 2019-10-05 02:09:55.200 0x00

0x00009DA2000C3BBA0001 2019-10-05 02:15:00.357 2019-10-05 02:15:00.357 0x00

0x00009DA2000C3D2A0001 2019-10-05 02:20:05.550 2019-10-05 02:20:05.550 0x00

0x00009DA2000C3EA00001 2019-10-05 02:25:10.700 2019-10-05 02:25:10.700 0x00

SELECT sys.fn_cdc_get_max_lsn()AS max_lsn;

0x00009DA20011FCC70001

SELECT bf1.physical_device_name, bs1.position, bs1.type,

bs1.last_lsn, bs1.backup_start_date, bs1.backup_finish_date

FROM msdb..backupset bs1

inner join

msdb..backupmediafamily bf1

on bf1.media_set_id = bs1.media_set_id

WHERE bs1.database_name = DB_NAME()

AND bs1.type = ‘D’

ORDER BY bs1.backup_start_date DESC

\\windows01\tmpAlwaysOn\db4.bak 1 D 40307000155818900001 2019-07-28 20:55:51.000 2019-07-28 21:23:25.000

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 242> alter extract EXT01, lsn 40307000155818900001

EXTRACT altered.

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 243> info ext01 detail

EXTRACT EXT01 Initialized 2019-10-08 15:25 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:25 ago)

VAM Read Checkpoint First Record

LSN: 0x00009D73:0017C6AD:0001, Tran: 0000:000000

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

D:\OGG\dirdat\TR 6 29928748 200 EXTTRAIL

Extract Source Begin End

Not Available * Initialized * First Record

Not Available 2019-09-02 15:46 2019-09-25 11:13

Not Available 2019-09-02 15:46 2019-09-25 11:13

Not Available 2019-09-02 15:46 2019-09-25 11:13

Not Available 2019-09-02 15:46 2019-09-25

 

 

11:1

 

 

 

 

 

3

 

 

 

Solution1: Start at LSN

DECLARE @lsn nvarchar(22)=’00009DA2000C34B00001′, @cmd nvarchar(2048),@declsn nchar(22);

select @cmd = ‘

select

right(replicate(”0”,8) + cast(convert(int, 0x’ + left(@lsn,8) + ‘) as nvarchar(8)),8) + N”:” +

right(replicate(”0”,8) + cast(convert(int, 0x’ + substring(@lsn,10,8) + ‘) as nvarchar(8)),8) + N”:” +

right(replicate(”0”,4) + cast(convert(int, 0x’ + right(@lsn,4) + ‘) as nvarchar(4)),4)

‘;

print @cmd;

exec sp_executesql @stmt = @cmd, @declsn = @declsn output;

00040354:12798720:0001

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 246> alter extract EXT01, lsn 00040354:12798720:0001

EXTRACT altered.

GGSCI (WINDOWS01 as ggadmin@OGGDSN_MASTER) 247> info ext01 detail

EXTRACT EXT01 Initialized 2019-10-08 15:54 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:05 ago)

VAM Read Checkpoint First Record

LSN: 0x00009DA2:00C34B00:0001, Tran: 0000:000000

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

D:\OGG\dirdat\TR 6 29928748 200 EXTTRAIL

Extract Source Begin End

Not Available * Initialized * First Record

 

 

 

 

 

 

Solution2: Start at timestamp

GGSCI (WINDOWS01) 1> alter extract ext01 begin 2019-10-05

EXTRACT altered.

GGSCI (WINDOWS01) 2> info ext01 detail

EXTRACT EXT01 Initialized 2019-10-08 16:52 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:08 ago)

VAM Read Checkpoint 2019-10-05 00:00:00.000000

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

D:\OGG\dirdat\TR 30 1494 200 EXTTRAIL

Extract Source Begin End

https://dmmaxwell.wordpress.com/2015/04/30/proc-convert-lsn-from-hex-to-decimal-for-fn_dblog/

http://www.rentadba.eu/2015/06/03/convert-lsn-to-numeric/

Leave a Reply

Your email address will not be published. Required fields are marked *