Troubleshoot Oracle GoldenGate lag


Troubleshoot Oracle GoldenGate lag

What is lag?

Lag is the time difference between when changes are made within the source database and when those changes are applied to the target database. This article discusses the following types of lag:
Extract lag: The difference, in seconds, between the system clock time that a record was processed by the Extract GoldenGate component and the timestamp of that record in the data source.
Replicat lag: The difference, in seconds, between the system clock time that the last record was processed by the Replicat GoldenGate component and the timestamp of the record in the trail files, which are a record of the transactions.

Monitor lag

You can monitor lag with the following GoldenGate Manager parameters (found in mgr.prm):
– LAGINFO {seconds | minutes | hours}: the basic informational warning lag threshold – LAGREPORT {minutes | hoursS}: the interval at which Manager checks for lag – LAGCRITICAL {seconds | minutes | hours}: the critical warning lag threshold
You can also monitor ggserr.log for latency information and review database query statistics in the GoldenGate dynamic performance views.

Obtain lag information

Use the following GoldenGate Software Command Interface (GGSCI) lag commands to obtain lag statistics for running Oracle GoldenGate Groups:
LAG EXTRACT

Use LAG EXTRACT to determine the true lag time between Extract and the data source, as shown the following example:
LAG EXTRACT group_name
The output should be similar to the following example:
2018-08-17 23:09:31  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.

Sending GETLAG request to EXTRACT EXT1 ...
Last record lag 4 seconds.
At EOF, no more records to process

LAG REPLICAT

Use LAG REPLICAT to determine the true lag time between Replicat and the trail, as shown in the following example:
LAG REPLICAT group_name

The output should be similar to the following example:
2018-08-17 23:02:49  INFO    OGG-14054  Lag from heartbeat table requires DBLOGIN.
Sending GETLAG request to REPLICAT REP1 ...
Last record lag 6 seconds.
Low watermark lag: 67.
High watermark lag: 8.
Low watermark position: 68765101167.
High watermark position: 68765101638.
At EOF, no more records to process

Control how lag is reported

Use the LAGREPORTMINUTES or LAGREPORTHOURS parameter to specify the interval at which Manager checks for Extract and Replicat lag.
Use the LAGCRITICALSECONDS, LAGCRITICALMINUTES, or LAGCRITICALHOURS parameter to specify a lag threshold that is considered critical and to force a warning message to the error log when the threshold is reached. This parameter affects Extract and Replicat processes on the local system.
Use the LAGINFOSECONDS, LAGINFOMINUTES, or LAGINFOHOURS parameter to specify a lag threshold. If the lag exceeds the specified value, GoldenGate reports lag information in the error log. If the lag exceeds the value specified with the LAGCRITICAL parameter, Manager reports the lag with a critical warning. Otherwise, it reports the lag with an informational message.

Analyze lag

Before trying to diagnose lag and potential bottlenecks in a GoldenGate environment, you should first understand the below Points.
  1. Flow of data between the source and target databases
  2. Check the Locks/Blocking Sessions in Database
  3. Always keep free space in UNDO Tablespace for Before and After Image Capture of data
  4. Take the Current Report using send report command
  5. Check the OGG log for more information 
  6. Check info replicate <<process_name>>,showch 
The following components can contribute to performance bottlenecks:
·         Extract reads the Oracle log files and captures any required data for replication.
·         Extract carries out any mapping and conversion of the data and writes to the trail files.
·         Data Pump reads the trail files and carries out any required mapping and conversion of the data.

·         Data Pump transfers the trail files from the source system to the target system, where Collector writes to the remote trail files.
·         Replicat reads the trail file, applies any mapping and conversions, and applies the data to the target database by using SQL statements.

Gather diagnostic lag information

To monitor GoldenGate performance, several key pieces of information must be gathered and analyzed. You should gather performance data on both the source and target environments for the same period to get a clear picture of lag.
Diagnostic information can be gathered from the following sources:
·         Extract and Replicat report files and error logs
·         Extract and Replicat statistics
·         Database views
·         Automatic Workload Repository (AWR) and Active Session History (ASH) reports
·         Oracle Streams Performance Advisor
·         Integrated Extract and Replicat Health Check report

Reduce lag

The following sections offer suggestions to troubleshoot and reduce lag within various GoldenGate components.
TUNE EXTRACT

Tune Extract by using the following techniques:
– Use the LOGALLSUPCOLS parameter to instruct Extract to write supplemental logged columns to the trail file. – Use the PARALLELISM parameter to control the number of preparers for processing logs. The Enterprise Edition allows for parallelism of preparers. - Avoid having long transactions. - Increase the input and output (I/O) throughput of the machine running Extract, especially write throughput. - Split large tables into row ranges across process groups.

TUNE PUMP

Tune Pump by using the following techniques:
·         Remove unnecessary processing in Pump.
·         Try to use the COMPRESSION feature.
·         Optimize network delivery by increasing the TCPBUFSIZE and TCPFLUSHBYTES parameter sizes.

TUNE REPLICAT

Tune Replicat by using the following techniques:
·         Use the BATCHSQL parameter, which improves the apply performance in the majority of workloads
·         Parallelize the Replicat apply by using integrated (IR) or coordinate replicat (CR) features.
·         Optimize target database SQL execution.
·         Use the EAGER_SIZE threshold to begin to apply transactions before a commit is received. The default is 9500.

TUNE GOLDENGATE IN GENERAL

Use the following techniques to tune GoldenGate in general:
·         Use multiple process groups.
·         Split large tables into row ranges across process groups.
·         Configure GoldenGate to use the network efficiently.
·         Eliminate disk I/O bottlenecks.
·         Manage virtual memory and paging.
·         Optimize data filtering and conversion.


Labels:

ADD TRANDATA

   WHAT IS ADD TRANDATA REALLY DOES

In the process of data replication using Oracle GoldenGate the first step is assuring that all the needed information has been written to the database redo log. By default the database does not write all the information that might be needed by the replication process. That’s why some additional supplemental logging is needed.
According to the documentation supplemental logging is being added by the ADD TRANDATA command. A very good research about this subject has been made by Julian Dyke in the article about adding supplemental logging. But what does the  GoldenGate command actually do and which options should be used?

1. ENVIRONMENT

This research has been made on Oracle Database 12.1.0.2 and GoldenGate 12.2.0.1 on Linux64 platform.The source database is in ARCHIVELOG mode and has FORCE LOGGING and SUPPLEMENTAL LOGGING turned on. I have created a test replication in two variants: one with primary key, and one without (but with an unique index). The source schema is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DROP TABLE usr.reptable;
DROP TABLE usr.reftable;
 
CREATE TABLE usr.reptable(
  repA CHAR(4) NOT NULL,
  repB CHAR(4) NOT NULL,
  refA CHAR(4) NOT NULL,
  refB CHAR(4) NOT NULL,
  uni1 CHAR(4) NOT NULL,
  uni2 CHAR(4) NOT NULL,
  ind1 CHAR(4) NOT NULL,
  ind2 CHAR(4) NOT NULL,
  col1 CHAR(4) NOT NULL,
  col2 CHAR(4) NOT NULL
);
 
-- variant 1
ALTER TABLE usr.reptable ADD CONSTRAINT reptablePK PRIMARY KEY(repA, repB);
-- variant 2
CREATE UNIQUE INDEX usr.reptablePK2 ON usr.reptable(repA, repB);
 
ALTER TABLE usr.reptable ADD CONSTRAINT reptableUK UNIQUE(uni1, uni2);
CREATE UNIQUE INDEX usr.reptableIdx ON usr.reptable(ind1, ind2);
 
CREATE TABLE usr.reftable(
  refA CHAR(4) NOT NULL,
  refB CHAR(4) NOT NULL,
  col1 CHAR(4) NOT NULL
);
ALTER TABLE usr.reftable ADD CONSTRAINT reftablePK PRIMARY KEY (refA, refB);
 
ALTER TABLE usr.reptable ADD CONSTRAINT reptableFKreftable FOREIGN KEY(refA, refB)
  REFERENCES usr.reftable(refA, refB);
The tables have been populated with data :
1
2
3
4
5
6
7
8
9
10
11
12
13
14

INSERT INTO usr.reftable VALUES ('AAAA', 'BBBB', 'DDDD');
INSERT INTO usr.reftable VALUES ('AAAA', 'CCCC', 'DDDD');
commit;
INSERT INTO usr.reptable VALUES ('XXX1', 'YYYY', 'AAAA', 'BBBB', 'XXX1', 'YYYY', 
  'KKK1', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX2', 'YYYY', 'AAAA', 'BBBB', 'XXX2', 'YYYY', 
  'KKK2', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX3', 'YYYY', 'AAAA', 'BBBB', 'XXX3', 'YYYY', 
  'KKK3', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX4', 'YYYY', 'AAAA', 'BBBB', 'XXX4', 'YYYY', 
  'KKK4', 'LLLL', 'VVVV', 'QQQQ');
INSERT INTO usr.reptable VALUES ('XXX5', 'YYYY', 'AAAA', 'BBBB', 'XXX5', 'YYYY', 
  'KKK5', 'LLLL', 'VVVV', 'QQQQ');
commit;
To check what is really going on I have created a replication of the tables. On the target site the table structure is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

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

GETUPDATEBEFORES
NOCOMPRESSDELETES
UPDATERECORDFORMAT FULL
TABLE usr.reptable;
TABLE usr.reftable;
And Replicat PARAMS:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

INSERTUPDATES
INSERTDELETES
MAP usr.reptable TARGET usr.reptable, KEYCOLS(after_repA, after_repB), COLMAP(
  before_repA = @BEFORE(repA), before_repB = @BEFORE(repB),
  before_refA = @BEFORE(refA), before_refB = @BEFORE(refB),
  before_uni1 = @BEFORE(uni1), before_uni2 = @BEFORE(uni2),
  before_ind1 = @BEFORE(ind1), before_ind2 = @BEFORE(ind2),
  before_col1 = @BEFORE(col1), before_col2 = @BEFORE(col2),
  after_repA = @AFTER(repA), after_repB = @AFTER(repB),
  after_refA = @AFTER(refA), after_refB = @AFTER(refB),
  after_uni1 = @AFTER(uni1), after_uni2 = @AFTER(uni2),
  after_ind1 = @AFTER(ind1), after_ind2 = @AFTER(ind2),
  after_col1 = @AFTER(col1), after_col2 = @AFTER(col2),
  ts = @GETENV('GGHEADER', 'COMMITTIMESTAMP'),
  opcode = @CASE(@GETENV('GGHEADER', 'OPTYPE'), 'INSERT', 'I', 'UPDATE', 'U', 
  'PK UPDATE', 'P', 'SQL COMPUPDATE', 'S', 'ENSCRIBE COMPUPDATE', 'E', 
  'DELETE', 'D', 'TRUNCATE', 'T', '?'));
 
MAP usr.reftable TARGET usr.reftable, KEYCOLS(after_refA, after_refB), COLMAP(
  before_refA = @BEFORE(refA), before_refB = @BEFORE(refB),
  before_col1 = @BEFORE(col1),
  after_refA = @AFTER(refA), after_refB = @AFTER(refB),
  after_col1 = @AFTER(col1),
  ts = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
  opcode = @CASE(@GETENV('GGHEADER', 'OPTYPE'), 'INSERT', 'I', 'UPDATE', 'U', 
  'PK UPDATE', 'P', 'SQL COMPUPDATE', 'S', 'ENSCRIBE COMPUPDATE', 'E', 'DELETE', 
  'D', 'TRUNCATE', 'T', '?'));
In other words, the replication configuration allows to check what is really written to the redo log as the before and target image of the table during UPDATE command.
I have checked the following operations:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

2. TEST

According to the documentation the ADD TRANDATA command stripped just to Oracle parameters has syntax:
1
2
3
4

ADD TRANDATA {[container.]owner.table | schema.table} 
[, NOSCHEDULINGCOLS | ALLCOLS] 
[, COLS (COLUMNS)] 
[, NOKEY]
So, actually there are 3 modes: default (no parameter), NOSCHEDULINGCOLS and ALLCOLS. And there are 2 additional optional parameters: COLS and NOKEY. This command has no information on what is about to be used as PRIMARY KEY in the TABLE command used by Extract parameter file.
So actually there are 18 test cases:
  1. (no add trandata command)
  2. ADD TRANDATA usr.reptable
  3. ADD TRANDATA usr.reptable, COLS(col1)
  4. ADD TRANDATA usr.reptable, NOKEY
  5. ADD TRANDATA usr.reptable, COLS(col1), NOKEY
  6. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS
  7. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1)
  8. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, NOKEY
  9. ADD TRANDATA usr.reptable, NOSCHEDULINGCOLS, COLS(col1), NOKEY
  10. ADD TRANDATA usr.reptable, ALLCOLS
  11. ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1)
  12. ADD TRANDATA usr.reptable, ALLCOLS, NOKEY
  13. ADD TRANDATA usr.reptable, ALLCOLS, COLS(col1), NOKEY
  14. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
  15. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
  16. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
  17. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG GROUP “GGS_xxx” (“COL1″,”COL2”) ALWAYS;
  18. ALTER TABLE “USR”.”REPTABLE” ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
I have added the last 5 tests (issued not using GoldenGate but directly in the database) to check what is the exact relationship between ADD TRANDATA and ALTER TABLE commands. Some of them are contradictory, but … all those combinations are actually accepted by GoldenGate. So let’s look at the results:

3. RESULTS

TEST 1 – NO ADD TRANDATA

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P  YYYY         YYY1
S     BBBB           CCCC
S        YYYY            ZZZZ
S           LLLL       MMMM
S       QQQQ         RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY         YYY1
S     BBBB           CCCC
S        YYYY            ZZZZ
P           LLLL       MMMM
S       QQQQ         RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
It looks like, that without adding any supplemental logging at the table level, by default:
  • for update operation – the before value is available (from the undo)
  • for update operation – the after value is available (from the redo)
  • for insert operations – all values are available (from the redo)
  • for delete operations – all values are available (from the undo)
What is interesting is, that both values for INSERT and DELETE are available in the @AFTER image.
The second interesting thing is that in the second variant (when there is not primary key), the primary key is identified by GoldenGate from the second unique index (reptableIdx), not the first (reptablePK2). It seems that the index with alphabetically first columns (ind1, ind2) is preferred than the other one (repA, repB). Not sure at this moment, who made the decission – database or GoldeGate.

TEST 2 – ADD TRANDATA USR.REPTABLE

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

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;
and variant without PK:
1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY           XXX1 YYY1
S   XXX2 YYYY AAAA BBBB                 XXX2 YYYY AAAA CCCC
S   XXX3 YYYY  XXX3 YYYY        XXX3 YYYY           XXX3 ZZZZ
S   XXX4 YYYY     KKK4 LLLL       XXX4 YYYY       KKK4 MMMM
S   XXX5 YYYY      QQQQ XXX5 YYYY        RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY     KKK1 LLLL       XXX1 YYY1              KKK1 LLLL
S   XXX2 YYYY AAAA BBBB    KKK2 LLLL       XXX2 YYYY AAAA CCCC     KKK2 LLLL
S   XXX3 YYYY  XXX3 YYYY KKK3 LLLL       XXX3 YYYY           XXX3 ZZZZ KKK3 LLLL
P   XXX4 YYYY     KKK4 LLLL       XXX4 YYYY              KKK4 MMMM
S   XXX5 YYYY     KKK5 LLLL  QQQQ XXX5 YYYY              KKK5 LLLL    RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
The default ADD TRANDATA command causes that the primary key is written to the redo log every time. If part of Rerefence Key, Unique Index or Unique Constraint is modified, the whole key is also written to the redo log. What is worth noticing, is that the ALTER TABLE command has been issued twice – one with LOG DATA PRIMARY KEY and once for all the columns that the primary key contains of.
The second variant – without the primary key on the table is more interesting. It seems that the database itself has identified the first created unique index (reptablePK2) as the primary key. Then GoldenGate has identified the second index (reptableIdx) as the primary key. As a result – both keys (repA, repB) and (ind1, ind2) are added to every change in the redo log.

TEST 3 – ADD TRANDATA USR.REPTABLE, COLS(COL1)

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;
and variant without PK:
1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY        VVVV      XXX1 YYY1          VVVV
S   XXX2 YYYY AAAA BBBB              VVVV      XXX2 YYYY AAAA CCCC        VVVV
S   XXX3 YYYY  XXX3 YYYY     VVVV      XXX3 YYYY           XXX3 ZZZZ        VVVV
S   XXX4 YYYY     KKK4 LLLL VVVV      XXX4 YYYY       KKK4 MMMM VVVV
S   XXX5 YYYY        VVVV QQQQ XXX5 YYYY          VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY     KKK1 LLLL VVVV      XXX1 YYY1       KKK1 LLLL VVVV
S   XXX2 YYYY AAAA BBBB    KKK2 LLLL VVVV      XXX2 YYYY AAAA CCCC     KKK2 LLLL VVVV
S   XXX3 YYYY  XXX3 YYYY KKK3 LLLL VVVV      XXX3 YYYY    XXX3 ZZZZ KKK3 LLLL VVVV
P   XXX4 YYYY     KKK4 LLLL VVVV      XXX4 YYYY       KKK4 MMMM VVVV
S   XXX5 YYYY     KKK5 LLLL VVVV QQQQ XXX5 YYYY       KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
This test is identical as Test 3, but COL1 is also added to redo log – as chosen by the COLS statement.

TEST 4 – ADD TRANDATA USR.REPTABLE, NOKEY

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
and variant without PK:
1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
A warning appears:
1
2

WARNING OGG-01387  TABLE USR.REPTABLE has no valid KEY COLUMNS, added unconditional 
supplemental log GROUP FOR ALL TABLE COLUMNS.
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
Choosing NOKEY causes all columns to be written as the primary key.

TEST 5 – ADD TRANDATA USR.REPTABLE, COLS(COL1), NOKEY

1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
and variant without PK:
1
2

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY        VVVV      XXX1 YYY1          VVVV
S       AAAA BBBB              VVVV  AAAA CCCC        VVVV
S   XXX3 YYYY     VVVV     XXX3 ZZZZ        VVVV
S      KKK4 LLLL VVVV        KKK4 MMMM VVVV
S         VVVV QQQQ           VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY        VVVV      XXX1 YYY1          VVVV
S       AAAA BBBB       VVVV  AAAA CCCC        VVVV
S   XXX3 YYYY     VVVV     XXX3 ZZZZ       VVVV
P      KKK4 LLLL VVVV        KKK4 MMMM VVVV
S         VVVV QQQQ           VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
This is somewhat supprising. It appears that choosing NOKEY + COLS is not the summary of changes for NOKEY and COLS.

TEST 6 – ADD TRANDATA USR.REPTABLE, NOSCHEDULINGCOLS

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;
and variant without PK:
1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY           XXX1 YYY1
S   XXX2 YYYY    BBBB          XXX2 YYYY      CCCC
S   XXX3 YYYY       YYYY        XXX3 YYYY         ZZZZ
S   XXX4 YYYY          LLLL       XXX4 YYYY            MMMM
S   XXX5 YYYY      QQQQ XXX5 YYYY        RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY     KKK1 LLLL     YYY1       KKK1 LLLL
S     BBBB    KKK2 LLLL        CCCC     KKK2 LLLL
S        YYYY KKK3 LLLL           ZZZZ KKK3 LLLL
P      KKK4 LLLL         KKK4 MMMM
S      KKK5 LLLL  QQQQ        KKK5 LLLL    RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
The documentation is suggesting that the NOSCHEDULINGCOLS is only for conditional supplemental logging. But in fact choosing this option would cause also not to issue ADD SUPPLEMENTAL GROUP (PRIMARY KEY) – which might be misleading.

TEST 7 – ADD TRANDATA USR.REPTABLE, NOSCHEDULINGCOLS, COLS(COL1)

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;
and variant without PK:
1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
P   XXX1 YYYY        VVVV      XXX1 YYY1          VVVV
S   XXX2 YYYY    BBBB              VVVV      XXX2 YYYY      CCCC        VVVV
S   XXX3 YYYY       YYYY     VVVV      XXX3 YYYY         ZZZZ        VVVV
S   XXX4 YYYY          LLLL VVVV      XXX4 YYYY            MMMM VVVV
S   XXX5 YYYY        VVVV QQQQ XXX5 YYYY          VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY     KKK1 LLLL VVVV    YYY1       KKK1 LLLL VVVV
S     BBBB    KKK2 LLLL VVVV       CCCC     KKK2 LLLL VVVV
S        YYYY KKK3 LLLL VVVV          ZZZZ KKK3 LLLL VVVV
P      KKK4 LLLL VVVV        KKK4 MMMM VVVV
S      KKK5 LLLL VVVV QQQQ        KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
A slightly modified variant of Test 7 with one column added.

TEST 8 – ADD TRANDATA USR.REPTABLE, NOSCHEDULINGCOLS, NOKEY

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
and variant without PK:
1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
A warning appears:
1
2

WARNING OGG-01387  TABLE USR.REPTABLE has no valid KEY COLUMNS, added unconditional supplemental 
log GROUP FOR ALL TABLE COLUMNS.
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
The NOKEY clause causes all columns to be added to the redo/undo log.

TEST 9 – ADD TRANDATA USR.REPTABLE, NOSCHEDULINGCOLS, COLS(COL1), NOKEY

1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
and variant without PK:
1

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY        VVVV    YYY1          VVVV
S     BBBB       VVVV       CCCC        VVVV
S        YYYY     VVVV          ZZZZ       VVVV
P           LLLL VVVV      MMMM VVVV
S         VVVV QQQQ           VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY        VVVV    YYY1          VVVV
S     BBBB       VVVV       CCCC        VVVV
S        YYYY     VVVV          ZZZZ       VVVV
P           LLLL VVVV      MMMM VVVV
S         VVVV QQQQ           VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
… But adding the COLS causes to add just the selected columns.

TEST 10 – ADD TRANDATA USR.REPTABLE, ALLCOLS

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB") ALWAYS;
and variant without PK:
1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

TEST 11 – ADD TRANDATA USR.REPTABLE, ALLCOLS, COLS(COL1)

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("REPA","REPB","COL1") ALWAYS;
and variant without PK:
1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("IND1","IND2","COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

TEST 12 – ADD TRANDATA USR.REPTABLE, ALLCOLS, COLS(COL1), NOKEY

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
and variant without PK:
1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
A warning appears:
1
2
3

WARNING OGG-00706  Failed TO ADD supplemental log GROUP ON TABLE USR.REPTABLE due TO ORA-32588: 
supplemental logging attribute ALL COLUMN EXISTS SQL ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL 
LOG DATA (ALL) COLUMNS  /* GOLDENGATE_DDL_REPLICATION */.
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

TEST 13 – ADD TRANDATA USR.REPTABLE, ALLCOLS, NOKEY

1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
and variant without PK:
1
2
3

ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (UNIQUE, FOREIGN KEY) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE "USR"."REPTABLE" ADD SUPPLEMENTAL LOG GROUP "GGS_xxx" ("COL1") ALWAYS;
After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

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

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY           XXX1 YYY1
S   XXX2 YYYY    BBBB          XXX2 YYYY      CCCC
S   XXX3 YYYY       YYYY        XXX3 YYYY         ZZZZ
P   XXX4 YYYY          LLLL       XXX4 YYYY     MMMM
S   XXX5 YYYY      QQQQ XXX5 YYYY        RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

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

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY           XXX1 YYY1
S     BBBB           CCCC
S   XXX3 YYYY       XXX3 ZZZZ
P      KKK4 LLLL         KKK4 MMMM
S       QQQQ         RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY           XXX1 YYY1
S     BBBB           CCCC
S   XXX3 YYYY       XXX3 ZZZZ
P      KKK4 LLLL         KKK4 MMMM
S       QQQQ         RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
Here we have identified that by UNIQUE the database treats: all primary keys, all unique indexes and unique constraints.

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

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY         YYY1
S       AAAA BBBB      AAAA CCCC
S        YYYY            ZZZZ
P           LLLL       MMMM
S       QQQQ         RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

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

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S  YYYY        VVVV QQQQ    YYY1          VVVV QQQQ
S     BBBB       VVVV QQQQ       CCCC        VVVV QQQQ
S        YYYY     VVVV QQQQ          ZZZZ       VVVV QQQQ
P           LLLL VVVV QQQQ      MMMM VVVV QQQQ
S         VVVV QQQQ           VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

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

After running the test the following data got replicated (variant with PK):
1
2
3
4
5
6
7
8
9

OPC BRPA BRPB BRFA BRFB BUN1 BUN2 BIN1 BIN2 BCO1 BCO2 ARPA ARPB ARFA ARFB AUN1 AUN2 AIN1 AIN2 ACO1 ACO2
--- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
S   XXX1 YYYY AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ XXX1 YYY1 AAAA BBBB XXX1 YYYY KKK1 LLLL VVVV QQQQ
S   XXX2 YYYY AAAA BBBB XXX2 YYYY KKK2 LLLL VVVV QQQQ XXX2 YYYY AAAA CCCC XXX2 YYYY KKK2 LLLL VVVV QQQQ
S   XXX3 YYYY AAAA BBBB XXX3 YYYY KKK3 LLLL VVVV QQQQ XXX3 YYYY AAAA BBBB XXX3 ZZZZ KKK3 LLLL VVVV QQQQ
P   XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 LLLL VVVV QQQQ XXX4 YYYY AAAA BBBB XXX4 YYYY KKK4 MMMM VVVV QQQQ
S   XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV QQQQ XXX5 YYYY AAAA BBBB XXX5 YYYY KKK5 LLLL VVVV RRRR
D            XXX6 YYYY AAAA BBBB XXX6 YYYY KKK6 LLLL VVVV QQQQ
I            XXX7 YYYY AAAA BBBB XXX7 YYYY KKK7 LLLL VVVV QQQQ
and variant without PK:
1
2
3
4
5
6
7
8
9

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

4. CONCLUSIONS

The table shows a summary of ADD TRANDATA parameters:
ADD TRANDATA …DATA (xxx) COLUMNSGROUP (xxx) ALWAYSDATA (ALL) COLUMNS
(no add trandata command)–––
(without parameters)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols–
COLS(col1)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols + col1–
NOKEYUNIQUE, FOREIGN KEY–ALL
COLS(col1), NOKEYUNIQUE, FOREIGN KEYcol1–
NOSCHEDULINGCOLS–PK cols or first unique index cols–
NOSCHEDULINGCOLS, COLS(col1)–PK cols or first unique index cols + col1–
NOSCHEDULINGCOLS, NOKEY––ALL
NOSCHEDULINGCOLS, COLS(col1), NOKEY–col1–
ALLCOLSPRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index colsALL
ALLCOLS, COLS(col1)PRIMARY KEY, UNIQUE, FOREIGN KEYPK cols or first unique index cols + col1ALL
ALLCOLS, NOKEYUNIQUE, FOREIGN KEY–ALL twice
ALLCOLS, COLS(col1), NOKEYUNIQUE, FOREIGN KEYcol1ALL
Conclusions:
  1. By default ADD TRANDATA causes:
    • executing LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS
    • executing LOG GROUP (key1, key2) COLUMNS
    • so actually the primary key supplemental logging is added twice: one with the LOG DATA and one with the LOG GROUP command
    • by default it also adds the conditional UNIQUE and FOREIGN KEY
  2. The NOSCHEDULINGCOLS causes:
    • removing LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS
    • it is strange, because it also removes the LOG DATA(PRIMARY KEY), which is an unconditional logging
    • but it adds the primary key using the LOG GROUP(key1, key2) – by specifying explicitly the primary key columns
  3. The NOKEY causes:
    • removing LOG DATA(PRIMARY KEY)
    • removing the LOG GROUP(key1, key2) with the primary key columns
    • when ALLCOLS is used: adding additional DATA (ALL) COLUMNS
  4. The COLS(col1) causes:
    • removing LOG DATA(key1, key2) with the primary key
    • if ALLCOLS or NOSCHEDULING is not used then removing  DATA(ALL) COLUMNS
  5. The database works differently than GoldenGate:
    • when the primary key is missing Database chooses the first created unique index (probably with lower obj#) as primary key – it is logged using DATA(PRIMARY KEY)
    • when the primary key is missing Oracle GoldenGate chooses the first alphabeticaly sorted unique index as primary key
Labels: