LOGDUMP is a great utility and a real bonus to the Oracle GoldenGate software bundle. Without LOGDUMP, we will not be able to read a trail file, which would make us blind to troubleshooting data-related issues.
LOGDUMP has a command-line interface that allows you to open files, format the display, and navigate through a file, including filtering data. To invoke the utility, navigate to the GoldenGate Home directory and enter logdump, as shown in the following command:
[oracle@db12c ogg]$ ./logdump Oracle GoldenGate Log File Dump Utility for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1 Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. Logdump 49 >ENV Version : Linux, x64, 64bit (optimized) on Aug 7 2014 02:57:05 Current Directory : /u01/app/oracle/product/12.1.2/ogg LogTrail : *Not Open* Display RecLen : 140 Logtrail Filter : On Trans History : 0 Transactions, Records 100, Bytes 100000 LargeBlock I/O : On, Blocksize 57344 Local System : LittleEndian Metadata Byte Order : LittleEndian Logtrail Data : BigEndian/ASCII Logtrail Headers : ASCII Dump : ASCII Savefile comments : Off Timeoffset : LOCAL Scan Notify Interval: 10000 records, Scrolling On
As far as the GGSCI utility is concerned, LOGDUMP increments a number at its Command Prompt for each command entered. Even if you exit LOGDUMP, the increment number will be maintained when you return. This is because LOGDUMP maintains a history of the commands used.
The preceding code shows the output of the ENV command, which is one of the many commands required to be productive with LOGDUMP. Firstly, we must tell LOGDUMP to open a file and then specify how much detail we require before scanning or filtering data. However, should we get stuck, there is always the HELP command to get us back on track, which shows many undocumented commands.
Let's start with the OPEN command. Before opening a file, we must choose one. Execute the following Linux command from the GoldenGate Home directory to list the available files in the dirdat subdirectory:
[oracle@db12server01 ogg]$ ls -lrt dirdat -rw-r-----. 1 oracle oracle 278407 May 16 14:17 lt000034
Now, log back into LOGDUMP and execute the following command to open the trail file found in the preceding step:
Logdump 50 >open dirdat/lt000034 Current LogTrail is /u01/app/oracle/product/12.1.2/ogg/dirdat/lt000034
Before we can see the contents of the file, we must set up preferences in LOGDUMP. The following table of commands will provide the necessary details, depending on your requirements:
|
Command |
Description |
|---|---|
|
|
This controls whether or not the trail file header is displayed and in how much detail. |
|
|
This controls whether or not the record header is displayed with each record. |
|
|
This displays a list of columns that includes the column ID, length, and the values in hex and ASCII. |
|
|
This displays the actual token data. |
|
|
This controls how much of the record data is displayed in characters. |
Taking a look at the list, we can enable the fileheader detail, GDHR, usertoken detail, and reclen 128 options, as follows:
Logdump 51 >fileheader detail Logdump 52 >ghdr on Logdump 53 >detail on Logdump 54 >usertoken detail Logdump 55 >reclen 128 Reclen set to 128
Now, we can navigate our way through the open GoldenGate trail file.
Start at position 0 (the first record in the file) and the beginning of the header record by entering the following command:
Logdump 56 >pos 0 Reading forward from RBA 0
To view the header record, we must step to the next RBA. This is easy with LOGDUMP; just enter next or n, as shown in the following command:
Logdump 57 >next 2015/05/16 11:55:41.236.164 FileHeader Len 1426 RBA 0 Name: *FileHeader* 3000 031a 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1... 0004 3200 0004 2000 0000 3300 0008 02f2 3c5e 63f2 | ..2... ...3.....<^c.
Given that we have enabled a detailed view, a wealth of information is included in the header record. The information is grouped by type with a list of related tokens. The last column shows the text that includes the GoldenGate and database versions.
Having studied the header record, we can now use LOGDUMP to view the transaction records in the rest of the trail file.
Entering next or n again steps us through each record in the file. The following code shows the details of the DELETE operation against the SRC.EMP table, including the actual data and record count. You could argue that each record would always have a record count (RecCount) of 1. This is not true for LOBs, which are split into 2 KB chunks when written to a trail file:
Logdump 58 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 121 (x0079) IO Time : 2015/05/16 13:49:30.000.000 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 151 AuditPos : 24482320 Continued : N (x00) RecCount : 1 (x01) 2015/05/16 13:49:30.000.000 Delete Len 121 RBA 1496 Name: PDB1.SRC.EMP Before Image: Partition 4 G b 0000 000a 0000 0000 0000 0000 1cc9 0001 0009 0000 | .................... 0005 534d 4954 4800 0200 0900 0000 0543 4c45 524b | ..SMITH........CLERK 0003 000a 0000 0000 0000 0000 1ede 0004 0015 0000 | ....................
The equivalent transaction record in the remote trail file is identical to the one found in the local trail file and is identifiable by the same audit position (AuditPos) number.
Each record in the trail file contains the following information:
- The operation type, such as an insert, update, or delete
- The transaction indicator (
TransInd): 00 beginning, 01 middle, 02 end, or 03 whole of transaction - The before or after indicator (
BeforeAfter) for update operations - The commit timestamp
- The time that the change was written to the GoldenGate file
- The type of database operation
- The length of the record
- The RBA (
AuditRBA) within the GoldenGate file - The schema and the table name
The transaction record provides ample information to help you troubleshoot data-related issues. For example, the before image of the UPDATE or DELETE operation would prove very useful in determining the reason for the ORA-01403: no data found error.
The miscellaneous commands are useful to display additional information and are listed in the following table:
|
Command |
Description |
|---|---|
|
|
This lists all the previous commands |
|
|
This displays the audit record |
|
|
This skips down the <count> records |
|
|
This scans for the file header record |
|
|
This displays the details of the GoldenGate environment |
|
|
This counts the records in the file |
|
|
This exits |
The following code highlights the power of the COUNT command:
Logdump 60 >count ** Count begins at RBA 1496 LogTrail /u01/app/oracle/product/12.1.2/ogg_src/dirdat/lt000034 has 1282 records Total Data Bytes 156780 Avg Bytes/Record 122 Delete 630 Insert 652 Before Images 630 After Images 652 Average of 112 Transactions Bytes/Trans ..... 1949 Records/Trans ... 11 Files/Trans ..... 0 PDB1.SRC.EMP Partition 4 Total Data Bytes 154800 Avg Bytes/Record 122 Delete 630 Insert 630 Before Images 630 After Images 630 PDB1.SRC.STATEMENT Partition 4 Total Data Bytes 1980 Avg Bytes/Record 90 Insert 22 After Images 22
We are now familiar with some of the miscellaneous LOGDUMP commands, which are commonly used to search and count records and display the command history and environment information. Let's now take a look at how to filter our search of the transaction data.
We can do some pretty fancy stuff with LOGDUMP filtering. A whole suite of commands are set aside for this. We can filter out just about anything that exists in the trail file, such as the process name, the RBA, the record length, the record type, and even a string!
The following command shows the required syntax to filter out DELETE operations:
Logdump 61 >filter include iotype delete Logdump 62 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 124 (x007c) IO Time : 2015/05/16 13:49:30.000.000 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x01) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 151 AuditPos : 24482984 Continued : N (x00) RecCount : 1 (x01) 2015/05/16 13:49:30.000.000 Delete Len 124 RBA 1742 Name: PDB1.SRC.EMP Before Image: Partition 4 G m 0000 000a 0000 0000 0000 0000 1d4b 0001 0009 0000 | .............K...... 0005 414c 4c45 4e00 0200 0c00 0000 0853 414c 4553 | ..ALLEN........SALES 4d41 4e00 0300 0a00 0000 0000 0000 001e 1200 0400 | MAN................. 1500 0031 3938 312d 3032 2d32 303a 3030 3a30 303a | ...1981-02-20:00:00: 3030 0005 000a 0000 0000 0000 0002 7100 0006 000a | 00............q.....
With filtering enabled, all subsequent commands carry the same filter condition. For example, the count command will only count the DELETE operations that we previously specified.