DMS - Some tables do not replicate change data after initial load from oracle to Postgres

[hanspetter]

Hi. We are running a database migration from onprem oracle to Cloud SQL for Postgres. For some reason, change data is not replicated for a few tables. We have tried to identify common attributes on these tables and this is what we have found so far:

Two of the tables have a generated primary key on oracle declared like 

number generated by default as identity primary key

 All tables have supplemental log data enabled in oracle.

From one of the tables, the log shows that the changes have been picked up, but we are not seeing the changes in the target table. (see figure below)

From the other table, we cannot see the changes being picked up by the migration job at all

The third table we have discovered suffering from similar problems do not have a generated id on the oracle-side. However it does lack a primary key, so DMS have added a row-id column to keep track of changes. We do however have other tables lacking primary key where this problem does not arise.

For this table, the changes do not appear to be picked up by the migration job either.

NOTICE 2024-08-13T12:39:16.772Z Successfully loaded 1474 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:16.061Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:15.114Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:13.883Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:11.862Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:10.277Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:08.734Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:06.524Z Successfully loaded 1727 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:05.178Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:03.586Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:01.978Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:39:00.028Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:58.592Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:56.941Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:54.115Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:53.582Z Successfully loaded 231 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:53.351Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:51.574Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:49.878Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:48.382Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:07.121Z Successfully loaded 35 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:06.589Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:05.020Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:03.399Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:38:01.935Z Successfully loaded 2500 FULL DUMP rows into table: VEILARBDIALOG_EKSTERN_VARSEL_KVITTERING
NOTICE 2024-08-13T12:36:15.064Z FULL DUMP read is completed for table: VEILARBDIALOG.EKSTERN_VARSEL_KVITTERING

There are no errors showing in the logs, neither in the migration logs nor in the database logs, and this is perhaps the biggest problem, that we cannot trust the migration to be working even though the monitoring shows a healthy status. 

Our last migration suffered from similar problems, where the error was discovered by manual verification during finalization, and we had to run a new full load of the affected tables during downtime, increasing the downtime in production considerably. 

[somdyuti]

Hi,

Can you please share the DMS CW for the DDLs of these Oracle tables having problems and converted PostgreSQL DDLs.

[hanspetter]

I can share the converted PostgreSQL DDLs, but how do I share the Conversion Workspace?

Would it help if I shared the original Oracle DDL vs the DMS-interpreted Oracle DDL vs the generated Postgres DDL?

[somdyuti]

Yes, that is what I want to look at->a) the original Oracle DDL vs b) the DMS-interpreted Oracle DDL vs the c) generated Postgres DDL?

Especially with 1) Oracle column with "generated as identity"- the DMS-interpreted Oracle DDL will create a sequence in DMS Conversion Workspace to implement the auto-increment PK column and for the converted PostgreSQL DDL the column will be "<columnname> DECIMAL NOT NULL DEFAULT nextval('<schema>.iseq$$_xxx')"- so the Converted PostgreSQL DDL will have the PK Constraint and also the sequence generated.

Hope you have applied the changed (both the table and sequence) from the DMS CW to the target PostgreSQL? If yes, the CDC should work fine.

[hanspetter]

Since we last communicated, one of the tables, BRUKERNOTIFIKASJON, has suddenly caught up with CDC, more than a day after the changes being reported applied in the logs. I will include still include the DDL of this table, for completeness. The other two still have no changes applied after initial load.

Original Oracle DDL run by flyway:

create table BRUKERNOTIFIKASJON
(
    ID                       NUMBER(19) generated by default as identity primary key,
    EVENT_ID                 VARCHAR(40)              not null,
    DIALOG_ID                NUMBER(19)                not null,
    FOEDSELSNUMMER           VARCHAR(255)              not null,
    OPPFOLGINGSPERIODE_ID    VARCHAR(255)              not null,
    TYPE                     VARCHAR(255)              not null,
    STATUS                   VARCHAR(255)              not null,
    OPPRETTET                TIMESTAMP                 not null,
    MELDING                  VARCHAR(500)              not null,
    VARSEL_FEILET            TIMESTAMP,
    AVSLUTTET                TIMESTAMP,
    BEKREFTET_SENDT          TIMESTAMP,
    FORSOKT_SENDT            TIMESTAMP,
    FERDIG_BEHANDLET         TIMESTAMP,
    VARSEL_KVITTERING_STATUS VARCHAR(255) default NULL not null,
    SMSTEKST                 VARCHAR2(160),
    EPOSTTITTEL              VARCHAR2(200),
    EPOSTBODY                VARCHAR2(3000),
    constraint BRUKERNOTIFIKASJON_FK
        foreign key (DIALOG_ID) references DIALOG (DIALOG_ID)
);

Oracle DDL as extracted by SqlDeveloper:

  CREATE TABLE "VEILARBDIALOG"."BRUKERNOTIFIKASJON" 
   (	"ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , ...
  CREATE UNIQUE INDEX "VEILARBDIALOG"."SYS_C0020075" ON "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ("ID") ....
  ALTER TABLE "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ADD PRIMARY KEY ("ID") USING INDEX ... 
  

Oracle DDL as extracted by IntelliJ:

create table BRUKERNOTIFIKASJON
(
    ID                       NUMBER(19)    default "VEILARBDIALOG"."ISEQSS_130623".nextval generated as identity
        primary key,
    EVENT_ID                 VARCHAR2(40)               not null,
    DIALOG_ID                NUMBER(19)                 not null
        constraint BRUKERNOTIFIKASJON_FK
            references DIALOG, 
   ...

Oracle DDL as interpreted by DMS WS:

CREATE SEQUENCE "VEILARBDIALOG"."ISEQSS_130623" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 START WITH 2998 CACHE 20;
CREATE TABLE "VEILARBDIALOG"."BRUKERNOTIFIKASJON"
(
  "ID" NUMBER(19, 0) DEFAULT "VEILARBDIALOG"."ISEQSS_130623".nextval NOT NULL,
  "EVENT_ID" VARCHAR2(40 BYTE) NOT NULL,
  "DIALOG_ID" NUMBER(19, 0) NOT NULL,
  "FOEDSELSNUMMER" VARCHAR2(255 BYTE) NOT NULL,
  "OPPFOLGINGSPERIODE_ID" VARCHAR2(255 BYTE) NOT NULL,
  "TYPE" VARCHAR2(255 BYTE) NOT NULL,
  "STATUS" VARCHAR2(255 BYTE) NOT NULL,
  "OPPRETTET" TIMESTAMP (6) NOT NULL,
  "MELDING" VARCHAR2(500 BYTE) NOT NULL,
  "VARSEL_FEILET" TIMESTAMP (6),
  "AVSLUTTET" TIMESTAMP (6),
  "BEKREFTET_SENDT" TIMESTAMP (6),
  "FORSOKT_SENDT" TIMESTAMP (6),
  "FERDIG_BEHANDLET" TIMESTAMP (6),
  "VARSEL_KVITTERING_STATUS" VARCHAR2(255 BYTE) DEFAULT NULL  NOT NULL,
  "SMSTEKST" VARCHAR2(160 BYTE),
  "EPOSTTITTEL" VARCHAR2(200 BYTE),
  "EPOSTBODY" VARCHAR2(3000 BYTE),
  "LENKE" NVARCHAR2(255)
);
ALTER TABLE "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ADD CONSTRAINT BRUKERNOTIFIKASJON_FK FOREIGN KEY ("DIALOG_ID") REFERENCES "VEILARBDIALOG"."DIALOG" ("DIALOG_ID") ENABLE;
ALTER TABLE "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ADD CONSTRAINT "SYS_C0020075" PRIMARY KEY ("ID") ENABLE;
CREATE UNIQUE INDEX "SYS_C0020075" ON "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ("ID");

Postgres DDL as generated by DMS WS:

CREATE SEQUENCE veilarbdialog.iseqSS_130623 START WITH 2998 INCREMENT BY 1 CACHE 20 MINVALUE 1 MAXVALUE 9223372036854775807;
DROP TABLE IF EXISTS "veilarbdialog"."brukernotifikasjon";
CREATE TABLE veilarbdialog.brukernotifikasjon (
  id BIGINT NOT NULL DEFAULT nextval('veilarbdialog.iseqSS_130623'),
  event_id VARCHAR(40) NOT NULL,
  dialog_id BIGINT NOT NULL,
  foedselsnummer VARCHAR(255) NOT NULL,
  oppfolgingsperiode_id VARCHAR(255) NOT NULL,
  type VARCHAR(255) NOT NULL,
  status VARCHAR(255) NOT NULL,
  opprettet TIMESTAMP(6) NOT NULL,
  melding VARCHAR(500) NOT NULL,
  varsel_feilet TIMESTAMP(6),
  avsluttet TIMESTAMP(6),
  bekreftet_sendt TIMESTAMP(6),
  forsokt_sendt TIMESTAMP(6),
  ferdig_behandlet TIMESTAMP(6),
  varsel_kvittering_status VARCHAR(255) NOT NULL DEFAULT NULL,
  smstekst VARCHAR(160),
  eposttittel VARCHAR(200),
  epostbody VARCHAR(3000),
  lenke VARCHAR(255)
);
ALTER TABLE veilarbdialog.brukernotifikasjon ADD CONSTRAINT brukernotifikasjon_fk FOREIGN KEY (dialog_id) REFERENCES veilarbdialog.dialog (dialog_id);
ALTER TABLE veilarbdialog.brukernotifikasjon ADD CONSTRAINT sys_c0020075 PRIMARY KEY (id);

All ddl has been applied.

I will include the DDLs of the other two tables in separate posts.

NB I had to change double dollar-signs to 'SS' in the source code due to forum limitations.

[somdyuti]

Can you check and let me know when the Full Load and CDC of this table happened-

veilarbdialog.dialog

[hanspetter]

veilarbdialog.dialog is complete and picking up changes within the minute.

[hanspetter]

Here follows ddl details for the table ESKALERINGSVARSEL

Original Oracle DDL run by flyway:

CREATE TABLE ESKALERINGSVARSEL
(
    id                               number generated by default as identity primary key,
    aktor_id                         NVARCHAR2(255) NOT NULL,
    opprettet_av                     NVARCHAR2(255) NOT NULL,
    opprettet_dato                   TIMESTAMP      NOT NULL,
    tilhorende_dialog_id             number         NOT NULL,
    tilhorende_brukernotifikasjon_id number,
    opprettet_begrunnelse            CLOB,
    avsluttet_dato                   TIMESTAMP,
    avsluttet_av                     NVARCHAR2(255),
    avsluttet_begrunnelse            CLOB,
    constraint tilhorende_dialog_id_fk foreign key (tilhorende_dialog_id) references DIALOG (DIALOG_ID),
    constraint tilhorende_brukernotifikasjon_id_fk foreign key (tilhorende_brukernotifikasjon_id) references BRUKERNOTIFIKASJON (ID)
);

Note that the foreign key declarations are not accepted by DMS WS, because the 'number' in the foreign key column is incompatible with the number(19,0) in the source tables when converted to postgres. After applying the scheme, we altered the column type in postgres and added the foreign keys ourselves.

Oracle DDL as extracted by SqlDeveloper:

CREATE TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" 
   (	"ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 310000 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
	"AKTOR_ID" NVARCHAR2(255), 
	"OPPRETTET_AV" NVARCHAR2(255), 
	"OPPRETTET_DATO" TIMESTAMP (6), 
	"TILHORENDE_DIALOG_ID" NUMBER, 
	"TILHORENDE_BRUKERNOTIFIKASJON_ID" NUMBER, 
        ...
   CREATE UNIQUE INDEX "VEILARBDIALOG"."SYS_C0020090" ON "VEILARBDIALOG"."ESKALERINGSVARSEL" ("ID") ...
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" MODIFY ("AKTOR_ID" NOT NULL ENABLE);
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" MODIFY ("OPPRETTET_AV" NOT NULL ENABLE);
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" MODIFY ("OPPRETTET_DATO" NOT NULL ENABLE);
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" MODIFY ("TILHORENDE_DIALOG_ID" NOT NULL ENABLE);
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD PRIMARY KEY ("ID")
     USING INDEX...
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" MODIFY ("ID" NOT NULL ENABLE);
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT "GJELDENDE" CHECK ((AVSLUTTET_DATO IS NULL AND GJELDENDE = AKTOR_ID) OR (AVSLUTTET_DATO IS NOT NULL AND GJELDENDE IS NULL)) ENABLE;
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD UNIQUE ("GJELDENDE") USING INDEX ...

   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT "TILHORENDE_DIALOG_ID_FK" FOREIGN KEY ("TILHORENDE_DIALOG_ID")
	  REFERENCES "VEILARBDIALOG"."DIALOG" ("DIALOG_ID") ENABLE;
   ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT "TILHORENDE_BRUKERNOTIFIKASJON_ID_FK" FOREIGN KEY ("TILHORENDE_BRUKERNOTIFIKASJON_ID")
	  REFERENCES "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ("ID") ENABLE;

Oracle DDL as extracted by IntelliJ:

create table ESKALERINGSVARSEL
(
    ID                               NUMBER generated as identity
        primary key,
    AKTOR_ID                         NVARCHAR2(255) not null,
    OPPRETTET_AV                     NVARCHAR2(255) not null,
    OPPRETTET_DATO                   TIMESTAMP(6)   not null,
    TILHORENDE_DIALOG_ID             NUMBER         not null
        constraint TILHORENDE_DIALOG_ID_FK
            references DIALOG,
    TILHORENDE_BRUKERNOTIFIKASJON_ID NUMBER
        constraint TILHORENDE_BRUKERNOTIFIKASJON_ID_FK
            references BRUKERNOTIFIKASJON,
    OPPRETTET_BEGRUNNELSE            CLOB,
    AVSLUTTET_DATO                   TIMESTAMP(6),
    AVSLUTTET_AV                     NVARCHAR2(255),
    AVSLUTTET_BEGRUNNELSE            CLOB,
    GJELDENDE                        NVARCHAR2(255)
        unique,
    constraint GJELDENDE
        check ((AVSLUTTET_DATO IS NULL AND GJELDENDE = AKTOR_ID) OR (AVSLUTTET_DATO IS NOT NULL AND GJELDENDE IS NULL))
)

Oracle DDL as interpreted by DMS WS:

CREATE SEQUENCE "VEILARBDIALOG"."ISEQSS_130634" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 START WITH 311040 CACHE 20;
CREATE TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL"
(
  "ID" NUMBER DEFAULT "VEILARBDIALOG"."ISEQSS_130634".nextval NOT NULL,
  "AKTOR_ID" NVARCHAR2(255) NOT NULL,
  "OPPRETTET_AV" NVARCHAR2(255) NOT NULL,
  "OPPRETTET_DATO" TIMESTAMP (6) NOT NULL,
  "TILHORENDE_DIALOG_ID" NUMBER NOT NULL,
  "TILHORENDE_BRUKERNOTIFIKASJON_ID" NUMBER,
  "OPPRETTET_BEGRUNNELSE" CLOB,
  "AVSLUTTET_DATO" TIMESTAMP (6),
  "AVSLUTTET_AV" NVARCHAR2(255),
  "AVSLUTTET_BEGRUNNELSE" CLOB,
  "GJELDENDE" NVARCHAR2(255)
);
ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT GJELDENDE CHECK ((AVSLUTTET_DATO IS NULL AND GJELDENDE = AKTOR_ID) OR (AVSLUTTET_DATO IS NOT NULL AND GJELDENDE IS NULL)) ENABLE;
ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT "SYS_C0020090" PRIMARY KEY ("ID") ENABLE;
ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT SYS_C0020469 UNIQUE ("GJELDENDE") ENABLE;
ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT TILHORENDE_BRUKERNOTIFIKASJON_ID_FK FOREIGN KEY ("TILHORENDE_BRUKERNOTIFIKASJON_ID") REFERENCES "VEILARBDIALOG"."BRUKERNOTIFIKASJON" ("ID") ENABLE;
ALTER TABLE "VEILARBDIALOG"."ESKALERINGSVARSEL" ADD CONSTRAINT TILHORENDE_DIALOG_ID_FK FOREIGN KEY ("TILHORENDE_DIALOG_ID") REFERENCES "VEILARBDIALOG"."DIALOG" ("DIALOG_ID") ENABLE;
CREATE UNIQUE INDEX "SYS_C0020090" ON "VEILARBDIALOG"."ESKALERINGSVARSEL" ("ID");
CREATE UNIQUE INDEX "SYS_C0020469" ON "VEILARBDIALOG"."ESKALERINGSVARSEL" ("GJELDENDE");

Postgres DDL as generated by DMS WS:

CREATE SEQUENCE veilarbdialog.iseqSS_130634 START WITH 311040 INCREMENT BY 1 CACHE 20 MINVALUE 1 MAXVALUE 9223372036854775807;
DROP TABLE IF EXISTS "veilarbdialog"."eskaleringsvarsel";
CREATE TABLE veilarbdialog.eskaleringsvarsel (
  id DECIMAL NOT NULL DEFAULT nextval('veilarbdialog.iseqSS_130634'),
  aktor_id VARCHAR(255) NOT NULL,
  opprettet_av VARCHAR(255) NOT NULL,
  opprettet_dato TIMESTAMP(6) NOT NULL,
  tilhorende_dialog_id DECIMAL NOT NULL,
  tilhorende_brukernotifikasjon_id DECIMAL,
  opprettet_begrunnelse TEXT,
  avsluttet_dato TIMESTAMP(6),
  avsluttet_av VARCHAR(255),
  avsluttet_begrunnelse TEXT,
  gjeldende VARCHAR(255)
);
ALTER TABLE veilarbdialog.eskaleringsvarsel ADD CONSTRAINT gjeldende CHECK (eskaleringsvarsel.avsluttet_dato IS NULL
 AND eskaleringsvarsel.gjeldende = eskaleringsvarsel.aktor_id
 OR eskaleringsvarsel.avsluttet_dato IS NOT NULL
 AND eskaleringsvarsel.gjeldende IS NULL);
ALTER TABLE veilarbdialog.eskaleringsvarsel ADD CONSTRAINT sys_c0020090 PRIMARY KEY (id);
ALTER TABLE veilarbdialog.eskaleringsvarsel ADD CONSTRAINT sys_c0020469 UNIQUE (gjeldende);
ALTER TABLE veilarbdialog.eskaleringsvarsel ADD CONSTRAINT tilhorende_brukernotifikasjon_id_fk FOREIGN KEY (tilhorende_brukernotifikasjon_id) REFERENCES veilarbdialog.brukernotifikasjon (id);
ALTER TABLE veilarbdialog.eskaleringsvarsel ADD CONSTRAINT tilhorende_dialog_id_fk FOREIGN KEY (tilhorende_dialog_id) REFERENCES veilarbdialog.dialog (dialog_id);

Note that the foreign key DDLs were not applied on the target, due to incompatible types, so these were applied later with the DDL

alter table eskaleringsvarsel
    alter column TILHORENDE_BRUKERNOTIFIKASJON_ID type bigint using TILHORENDE_BRUKERNOTIFIKASJON_ID::bigint;

alter table eskaleringsvarsel
    add constraint TILHORENDE_BRUKERNOTIFIKASJON_ID_FK
        foreign key (TILHORENDE_BRUKERNOTIFIKASJON_ID) references brukernotifikasjon(id);

alter table eskaleringsvarsel
    alter column tilhorende_dialog_id type bigint using tilhorende_dialog_id::bigint;

alter table eskaleringsvarsel
    add constraint TILHORENDE_DIALOG_ID_FK
        foreign key (TILHORENDE_DIALOG_ID) references dialog(dialog_id);

Double dollar signs in generated index names where change to 'SS' in the ddl.

[hanspetter]

By the way, this is the scope of our migration.

The main tables containing most of the data are dialog and henvendelse, and also the event table is an append-only table which is rather large.

The only table experiencing problems are the three mentioned. Curiously enough, our last migration of another database experienced these problems with exactly three tables as well.

[hanspetter]

Finally this is the declarations for the EKSTERN_VARSEL_KVITTERING table

Original Oracle DDL run by flyway:

CREATE TABLE EKSTERN_VARSEL_KVITTERING
(
    TIDSPUNKT TIMESTAMP not null,
    BRUKERNOTIFIKASJON_BESTILLING_ID varchar2(255) not null,
    DOKNOTIFIKASJON_STATUS varchar2(255) not null,
    MELDING varchar2(255) not null,
    DISTRIBUSJON_ID number,
    JSON_PAYLOAD clob not null
);
ALTER TABLE EKSTERN_VARSEL_KVITTERING
MODIFY MELDING varchar2(1024);

Oracle DDL as extracted by SqlDeveloper:

  CREATE TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" 
   (	"TIDSPUNKT" TIMESTAMP (6), 
	"BRUKERNOTIFIKASJON_BESTILLING_ID" VARCHAR2(255 BYTE), 
	"DOKNOTIFIKASJON_STATUS" VARCHAR2(255 BYTE), 
	"MELDING" VARCHAR2(1024 BYTE), 
	"DISTRIBUSJON_ID" NUMBER, 
	"JSON_PAYLOAD" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "VEILARBDIALOG" 
 LOB ("JSON_PAYLOAD") STORE AS SECUREFILE (
  TABLESPACE "VEILARBDIALOG" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES 
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
--------------------------------------------------------
--  Constraints for Table EKSTERN_VARSEL_KVITTERING
--------------------------------------------------------

  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" MODIFY ("BRUKERNOTIFIKASJON_BESTILLING_ID" NOT NULL ENABLE);
  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" MODIFY ("DOKNOTIFIKASJON_STATUS" NOT NULL ENABLE);
  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" MODIFY ("MELDING" NOT NULL ENABLE);
  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" MODIFY ("JSON_PAYLOAD" NOT NULL ENABLE);
  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" MODIFY ("TIDSPUNKT" NOT NULL ENABLE);
  ALTER TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Oracle DDL as extracted by IntelliJ:

create table EKSTERN_VARSEL_KVITTERING
(
    TIDSPUNKT                        TIMESTAMP(6)   not null,
    BRUKERNOTIFIKASJON_BESTILLING_ID VARCHAR2(255)  not null,
    DOKNOTIFIKASJON_STATUS           VARCHAR2(255)  not null,
    MELDING                          VARCHAR2(1024) not null,
    DISTRIBUSJON_ID                  NUMBER,
    JSON_PAYLOAD                     CLOB           not null
)

Oracle DDL as interpreted by DMS WS:

CREATE TABLE "VEILARBDIALOG"."EKSTERN_VARSEL_KVITTERING"
(
  "TIDSPUNKT" TIMESTAMP (6) NOT NULL,
  "BRUKERNOTIFIKASJON_BESTILLING_ID" VARCHAR2(255 BYTE) NOT NULL,
  "DOKNOTIFIKASJON_STATUS" VARCHAR2(255 BYTE) NOT NULL,
  "MELDING" VARCHAR2(1024 BYTE) NOT NULL,
  "DISTRIBUSJON_ID" NUMBER,
  "JSON_PAYLOAD" CLOB NOT NULL
);

Postgres DDL as generated by DMS WS:

DROP TABLE IF EXISTS "veilarbdialog"."ekstern_varsel_kvittering";
CREATE TABLE veilarbdialog.ekstern_varsel_kvittering (
tidspunkt TIMESTAMP(6) NOT NULL,
brukernotifikasjon_bestilling_id VARCHAR(255) NOT NULL,
doknotifikasjon_status VARCHAR(255) NOT NULL,
melding VARCHAR(1024) NOT NULL,
distribusjon_id DECIMAL,
json_payload TEXT NOT NULL
);
ALTER TABLE "veilarbdialog"."ekstern_varsel_kvittering" ADD COLUMN rowid numeric(33,0) NOT NULL;
CREATE SEQUENCE "veilarbdialog"."ekstern_varsel_kvittering_rowid_seq" INCREMENT BY -1 START WITH -1 OWNED BY "veilarbdialog"."ekstern_varsel_kvittering".rowid;
ALTER TABLE "veilarbdialog"."ekstern_varsel_kvittering" ALTER COLUMN rowid SET DEFAULT nextval('"veilarbdialog"."ekstern_varsel_kvittering_rowid_seq"');

I note now that the json_payload CLOB column is declared as not null, which will produce errors in the migration job once the changes are applied, since the club values are migrated separately. We will alter this column to be nullable, and see if this affects the CDC processing.

Update: This did not affect the replication. CDC changes are still not picked up for EKSTERN_VARSEL_KVITTERING after the initial load.

[somdyuti]

Hi,

The DDLs look fine.  Ideally there should not be any delay between the changes being reported applied in the DMS Migration logs and the rows being visible in the target Cloud SQL PostgreSQL database.

I tried to reproduce the issue at my end.

In DMS Migration job logs

NOTICE 2024-08-16T10:31:03.241Z Successfully applied 2 CDC rows into table: SOM_TABAUG15

occurrenceTimestamp: "2024-08-16T10:31:03.241594420Z"

textMessage: "Successfully applied 2 CDC rows into table: SOM_TABAUG15"

In Cloud SQL for PostgreSQL log:-

textPayload: "2024-08-16 10:31:03.232 UTC [662288]: [25-1] db=oratest,user=oratest LOG: statement: INSERT INTO "som"."tabaug15"("id","name") SELECT "id","name" FROM "tabaug15_tmp" ORDER BY "id" ON CONFLICT ("id") DO UPDATE SET ("id","name")=ROW(EXCLUDED."id",EXCLUDED."name")"

timestamp: "2024-08-16T10:31:03.232679Z"

To diagnose further please do the following:-

1. Set log_statement=mod  in the target Cloud SQL PostgreSQL Instance.

2. Do some DMLs in the source Oracle database on the table 

veilarbdialog.brukernotifikasjon

3. Check the DMS Migration logs and also the PostgreSQL Log (not the replication-setup.log).

To filter the PostgreSQL Log for DMLs applied to the table you can use filter using textPayload:-

resource.type="cloudsql_database"

log_name="projects/<project_id>/logs/cloudsql.googleapis.com%2Fpostgres.log"

resource.labels.database_id="<project_id>:<db_id>"

textPayload:brukernotifikasjon

Note:- Replace the <project_id> and <db_id> with your project id and database id

[hanspetter]

The changes are being applied immediately now to the brukernotifikasjon table, so this seems to have stabilized. We are unsure why this suddenly started working after several days. We will keep an eye on it, now that we have instance logging enabled as well.

However the problem with missing CDC from the other two tables eskaleringsvarsel and ekstern_varsel_kvittering remains. 

[somdyuti]

Hi,

 For the table that has a NOT NULL CLOB, the CDC is missing because the CLOB values are migrated separately.

You will see these errors in PostgreSQL log for the Inserts by DMS:-

severity: "ERROR"

textPayload: "2024-08-16 14:31:10.167 UTC [667934]: [56-1] db=oratest,user=oratest ERROR:  null value in column "c" of relation "testclob" violates not-null constraint"

The DMS job keeps on trying and you may also see success like below

—DMS Migration job log—

NOTICE 2024-08-16T15:01:07.314Z Successfully applied 1 CDC rows into table: SOM_TESTCLOB

NOTICE 2024-08-16T14:32:07.230Z Successfully applied 1 CDC rows into table: SOM_TESTCLOB

NOTICE 2024-08-16T14:20:03.511Z Successfully loaded 1 FULL DUMP rows into table:SOM_TESTCLOB

As a workaround, make these CLOB columns as NULLable.