ADD TRANDATA

   WHAT IS ADD TRANDATA REALLY DOES

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) COLUMNSGROUP (xxx) ALWAYSDATA (ALL) COLUMNS
(no add trandata command)–––
(without parameters)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols–
COLS(col1)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols + col1–
NOKEYUNIQUE, FOREIGN KEY–ALL
COLS(col1), NOKEYUNIQUE, FOREIGN KEYcol1–
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–
ALLCOLSPRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index colsALL
ALLCOLS, COLS(col1)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols + col1ALL
ALLCOLS, NOKEYUNIQUE, FOREIGN KEY–ALL twice
ALLCOLS, COLS(col1), NOKEYUNIQUE, FOREIGN KEYcol1ALL
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
Labels: