SCN information from Goldengate trail files

Goldengate trails by default doesnot contain the SCN information. To extract this in the trail files we need to use the TOKENS parameter to the table mapping. TOKENS would store the SCN information for each transaction with an extra filed 'token' in the trails during the capture.

Here is a sample extract parameter to accomplish:

EXTRACT E_SITEA
SETENV (ORACLE_SID=ORCL)
GETENV (ORACLE_SID)
USERID orclgg, PASSWORD oracle
EXTTRAIL D:/GG11/dirdat/ea
TRANLOGOPTIONS EXCLUDEUSER ORCLGG
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 60 SECONDS, RATE
GETUPDATEBEFORES
TABLE sitea.products, tokens (tk-scn=@getenv("ORATRANSACTION","SCN));

Now start the extract and see the trail file generated using LOGDUMP.

OGG_HOME> logdump
Logdump 275 >open D:/GG11/dirdat/ea000004
Current LogTrail is D:\GG11\dirdat\ea000004
Logdump 276 >ghdr on
Logdump 277 >detail data
Logdump 278 >usertoken detail
Logdump 279 >n

2011/12/20 20:24:26.633.000 FileHeader Len 952 RBA 0
Name: *FileHeader*
3000 01b0 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
0002 3200 0004 2000 0000 3300 0008 02f1 daba b318 | ..2... ...3.........
a528 3400 002e 002c 7572 693a 564d 5241 4748 4156 | .(4....,uri:xxxxx
2d4c 4150 3a69 6463 3a6f 7261 636c 653a 636f 6d3a | -LAP:idc:oracle:com:
6472 6976 652d 443a 4747 3131 3600 0019 0017 443a | drive-D:GG116.....D:
2f47 4731 312f 6469 7264 6174 2f65 6130 3030 3030 | /GG11/dirdat/ea00000
3437 0000 0101 3800 0004 0000 0004 39ff 0008 0000 | 47....8.......9.....

Logdump 280 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 0 (x0000) IO Time : 2011/12/20 20:24:26.592.000
IOType : 150 (x96) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 0 (x00)

2011/12/20 20:17:15.000.000 Insert Len 39 RBA 1022
Name: SITEB.PRODUCTS
After Image: Partition 4 GU s
0000 000a 0000 0000 0000 0000 0002 0001 0007 0000 | ....................
0003 4a4a 4a00 0200 0a00 0000 0000 0000 0000 14 | ..JJJ..............
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0002 | ..........
Column 1 (x0001), Len 7 (x0007)
0000 0003 4a4a 4a | ....JJJ
Column 2 (x0002), Len 10 (x000a)
0000 0000 0000 0000 0014 | ..........

User tokens: 15 bytes
tk-scn : 1109315 -->> SCN number