Oracle GoldenGate Logdump分析工具详解

1. Introduction

Logdump is a standard utility that comes with the Oracle GoldenGate (OGG) application. Logdump enables the user to view and scan for transactions and records, obtain transaction statistics, and save data contained within OGG Trails. In this article we shall present examples of the Oracle GoldenGate Log File Dump Utility version 12.1.2 functionality that aid in troubleshooting data issues, determining database recovery points, and OGG group load balancing.

2. Main Article

To fully comprehend the data available via Logdump, one must first know something about the OGG Trail and its record layout. The following diagram provides a simplified view of an OGG Trail.

The File Header contains a wealth of information about the physical environment that produced the Trail file and Trail file contents. This information includes:

GroupID	Tokens
Trail Info	Signature, Compatibility, Character Set, Creation Time, URI, URI History, File Name, Multi-part, Trail Sequence Number, File Size, First CSN, Last CSN, First IO Time, Last IO Time, Log BSN
Machine Info	Operating System Name, Node Name, Release, Version, Hardware
Database Info	Vendor, Name, Character Set, Major Version, Minor Version, Version String, Client Character Set, Client Version String, Client NCharacter Set, Db Locale, Db NCharacter Set, Db Object Metadata, Db Time Zone
Producer Info	Name, Data Source, Major Version, Minor Version, Maintenance Level, Bug Fix Level, Build Number, Version String
Continuity Info	Recovery Mode

Each source operation captured is encapsulated into a unique transaction record. This record consists of a GoldenGate Header (GHDR), variable length data area, and GGS and User Tokens.

2.1 GHDR

The GoldenGate Header, or GHDR, area details information about the source operation and transaction. This includes the source commit timestamp (IO Time token), record length, database operation type (IO Type), whether this record is a before or after data image, and source table name. The GHDR also contains information that denotes each record as the beginning record of a transaction, the ending record of a transaction, a record between the beginning and end of a transaction, or a single record transaction.

2.2 Data Area

The variable length data area contains the atomic data for the source database operation. This includes the column index number and data changed for an after image, or original column data for a before image.

2.3 GGS Tokens

GoldenGate Tokens written for each transaction record include the source database row id, source database change number (LOGCSN), and source database transaction id (TRANID).

2.4 User Tokens

User Tokens are data defined by the user and added to the Trail via the Extract @TOKEN column conversion function.

Now that we know the basic layout for an OGG Trail, lets see what information we can view via Logdump.

2.5 Getting Started With Logdump

Logdump is run from a terminal window. When the application starts, a banner is displayed showing the version and copyright information, followed by the Logdump prompt. The command open [trail] will create a shared read file handler to the designated trail file.

[lpenton@oelora114 OGG_East]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316

Logdump 883 >open ./dirdat/pe000038

Current LogTrail is /home/lpenton/OGG/OGG_East/dirdat/pe000038

You may notice in the above example the number “883” in the Logdump prompt. Logdump maintains a history buffer of all commands issued. There are 883 items in my Logdump command buffer which may be viewed or re-executed via the help, fc, and ! commands. To learn more about the usage of these commands, refer to my blog article Oracle GoldenGate: Using the GGSCI !, History, and FC Commands. The article is written using GGSCI, but the command function and use is the same for Logdump.

2.6 Getting Record Counts

The command count will return data for all records contain in the trail. This data includes, total data bytes, average data bytes per record, totals for each type of database operation, the number of before and after image records, average number of transactions in the Trail, average data bytes per transaction, and average number of records per transaction.

Logdump 884 >count

LogTrail /home/lpenton/OGG/OGG_East/dirdat/pe000038 has 227838 records

Total Data Bytes 22672115

Avg Bytes/Record 99

Insert 76626

FieldComp 145759

GGSPKUpdate 5437

RestartOK 1

DDL 14

Others 1

Before Images 75598

After Images 152239

Average of 146209 Transactions

Bytes/Trans ..... 229

Records/Trans ... 1

Files/Trans ..... 0

This is great as an overview, but it does not provide any level of real detail about the Extract’s workload. The command detail on tells Logdump to display the totals and averages shown above and then sort the data and display it for each unique table contained within the Trail.

Logdump 885 >detail on

Logdump 886 >count

LogTrail /home/lpenton/OGG/OGG_East/dirdat/pe000038 has 227838 records

Total Data Bytes 22672115

Avg Bytes/Record 99

Insert 76626

FieldComp 145759

GGSPKUpdate 5437

RestartOK 1

DDL 14

Others 1

Before Images 75598

After Images 152239

Average of 146209 Transactions

Bytes/Trans ..... 229

Records/Trans ... 1

Files/Trans ..... 0

Partition 0

Total Data Bytes 16256

Avg Bytes/Record 1083

RestartOK 1

DDL 14

After Images 15

*FileHeader* Partition 0

Total Data Bytes 1455

Avg Bytes/Record 1455

Others 1

EAST.CATEGORIES Partition 4

Total Data Bytes 3023

Avg Bytes/Record 143

Insert 21

After Images 21

EAST.CATEGORIES_DESCRIPTION Partition 4

Total Data Bytes 930

Avg Bytes/Record 44

Insert 21

After Images 21

EAST.CUSTOMERS Partition 4

Total Data Bytes 63999

Avg Bytes/Record 144

Insert 443

After Images 443

EAST.CUSTOMERS_INFO Partition 4

Total Data Bytes 640059

Avg Bytes/Record 66

Insert 443

FieldComp 9126

Before Images 4563

After Images 5006

EAST.CUSTOMERS_LKUP Partition 4

Total Data Bytes 3326

Avg Bytes/Record 92

Insert 36

After Images 36

EAST.NEXT_CUST Partition 4

Total Data Bytes 19242

Avg Bytes/Record 21

Insert 1

FieldComp 437

GGSPKUpdate 437

Before Images 437

After Images 438

EAST.NEXT_ORDER Partition 4

Total Data Bytes 220014

Avg Bytes/Record 21

Insert 1

FieldComp 5000

GGSPKUpdate 5000

Before Images 5000

After Images 5001

EAST.ORDERS Partition 4

Total Data Bytes 3480300

Avg Bytes/Record 695

Insert 5001

After Images 5001

EAST.ORDERS_PRODUCTS Partition 4

Total Data Bytes 9274325

Avg Bytes/Record 141

Insert 65570

After Images 65570

EAST.ORDERS_STATUS_HISTORY Partition 4

Total Data Bytes 660239

Avg Bytes/Record 131

Insert 5002

After Images 5002

EAST.ORDERS_TOTAL Partition 4

Total Data Bytes 343

Avg Bytes/Record 114

Insert 3

After Images 3

EAST.PRODUCTS Partition 4

Total Data Bytes 8270694

Avg Bytes/Record 63

Insert 28

FieldComp 131196

Before Images 65598

After Images 65626

EAST.PRODUCTS_DESCRIPTION Partition 4

Total Data Bytes 17126

Avg Bytes/Record 611

Insert 28

After Images 28

EAST.PRODUCTS_TO_CATEGORIES Partition 4

Total Data Bytes 784

Avg Bytes/Record 28

Insert 28

After Images 28

If you are new to OGG you maybe confused by the database operation types FieldComp and GGSPkUpdate.

FieldComp identifies records where a Compressed Update operation was written to the source database transaction log. Because most databases only write the primary key or table index data, column index, and change data to the transaction log, the majority of Update operations will be identified as FieldComp in the Trail.

GGSPkUpdate identifies records identified as a Primary Key Update in the database transaction log.

2.7 View File Header

The command fileheader detail will set Logdump to display each file header token when the header record is encountered.

To view the file header record, I want to first ensure Logdump is positioned at the beginning of the Trail. The command pos 0, tells Logdump to position to relative byte address, or RBA, 0 within the file. RBA 0 is the beginning of the file.

We then execute the command fileheader detail to set the display option and then the command n, which is shorthand fornext, to display the next Trail record.

Logdump 893 >pos 0

Reading forward from RBA 0

Logdump 894 >fileheader detail

Logdump 895 >n

2014/03/17 08:30:56.729.864 FileHeader Len 1455 RBA 0

Name: *FileHeader*

3000 0341 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..A0...GG..TL..1...

0004 3200 0004 2000 0000 3300 0008 02f2 1b00 0c18 | ..2... ...3.........

b308 3400 0032 0030 7572 693a 6f65 6c6f 7261 3131 | ..4..2.0uri:oelora11

343a 3a68 6f6d 653a 6c70 656e 746f 6e3a 4f47 473a | 4::home:lpenton:OGG:

4f47 475f 4561 7374 3a50 5f49 4541 5354 3500 0036 | OGG_East:P_IEAST5..6

3500 0032 0030 7572 693a 6f65 6c6f 7261 3131 343a | 5..2.0uri:oelora114:

3a68 6f6d 653a 6c70 656e 746f 6e3a 4f47 473a 4f47 | :home:lpenton:OGG:OG

GroupID x30 '0' TrailInfo Info x00 Length 833

3000 0341 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..A0...GG..TL..1...

0004 3200 0004 2000 0000 3300 0008 02f2 1b00 0c18 | ..2... ...3.........

b308 3400 0032 0030 7572 693a 6f65 6c6f 7261 3131 | ..4..2.0uri:oelora11

343a 3a68 6f6d 653a 6c70 656e 746f 6e3a 4f47 473a | 4::home:lpenton:OGG:

4f47 475f 4561 7374 3a50 5f49 4541 5354 3500 0036 | OGG_East:P_IEAST5..6

3500 0032 0030 7572 693a 6f65 6c6f 7261 3131 343a | 5..2.0uri:oelora114:

3a68 6f6d 653a 6c70 656e 746f 6e3a 4f47 473a 4f47 | :home:lpenton:OGG:OG

475f 4561 7374 3a49 455f 4541 5354 3600 0013 0011 | G_East:IE_EAST6.....

2e2f 6469 7264 6174 2f70 6530 3030 3033 3837 0000 | ./dirdat/pe0000387..

0101 3800 0004 0000 0026 3900 0008 0000 0000 02d8 | ..8......&9.........

cd38 3a00 0081 0735 3837 3535 3337 0000 0000 0000 | .8:....5875537......

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 0000 0000 0000 003b 0000 8107 | ...............;....

3631 3434 3230 3000 0000 0000 0000 0000 0000 0000 | 6144200.............

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 3c00 0008 02f2 1b00 126e 40c0 | ........<........n@.

3d00 0008 02f2 1b00 21cf 0918 3eff 0081 0000 0000 | =.......!...>.......

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 003f ff00 0800 0000 0000 0000 0040 ff00 | .....?...........@..

8100 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 0000 41ff 0008 0000 0000 0000 | ..........A.........

0000 4200 0033 0000 2c2e 2e2e 2e2e 2e2e 2e2e 2e2e | ..B..3..,...........

2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e | ....................

2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e | .............

TokenID x30 '0' Signature Info x00 Length 8

4747 0d0a 544c 0a0d | GG..TL..

TokenID x31 '1' Compatibility Info x00 Length 2

0004 | ..

TokenID x32 '2' Charset Info x00 Length 4

2000 0000 | ...

TokenID x33 '3' CreationTime Info x00 Length 8

02f2 1b00 0c18 b308 | ........

TokenID x34 '4' URI Info x00 Length 50

0030 7572 693a 6f65 6c6f 7261 3131 343a 3a68 6f6d | .0uri:oelora114::hom

653a 6c70 656e 746f 6e3a 4f47 473a 4f47 475f 4561 | e:lpenton:OGG:OGG_Ea

7374 3a50 5f49 4541 5354 | st:P_IEAST

TokenID x35 '5' URIHistory Info x00 Length 54

3500 0032 0030 7572 693a 6f65 6c6f 7261 3131 343a | 5..2.0uri:oelora114:

3a68 6f6d 653a 6c70 656e 746f 6e3a 4f47 473a 4f47 | :home:lpenton:OGG:OG

475f 4561 7374 3a49 455f 4541 5354 | G_East:IE_EAST

TokenID x36 '6' Filename Info x00 Length 19

0011 2e2f 6469 7264 6174 2f70 6530 3030 3033 38 | .../dirdat/pe000038

TokenID x37 '7' MultiPart Info x00 Length 1

01 | .

TokenID x38 '8' Seqno Info x00 Length 4

0000 0026 | ...&

TokenID x39 '9' FileSize Info x00 Length 8

0000 0000 02d8 cd38 | .......8

TokenID x3a ':' FirstCSN Info x00 Length 129

0735 3837 3535 3337 0000 0000 0000 0000 0000 0000 | .5875537............

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

TokenID x3b ';' LastCSN Info x00 Length 129

0736 3134 3432 3030 0000 0000 0000 0000 0000 0000 | .6144200............

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

TokenID x3c '<' FirstIOTime Info x00 Length 8

02f2 1b00 126e 40c0 | .....n@.

TokenID x3d '=' LastIOTime Info x00 Length 8

02f2 1b00 21cf 0918 | ....!...

TokenID x3e '>' LOGBSN Info xff Length 129

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

TokenID x3f '?' BITFLAGS Info xff Length 8

0000 0000 0000 0000 | ........

TokenID x40 '@' UNUSEDSEQUENCE Info xff Length 129

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

TokenID x41 'A' UNUSEDINT64 Info xff Length 8

0000 0000 0000 0000 | ........

TokenID x42 'B' TrailEncryption Info x00 Length 51

0000 2c2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e | ..,.................

2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e 2e2e | ....................

2e2e 2e2e 2e2e 2e00 0000 00 | ...........

GroupID x31 '1' MachineInfo Info x00 Length 107

3100 006b 3000 0007 0005 4c69 6e75 7831 0000 0b00 | 1..k0.....Linux1....

096f 656c 6f72 6131 3134 3200 0018 0016 322e 362e | .oelora1142.....2.6.

3332 2d34 3030 2e32 392e 322e 656c 3575 656b 3300 | 32-400.29.2.el5uek3.

0025 0023 2331 2053 4d50 2057 6564 204a 756c 2031 | .%.##1 SMP Wed Jul 1

3720 3130 3a35 383a 3336 2050 4454 2032 3031 3334 | 7 10:58:36 PDT 20134

0000 0800 0678 38 | .....x8

TokenID x30 '0' Sysname Info x00 Length 7

0005 4c69 6e75 78 | ..Linux

TokenID x31 '1' Nodename Info x00 Length 11

0009 6f65 6c6f 7261 3131 34 | ..oelora114

TokenID x32 '2' Release Info x00 Length 24

0016 322e 362e 3332 2d34 3030 2e32 392e 322e 656c | ..2.6.32-400.29.2.el

3575 656b | 5uek

TokenID x33 '3' Version Info x00 Length 37

0023 2331 2053 4d50 2057 6564 204a 756c 2031 3720 | .##1 SMP Wed Jul 17

3130 3a35 383a 3336 2050 4454 2032 3031 33 | 10:58:36 PDT 2013

TokenID x34 '4' Hardware Info x00 Length 8

0006 7838 365f 3634 | ..x86_64

GroupID x32 '2' DatabaseInfo Info x00 Length 360

3200 0168 3000 0002 0007 3100 0006 0004 4f52 434c | 2..h0.....1.....ORCL

3200 0006 0004 6f72 636c 3300 0004 0000 27e0 3400 | 2.....orcl3.....'.4.

0002 000b 3500 0002 0002 3600 00e7 00e5 4f72 6163 | ....5.....6.....Orac

6c65 2044 6174 6162 6173 6520 3131 6720 456e 7465 | le Database 11g Ente

7270 7269 7365 2045 6469 7469 6f6e 2052 656c 6561 | rprise Edition Relea

7365 2031 312e 322e 302e 342e 3020 2d20 3634 6269 | se 11.2.0.4.0 - 64bi

7420 5072 6f64 7563 7469 6f6e 0a50 4c2f 5351 4c20 | t Production.PL/SQL

5265 6c65 6173 6520 3131 2e32 2e30 2e34 2e30 202d | Release 11.2.0.4.0 -

2050 726f 6475 6374 696f 6e0a 434f 5245 0931 312e | Production.CORE.11.

322e 302e 342e 3009 5072 6f64 7563 7469 6f6e 0a54 | 2.0.4.0.Production.T

4e53 2066 6f72 204c 696e 7578 3a20 5665 7273 696f | NS for Linux: Versio

6e20 3131 2e32 2e30 2e34 2e30 202d 2050 726f 6475 | n 11.2.0.4.0 - Produ

6374 696f 6e0a 4e4c 5352 544c 2056 6572 7369 6f6e | ction.NLSRTL Version

2031 312e 322e 302e 342e 3020 2d20 5072 6f64 7563 | 11.2.0.4.0 - Produc

7469 6f6e 0a37 0000 0440 0000 0038 0000 0c00 0a31 | tion.7...@...8.....1

312e 322e 302e 342e 3039 0000 0400 0000 013a 0000 | 1.2.0.4.09.......:..

0200 003b 0000 0400 0000 013c 0000 1400 0000 1014 | ...;.......<........

1414 1414 1414 1414 1414 1411 1414 143d 0000 0500 | ...............=....

TokenID x30 '0' Vendor Info x00 Length 2

0007 | ..

TokenID x31 '1' Name Info x00 Length 6

0004 4f52 434c | ..ORCL

TokenID x32 '2' Instance Info x00 Length 6

0004 6f72 636c | ..orcl

TokenID x33 '3' Charset Info x00 Length 4

0000 27e0 | ..'.

TokenID x34 '4' MajorVersion Info x00 Length 2

000b | ..

TokenID x35 '5' MinorVersion Info x00 Length 2

0002 | ..

TokenID x36 '6' VerString Info x00 Length 231

00e5 4f72 6163 6c65 2044 6174 6162 6173 6520 3131 | ..Oracle Database 11

6720 456e 7465 7270 7269 7365 2045 6469 7469 6f6e | g Enterprise Edition

2052 656c 6561 7365 2031 312e 322e 302e 342e 3020 | Release 11.2.0.4.0

2d20 3634 6269 7420 5072 6f64 7563 7469 6f6e 0a50 | - 64bit Production.P

4c2f 5351 4c20 5265 6c65 6173 6520 3131 2e32 2e30 | L/SQL Release 11.2.0

2e34 2e30 202d 2050 726f 6475 6374 696f 6e0a 434f | .4.0 - Production.CO

5245 0931 312e 322e 302e 342e 3009 5072 6f64 7563 | RE.11.2.0.4.0.Produc

7469 6f6e 0a54 4e53 2066 6f72 204c 696e 7578 3a20 | tion.TNS for Linux:

5665 7273 696f 6e20 3131 2e32 2e30 2e34 2e30 202d | Version 11.2.0.4.0 -

2050 726f 6475 6374 696f 6e0a 4e4c 5352 544c 2056 | Production.NLSRTL V

6572 7369 6f6e 2031 312e 322e 302e 342e 3020 2d20 | ersion 11.2.0.4.0 -

5072 6f64 7563 7469 6f6e 0a | Production.

TokenID x37 '7' ClientCharset Info x00 Length 4

4000 0000 | @...

TokenID x38 '8' ClientVerString Info x00 Length 12

000a 3131 2e32 2e30 2e34 2e30 | ..11.2.0.4.0

TokenID x39 '9' ClientNCharset Info x00 Length 4

0000 0001 | ....

TokenID x3a ':' DbLocale Info x00 Length 2

0000 | ..

TokenID x3b ';' DbNCharset Info x00 Length 4

0000 0001 | ....

TokenID x3c '<' DbObjNameMetadata Info x00 Length 20

0000 0010 1414 1414 1414 1414 1414 1414 1114 1414 | ....................

TokenID x3d '=' DbTimeZone Info x00 Length 5

0003 474d 54 | ..GMT

GroupID x33 '3' ProducerInfo Info x00 Length 127

3300 007f 3000 0009 0007 4945 5f45 4153 5431 0000 | 3...0.....IE_EAST1..

0200 0332 0000 0200 0c33 0000 0200 0134 0000 0200 | ...2.....3.....4....

0235 0000 0200 0036 0000 0200 0037 0000 4a00 4856 | .5.....6.....7..J.HV

6572 7369 6f6e 2031 322e 312e 322e 302e 3020 3137 | ersion 12.1.2.0.0 17

3138 3530 3033 204f 4747 434f 5245 5f31 322e 312e | 185003 OGGCORE_12.1.

322e 302e 305f 504c 4154 464f 524d 535f 3133 3039 | 2.0.0_PLATFORMS_1309

3234 2e31 3331 36 | 24.1316

TokenID x30 '0' Name Info x00 Length 9

0007 4945 5f45 4153 54 | ..IE_EAST

TokenID x31 '1' DataSource Info x00 Length 2

0003 | ..

TokenID x32 '2' MajorVersion Info x00 Length 2

000c | ..

TokenID x33 '3' MinorVersion Info x00 Length 2

0001 | ..

TokenID x34 '4' MaintLevel Info x00 Length 2

0002 | ..

TokenID x35 '5' BugFixLevel Info x00 Length 2

0000 | ..

TokenID x36 '6' BuildNumber Info x00 Length 2

0000 | ..

TokenID x37 '7' VerString Info x00 Length 74

0048 5665 7273 696f 6e20 3132 2e31 2e32 2e30 2e30 | .HVersion 12.1.2.0.0

2031 3731 3835 3030 3320 4f47 4743 4f52 455f 3132 | 17185003 OGGCORE_12

2e31 2e32 2e30 2e30 5f50 4c41 5446 4f52 4d53 5f31 | .1.2.0.0_PLATFORMS_1

3330 3932 342e 3133 3136 5f46 424f | 30924.1316_FBO

GroupID x34 '4' ContinunityInfo Info x00 Length 8

3400 0008 3000 0004 | 4...0...

TokenID x30 '0' RecoveryMode Info x00 Length 4

0000 0001 | ....

In the file header record above, you can see a lot of information about my test environment and database. Take particular note of these two tokens:

TokenID x3a ':' FirstCSN Info x00 Length 129

0735 3837 3535 3337 0000 0000 0000 0000 0000 0000 | .5875537............

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

TokenID x3b ';' LastCSN Info x00 Length 129

0736 3134 3432 3030 0000 0000 0000 0000 0000 0000 | .6144200............

0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................

0000 0000 0000 0000 00 | .........

The tokens FirstCSN and LastCSN identify the beginning and ending CSN (SCN for Oracle Databases) for transaction records in this trail. This may come in handy should you ever need to perform a database recovery or start an OGG Group that reads the Trail at a specific CSN.

The LastCSN token is only populated when a Trail has been closed by its Extract. Trails are closed only when an Extract opens and begins writing to a new trail. If an Extract is in the STOPPED or ABEND state, or you are viewing the most current Trail; this token will contain no data.

2.8 View GHDR

The command ghdr on will tell Logdump to display the GoldenGate Header for each record in the Trail. The command sfh, which is shorthand for scanforheader, will read through the trail and display the first record with a GoldenGate Header.

Logdump 904 >ghdr on

Logdump 905 >sfh

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 141 (x008d) IO Time : 2014/03/25 12:08:43.003.270

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x00) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 73 AuditPos : 141483536

Continued : N (x00) RecCount : 1 (x01)

2014/03/25 12:08:43.003.270 Insert Len 141 RBA 2777

Name: EAST.CATEGORIES

After Image: Partition 4 G b

0000 000a 0000 0000 0000 0000 0001 0001 0019 0000 | ....................

0015 6361 7465 676f 7279 5f68 6172 6477 6172 652e | ..category_hardware.

6769 6600 0200 0a00 0000 0000 0000 0000 0000 0300 | gif.................

0a00 0000 0000 0000 0000 0100 0400 1f00 0032 3031 | .................201

312d 3034 2d30 343a 3038 3a33 313a 3030 2e30 3030 | 1-04-04:08:31:00.000

3030 3030 3030 0005 001f ffff 3139 3030 2d30 312d | 000000......1900-01-

3031 3a30 303a 3030 3a30 302e 3030 3030 3030 3030 | 01:00:00:00.00000000

Column 0 (x0000), Len 10 (x000a)

Column 1 (x0001), Len 25 (x0019)

Column 2 (x0002), Len 10 (x000a)

Column 3 (x0003), Len 10 (x000a)

Column 4 (x0004), Len 31 (x001f)

Column 5 (x0005), Len 31 (x001f)

In the record above, the GHDR is the data below the solid line and above the after image line. The GHDR identifies this record as an After Image (BeforeAfter), is 141 bytes in length (RecLength), was committed in the source database at on 2014/03/25 at 12:08:43.003270 (IO Time), is the start of a multi-record transaction (TransInd 0), and is an insert operation (IOType) to the table EAST.CATEGORIES (Name).

The remaining data is the hexadecimal representation of the inserted record and table column index information. From this view of the data we cannot determine much about this transaction record, so lets display two additional options.

2.9 View Data Details and GGS Tokens

Lets look at the record shown above again, but this time we will tell Logdump to display the GGS Tokens and additional details about the transaction record.

First, we need to reposition Logdump’s read pointer to a location prior to the record. In the record details shown above, we see that the starting point for this record is RBA 2777. The command pos 2776 will place the read pointer to byte offset 2,776 in the Trail file. The command detail data will tell Logdump to display details about the data in this record, and the commandggstoken detail will cause Logdump to display data from the GGS Token area. Then we sfh to scan for the record header and display the complete record.

Logdump 911 >pos 2776

Reading forward from RBA 2776

Logdump 912 >detail data

Logdump 913 >ggstoken detail

Logdump 914 >sfh

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 141 (x008d) IO Time : 2014/03/25 12:08:43.003.270

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x00) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 73 AuditPos : 141483536

Continued : N (x00) RecCount : 1 (x01)

2014/03/25 12:08:43.003.270 Insert Len 141 RBA 2777

Name: EAST.CATEGORIES

After Image: Partition 4 G b

0000 000a 0000 0000 0000 0000 0001 0001 0019 0000 | ....................

0015 6361 7465 676f 7279 5f68 6172 6477 6172 652e | ..category_hardware.

6769 6600 0200 0a00 0000 0000 0000 0000 0000 0300 | gif.................

0a00 0000 0000 0000 0000 0100 0400 1f00 0032 3031 | .................201

312d 3034 2d30 343a 3038 3a33 313a 3030 2e30 3030 | 1-04-04:08:31:00.000

3030 3030 3030 0005 001f ffff 3139 3030 2d30 312d | 000000......1900-01-

3031 3a30 303a 3030 3a30 302e 3030 3030 3030 3030 | 01:00:00:00.00000000

Column 0 (x0000), Len 10 (x000a)

0000 0000 0000 0000 0001 | ..........

Column 1 (x0001), Len 25 (x0019)

0000 0015 6361 7465 676f 7279 5f68 6172 6477 6172 | ....category_hardwar

652e 6769 66 | e.gif

Column 2 (x0002), Len 10 (x000a)

0000 0000 0000 0000 0000 | ..........

Column 3 (x0003), Len 10 (x000a)

0000 0000 0000 0000 0001 | ..........

Column 4 (x0004), Len 31 (x001f)

0000 3230 3131 2d30 342d 3034 3a30 383a 3331 3a30 | ..2011-04-04:08:31:0

302e 3030 3030 3030 3030 30 | 0.000000000

Column 5 (x0005), Len 31 (x001f)

ffff 3139 3030 2d30 312d 3031 3a30 303a 3030 3a30 | ..1900-01-01:00:00:0

302e 3030 3030 3030 3030 30 | 0.000000000

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 4157 476f 4141 4541 4141 4149 4c41 4141 0001 | AAAWGoAAEAAAAILAAA..

TokenID x4c 'L' LOGCSN Info x00 Length 7

3631 3838 3238 38 | 6188288

TokenID x36 '6' TRANID Info x00 Length 10

3137 2e31 382e 3234 3437 | 17.18.2447

Once again the GHDR and Data records are displayed, along with the data for each record column, the Oracle Row Id of the source data, the source database CSN for this transaction, and the source database Transaction Id.

2.10 Positioning and Filtering Data

So far we have seen that we can read Trail data by positioning to a byte offset within the Trail and either scanning for the GoldenGate Header, or choosing to select the next record. Lets take this a step further to show more of the advanced functionality in Logdump.

Earlier we displayed a count for the data contained in the trail by source table. Lets use Logdump to locate the last record for the EAST.ORDERS table and then save all of the records with the same CSN to a new Trail file.

First we set a data filter for the table via the command filter inc filename EAST.ORDERS. This tells Logdump to ignore everything except the EAST.ORDERS table. Next, pos eof will position Logdump’s read pointer to the end of the Trail. To tell Logdump to read the Trail backwards, we use the command pos rev. The command n will then have Logdump scan backwards through the trail and display the first record encountered for the EAST.ORDERS table.

Logdump 933 >filter inc filename EAST.ORDERS

Logdump 934 >pos eof

Reading forward from RBA 14469316

Logdump 935 >pos rev

Reading in reverse from RBA 14469316

Logdump 936 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 705 (x02c1) IO Time : 2014/04/07 10:06:16.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 101 AuditPos : 223086608

Continued : N (x00) RecCount : 1 (x01)

2014/04/07 10:06:16.000.000 Insert Len 705 RBA 63547

Name: EAST.ORDERS

After Image: Partition 4 G s

0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................

0000 0000 0000 0001 0002 0010 0000 000c 4c6f 7265 | ................Lore

6e20 5065 6e74 6f6e 0003 0004 ffff 0000 0004 0014 | n Penton............

0000 0010 3338 3230 2042 7572 6775 6e64 7920 5374 | ....3820 Burgundy St

0005 0004 ffff 0000 0006 000f 0000 000b 4e65 7720 | ................New

4f72 6c65 616e 7300 0700 0900 0000 0537 3031 3137 | Orleans........70117

0008 000d 0000 0009 4c6f 7569 7369 616e 6100 0900 | ........Louisiana...

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 4157 534c 4141 4541 4141 414a 3141 4141 0001 | AAAWSLAAEAAAAJ1AAA..

TokenID x4c 'L' LOGCSN Info x00 Length 7

3831 3633 3430 34 | 8163404

TokenID x36 '6' TRANID Info x00 Length 9

312e 3239 2e39 3835 30 | 1.29.9850

Filtering suppressed 12 records

The GGS Token LOGCSN shows that the Oracle SCN for this transaction is 8163404. Now lets find all records the source database associated with that CSN.

First, we use the command filter clear all to reset the existing Logdump data filter. Then we set a new filter to scan for all records associated with the desired LOGCSN, filter logcsn 8163404. Tell Logdump to start scanning forward again, pos for, and to reposition to the beginning of the trail, pos 0. The count command will then tell us how many records in this trail have this CSN.

Logdump 937 >pos for

Reading forward from RBA 63547

Logdump 938 >pos 0

Reading forward from RBA 0

Logdump 939 >filter reset all

Logdump 940 >filter logcsn 8163404

Logdump 941 >count

LogTrail /home/lpenton/OGG/OGG_East/dirdat/pe000038 has 2 records

Total Data Bytes 1870

Avg Bytes/Record 935

Insert 1

DDL 1

After Images 2

Filtering matched 2 records

suppressed 255 records

Average of 2 Transactions

Bytes/Trans ..... 983

Records/Trans ... 1

Files/Trans ..... 1

The count command shows there are two records in the trail associated with this CSN. Executing the n command twice, will show the two records.

Logdump 942 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 705 (x02c1) IO Time : 2014/04/07 10:06:16.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 101 AuditPos : 223086608

Continued : N (x00) RecCount : 1 (x01)

2014/04/07 10:06:16.000.000 Insert Len 705 RBA 63547

Name: EAST.ORDERS

After Image: Partition 4 G s

0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................

0000 0000 0000 0001 0002 0010 0000 000c 4c6f 7265 | ................Lore

6e20 5065 6e74 6f6e 0003 0004 ffff 0000 0004 0014 | n Penton............

0000 0010 3338 3230 2042 7572 6775 6e64 7920 5374 | ....3820 Burgundy St

0005 0004 ffff 0000 0006 000f 0000 000b 4e65 7720 | ................New

4f72 6c65 616e 7300 0700 0900 0000 0537 3031 3137 | Orleans........70117

0008 000d 0000 0009 4c6f 7569 7369 616e 6100 0900 | ........Louisiana...

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 4157 534c 4141 4541 4141 414a 3141 4141 0001 | AAAWSLAAEAAAAJ1AAA..

TokenID x4c 'L' LOGCSN Info x00 Length 7

3831 3633 3430 34 | 8163404

TokenID x36 '6' TRANID Info x00 Length 9

312e 3239 2e39 3835 30 | 1.29.9850

Filtering suppressed 206 records

Logdump 943 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x00)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 1165 (x048d) IO Time : 2014/04/07 10:06:16.000.000

IOType : 160 (xa0) OrigNode : 0 (x00)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 0 AuditPos : 0

Continued : N (x00) RecCount : 1 (x01)

2014/04/07 10:06:16.000.000 DDLOP Len 1165 RBA 64366

Name:

After Image: Partition 0 G s

2c43 353d 2730 272c 2c42 373d 2730 272c 2c42 323d | ,C5='0',,B7='0',,B2=

2738 3836 3337 272c 2c43 3231 3d27 272c 2c42 333d | '88637',,C21='',,B3=

2745 4153 5427 2c2c 4234 3d27 4f52 4445 5253 5f50 | 'EAST',,B4='ORDERS_P

524f 4455 4354 5327 2c2c 4331 323d 2745 4153 5427 | RODUCTS',,C12='EAST'

2c2c 4331 333d 274f 5244 4552 535f 5052 4f44 5543 | ,,C13='ORDERS_PRODUC

5453 272c 2c42 353d 2754 4142 4c45 272c 2c42 363d | TS',,B5='TABLE',,B6=

2754 5255 4e43 4154 4527 2c2c 4238 3d27 272c 2c42 | 'TRUNCATE',,B8='',,B

GGS tokens:

TokenID x44 'D' DDL Info x00 Length 24

4541 5354 004f 5244 4552 535f 5052 4f44 5543 5453 | EAST.ORDERS_PRODUCTS

0000 004e | ...N

TokenID x4c 'L' LOGCSN Info x00 Length 7

3831 3633 3430 34 | 8163404

TokenID x36 '6' TRANID Info x00 Length 10

3130 2e32 362e 3937 3634 | 10.26.9764

The Logdump output shows that 206 records were skipped because they were not part of the desired CSN. Now we can use the save command to create a new Trail file that will contain all of the records.

2.11 Save Records To New Trail File

To save the records to a new Trail file, first let’s make sure we are at the initial record associated with the CSN. As shown prior, position Logdump at the beginning of the Trail file with the command pos 0 and then scan to the first record via the ncommand. This displays the Insert record for EAST.ORDERS at RBA 63547 in the Trail. To make sure this record is saved to the new Trail, we need to position the Logdump read pointer to the record prior to this one.

Set Logdump to read the Trail in reverse, pos rev, and then scan for the header of the previous record, sfh. This displays the previous record, a DDL operation performed on the EAST.ORDERS table at RBA 62311 in the Trail.

Logdump 944 >pos 0

Reading forward from RBA 0

Logdump 945 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x04)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 705 (x02c1) IO Time : 2014/04/07 10:06:16.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 101 AuditPos : 223086608

Continued : N (x00) RecCount : 1 (x01)

2014/04/07 10:06:16.000.000 Insert Len 705 RBA 63547

Name: EAST.ORDERS

After Image: Partition 4 G s

0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................

0000 0000 0000 0001 0002 0010 0000 000c 4c6f 7265 | ................Lore

6e20 5065 6e74 6f6e 0003 0004 ffff 0000 0004 0014 | n Penton............

0000 0010 3338 3230 2042 7572 6775 6e64 7920 5374 | ....3820 Burgundy St

0005 0004 ffff 0000 0006 000f 0000 000b 4e65 7720 | ................New

4f72 6c65 616e 7300 0700 0900 0000 0537 3031 3137 | Orleans........70117

0008 000d 0000 0009 4c6f 7569 7369 616e 6100 0900 | ........Louisiana...

GGS tokens:

TokenID x52 'R' ORAROWID Info x00 Length 20

4141 4157 534c 4141 4541 4141 414a 3141 4141 0001 | AAAWSLAAEAAAAJ1AAA..

TokenID x4c 'L' LOGCSN Info x00 Length 7

3831 3633 3430 34 | 8163404

TokenID x36 '6' TRANID Info x00 Length 9

312e 3239 2e39 3835 30 | 1.29.9850

Filtering suppressed 206 records

Logdump 946 >pos rev

Reading in reverse from RBA 63547

Logdump 947 >sfh

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x00)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 1138 (x0472) IO Time : 2014/04/07 10:06:16.000.000

IOType : 160 (xa0) OrigNode : 0 (x00)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 0 AuditPos : 0

Continued : N (x00) RecCount : 1 (x01)

2014/04/07 10:06:16.000.000 DDLOP Len 1138 RBA 62311

Name:

After Image: Partition 0 G s

2c43 353d 2730 272c 2c42 373d 2730 272c 2c42 323d | ,C5='0',,B7='0',,B2=

2738 3836 3335 272c 2c43 3231 3d27 272c 2c42 333d | '88635',,C21='',,B3=

2745 4153 5427 2c2c 4234 3d27 4f52 4445 5253 272c | 'EAST',,B4='ORDERS',

2c43 3132 3d27 4541 5354 272c 2c43 3133 3d27 4f52 | ,C12='EAST',,C13='OR

4445 5253 272c 2c42 353d 2754 4142 4c45 272c 2c42 | DERS',,B5='TABLE',,B

363d 2754 5255 4e43 4154 4527 2c2c 4238 3d27 272c | 6='TRUNCATE',,B8='',

2c42 393d 2745 4153 5427 2c2c 4337 3d27 272c 2c43 | ,B9='EAST',,C7='',,C

GGS tokens:

TokenID x44 'D' DDL Info x00 Length 15

4541 5354 004f 5244 4552 5300 0000 4e | EAST.ORDERS...N

TokenID x4c 'L' LOGCSN Info x00 Length 7

3831 3633 3336 37 | 8163367

TokenID x36 '6' TRANID Info x00 Length 9

352e 392e 3130 3031 30 | 5.9.10010

Logdump 948 >pos for

Reading forward from RBA 62311

Logdump 949 >save ./dirdat/zz999999 2 records

Saved 2 records to /home/lpenton/OGG/OGG_East/dirdat/zz999999

From this point in the Trail file, have Logdump read forward via the pos for command. To save the records to a new trail named zz999999, issue the command save ./dirdat/zz999999 2 records. The new Trail file will be created and will contain the header record and the two data records associated with the source database CSN.

3. Summary

With Logdump you can view data in transactions present for a trail file, identify missing transactions, and troubleshoot replication issues. In this article we presented a small subset of the commands and their possible use; however, there is much more to this utility and every Oracle GoldenGate Administrator should learn and understand this potent tool.

All site content is the property of Oracle Corp. Redistribution not allowed without written permission