Table Definitions in Oracle #GoldenGate #12c Trail Files
Oracle GoldenGate 12c (220.127.116.11.0) has changed the information that is stored in the trail files. All the standard information is still there. What Oracle changed has to do with the meta data that is used to define a table.
Note: If you want a understand how to use log dump and general trail information, look here.
Prior to 18.104.22.168.0 release of Oracle GoldenGate, if the column order of tables between source and target were different, you needed to generate a “definition” file using the “defgen” utility located in $OGG_HOME. This file allowed you to specify either a source or target definitions file which could be used to map the order of columns correctly. This was a nice tool when needed.
In 22.214.171.124.0, Oracle took this concept a little bit further. Instead of using a definitions file to do the mapping between source and target tables; Oracle has started to provide this information in the trail files. Review the image below, and you will see the table definition for SOE.ORDERS, which I run in my test environment.
Notice at the top, the general header information is still available for view. Directly under that, you will see a line that has the word “metadata” in it. This is the start of the “metadata” section. Below this is the name of the table and a series of number categories (keep this in mind). Then below this, is the definition of the table with columns and the length of the record.
A second ago, I mentioned the “numbered categories”. The categories correspond to the information defined to the right of the columns defined for the table. When comparing the table/columns between the database and trail file, as few things stand out.
In column 2 (Data Types), the following numbers correspond to this information:
134 = NUMBER
192 = TIMESTAMP (6) WITH LOCAL TIME ZONE
64 = VARCHAR2
In column 3 (External Length), is the size of the data type:
13 = NUMBER(12,0) + 1
29 = Length of TIMESTAMP (6) WITH LOCAL TIME ZONE
8 = VARCHAR2 length of 8
15 = VARCHAR2 length of 15
30 = VARCHAR2 length of 30
There is more information that stands out, but I’ll leave a little bit for you to decode. Below is the table structure that is currently mapped to the example given so far.
Now, you may be wondering, how do you get this information to come up in the logdump interface? Oracle has provided a logdump command that is used to display/find metadata information. This command is:
There are a few options that can be passed to this command to gather specific information. These options are:
DDR | TDR
NEXT | INDEX
If you issue:
You will get information related to Data Definition Records (DDR) of the table. Information this provides includes the following output:
If you issue:
You will get information related to Table Definition Record (TDR) on the table. Information provide includes the output already discussed earlier.
As you can tell, Oracle has provided a lot of information that is traditionally in the definitions files for mapping tables directly into the trail files. This will make mapping data between systems a bit easier and less complicated architectures.
Current Oracle Certs
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.