How does SharePlex use key-level supplemental logging information?
Starting with SharePlex 6.0 and above, SharePlex will be able to utilize key-level supplemental logging information. When any key-level supplemental logging is enabled, the value of the key columns are added to the redo log record. Whether this is used by Shareplex depends upon the key that Shareplex is using to replicate as well as the type of key level logging enabled.
If there is a primary key and PK supplemental logging is enabled, then capture can get all of the key column values from the redo log record.
If the table has one unique key and UK supplemental logging is enabled, then capture can get all the key column values from the redo log record.
If the table has more than one unique key and UK supplemental logging is enabled, then we may not be able to get all the key column values from the redo log. When there is more than one unique key, Shareplex analyzes the unique keys during activation and chooses one for replication. Oracle does the same thing. If the unique key that Shareplex decides to use for replication is not the same as the unique key that Oracle chose when the "ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;" command was issued, then capture may not find all the key column values in the redo log. In this case reader still has to query key values and will process the data in two passes. There is a new sp_ctrl command that will allow you to see if this is happening and on which tables it is happening - trace read.
If the key that shareplex is using is user defined, table level supplemental logging can be used to ensure that the key column values for the shareplex key are logged. Again, the trace read command can be used to determine whether this is working.
If all columns supplemental logging are turned on either at database level or table level, capture would be able to take advantage of it and get the key column values from the redo log even if the table has no key.
The only way that supplemental logging of foreign keys would have any effect on Shareplex is if the key columns that Shareplex uses were not being logged through any other supplemental logging mechanism as described above. For instance, if the shareplex key was a user defined key and the table had a foreign key that was the same as the user-defined key, then turning on supplemental logging of foreign keys could make a difference.
Shareplex does perform a query to determine whether PK or UK supplemental logging is enabled and it does log a message in the event log if they are. However, Shareplex does not depend upon the results of this query to change its behavior. On the contrary, every time capture reads a redo log record it looks for the supplemental information and uses it if found.
The queries for table level supplemental logging are much more complicated and so we do not perform those queries just for user information. But if table level supplemental logging is providing key column data in the redo log we use it.
For tables with horizontal partitioning, reader may still have to fetch key from oracle if the column you set up for horizontal partitioning condition is not part of the primary key even with key-level supplemental logging turned on. In this case, you can create a group to include this column and turn on supplemental logging for this group.
Database Level:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Table level:
ALTER TABLE [a] ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE [a] ADD SUPPLEMENTAL LOG GROUP [group_name] (column_name1, column_name2, column_name3) ALWAYS;
