Raj Kiran's

Logdump: finding RBA using SCN, Timestamp, finding the records

Topics:
Finding RBA Using Timestamp
Finding RBA Using SCN
Finding records in a table using Filter

get either SCN or Timestamp from database and save it

22:18:53 SQL> select scn_to_timestamp(current_scn), current_scn from v$database;

SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_SCN
--------------------------------------------------------------------------- --------------------
03-MAY-13 10.18.50.000000000 PM 6808061561351

1. Using sfts or scanfortimestamp

now: start logdump

Oracle GoldenGate Log File Dump Utility
Version 11.1.1.1.2_01 OGGCORE_11.1.1.1.3_PLATFORMS_111209.1035

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

usertoken on
ggstoken on
ghdr on
detail on

Logdump 5 >open /goldengate/traildata/ORCL/zb001058
Current LogTrail is /goldengate/traildata/ORCL/zb001058
Logdump 6 >sfts 2013/05/03 10:18:50
Scan for timestamp >= 2013/05/03 17:18:50.000.000 GMT

2013/05/03 11:35:25.208.987 FileHeader Len 1284 RBA 0
Name: *FileHeader*
3000 01b5 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
0002 3200 0004 2000 0000 3300 0008 02f2 0208 1205 | ..2... ...3.........
f59b 3400 001f 001d 7572 693a 736c 6373 6264 6231 | ..4.....uri:hostnode1
343a 3a67 6f6c 6465 6e67 6174 653a 3131 3136 0000 | 4::goldengate:1116..
2d00 2b2f 676f 6c64 656e 6761 7465 2f74 7261 696c | -.+/goldengate/trail
6461 7461 2f44 522f 434f 4e46 3243 544f 432f 7a62 | data/ORCL/zb
3030 3130 3538 3700 0001 0138 0000 0400 0004 2239 | 0010587....8......"9

Logdump 7 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 33 (x0021) IO Time : 2013/05/03 11:31:15.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x01) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 6682 AuditPos : 1256873432
Continued : N (x00) RecCount : 1 (x01)

2013/05/03 11:31:15.000.000 FieldComp Len 33 RBA 1292
Name: RAJ.USER_INFO
After Image: Partition 4 GU m
0000 000b 0000 0007 3238 3133 3537 3100 0300 0e00 | ........2813571.....
0000 0a31 3336 3736 3035 3834 38 | ...1367605848
Column 0 (x0000), Len 11 (x000b)
Column 3 (x0003), Len 14 (x000e)

User tokens: 18 bytes
5343 4e00 3638 3037 3833 3738 3737 3832 3000 | SCN.6807837877820.

GGS tokens:
5200 0014 4141 434e 4464 4149 3641 4142 6331 6b41 | R...AACNDdAI6AABc1kA
4244 0001 | BD..

2) Using SCN

Logdump 14> filter usertoken scn >=6808061561351
Scan for timestamp >= 2013/05/04 05:18:50.000.000 GMT

2013/05/03 22:20:20.000.000 FieldComp Len 33 RBA 278541
Name: RAJ.USER_INFO
Before Image: Partition 4 GU b
0000 000b 0000 0007 3339 3634 3538 3300 0300 0e00 | ........3964583.....
0000 0a31 3336 3736 3434 3133 33 | ...1367644133

OR

3) filter using csn

Logdump 18 >filter usertoken csn >=6808061561351

logdump
ghdr on
usertoken on
detail on
ggstoken on

Logdump 19 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 33 (x0021) IO Time : 2013/05/03 22:20:20.000.000
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 6726 AuditPos : 39170576
Continued : N (x00) RecCount : 1 (x01)

2013/05/03 22:20:20.000.000 FieldComp Len 33 RBA 278541
Name: RAJ.USER_INFO
Before Image: Partition 4 GU b
0000 000b 0000 0007 3339 3634 3538 3300 0300 0e00 | ........3964583.....
0000 0a31 3336 3736 3434 3133 33 | ...1367644133
Column 0 (x0000), Len 11 (x000b)
Column 3 (x0003), Len 14 (x000e)

User tokens: 18 bytes
5343 4e00 3638 3038 3036 3136 3731 3436 3700 | SCN.6808061671467.

GGS tokens:
5200 0014 4141 434e 4464 4147 6541 4144 7a39 4141 | R...AACNDdAGeAADz9AA
4273 0001 4c00 000d 3638 3038 3036 3136 3731 3436 | Bs..L...680806167146
3736 0000 0c34 3434 342e 3130 2e39 3937 38 | 76...4444.10.9978

Filtering suppressed 1455 records

4. Finding Records in table using FILTER

Logdump 172 >open /tmp/yp032733
Current LogTrail is /tmp/yp032733
Logdump 173 >filter include filename RAJ.USER_LIST
Logdump 174 >N
Scanned 10000 records, RBA 19431038, 2013/02/11 17:01:24.010.061
Scanned 20000 records, RBA 39075232, 2013/02/11 17:01:49.005.345
<o/p truncated>
Scanned 240000 records, RBA 463168443, 2013/02/11 17:10:41.005.732
Scanned 250000 records, RBA 483308009, 2013/02/11 17:11:06.000.784
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 136 (x0088) IO Time : 2013/02/11 17:11:15.005.240
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 19691 AuditPos : 2825134096
Continued : N (x00) RecCount : 1 (x01)

2013/02/11 17:11:15.005.240 Insert Len 136 RBA 490378100
Name: RAJ.USER_LIST
After Image: Partition 4 GU b
0000 0018 0000 0014 3133 3732 3331 3838 3236 3632 | ........137231882662
3738 3534 3735 3620 0001 000a 0000 0000 0000 0000 | 7854756 ............
0000 0002 0004 ffff 0000 0003 000a 0000 0000 0000 | ....................
0000 0001 0004 0004 ffff 0000 0005 000a 0000 0000 | ....................
0000 004c 311e 0006 000a 0000 0000 0000 0000 0001 | ...L1...............
0007 000e 0000 000a 3133 3630 3633 3134 3730 0008 | ........1360631470..
000e 0000 000a 3133 3630 3633 3134 3730 | ......1360631470
Column 0 (x0000), Len 24 (x0018)
Column 1 (x0001), Len 10 (x000a)
Column 2 (x0002), Len 4 (x0004)
Column 3 (x0003), Len 10 (x000a)
Column 4 (x0004), Len 4 (x0004)
Column 5 (x0005), Len 10 (x000a)
Column 6 (x0006), Len 10 (x000a)
Column 7 (x0007), Len 14 (x000e)
Column 8 (x0008), Len 14 (x000e)

User tokens: 22 bytes
746b 6e2d 7363 6e00 3637 3338 3530 3039 3832 3734 | tkn-scn.673850098274
3900 | 9.

Filtering suppressed 253736 records

Raj Kiran's

Saturday, May 4, 2013

3 comments: