Forum Stats

  • 3,851,977 Users
  • 2,264,055 Discussions
  • 7,904,923 Comments

Discussions

Message: Please provide the full image for insertion as PKUpdateHandlingMode is set as DELETE and In

Robeen
Robeen Member Posts: 2,215 Silver Badge

OGG 18.1.0

DB 12.1.0.2

Dear Team,

one my CDC application which reads goldengate trail files, I am getting error

Message: Please provide the full image for insertion as PKUpdateHandlingMode is set as DELETE and Insert

I added

LOGALLSUPCOLS

UPDATERECORDFORMAT FULL

in extract parameter file and restarted. Also, I added trandata allcols on the tables. However I am still getting error. Is it because of the supplemental log group which is present in the table?


 CREATE TABLE "CBS_CORE"."CB_SUBS_INFORMATION_MASTER" 
  (	"ACCOUNT_LINK_CODE_N" NUMBER(10,0) NOT NULL ENABLE, 
	"ACCOUNT_CODE_N" NUMBER(10,0) DEFAULT NULL, 
	"SERVICE_CODE_V" VARCHAR2(3 BYTE) DEFAULT NULL, 
	"PRIVILEGE_CARD_NUMBER_N" NUMBER(10,0) DEFAULT NULL, 
	"VENDOR_CODE_V" VARCHAR2(8 BYTE) DEFAULT NULL, 
	"SALES_REGION_N" NUMBER(10,0) DEFAULT NULL, 
	"DEPOSIT_AMT_N" NUMBER(14,0) DEFAULT NULL, 
	"ADV_SERVICE_CHRG_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"CR_LIMIT_N" NUMBER(14,0) DEFAULT 0, 
	"LAST_FIXED_CALC_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"LAST_PRORATA_CALC_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"CREDIT_RISK_CATEGORY_V" VARCHAR2(5 BYTE) DEFAULT NULL, 
	"CREDIT_RATING_N" NUMBER(2,0) DEFAULT NULL, 
	"CREDIT_CONTROL_SCHEDULE_V" VARCHAR2(7 BYTE) DEFAULT NULL, 
	"MESG_MEDIA_OPTN_V" VARCHAR2(1 BYTE) DEFAULT NULL, 
	"ACTION_LEVEL_N" NUMBER(3,0) DEFAULT NULL, 
	"ACTION_BY_V" VARCHAR2(1 BYTE) DEFAULT NULL, 
	"NEXT_SUMRY_SEQ_NUM_N" NUMBER(8,0) DEFAULT 1, 
	"LAST_BILL_CALC_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"UNBIL_PRORATA_WITH_TAX_N" NUMBER(14,0) DEFAULT 0, 
	"UNBIL_FIXED_WITH_TAX_N" NUMBER(14,0) DEFAULT 0, 
	"UNBIL_USAGE_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"UNBIL_USAGE_TAX_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"PRE_INV_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"PRE_INV_TAX_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"TRNF_PRE_INV_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"TRNF_PRE_INV_TAX_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"LINKED_PRE_INV_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"LINKED_PRE_INV_AMT_TAX_N" NUMBER(14,0) DEFAULT 0, 
	"TRNF_INV_WITH_TAX_N" NUMBER(14,0) DEFAULT 0, 
	"LINKED_INV_WITH_TAX_N" NUMBER(14,0) DEFAULT 0, 
	"DISPUTE_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_1_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_2_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_3_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_4_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"LAST_PAYMENT_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"LAST_AGEING_PROCS_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"LAST_INVOICE_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"LAST_INVOICE_NUM_V" VARCHAR2(20 BYTE) DEFAULT NULL, 
	"LAST_INVOICE_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"AMT_CLR_IN_LAST_INV_N" NUMBER(14,0) DEFAULT NULL, 
	"LAST_INV_DUE_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"TO_BILL_SEQ_NUM_N" NUMBER(8,0) DEFAULT 1, 
	"NEXT_BILL_CYCL_FULL_CODE_N" NUMBER(16,0) DEFAULT NULL, 
	"ADDNL_CREDIT_LIMIT_N" NUMBER(14,0) DEFAULT 0, 
	"LAST_PAYMENT_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_5_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"BUCKET_6_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"APP_MODE_V" VARCHAR2(5 BYTE), 
	"LOCATION_CODE_V" VARCHAR2(10 BYTE), 
	"SERVICE_LINE_SEQUENCE_N" NUMBER(10,0), 
	"INITIAL_CHARGE_CODE_V" VARCHAR2(7 BYTE), 
	"EXCL_FRM_CREDIT_CNTRL" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"EXCL_CRDT_TILL_DATE_DT" TIMESTAMP (6) WITH TIME ZONE, 
	"EXCL_FRM_DUNNING" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"EXCL_DUNNING_TILL_DATE_DT" TIMESTAMP (6) WITH TIME ZONE, 
	"MAX_AMT_FLAG_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"SUSPENSION_SCHEME_N" NUMBER(6,0), 
	"LAST_EARNED_REV_CALC_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT NULL, 
	"LAST_INTRST_CALC_DATE_D" TIMESTAMP (6) WITH TIME ZONE, 
	"INSTALLMENT_FLAG_V" VARCHAR2(1 BYTE), 
	"SECURED_CREDIT_N" NUMBER(14,0), 
	"UNSECURED_CREDIT_N" NUMBER(14,0), 
	"NOTIFICATION_EMAIL_ID_V" VARCHAR2(100 BYTE), 
	"LAST_CALL_DT_D" TIMESTAMP (6) WITH TIME ZONE, 
	"NEXT_TRIGGER_THRESHOLD_N" NUMBER(14,0), 
	"BILLED_BALANCE_N" NUMBER(14,0), 
	"CLEARED_UNBILLED_AMT_N" NUMBER(14,0), 
	"AUCTION_CREDIT_LIMIT_N" NUMBER(14,0) DEFAULT 0, 
	"EXCL_CRDT_TILL_AMT_N" NUMBER(14,0), 
	"EXCL_DUNNING_TILL_AMT_N" NUMBER(14,0), 
	"NOTIFICATION_SMS_NUMBER_V" VARCHAR2(200 BYTE), 
	"CR_CTRL_APPLICABLE_FLG_V" VARCHAR2(1 BYTE), 
	"MESG_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"MESG_CODE_N" NUMBER(3,0), 
	"TMP_UNBILLED_AMT_N" NUMBER(14,0), 
	"CR_LIMIT_TILL_DATE_D" TIMESTAMP (6) WITH TIME ZONE, 
	"SEND_NOTIFICATION_MAIL_FLG_V" VARCHAR2(1 BYTE), 
	"SEND_NOTIFICATION_SMS_FLG_V" VARCHAR2(1 BYTE), 
	"PREF_COMM_LANG_CODE_V" VARCHAR2(10 BYTE), 
	"PENDING_ORD_CNT_N" NUMBER(14,0), 
	"CANCELED_ORD_CNT_N" NUMBER(14,0), 
	"REJECTED_ORD_CNT_N" NUMBER(14,0), 
	"EXECUTED_ORD_CNT_N" NUMBER(14,0), 
	"SALES_OUTSTANDING_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"MUL_APPLICABLE_FLG_V" VARCHAR2(1 BYTE), 
	"MMC_ON_ACTIVATION" VARCHAR2(1 BYTE), 
	"MMC_ON_TERMINATION" VARCHAR2(1 BYTE), 
	"INVOICE_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"NEXT_FACILITY_REF_ID_V" VARCHAR2(10 BYTE), 
	"SECRET_CODE_FLG_N" NUMBER(1,0) DEFAULT 0, 
	"SECRET_CODE_V" VARCHAR2(8 BYTE), 
	"RATING_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'Y', 
	"PREPAID_CR_LIMIT_N" NUMBER(14,0) DEFAULT 0, 
	"DB_CURR_BAL_FOR_PREPAID_N" NUMBER(14,0) DEFAULT 0, 
	"LOC_UNBIL_USAGE_AMT_N" NUMBER(14,0) DEFAULT 0, 
	"SUBS_TYPE_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"CONTRACT_PERIOD_N" NUMBER(4,0), 
	"CALLER_ID_NUM_V" VARCHAR2(16 BYTE), 
	"CREDIT_LIMIT_FLAG_V" VARCHAR2(1 BYTE) DEFAULT 'I', 
	"BASIC_UNIT_AREA_DIST_N" NUMBER(10,2), 
	"CONTRACT_PERIOD_TYPE_V" VARCHAR2(1 BYTE), 
	"CUST_PREMISES_DIST_N" NUMBER(10,0), 
	"DIST_IN_BASIC_UNIT_AREA_N" NUMBER(10,2), 
	"LYT_CONTACT_NUMBER_V" VARCHAR2(20 BYTE), 
	"LYT_LANGUAGE_CODE_V" VARCHAR2(10 BYTE), 
	"LYT_NOTIFICATION_EMAIL_ID_V" VARCHAR2(200 BYTE), 
	"LYT_NOTIFICATION_FLG_V" VARCHAR2(1 BYTE), 
	"OBASA_SCHEME_REF_CODE_N" NUMBER(6,0), 
	"LYT_NOTIFICATION_APP_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"LAST_MODIFIED_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 
	"MIG_INITIAL_SECURE_DEPOSIT_N" NUMBER(14,0), 
	"SPECIFIC_CRD_LMT_PER_FLG_V" VARCHAR2(1 BYTE), 
	"SPECIFIC_CRD_LMT_PER_N" NUMBER(5,2) DEFAULT 0, 
	"SUBSCRIBED_ADVTPBP_FLAG_V" VARCHAR2(1 BYTE) DEFAULT 'N', 
	"DEF_SEG_CODE_V" VARCHAR2(20 BYTE), 
	"DYN_SEG_CODE_V" VARCHAR2(20 BYTE), 
	 CONSTRAINT "CB_SUBS_INFO#MESG_OPTN$CK" CHECK (MESG_MEDIA_OPTN_V IN ('Y','N')) ENABLE, 
	 CONSTRAINT "CB_SUBS_INFO#ACTION_BY$CK" CHECK (ACTION_BY_V IN ('D','C','I', 'Z')) ENABLE, 
	 PRIMARY KEY ("ACCOUNT_LINK_CODE_N")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
 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 "CBS" ENABLE, 
	 SUPPLEMENTAL LOG GROUP "GGS_129091" ("ACCOUNT_LINK_CODE_N") ALWAYS, 
	 SUPPLEMENTAL LOG DATA (ALL) COLUMNS, 
	 SUPPLEMENTAL LOG GROUP "GGS_144879" ("ACCOUNT_LINK_CODE_N") ALWAYS, 
	 SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, 
	 SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS, 
	 SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
  ) SEGMENT CREATION IMMEDIATE 
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
 STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "CBS" ;

Kindly advise if I drop them?


Regards,


Roshan

Best Answer

  • ORASCN
    ORASCN Member Posts: 1,874 Bronze Crown
    Answer ✓

    Hi Roshan,

    The record which is already captured and written in the trail file cannot be modified. Either you need to re-extract the data using the Extract process or resynchronize the table.

    Regards,

    Veera

Answers

  • ORASCN
    ORASCN Member Posts: 1,874 Bronze Crown
    Answer ✓

    Hi Roshan,

    The record which is already captured and written in the trail file cannot be modified. Either you need to re-extract the data using the Extract process or resynchronize the table.

    Regards,

    Veera

  • Robeen
    Robeen Member Posts: 2,215 Silver Badge

    Hi Veera,

    if supplemental log (ALL) columns is already enabled in the table, will this be enough to overcome the PKHandling error?

    What do you mean by re-extract the table?

    Regards,

    Roshan

  • ORASCN
    ORASCN Member Posts: 1,874 Bronze Crown

    I added

    LOGALLSUPCOLS

    UPDATERECORDFORMAT FULL

    in extract parameter file and restarted. Also, I added trandata allcols on the tables. However I am still getting error. Is it because of the supplemental log group which is present in the table?


    ---- Below is the my reponse..


    UPDATERECORDFORMAT FULL - this is the default one.

    FULL

    Generates one trail record that contains the before and after images of an UPDATE, where the before image includes all of the columns that are available in the transaction record for both the before and after images. This is the default. When viewed in the Logdump utility, this record appears as GGSUnifiedUpdate.


    LOGALLSUPCOLS - LOGALLSUPCOLS causes Extract to do the following with these supplementally logged columns:

    • Automatically includes in the trail record the before image for UPDATE operations.
    • Automatically includes in the trail record the before image of all supplementally logged columns for both UPDATE and DELETE operations.

    In your scenario, the record has been already captured and written to the trail file. So, even though if you the parameters later, only the incoming or new DML operations will be written correctly. The already written one, cannot be or will not get changed.


    Regards,

    Veera

    Robeen