Accessing Trail File Data

To access a trail file using the logdump utility, you have to explicitly tell logdump what trail file you wish to open. This is done by issuing the open command with the trail file you want to look at. Listing 10-5 illustrates this process.

Listing 10-5. Open a Trail File

Logdump 5 >open ./dirdat/rt000000000
Current LogTrail is /opt/app/oracle/product/12.2.0.0/oggcore_1/dirdat/rt000000000

Now that you have an open trail file, you want to look at some data. Before you can do that, you have to set up the environment to display what you want to see. To do this, you need to refer back to the list of commands in Listing 10-4. Although there are several commands to choose from, there are just a few common ones that will get you started. These common logdump commands are highlighted in Listing 10-6.

Listing 10-6. Common Logdump Commands

GHDR  ON | OFF     - Toggle GHDR display
DETAIL ON | OFF | DATA - Toggle detailed data display
USERTOKEN     on  | OFF | detail  - Show user token info
HEADERTOKEN   on  | OFF | detail  - Show header token info
GGSTOKEN      on  | OFF | detail  - Show GGS token info

You might be asking yourself how to use these commands within logdump. It is quite simple. The commands can be entered at the logdump prompt to enable or disable the command you want to use. Use of these common commands can be seen in Listing 10-7.

Listing 10-7. Common Logdump Commands

Logdump 6 >ghdr on
Logdump 7 >detail on
Logdump 8 >detail data
Logdump 9 >ggstoken on
Logdump 10 >usertoken on

With the commands in Listing 10-7 executed, you now have a basic setup for reviewing a trail file established. The next thing you need to do is to start looking at the trail file and reviewing the data that it contains. By default you are positioned at the top of the trail file; that is, relative byte address (RBA) 0. All you need to do at this point is use the next (n) command to move forward in the file. Listing 10-8 shows you the contents of the trail file starting at RBA 0 when you click Next.

Listing 10-8. Trail File  Header Information

Logdump 11 >n
2016/03/14 19:34:21.383.380 FileHeader           Len  1497 RBA 0
Name: *FileHeader*
 3000 0366 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..f0...GG..TL..1...
 0005 3200 0004 2000 0000 3300 0008 02f2 543e 2d1c | ..2... ...3.....T>-.
 82d4 3400 0043 0041 7572 693a 6672 6564 3a61 636d | ..4..C.Auri:fred:acm
 653a 636f 6d3a 3a75 3031 3a61 7070 3a6f 7261 636c | e:com::u01:app:oracl
 653a 7072 6f64 7563 743a 3132 2e32 2e30 3a6f 6767 | e:product:12.2.0:ogg
 636f 7265 5f31 3a50 4747 3132 4335 0000 4735 0000 | core_1:PGG12C5..G5..
 4300 4175 7269 3a66 7265 643a 6163 6d65 3a63 6f6d | C.Auri:fred:acme:com

All you see when you start at RBA 0 is the header of the trail file. The header tells you where the trail file came from, the version of GoldenGate being used, and what data pump process created the trail file. To get to an actual record in the trail file, you need to move forward in the trail file again using the next command.

After moving forward in the trail file a few times, you should come upon the first record that is stored in the trail file.

As part of the record, you will notice there are metadata for the table that was captured. This is new in Oracle GoldenGate 12c and helps with the mapping of data if columns are in different orders. Listing 10-9 shows you the header of the transaction header that corresponds to the metadata for the record that is coming through. Take a moment to review the header information.

Listing 10-9. Trail File Output (1)

Logdump 14 >n
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   281  (x0119)   IO Time    : 2016/03/14 20:27:28.773.265
IOType     :   170  (xaa)     OrigNode   :     2  (x02)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
DDR/TDR Idx:   (001, 001)     AuditPos   : 25851148
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/03/14 20:27:28.773.265 Metadata             Len 281 RBA 1707
Name: SOE.LOGON
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale 6)Level         7)Null             8)Bump if Odd      9)Internal Length  10)Binary Length11)Table Length 12)Most Sig DT     13)Least Sig DT     14)High Precision  15)Low Precision16)Elementary Item  17)Occurs      18)Key Column       19)Sub DataType    20)Native DataType 21)Character Set    22)Character Length 23)LOB Type    24)Partial Type
*
TDR version: 1
Definition for table SOE.LOGON
Record Length: 134
Columns: 3
LOGON_ID      64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
CUSTOMER_ID   64     50       56  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
LOGON_DATE   192     19      112  0  0 1 0     19     19     19 0 5 0 0 1    0 1   0   12       -1      0 0 0
End of definition

This example of a trail file header looks like Greek to the untrained eye. The information contained in the header is for the SOE.LOGON table. The cryptic header section provides information relative to the transaction that is coming through, such as record length, if it is a before or after transaction, the date the transaction occurred, and the number of records contained with the transaction.

The next section in this header provides details on the metadata for the SOE.LOGON table . The section that has the numbers that look like column headers is a key to help you decipher the specific information related to the columns provided just below that. Finally, the section below that area is information related to the record length and the definition of the table. This section lists the names of the columns in the record that is being sent. This helps with the mapping resolutions between the source and target metadata.

In the first section of the transaction header, you can see how the metadata are being passed in the trail file. In this portion of the trail file, you look at the same transaction, just more of the actual transaction. Just like the metadata header, the transaction has a header that specifies a lot of information for the transactions. Listing 10-10 provides this output for review.

Listing 10-10. Trail Output (2)

Logdump 15 >n
Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    54  (x0036)   IO Time    : 2016/03/14 20:27:20.008.002
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :       4814       AuditPos   : 25851148
Continued  :     N  (x00)     RecCount   :     1  (x01)

2016/03/14 20:27:20.008.002 Insert               Len    54 RBA 2048
Name: SOE.LOGON  (TDR Index: 1)
After  Image:                                             Partition 12   G  s
 0000 000b 0000 0007 3239 3832 3938 3600 0100 0a00 | ........2982986.....
 0000 0637 3034 3737 3000 0200 1500 0032 3031 362d | ...704770......2016-
 3033 2d31 343a 3230 3a32 373a 3139                | 03-14:20:27:19
Column     0 (x0000), Len    11 (x000b)
 0000 0007 3239 3832 3938 36                       | ....2982986
Column     1 (x0001), Len    10 (x000a)
 0000 0006 3730 3437 3730                          | ....704770
Column     2 (x0002), Len    21 (x0015)
 0000 3230 3136 2d30 332d 3134 3a32 303a 3237 3a31 | ..2016-03-14:20:27:1
 39                                                | 9

GGS tokens:
 5200 0014 4141 415a 324a 4141 4841 4141 674d 2b41 | R...AAAZ2JAAHAAAgM+A
 4345 0001 7401 0000 4c00 0008 3235 3835 3835 3430 | CE..t...L...25858540
 3600 000a 322e 3235 2e32 3032 3137 6901 0002 0001 | 6...2.25.20217i.....

The header information provides the same information as was provided with the metadata. This includes the record length, before and after image information, and the date the transaction occurred. Along with this information, you will see just below the header a section with a date and timestamp. This line in the record provides information on the type of transaction that occurred, the length of the transaction, and the relative byte address. In the case of this transaction, it is an insert that had a length of 54 bytes located at the 2048 byte address.

Immediately after the header, comes the transaction-specific information. This section provides the binary and ASCII format of data. The binary section is to the left of the ASCII information that is displayed. The binary section also provides the column mappings for the data; however, it is not clear on the column names. This is where the metadata in the header are helpful. By marrying the two together, you can quickly see what columns are being mapped for the transaction.

This was a quick overview of running logdump and reviewing a trail file. Trail files are a good thing to take a look at to get a good understanding of what logdump can do for you. Logdump is one of the most powerful tools you can have in your GoldenGate toolkit when problems arise.

Configure Defgen

Like anything in Oracle GoldenGate, before defgen can be used it needs to be configured. To configure a source-side mapping with the defgen utility, we need to create a parameter file. The following steps will create a parameter file called defgen.prm.

1. From GGSCI, edit a parameter file called defgen.

   edit params defgen

2. Edit the parameter file with the parameters needed.

   --Your parameter file will vary, this is an example only--
   DEFSFILE ./dirdef/defgen.def
   USERID ggate, PASSWORD ggate
   TABLE sfi.clients;

3. Save and close the file.

4. Exit GGSCI.

After saving the parameter file, you will be ready to run the defgen utility to generate the definition file needed to ensure mapping of the source columns to the columns on the target side.

The defgen utility has a few parameters that can be used when setting up the definitions, shown in Table 10-1. The parameters that are listed in Table 10-1 are to be used when configuring the parameter file for generating definition files.

Running Defgen

After you have built and saved your parameter file, the next step is to run the defgen utility to create the definition file. To run defgen, make sure that you are in the Oracle GoldenGate Home directory. The utility is run from the GGSCI prompt using the parameters that are defined in Table 10-2.

To run the defgen utility that will create the definition file needed, the command is fairly straightforward. Listing 10-11 provides an example of running the command using relative paths.

Listing 10-11. Running Defgen Utility

$ defgen paramfile ./dirprm/defgen.prm reportfile ./dirrpt/defgen.rpt

After the definition file has been generated, the file needs to be copied over to the target system and specified in the replicat parameter file. To copy the definition file to the target system, any File Transfer Protocol (FTP) or Session Control Protocol [SCP] utility can be used as long as the transfer is done in binary mode. This ensures that the file is copied correctly between any platforms.

Once the definition file is copied to the target system, the file is associated with the replicat parameter file using the SOURCEDEFS parameter. Listing 10-12 displays how a replicat parameter file would look with the definition file defined.

Listing 10-12. Replicat Parameter File with Definition File Defined

-- Example replicat parameter file. Your requirements may vary.--
REPLICAT REPI
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/db12cr1")
SETENV (ORACLE_SID="oragg")
USERID ggate, PASSWORD <password>
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/REPI.dsc, append, megabytes 50
SOURCEDEFS ./dirdef/defgen.def                
map SFI.CLIENTS, target ATLI.CLIENTS;            

After configuring the replicat to use the definition file, data can be mapped between source and target without any errors. Notice, in Listing 10-12, that the MAP statement has an extract option after telling Oracle GoldenGate where the target table is. The DEF option that is an option for MAP is used to tell the replicat to use the definition file specified. Using a definition file allows table metadata to be different between source and target systems. This makes the process of integrating data between different systems easier and more flexible.

Using Checkprm

Let’s take a look at a few examples of using the checkprm utility against a replicat parameter file. These examples provide you with a quick understanding of how this utility can be used. In Listing 10-14, there are no parameters passed and the parameter file is strictly being tested for validity.

Listing 10-14. No Parameters Passed

[email protected]:/opt/app/oracle/product/12.2.0.0/oggcore_1 >./checkprm ./dirprm/rgg12c.prm

2016-03-16 21:15:46 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/dbhome_1.

2016-03-16 21:15:46 INFO OGG-02095 Successfully set environment variable ORACLE_SID=rmt12c.

2016-03-16 21:15:46 INFO OGG-10139 Parameter file ./dirprm/rgg12c.prm: Validity check: PASS.

Runtime parameter validation is not reflected in the above check.

In reviewing the output, you will notice that the utility successfully set the Oracle Home, set the Oracle SID, and verified the parameter file with a PASS. This means that the parameter file validated successfully and can be used within your environment.

Now, let’s take a more verbose look at the same parameter file. We know that the parameter file passes validation, but you want to see what is contained in the parameter file and if everything is syntax validated. To do this, you would pass the verbose options. Listing 10-15 provides a sample of this output.

Listing 10-15. Verbose Checking with Checkprm

[email protected]:/opt/app/oracle/product/12.2.0.0/oggcore_1 >./checkprm ./dirprm/rgg12c.prm -V

2016-03-16 21:21:46 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/dbhome_1.

2016-03-16 21:21:46 INFO OGG-02095 Successfully set environment variable ORACLE_SID=rmt12c.

Parameter file validation context:

component(s): REPLICAT
mode(s)     : N/A
platform(s) : Linux
database(s) : Oracle 12c

GLOBALS

enablemonitoring                     : <enabled>
checkpointtable                      : ggate.checkpoint
ggschema                             : ggate

./dirprm/rgg12c.prm

replicat                             : RGG12C
setenv                               : (ORACLE_HOME="/opt/app/oracle/product/12.1.0.2/dbhome_1")
setenv                               : (ORACLE_SID="rmt12c")
userid                               : ggate
  password                           : *******
reperror                             : <enabled>
  spec_option                        : default
    discard                          : <enabled>
assumetargetdefs                     : <enabled>
map                                  : SOE.*
  target                             : SOE.*

2016-03-16 21:21:46 INFO OGG-10139 Parameter file ./dirprm/rgg12c.prm:  Validity check: PASS.
Runtime parameter validation is not reflected in the above check.

Notice that in the verbose output, you can see what options have been enabled, the name of the process, what environment is set, and what the mappings are tied to. Just like before, the parameter file passes and can be used with your environment.

The last example shows how you can check to see if the parameter file and the associated parameters can be used between different modes of a process. The parameter file that has been used in the previous two examples has been from a classic replicat process. What if you wanted to take that same parameter file and convert to an integrated replicat? You can use the move option of checkprm to validate the parameter file before attempting the change. Listing 10-16 shows how you can validate the parameter for such a move.

Listing 10-16. Validating for Integrated Processes

[email protected]:/opt/app/oracle/product/12.2.0.0/oggcore_1 >./checkprm ./dirprm/rgg12c.prm -M Integrated Replicat

2016-03-16 21:24:17 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/dbhome_1.

2016-03-16 21:24:17 INFO OGG-02095 Successfully set environment variable ORACLE_SID=rmt12c.

2016-03-16 21:24:17 INFO OGG-10139 Parameter file ./dirprm/rgg12c.prm: Validity check: PASS.

Runtime parameter validation is not reflected in the above check.

From looking at the output, you can see that the current parameter file can be moved to an integrated replicat process without any issues. This is designed to help make your decision-making process easier and to check legacy environments before converting to newer Oracle GoldenGate standards.