To TRANDATA or To SCHEMATRANDATA? … That is the #GoldenGate questions of the day!
If you are familiar with using Oracle GoldenGate, you know that on the source side of the equation you have to enable supplemental logging and sometimes force logging on the database. I traditionally do both just to make sure that I capture as much as I can into the redo stream from the transactions on the database. For Oracle GoldenGate purposes, this is not the only thing you need to turn on to ensure all needed information is captured to the trail files.
There are two Oracle GoldenGate GGSCI commands that can be ran to enable supplemental logging at the schema or table level. These commands are ADD TRANDATA and ADD SCHEMATRANDATA. What is the difference between the two, you may ask?
ADD TRANDATA – is used to enable supplemental logging at the table level
ADD SCHEMATRANDATA – is used to enable supplemental logging at the schema level
That is such a high-level view of the concept. What is the difference between the two trandata approaches, really?
ADD TRANDATA command is used to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records. This version of the command can be used on the following databases:
- DB2 for i Database
- DB2 LUW Database
- DB2 z/OS Database
- Oracle Database
- MS SQL Server
- Sybase Database
For an Oracle Database, ADD TRANDATA enables the unconditional logging of the primary key and conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table. Additionally, you can use ADD TRANDATA with the COLS option to log any non-key columns that can be used with the FILTER statements and KEYCOLS clauses in the TABLE and MAP parameters.
An example of adding trandata to a schema would be:
GGSCI> dblogin useridalias gate GGSCI> add trandata soe.*
Once transdata has been added to the schema/tables, you can verify the existence of trandata from GGSCI using the INFO TRANDATA command as demonstrated in the below command set.
GGSCI> dblogin useridalias gate GGSCI> info trandata soe.addresses 2016-08-12 15:07:23 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE. 2016-08-12 15:07:23 INFO OGG-01980 Schema level supplemental logging is enabled on schema SOE for all scheduling columns. Logging of supplemental redo log data is enabled for table SOE.ADDRESSES. Columns supplementally logged for table SOE.ADDRESSES: ADDRESS_ID, COUNTRY, COUNTY, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, POST_CODE, STREET_NAME, TOWN, ZIP_CODE.
Now that ADD TRANDATA has been ran, what exactly does ADD TRANDATA do to the database it is ran against? For an Oracle Database, ADD TRANDATA adds a Supplemental Log Group (SLG) on to the table. This can be seen from the DBA_LOG_GROUP view under SYS. The SLGs that are corrected are all labeled with a prefix of “GGS”. The following output shows what this looks like after running it for a whole schema.
select owner, log_group_name, table_name, log_group_type, always, generated from dba_log_groups where owner = 'SOE' and log_group_name like 'GGS%';
Now, there are some who will argue that the same effect can be done by just asking a SLG to a table manually. Although this is true, Oracle GoldenGate uses the GGS_ prefix to keep track of the tables that are in the replication process. Also, easier to clean up when you issue DROP TRANDATA, which will remove all the associated SLG items from the tables.
The ADD TRANDATA approach should be used with 11g or older versions of Oracle GoldenGate. As you move towards new version of Oracle GoldenGate, Oracle is pushing that everyone pick up and use the ADD SCHEMATRANDATA method. So let’s take a look at that now.
The ADD SCHEMATRANDATA is used on all the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification. Using this version of TRANDATA, it can be used with both the integrated and classic capture processes.
There are four key reasons why you should use ADD SCHEMATRANDATA:
- Enables supplemental logging for new tables created with a CREATE TABLE DDL command.
- Updates supplemental logging for tables affected by an ALTER TABLE DDL command that adds or drops columns
- Updates supplemental logging for tables affected by RENAME TABLE command
- Updates supplemental logging for tables affected by adding or dropping of unique or primary key constraints
Although ADD SCHEMATRANDATA can be used with both integrated and classic capture processes, it is mostly geared towards the integrated process. There are three primary reasons to use ADD SCHEMATRANDATA with the integrated capture:
Ensures that the correct key is logged by logging all the keys
Options are provided that enable the logging of the primary, unique, and foreign keys to support the computation of dependences among the tables being processed by the integrated replicats (think apply servers)
Ensures the appropriate key values are logged in the redo to allow DML to be mapped to object that have DDL issued against them.
Earlier in this post, I mentioned that I often enable “force logging” on the database when I do the minimal supplemental logging. Force logging is encouraged by Oracle, especially when using ADD SCHEMATRANDATA.
Now to add issue ADD SCHEMATRANDATA against an Oracle database, it is similar the same way as ADD TRANDATA, with the difference that you don’t have to provide any wildcards. In the examples below, I show you how this can be done:
GGSCI> dblogin useridalias ggate GGSCI> add schematrandata soe 2016-08-12 15:47:40 INFO OGG-01788 SCHEMATRANDATA has been added on schema soe. 2016-08-12 15:47:40 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema soe.
After running ADD SCHEMATRANDATA, you can perform an INFO SCHEMATRANDATA on the schema to see what has been modified.
GGSCI (fred.acme.com as [email protected]) 9> info schematrandata soe 2016-08-12 15:51:52 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema SOE. 2016-08-12 15:51:52 INFO OGG-01980 Schema level supplemental logging is enabled on schema SOE for all scheduling columns.
Digging around in the database, to see if ADD SCHEMATRANDATA does the same as ADD TRANDATA with SLG; well, it doesn’t. ADD SCHEMATRANDATA does not create any SLGs. The only place that I have found that has any record of supplemental logging turned on with ADD SCHEMATRANDATA is in the V_$GOLDENGATE_CAPABILITIES view. Here, you can see that supplemental logging has been enabled, the number of times it has been acted upon and when it was last executed.
NAME COUNT TO_CHAR(LAST_USED CON_ID ---------------------- ---------- ----------------- ---------- DBENCRYPTION 0 12-JUN-2016 21:20 0 DBLOGREADER 0 12-JUN-2016 21:20 0 TRIGGERSUPPRESSION 0 12-JUN-2016 21:20 0 TRANSIENTDUPLICATE 0 12-JUN-2016 21:20 0 DDLTRIGGEROPTIMIZATION 0 12-JUN-2016 21:20 0 GGSESSION 0 12-JUN-2016 21:20 0 DELETECASCADEHINT 0 12-JUN-2016 21:20 0 SUPPLEMENTALLOG 5 12-AUG-2016 16:02 0
Now, being that the integrated items of Oracle GoldenGate are closely related to Oracle Streams, there may be a table or view related to Streams that has this information. Once I find it, I’ll provide an update to this post.
In the mean time, I hope this post has provided some insight into the differences between ADD TRANDATA and ADD SCHEMATRANDATA.
If you are moving to or using the integrated products of Oracle GoldenGate, then ADD SCHEMATRANDATA is the method that you should be using.
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”.
If you’re not planning to replicate every table in a schema and still are using integrated extract, should you still use SCHEMATRANDATA? Or should you just use ADD TRANDATA for the tables you plan to replicate?
Just use ADD TRANDATA. SCHEMATRANDATA effects the whole schema.
Very well explained. Thanks!
In this link I read – ADD SCHEMATRANDATA can be used instead of the ADD TRANDATA command when DDL replication is not enabled.
I did not understand the meaning of this line and thought of asking. Does it mean that either we switch on DDL Replication (after getting executed multiple scripts to get DDL replication done) or just use ADD SCHEMATRANDATA? Please explain.
>>>> Digging around in the database, to see if ADD SCHEMATRANDATA does the same as ADD TRANDATA with SLG; well, it doesn’t. ADD SCHEMATRANDATA does not create any SLGs.<<<<
select * from LOGMNR$SCHEMA_ALLKEY_SUPLOG where allkey_suplog != 'NO';
You are correct. SCHEMATRANDATA is stored in a different area. Look at my other post on SCHEMATRANDTA.