原文:https://www.bersler.com/blog/what-is-add-trandata-really-doing/
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:
- (no add trandata command)
- ADD TRANDATA usr.reptable
- ADD TRANDATA usr.reptable, COLS(col1)
- ADD TRANDATA usr.reptable, NOKEY
- ADD TRANDATA usr.reptable, COLS(col1), NOKEY
- ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS
- ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1)
- ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, NOKEY
- ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1), NOKEY
- ADD TRANDATA usr.reptable, ALLCOLS
- ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1)
- ADD TRANDATA usr.reptable, ALLCOLS, NOKEY
- ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1), NOKEY
- ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
- ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
- ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
- ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG GROUP “GGS_xxx” (“COL1″,”COL2”) ALWAYS;
- 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:
- 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
- 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
- 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
- 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
- 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