This discussion is archived
1 Reply Latest reply: Mar 15, 2012 9:34 AM by 818442 RSS

Trying to understand what columns have to be supplementally logged?

user268914 Newbie
Currently Being Moderated
We are using N-way replication.

Every table has a column called CUR_TIME which is updated via a trigger to the current time whenever anything in the table changes. We currently supplementally log every column in these tables:

ALTER TABLE FOO ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

With respect to conflict detection/resolution, each table has the pre-built MAXIMUM conflict handler set up on it with a resolution column of CUR_TIME and a column list of all non-%LOB columns.

This all works fine.

However, we'd like to cut back on the amount of redo generated and network bandwidth consumed by reducing the logging.

From reading Oracle docs it would appear that if you're using a time column like we are, there is no reason to look at any columns other than CUR_TIME. So on a test table we tried the following:

ALTER TABLE TEST_TABLE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE TEST_TABLE ADD SUPPLEMENTAL LOG GROUP (CUR_TIME) ALWAYS;

Then we set up the conflict handler by specifying the resolution column as CUR_TIME as before but this time having the column list be only CUR_TIME.

When we triggered conflicts this didn't work and we got apply errors saying there were conflicting columns. Why is this? We're only logging the primary key (which never changes once inserted) and CUR_TIME. And we've only installed the conflict handler on the CUR_TIME column (as well as using its value for resolution). So what columns could be conflicting?

One thing that isn't clear to me is what the whole apply system looks at when deciding there is a conflict and what it expects there to be in the supplemental logging.

So what exactly IS the minimum supplemental logging you can get away with if you have a trigger-updated time column like our CUR_TIME? And how do you set up conflict detection and resolution to use it in the context of that minimum supplemental logging?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points