Oracle GoldenGate: What is ADD TRANDATA really doing?

3 Comments / Oracle GoldenGate / By / / add trandata, GoldenGate, Oracle, redo log, supplementary log

In the process of data replication using Oracle GoldenGate the first step is assuring that all the needed information has been written to the database redo log. By default the database does not write all the information that might be needed by the replication process. That’s why some additional supplemental logging is needed.

According to the documentation supplemental logging is being added by the ADD TRANDATA command. A very good research about this subject has been made by Julian Dyke in the article about adding supplemental logging. But what does the  GoldenGate command actually do and which options should be used?

1. Environment

This research has been made on Oracle Database 12.1.0.2 and GoldenGate 12.2.0.1 on Linux64 platform.The source database is in ARCHIVELOG mode and has FORCE LOGGING and SUPPLEMENTAL LOGGING turned on. I have created a test replication in two variants: one with primary key, and one without (but with an unique index). The source schema is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DROP TABLE usr.reptable;
DROP TABLE usr.reftable;
 
CREATE TABLE usr.reptable(
  repA CHAR(4) NOT NULL,
  repB CHAR(4) NOT NULL,
  refA CHAR(4) NOT NULL,
  refB CHAR(4) NOT NULL,
  uni1 CHAR(4) NOT NULL,
  uni2 CHAR(4) NOT NULL,
  ind1 CHAR(4) NOT NULL,
  ind2 CHAR(4) NOT NULL,
  col1 CHAR(4) NOT NULL,
  col2 CHAR(4) NOT NULL
);
 
-- variant 1
ALTER TABLE usr.reptable ADD CONSTRAINT reptablePK PRIMARY KEY(repA, repB);
-- variant 2
CREATE UNIQUE INDEX usr.reptablePK2 ON usr.reptable(repA, repB);
 
ALTER TABLE usr.reptable ADD CONSTRAINT reptableUK UNIQUE(uni1, uni2);
CREATE UNIQUE INDEX usr.reptableIdx ON usr.reptable(ind1, ind2);
 
CREATE TABLE usr.reftable(
  refA CHAR(4) NOT NULL,
  refB CHAR(4) NOT NULL,
  col1 CHAR(4) NOT NULL
);
ALTER TABLE usr.reftable ADD CONSTRAINT reftablePK PRIMARY KEY (refA, refB);
 
ALTER TABLE usr.reptable ADD CONSTRAINT reptableFKreftable FOREIGN KEY(refA, refB)
  REFERENCES usr.reftable(refA, refB);

The tables have been populated with data :

1
2
3
4
5
6
7
8
9
10
11
12
13
14

INSERT INTO usr.reftable VALUES ('AAAA', 'BBBB', 'DDDD');
INSERT INTO usr.reftable VALUES ('AAAA', 'CCCC', 'DDDD');
commit;
INSERT INTO usr.reptable VALUES ('XXX1', 'YYYY', 'AAAA', 'BBBB', 'XXX1', 'YYYY', 
  'KKK1', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX2', 'YYYY', 'AAAA', 'BBBB', 'XXX2', 'YYYY', 
  'KKK2', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX3', 'YYYY', 'AAAA', 'BBBB', 'XXX3', 'YYYY', 
  'KKK3', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX4', 'YYYY', 'AAAA', 'BBBB', 'XXX4', 'YYYY', 
  'KKK4', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX5', 'YYYY', 'AAAA', 'BBBB', 'XXX5', 'YYYY', 
  'KKK5', 'LLLL', 'VVVV', 'QQQQ');
commit;

To check what is really going on I have created a replication of the tables. On the target site the table structure is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

CREATE SEQUENCE usr.seq1;
CREATE TABLE usr.reptable(
  seqid NUMERIC invisible DEFAULT usr.seq1.nextval NULL,
  before_repA CHAR(4) NULL,
  before_repB CHAR(4) NULL,
  before_refA CHAR(4) NULL,
  before_refB CHAR(4) NULL,
  before_uni1 CHAR(4) NULL,
  before_uni2 CHAR(4) NULL,
  before_ind1 CHAR(4) NULL,
  before_ind2 CHAR(4) NULL,
  before_col1 CHAR(4) NULL,
  before_col2 CHAR(4) NULL,
  after_repA CHAR(4) NULL,
  after_repB CHAR(4) NULL,
  after_refA CHAR(4) NULL,
  after_refB CHAR(4) NULL,
  after_uni1 CHAR(4) NULL,
  after_uni2 CHAR(4) NULL,
  after_ind1 CHAR(4) NULL,
  after_ind2 CHAR(4) NULL,
  after_col1 CHAR(4) NULL,
  after_col2 CHAR(4) NULL,
  opcode CHAR(1) NULL,
  ts TIMESTAMP NULL
);
 
CREATE SEQUENCE usr.seq2;
CREATE TABLE usr.reftable(
  seqid NUMERIC invisible DEFAULT usr.seq1.nextval NULL,
  before_refA CHAR(4) NULL,
  before_refB CHAR(4) NULL,
  before_col1 CHAR(4) NULL,
  after_refA CHAR(4) NULL,
  after_refB CHAR(4) NULL,
  after_col1 CHAR(4) NULL,
  opcode CHAR(1) NULL,
  ts TIMESTAMP NULL
);

The following are Extract params:

1
2
3
4
5

GETUPDATEBEFORES
NOCOMPRESSDELETES
UPDATERECORDFORMAT FULL
TABLE usr.reptable;
TABLE usr.reftable;

And Replicat PARAMS:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

INSERTUPDATES
INSERTDELETES
MAP usr.reptable TARGET usr.reptable, KEYCOLS(after_repA, after_repB), COLMAP(
  before_repA = @BEFORE(repA), before_repB = @BEFORE(repB),
  before_refA = @BEFORE(refA), before_refB = @BEFORE(refB),
  before_uni1 = @BEFORE(uni1), before_uni2 = @BEFORE(uni2),
  before_ind1 = @BEFORE(ind1), before_ind2 = @BEFORE(ind2),
  before_col1 = @BEFORE(col1), before_col2 = @BEFORE(col2),
  after_repA = @AFTER(repA), after_repB = @AFTER(repB),
  after_refA = @AFTER(refA), after_refB = @AFTER(refB),
  after_uni1 = @AFTER(uni1), after_uni2 = @AFTER(uni2),
  after_ind1 = @AFTER(ind1), after_ind2 = @AFTER(ind2),
  after_col1 = @AFTER(col1), after_col2 = @AFTER(col2),
  ts = @GETENV('GGHEADER', 'COMMITTIMESTAMP'),
  opcode = @CASE(@GETENV('GGHEADER', 'OPTYPE'), 'INSERT', 'I', 'UPDATE', 'U', 
  'PK UPDATE', 'P', 'SQL COMPUPDATE', 'S', 'ENSCRIBE COMPUPDATE', 'E', 
  'DELETE', 'D', 'TRUNCATE', 'T', '?'));
 
MAP usr.reftable TARGET usr.reftable, KEYCOLS(after_refA, after_refB), COLMAP(
  before_refA = @BEFORE(refA), before_refB = @BEFORE(refB),
  before_col1 = @BEFORE(col1),
  after_refA = @AFTER(refA), after_refB = @AFTER(refB),
  after_col1 = @AFTER(col1),
  ts = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
  opcode = @CASE(@GETENV('GGHEADER', 'OPTYPE'), 'INSERT', 'I', 'UPDATE', 'U', 
  'PK UPDATE', 'P', 'SQL COMPUPDATE', 'S', 'ENSCRIBE COMPUPDATE', 'E', 'DELETE', 
  'D', 'TRUNCATE', 'T', '?'));

In other words, the replication configuration allows to check what is really written to the redo log as the before and target image of the table during UPDATE command.

I have checked the following operations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- test1: change part of PK
UPDATE usr.reptable SET repB = 'YYY1' WHERE repA = 'XXX1';
commit;
-- test2: change part of FK
UPDATE usr.reptable SET refB = 'CCCC' WHERE repA = 'XXX2';
commit;
-- test3: change part of UK
UPDATE usr.reptable SET uni2 = 'ZZZZ' WHERE repA = 'XXX3';
commit;
-- test4: change part of IND
UPDATE usr.reptable SET ind2 = 'MMMM' WHERE repA = 'XXX4';
commit;
-- test5: change part of COL
UPDATE usr.reptable SET col2 = 'RRRR' WHERE repA = 'XXX5';
commit;
-- test6: delete row
DELETE FROM usr.reptable WHERE repA = 'XXX6';
commit;
-- test7: insert row
INSERT INTO usr.reptable VALUES ('XXX7', 'YYYY', 'AAAA', 'BBBB', 'XXX7', 
  'YYYY', 'KKK7', 'LLLL', 'VVVV', 'QQQQ');
commit;

2. Test

According to the documentation the ADD TRANDATA command stripped just to Oracle parameters has syntax:

1
2
3
4

ADD TRANDATA {[container.]owner.table | schema.table} 
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, COLS (COLUMNS)] 
[, NOKEY]

So, actually there are 3 modes: default (no parameter), NOSCHEDULINGCOLS and ALLCOLS. And there are 2 additional optional parameters: COLS and NOKEY. This command has no information on what is about to be used as PRIMARY KEY in the TABLE command used by Extract parameter file.

So actually there are 18 test cases:

  1. (no add trandata command)
  2. ADD TRANDATA usr.reptable
  3. ADD TRANDATA usr.reptable, COLS(col1)
  4. ADD TRANDATA usr.reptable, NOKEY
  5. ADD TRANDATA usr.reptable, COLS(col1), NOKEY
  6. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS
  7. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1)
  8. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, NOKEY
  9. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1), NOKEY
  10. ADD TRANDATA usr.reptable, ALLCOLS
  11. ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1)
  12. ADD TRANDATA usr.reptable, ALLCOLS, NOKEY
  13. ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1), NOKEY
  14. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
  15. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
  16. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
  17. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG GROUP “GGS_xxx” (“COL1″,”COL2”) ALWAYS;
  18. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

I have added the last 5 tests (issued not using GoldenGate but directly in the database) to check what is the exact relationship between ADD TRANDATA and ALTER TABLE commands. Some of them are contradictory, but … all those combinations are actually accepted by GoldenGate. So let’s look at the results:

3. Results

Test 1 – no ADD TRANDATA

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P	 YYYY						   YYY1
S		   BBBB 					     CCCC
S			     YYYY					       ZZZZ
S				       LLLL						 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY						   YYY1
S		   BBBB 					     CCCC
S			     YYYY					       ZZZZ
P				       LLLL						 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

It looks like, that without adding any supplemental logging at the table level, by default:

  • for update operation – the before value is available (from the undo)
  • for update operation – the after value is available (from the redo)
  • for insert operations – all values are available (from the redo)
  • for delete operations – all values are available (from the undo)

What is interesting is, that both values for INSERT and DELETE are available in the @AFTER image.

The second interesting thing is that in the second variant (when there is not primary key), the primary key is identified by GoldenGate from the second unique index (reptableIdx), not the first (reptablePK2). It seems that the index with alphabetically first columns (ind1, ind2) is preferred than the other one (repA, repB). Not sure at this moment, who made the decission – database or GoldeGate.

Test 2 – ADD TRANDATA usr.reptable

Tracing shows that GoldenGate issues in the database (variant with PK):

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;

and variant without PK:

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY					      XXX1 YYY1
S   XXX2 YYYY AAAA BBBB			              XXX2 YYYY AAAA CCCC
S   XXX3 YYYY		XXX3 YYYY		      XXX3 YYYY	          XXX3 ZZZZ
S   XXX4 YYYY			  KKK4 LLLL	      XXX4 YYYY			    KKK4 MMMM
S   XXX5 YYYY					 QQQQ XXX5 YYYY					   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY			  KKK1 LLLL	      XXX1 YYY1		            KKK1 LLLL
S   XXX2 YYYY AAAA BBBB 	  KKK2 LLLL	      XXX2 YYYY AAAA CCCC	    KKK2 LLLL
S   XXX3 YYYY		XXX3 YYYY KKK3 LLLL	      XXX3 YYYY	          XXX3 ZZZZ KKK3 LLLL
P   XXX4 YYYY			  KKK4 LLLL	      XXX4 YYYY		            KKK4 MMMM
S   XXX5 YYYY			  KKK5 LLLL	 QQQQ XXX5 YYYY		            KKK5 LLLL	   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

The default ADD TRANDATA command causes that the primary key is written to the redo log every time. If part of Rerefence Key, Unique Index or Unique Constraint is modified, the whole key is also written to the redo log. What is worth noticing, is that the ALTER TABLE command has been issued twice – one with LOG DATA PRIMARY KEY and once for all the columns that the primary key contains of.

The second variant – without the primary key on the table is more interesting. It seems that the database itself has identified the first created unique index (reptablePK2) as the primary key. Then GoldenGate has identified the second index (reptableIdx) as the primary key. As a result – both keys (repA, repB) and (ind1, ind2) are added to every change in the redo log.

Test 3 – ADD TRANDATA usr.reptable, COLS(col1)

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;

and variant without PK:

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY				    VVVV      XXX1 YYY1				      VVVV
S   XXX2 YYYY AAAA BBBB		            VVVV      XXX2 YYYY AAAA CCCC		      VVVV
S   XXX3 YYYY		XXX3 YYYY	    VVVV      XXX3 YYYY	          XXX3 ZZZZ 	      VVVV
S   XXX4 YYYY			  KKK4 LLLL VVVV      XXX4 YYYY			    KKK4 MMMM VVVV
S   XXX5 YYYY				    VVVV QQQQ XXX5 YYYY				      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY			  KKK1 LLLL VVVV      XXX1 YYY1 		    KKK1 LLLL VVVV
S   XXX2 YYYY AAAA BBBB 	  KKK2 LLLL VVVV      XXX2 YYYY AAAA CCCC	    KKK2 LLLL VVVV
S   XXX3 YYYY		XXX3 YYYY KKK3 LLLL VVVV      XXX3 YYYY 	  XXX3 ZZZZ KKK3 LLLL VVVV
P   XXX4 YYYY			  KKK4 LLLL VVVV      XXX4 YYYY 		    KKK4 MMMM VVVV
S   XXX5 YYYY			  KKK5 LLLL VVVV QQQQ XXX5 YYYY 		    KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

This test is identical as Test 3, but COL1 is also added to redo log – as chosen by the COLS statement.

Test 4 – ADD TRANDATA usr.reptable, NOKEY

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

and variant without PK:

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

A warning appears:

1
2

WARNING OGG-01387  TABLE USR.REPTABLE has no valid KEY COLUMNS, added unconditional 
supplemental log GROUP FOR ALL TABLE COLUMNS.

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Choosing NOKEY causes all columns to be written as the primary key.

Test 5 – ADD TRANDATA usr.reptable, COLS(col1), NOKEY

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

and variant without PK:

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY				    VVVV      XXX1 YYY1				      VVVV
S	      AAAA BBBB		            VVVV		AAAA CCCC		      VVVV
S			XXX3 YYYY	    VVVV			  XXX3 ZZZZ 	      VVVV
S				  KKK4 LLLL VVVV				    KKK4 MMMM VVVV
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY				    VVVV      XXX1 YYY1 			      VVVV
S	      AAAA BBBB 		    VVVV		AAAA CCCC		      VVVV
S			XXX3 YYYY	    VVVV			  XXX3 ZZZZ	      VVVV
P				  KKK4 LLLL VVVV				    KKK4 MMMM VVVV
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

This is somewhat supprising. It appears that choosing NOKEY + COLS is not the summary of changes for NOKEY and COLS.

Test 6 – ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;

and variant without PK:

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY					      XXX1 YYY1
S   XXX2 YYYY	   BBBB				      XXX2 YYYY	     CCCC
S   XXX3 YYYY		     YYYY		      XXX3 YYYY		       ZZZZ
S   XXX4 YYYY			       LLLL	      XXX4 YYYY			         MMMM
S   XXX5 YYYY					 QQQQ XXX5 YYYY					   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY			  KKK1 LLLL		   YYY1 		    KKK1 LLLL
S		   BBBB 	  KKK2 LLLL			     CCCC	    KKK2 LLLL
S			     YYYY KKK3 LLLL				       ZZZZ KKK3 LLLL
P				  KKK4 LLLL					    KKK4 MMMM
S				  KKK5 LLLL	 QQQQ				    KKK5 LLLL	   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

The documentation is suggesting that the NOSCHEDULINGCOLS is only for conditional supplemental logging. But in fact choosing this option would cause also not to issue ADD SUPPLEMENTAL GROUP (PRIMARY KEY) – which might be misleading.

Test 7 – ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1)

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;

and variant without PK:

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY				    VVVV      XXX1 YYY1				      VVVV
S   XXX2 YYYY	   BBBB		            VVVV      XXX2 YYYY	     CCCC		      VVVV
S   XXX3 YYYY		     YYYY	    VVVV      XXX3 YYYY		       ZZZZ 	      VVVV
S   XXX4 YYYY			       LLLL VVVV      XXX4 YYYY			         MMMM VVVV
S   XXX5 YYYY				    VVVV QQQQ XXX5 YYYY				      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY			  KKK1 LLLL VVVV	   YYY1 		    KKK1 LLLL VVVV
S		   BBBB 	  KKK2 LLLL VVVV		     CCCC	    KKK2 LLLL VVVV
S			     YYYY KKK3 LLLL VVVV			       ZZZZ KKK3 LLLL VVVV
P				  KKK4 LLLL VVVV				    KKK4 MMMM VVVV
S				  KKK5 LLLL VVVV QQQQ				    KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

A slightly modified variant of Test 7 with one column added.

Test 8 – ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, NOKEY

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

and variant without PK:

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

A warning appears:

1
2

WARNING OGG-01387  TABLE USR.REPTABLE has no valid KEY COLUMNS, added unconditional supplemental 
log GROUP FOR ALL TABLE COLUMNS.

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

The NOKEY clause causes all columns to be added to the redo/undo log.

Test 9 – ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1), NOKEY

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

and variant without PK:

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY				    VVVV	   YYY1 			      VVVV
S		   BBBB 		    VVVV		     CCCC		      VVVV
S			     YYYY	    VVVV			       ZZZZ	      VVVV
P				       LLLL VVVV					 MMMM VVVV
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY				    VVVV	   YYY1 			      VVVV
S		   BBBB 		    VVVV		     CCCC		      VVVV
S			     YYYY	    VVVV			       ZZZZ	      VVVV
P				       LLLL VVVV					 MMMM VVVV
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

… But adding the COLS causes to add just the selected columns.

Test 10 – ADD TRANDATA usr.reptable, ALLCOLS

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;

and variant without PK:

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

Test 11 – ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1)

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;

and variant without PK:

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

Test 12 – ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1), NOKEY

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

and variant without PK:

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

A warning appears:

1
2
3

WARNING OGG-00706  Failed TO ADD supplemental log GROUP ON TABLE USR.REPTABLE due TO ORA-32588: 
supplemental logging attribute ALL COLUMN EXISTS SQL ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL 
LOG DATA (ALL) COLUMNS  /* GOLDENGATE_DDL_REPLICATION */.

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

Test 13 – ADD TRANDATA usr.reptable, ALLCOLS, NOKEY

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

and variant without PK:

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

Test 14 – ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY					      XXX1 YYY1
S   XXX2 YYYY	   BBBB 			      XXX2 YYYY      CCCC
S   XXX3 YYYY		     YYYY		      XXX3 YYYY 	       ZZZZ
P   XXX4 YYYY			       LLLL	      XXX4 YYYY 			 MMMM
S   XXX5 YYYY					 QQQQ XXX5 YYYY 				   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY					      XXX1 YYY1
S   XXX2 YYYY	   BBBB 			      XXX2 YYYY      CCCC
S   XXX3 YYYY		     YYYY		      XXX3 YYYY 	       ZZZZ
P   XXX4 YYYY			       LLLL	      XXX4 YYYY 			 MMMM
S   XXX5 YYYY					 QQQQ XXX5 YYYY 				   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

Test 15 – ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY					      XXX1 YYY1
S		   BBBB 					     CCCC
S			XXX3 YYYY					  XXX3 ZZZZ
P				  KKK4 LLLL					    KKK4 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY					      XXX1 YYY1
S		   BBBB 					     CCCC
S			XXX3 YYYY					  XXX3 ZZZZ
P				  KKK4 LLLL					    KKK4 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here we have identified that by UNIQUE the database treats: all primary keys, all unique indexes and unique constraints.

Test 16 – ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY						   YYY1
S	      AAAA BBBB 					AAAA CCCC
S			     YYYY					       ZZZZ
P				       LLLL						 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY						   YYY1
S	      AAAA BBBB 					AAAA CCCC
S			     YYYY					       ZZZZ
P				       LLLL						 MMMM
S						 QQQQ						   RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

In this test all foreign keys are logged conditionally.

Test 17 – ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG GROUP “GGS_xxx” (“COL1″,”COL2”) ALWAYS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY				    VVVV QQQQ	   YYY1 			      VVVV QQQQ
S		   BBBB 		    VVVV QQQQ		     CCCC		      VVVV QQQQ
S			     YYYY	    VVVV QQQQ			       ZZZZ	      VVVV QQQQ
P				       LLLL VVVV QQQQ					 MMMM VVVV QQQQ
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S	 YYYY				    VVVV QQQQ	   YYY1 			      VVVV QQQQ
S		   BBBB 		    VVVV QQQQ		     CCCC		      VVVV QQQQ
S			     YYYY	    VVVV QQQQ			       ZZZZ	      VVVV QQQQ
P				       LLLL VVVV QQQQ					 MMMM VVVV QQQQ
S					    VVVV QQQQ					      VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

The column clause works as one might suspect.

Test 18 – ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

After running the test the following data got replicated (variant with PK):

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

and variant without PK:

1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D						      XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I						      XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ

Here, all columns are logged.

4. Conclusions

The table shows a summary of ADD TRANDATA parameters:

ADD TRANDATA … DATA (xxx) COLUMNS GROUP (xxx) ALWAYS DATA (ALL) COLUMNS
(no add trandata command) – – –
(without parameters) PRIMARY KEY, UNIQUE, FOREIGN KEY PK cols or first unique index cols –
COLS(col1) PRIMARY KEY, UNIQUE, FOREIGN KEY PK cols or first unique index cols + col1 –
NOKEY UNIQUE, FOREIGN KEY – ALL
COLS(col1), NOKEY UNIQUE, FOREIGN KEY col1 –
NOSCHEDULINGCOLS – PK cols or first unique index cols –
NOSCHEDULINGCOLS, COLS(col1) – PK cols or first unique index cols + col1 –
NOSCHEDULINGCOLS, NOKEY – – ALL
NOSCHEDULINGCOLS, COLS(col1), NOKEY – col1 –
ALLCOLS PRIMARY KEY, UNIQUE, FOREIGN KEY PK cols or first unique index cols ALL
ALLCOLS, COLS(col1) PRIMARY KEY, UNIQUE, FOREIGN KEY PK cols or first unique index cols + col1 ALL
ALLCOLS, NOKEY UNIQUE, FOREIGN KEY – ALL twice
ALLCOLS, COLS(col1), NOKEY UNIQUE, FOREIGN KEY col1 ALL

Conclusions:

  1. By default ADD TRANDATA causes:
    • executing LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS
    • executing LOG GROUP (key1, key2) COLUMNS
    • so actually the primary key supplemental logging is added twice: one with the LOG DATA and one with the LOG GROUP command
    • by default it also adds the conditional UNIQUE and FOREIGN KEY
  2. The NOSCHEDULINGCOLS causes:
    • removing LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS
    • it is strange, because it also removes the LOG DATA(PRIMARY KEY), which is an unconditional logging
    • but it adds the primary key using the LOG GROUP(key1, key2) – by specifying explicitly the primary key columns
  3. The NOKEY causes:
    • removing LOG DATA(PRIMARY KEY)
    • removing the LOG GROUP(key1, key2) with the primary key columns
    • when ALLCOLS is used: adding additional DATA (ALL) COLUMNS
  4. The COLS(col1) causes:
    • removing LOG DATA(key1, key2) with the primary key
    • if ALLCOLS or NOSCHEDULING is not used then removing  DATA(ALL) COLUMNS
  5. The database works differently than GoldenGate:
    • when the primary key is missing Database chooses the first created unique index (probably with lower obj#) as primary key – it is logged using DATA(PRIMARY KEY)
    • when the primary key is missing Oracle GoldenGate chooses the first alphabeticaly sorted unique index as primary key

3 thoughts on “Oracle GoldenGate: What is ADD TRANDATA really doing?”

  1. PC

    On OGG 12.1, with a table with unique key alone (no PK) and with “add schematrandata”, “info trandata” shows that all columns are logged. Doesn’t look like a bug?

    Reply
    1. Adam Leszczyński

      I have not made a detailed test of ADD SCHEMATRANDATA. Information that INFO TRANDATA show is not very detailed. It is better to check the information directly querying the data dictionaries.

      Reply

  2. Pingback: Oracle GoldenGate Integrated Replicat and NOSCHEDULINGCOLS – Bersler

Leave a Comment

Your email address will not be published. Required fields are marked *