Oracle GoldenGate: Logdump

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.

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:

GroupIDTokensTrail InfoSignature, 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 BSNMachine InfoOperating System Name, Node Name, Release, Version, HardwareDatabase InfoVendor, 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 ZoneProducer InfoName, Data Source, Major Version, Minor Version, Maintenance Level, Bug Fix Level, Build Number, Version StringContinuity InfoRecovery 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.

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.

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.

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).

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.

 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]$ ./logdumpOracle GoldenGate Log File Dump Utility for OracleVersion 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.Logdump 883 >open ./dirdat/pe000038Current 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.

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 >countLogTrail /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 onLogdump 886 >countLogTrail /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.

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 0Reading forward from RBA 0 Logdump 894 >fileheader detailLogdump 895 >n2014/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 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 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 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................   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.

 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 onLogdump 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.

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 2776Reading forward from RBA 2776 Logdump 912 >detail dataLogdump 913 >ggstoken detailLogdump 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.

 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.ORDERSLogdump 934 >pos eofReading forward from RBA 14469316 Logdump 935 >pos revReading 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 forReading forward from RBA 63547 Logdump 938 >pos 0Reading forward from RBA 0 Logdump 939 >filter reset allLogdump 940 >filter logcsn 8163404Logdump 941 >countLogTrail /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.

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 0Reading 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 revReading 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 recordsSaved 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.

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.