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) 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

ORA-942 errors

In an Oracle database, a materialized view log is a table associated with the master table of a materialized view. When master table data undergoes DML changes (such as INSERT, UPDATE, or DELETE), the Oracle database stores rows describing those changes in the materialized view log. A materialized view log is similar to an AUDIT table and is the mechanism used to capture changes made to its related master table. Rows are automatically added to the Materialized View Log table when the master table changes. The Oracle database uses the materialized view log to refresh materialized views based on the master table. This process is called fast refresh and improves performance in the source database.
A materialized view log can capture the primary keys, row IDs, or object identifiers of rows that have been updated in the master table. The standard naming convention for a materialized view log table




Bug 14215010  ORA-942 on DMLs after an illegal drop of a MV log (if fix 13863326 present)

This bug is only relevant when using Query Rewrite (Including Materialized Views)
This problem is caused by the fix for bug 13863326.

If a materialized view log table is dropped by a different method than 

  "DROP MATERIALIZED VIEW LOG"
  or
  "DROP MATERIALIZED VIEW LOG ON T ;"
 
it can cause DMLs over the source table to raise ORA-942 errors.

Once this error is hit, the corruption already exists. The fix for this bug prevents the
illegal drop of a materialized view log in the future but will not correct existing
dictionary issues.

Workaround
  To remove the dictionary inconsistency caused by this bug, connect as the schema owner
  of the materialized view log, and issue the correct drop statement:
  eg:  drop materialized view log on <master_table>;

Note:
  For interim patches please use this fix instead of 13863326 to address both issues.
 
 
  Get all materialized views
select * from user_mviews

Get latest refresh times for all materialized views
select * from user_mview_refresh_times

Get information on a log
select count(*) from mlog$_MyTable;

Get the list of all materialized views on a view log
SELECT master, owner, NAME, snapshot_site,
TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots
FROM user_registered_snapshots, user_snapshot_logs
WHERE user_registered_snapshots.snapshot_id = user_snapshot_logs.snapshot_id (+)



complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log. We can create a materialized view log on our test table, T, like this.

describe T
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                          NOT NULL NUMBER
 VAL                                                   VARCHAR2(5)

create materialized view log on t ;

Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.
To see what a materialized view log looks like we can examine the table used to implement it. In practice developers other than Dizwell never actually need to reference this table, but showing it here helps illustrate materialized view log behaviour.

describe MLOG$_T
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                                   NUMBER
 SNAPTIME$$                                            DATE
 DMLTYPE$$                                             VARCHAR2(1)
 OLD_NEW$$                                             VARCHAR2(1)
 CHANGE_VECTOR$$                                       RAW(255)

The MLOG$_T.KEY column mirrors the base table's primary key column T.KEY. The other MLOG$ columns are system generated


select * from MLOG$_T ;
no rows selected

The query above shows that a materialized view log is initially empty upon creation. Rows are automatically added to MLOG$_T when base table T is changed.


UPDATE t set val = upper( val ) where KEY = 1 ;
INSERT into t ( KEY, val ) values ( 5, 'e' );
column dmltype$$ format a10
select key, dmltype$$ from MLOG$_T ;

       KEY DMLTYPE$$
---------- ----------
         1 U
         5 I

If the changes affecting T are rolled back, so are the changes to MLOG$_T.
rollback ;
Rollback complete.

select key, dmltype$$ from MLOG$_T ;
no rows selected

WITH PRIMARY KEY
To include the base table's primary key column in a materialized view log the WITH PRIMARY KEY clause can be specified.


drop materialized view log on t ;
create materialized view log on t WITH PRIMARY KEY ;
desc mlog$_t
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                                   NUMBER
 SNAPTIME$$                                            DATE
 DMLTYPE$$                                             VARCHAR2(1)
 OLD_NEW$$                                             VARCHAR2(1)
 CHANGE_VECTOR$$                                       RAW(255)

Note how MLOG$_T contains T's primary key column, T.KEY. This materialized view log is equivalent to the one created earlier in this topic, which did not have a WITH clause, because WITH PRIMARY KEY is the default option when no WITH clause is specified.
WITH ROWID
To include rowids instead of primary keys WITH ROWID can be specified.


drop materialized view log on t ;
create materialized view log on t WITH ROWID ;
desc mlog$_t
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 M_ROW$$                                               VARCHAR2(255)
 SNAPTIME$$                                            DATE
 DMLTYPE$$                                             VARCHAR2(1)
 OLD_NEW$$                                             VARCHAR2(1)
 CHANGE_VECTOR$$                                       RAW(255)

Note how the KEY column was replaced by the M_ROW$$ column, which contains rowids from table T. A materialized view log can also be created with both a rowid and a primary key column.

drop materialized view log on t ;
create materialized view log on t WITH ROWID, PRIMARY KEY ;
desc mlog$_t
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                                   NUMBER
 M_ROW$$                                               VARCHAR2(255)
 SNAPTIME$$                                            DATE
 DMLTYPE$$                                             VARCHAR2(1)
 OLD_NEW$$                                             VARCHAR2(1)
 CHANGE_VECTOR$$                                       RAW(255)

In this case both KEY and M_ROW$$ appear in the log table.
WITH SEQUENCE
A special SEQUENCE column can be include in the materialized view log to help Oracle apply updates to materialized view logs in the correct order when a mix of Data Manipulation (DML) commands, e.g. insert, update and delete, are performed on multiple base tables in a single transaction.


drop materialized view log on t ;
create materialized view log on t  WITH SEQUENCE ;
create materialized view log on t2 WITH SEQUENCE ;
INSERT into T  values ( 5, 'e' );
INSERT into T2 values ( 60, 3, 300 );
UPDATE T  set val = upper(val) where key = 5 ;
UPDATE T2 set amt = 333 where key = 60 ;
commit;

select SEQUENCE$$, key, dmltype$$ from mlog$_T ;

SEQUENCE$$        KEY DMLTYPE$$
---------- ---------- ----------
     60081          5 I
     60083          5 U

select SEQUENCE$$, key, dmltype$$ from mlog$_T2 ;

SEQUENCE$$        KEY DMLTYPE$$
---------- ---------- ----------
     60082         60 I
     60084         60 U

Since mixed DML is a common occurrence SEQUENCE will be specified in most materialized view logs. In fact, Oracle recommends it.
"Oracle recommends that the keyword SEQUENCE be included in your materialized view log statement unless you are sure that you will never perform a mixed DML operation (a combination of INSERT, UPDATE, or DELETE operations on multiple tables)."
-- from Creating Materialized Views: Materialized View Logs"
WITH Column List
The WITH clause can also contain a list of specific base table columns. In the next snippet we include the VAL column.

drop materialized view log on t ;
create materialized view log on t WITH ( VAL );
desc mlog$_t
 Name                                         Null?    Type
 -------------------------------------------- -------- ------------------------------
 KEY                                                   NUMBER
 VAL                                                   VARCHAR2(5)
 SNAPTIME$$                                            DATE
 DMLTYPE$$                                             VARCHAR2(1)
 OLD_NEW$$                                             VARCHAR2(1)
 CHANGE_VECTOR$$                                       RAW(255)

select * from t ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
         5 E

UPDATE t set val = 'f' where key = 5 ;
column old_new$$ format a10
select key, val, old_new$$ from mlog$_t ;

       KEY VAL   OLD_NEW$$
---------- ----- ----------
         5 E     O

INCLUDING NEW VALUES Clause
In the last snippet we see that the VAL column contains values as they existed before the update operation, aka the "old" value. There is no need to store the new value for an update because it can be derived by applying the change vector (a RAW value stored in CHANGE_VECTOR$$, which Oracle uses internally during refreshes) to the old value. In some situations, which we will identify in later topics, it helps to have both the old value and the new value explicitly saved in the materialized view log. We can do that using the INCLUDING NEW VALUES clause, like this.

drop materialized view log on T ;
create materialized view log on t
  with sequence ( VAL )
  INCLUDING NEW VALUES
;
update t set val = 'g' where key = 5 ;
column old_new$$ format a9
select sequence$$, key, val, old_new$$
from mlog$_t
order by sequence$$ ;

SEQUENCE$$        KEY VAL   OLD_NEW$$
---------- ---------- ----- ---------
     60085          5 f     O
     60086          5 g     N

Note how both the old and the new values are stored in the same column, VAL. The OLD_NEW$$ column identifies the value as either an old or a new value.
Gotcha - Commas
The syntax diagrams for the CREATE MATERIALIZED VIEW LOG command indicate a comma is required between each component of the WITH clause. However this does not appear to be the case when the component is a column list, e.g. "( VAL )".

drop materialized view log on t ;
create materialized view log on t with sequence, ( VAL ), primary key ;
create materialized view log on t with sequence, ( VAL ), primary key
                                                          *
ERROR at line 1:
ORA-00922: missing or invalid option


Omitting the comma before the column list works better.
create materialized view log on t with sequence ( VAL ), primary key;
Materialized view log created.

DBMS_REDEFINITION

The DBMS_REDEFINITION package has certain restrictions related to materialized view logs. In

Oracle 10g these restrictions are:
Tables with materialized view logs defined on them cannot be redefined online.
For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.
-- f-rom Oracle® Database Administrator's Guide 10g Release 2 (10.2) - Restrictions for Online Redefinition of Tables


In Oracle 11g they are:
After redefining a table that has a materialized view log, the subsequent refresh of any dependent materialized view must be a complete refresh.
For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties. No horizontal or vertical subsetting is permitted, nor are any column transformations. The only valid value for the column mapping string is NULL.
-- from Oracle® Database Administrator's Guide 11g Release 1 (11.1) - Restrictions for Online

Redefinition of Tables
Cleanup
delete t2 ;
delete t ;
insert into t select * from t_backup ;
insert into t2 select * from t2_backup ;
commit;
drop materialized view log on t ;
drop materialized view log on t2 ; 

java.sql.SQLRecoverableException: IO Error: Socket read timed out

java.sql.SQLRecoverableException: IO Error: Socket read timed out during adop/adpatch


While Application Team try to applying the R12.2 upgrade driver, They  faced the issue of WFXLoad.class failing in adworker log but showing up as running on adctrl
        Control





Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120 pl  WFXLoad.class               Running     
     2  Run       AutoPatch R120 pl  WFXLoad.class               Running     
     3  Run       AutoPatch R120 pl  WFXLoad.class               Running     
     4  Run       AutoPatch R120 pl  WFXLoad.class               Running     
     5  Run       AutoPatch R120 pl  WFXLoad.class               Running     
     6  Run       AutoPatch R120 pl                              Wait       
     7  Run       AutoPatch R120 pl  WFXLoad.class               Running     
 
log shows:




Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Socket read timed out
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:482)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:678)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:238)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at oracle.apps.ad.worker.AdJavaWorker.getAppsConnection(AdJavaWorker.java:1041)
        at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:276)
Caused by: oracle.net.ns.NetException: Socket read timed out
        at oracle.net.ns.Packet.receive(Packet.java:341)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:308)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1222)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:330)
        ... 8 more




This was happening again and again. The DBAs were suspecting network issue, cluster issue, server issue and all the usual suspects.  In Database alert log we saw these errors coming every few seconds:
Fatal NI connect error 12537, connecting to:







 (LOCAL=NO)
  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 11-NOV-2014 21:58:21
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (26388) as a result of ORA-609

We tried changing the parameters in sqlnet.ora and listener.ora as instructed in the article:
Troubleshooting Guide for
ORA-12537 / TNS-12537 TNS:Connection Closed (Doc ID 555609.1)
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

However, the errors continued.  To rule out any issues in network, I also restarted the network service

on Linux:
service network restart


One thing which I noticed was the extra amount of time that the connect was taking 4 seconds:


21:22:36 SQL> conn apps/apps
Connected.
21:22:40 SQL>


Checked from remote app tier and it was same 4 seconds.
Stopped listener and checked on DB server that uses bequeath protocol:


23:23:55 SQL> conn / as sysdba
Connected.
23:23:59 SQL> conn / as sysdba
Connected.


Again it took 4 seconds.
A few days back, I had seen that connect time had increased after turning setting the DB initialization parameter pre_page_sga to true in a different instance.  On a hunch, I checked this and indeed pre_page_sga was set to true.  I set this back to false:


alter system set pre_page_sga=false scope=spfile;
shutdown immediate;
exit
sqlplus /nolog
conn / as sysdba
startup
SQL> set time on




23:39:46 SQL> conn / as sysdba
Connected.
23:39:48 SQL>


The connections were happening instantly(2sec).  So I went ahead and resumed the patch after setting:

update fnd_install_processes
set control_code='W', status='W';


commit;


Application Team  restarted the patch and all the workers completed successfully.  And the patch was running significantly faster.  So I did a search on support.oracle.com to substantiate my solution with official Oracle documentation.  I found the following articles:


Slow Connection or ORA-12170 During Connect when PRE_PAGE_SGA init.ora Parameter is Set (Doc ID 289585.1)

Health Check Alert: Consider setting PRE_PAGE_SGA to FALSE (Doc ID 957525.1)


The first article (289585.1) says:

PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. The advantage that PRE_PAGE_SGA can afford depends on page size.


The second article (957525.1) says:

Having the PRE_PAGE_SGA initialization parameter set to TRUE can significantly increase the time required to establish database connections.


 "Overhead can be significant if your system frequently creates and destroys processes by,
example, continually logging on and logging off.".  That is exactly what happens when you do adpatch or adop.


Keep this in mind, whenever you do adpatch or adop, make sure that pre_page_sga is set to false.  It is possible that you may get the error "java.sql.SQLRecoverableException: IO Error: Socket read timed out" if you don't.  Also the patch will run significantly slower if pre_page_sga is set to true.  So set it to false and avoid these issues.


adpatch or adop :  https://blogs.oracle.com/ebstech/top-9-frequently-asked-questions-about-online-patching

Exadata -- Find Exadata Rack Model (Eight/Quarter/Half ..)

Exadata -- Find Exadata Rack Model (Eight/Quarter/Half ..)

In order to find the rack model( eigth , quarter, half or full rack) of Exadata, you can use the databasemachine.xml file.. This file is created by the onecommand process during provisioning of

Oracle Exadata..

For example:

cd /opt/oracle.SupportTools
grep -i MACHINETYPES databasemachine.xml

Output : X4-2 Eighth Rack HP 1.2TB

As you see here, MACHINETYPE tag gives us the Rack Model, type of the machine (HP for High performance, HC for High Capactiy) and storage size, as well.
Keep in mind that; You can gather more info using databasemachine.xml file..
Admin ip, MACHINEUSIZE, ILONNAMES are just some of those information stored in this file ..

"databasemachine.xml" file is actually used by Oracle Enterprise Manager 12c  during the discovery process and also used to gather the information to render the schematic diagram in the Enterprise Manager's Exadata home page.