Forum Stats

  • 3,752,284 Users
  • 2,250,483 Discussions
  • 7,867,783 Comments

Discussions

rowid as primary key

Robeen
Robeen Member Posts: 2,066 Silver Badge
edited Jul 24, 2021 10:37AM in SQL & PL/SQL

Oracle DB 12.1.0.2

Hello Team,


there are a few tables on Oracle which do not have primary keys. On Oracle we can create a table with no primary key. I am using a CDC tool (Striim) to replicate data from these tables to Kudu. There is a feature called GGGTrail which will read changes from Golden Gate trail files and apply to target table. On Kudu, it is crucial to set the primary key for these tables.

I am planning to use the rowid as primary key on Kudu for these tables.. the CDC app will read metadata (ROWID) for each record and apply them on target. In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid


Do you think I can proceed with rowid as primary key on target?


Thanks,


Roshan

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,925 Red Diamond
    Accepted Answer

    Hi, @Robeen

     In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid

    That's exactly why you need a primary key. Imagine you move a table to a different tablespace, so all rows will have new ROWIDs.

    Create a real primary key.

    Robeen

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,925 Red Diamond
    Accepted Answer

    Hi, @Robeen

     In case the rowid changes on Oracle (due to deletes, flashback etc..), CDC app will delete and insert record with new rowid

    That's exactly why you need a primary key. Imagine you move a table to a different tablespace, so all rows will have new ROWIDs.

    Create a real primary key.

    Robeen
  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    If rowid cannot be used, is there a way how I can add a unique column (hidden)? I cannot alter the table since it might affect golden gate replication on source.


  • Paulzip
    Paulzip Member Posts: 8,422 Blue Diamond
    edited Jul 24, 2021 11:37AM

    is there a way how I can add a unique column (hidden)? 

    Yes you can, but whether or not Kudu and Golden Gate can handle that is another matter.

    -- Create demo table, no PK
    create table T_TABLE_TEST as
      select dbms_random.value(0, 100) SOME_VAL
           , dbms_random.string('x', trunc(dbms_random.value(0, 30))) SOME_STR
      from dual
      connect by level <= 5
    /
    
    Table created.
    
    select *
    from   T_TABLE_TEST
    /
    
      SOME_VAL|SOME_STR
    ----------|------------------------------
     6.8254594|6XO91A80Z3BVVZVHWG4PQ
    16.7237219|AMCJKGVRKFS57UON3VX2R2
    61.3746587|VPWQORO07P5KJ0MSO3
    57.8522938|BOCSYW1E1XQFCUJY9H8JV
    12.3681307|KXDKL25CNX25W
    
    -- Add an identity column, which will be our PK
    alter table T_TABLE_TEST add UNIQUE_ID integer
      generated by default on null as identity not null
    /
    
    Table altered.
    
    -- Make it a PK
    alter table T_TABLE_TEST add constraint PK_T_TABLE_TEST primary key (UNIQUE_ID)
    /
    
    Table altered.
    
    select *
    from   T_TABLE_TEST
    /
    
      SOME_VAL|SOME_STR                      | UNIQUE_ID
    ----------|------------------------------|----------
     6.8254594|6XO91A80Z3BVVZVHWG4PQ         |         1
    16.7237219|AMCJKGVRKFS57UON3VX2R2        |         2
    61.3746587|VPWQORO07P5KJ0MSO3            |         3
    57.8522938|BOCSYW1E1XQFCUJY9H8JV         |         4
    12.3681307|KXDKL25CNX25W                 |         5
    
    -- Make the column invisible
    alter table T_TABLE_TEST modify UNIQUE_ID invisible
    /
    
    Table altered.
    
    
    select *
    from   T_TABLE_TEST
    /
    
    
      SOME_VAL|SOME_STR
    ----------|------------------------------
     6.8254594|6XO91A80Z3BVVZVHWG4PQ
    16.7237219|AMCJKGVRKFS57UON3VX2R2
    61.3746587|VPWQORO07P5KJ0MSO3
    57.8522938|BOCSYW1E1XQFCUJY9H8JV
    12.3681307|KXDKL25CNX25W
    
    
    exit
    
    
    
    Robeen
  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    Thanks for the update.

    Unfortunately we cannot modify the source tables as application will stop running on production. Do you think I can carry on with the CDC replication with rowid as PK on Kudu? If rowids are changed on source Oracle, there will be delete and insert operation.

  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    I also noticed some records with rowid exist on target but not on Oracle


    Error on CDC:

    AdapterException | TGT_KUDU_Insert_CB_ORDERS

    Error while writing batch on table impala::cbs.cb_subs_pos_services Reason Couldnot persist row : (string servid="AAAjKWAGZAACVsiAA/") : Row error for primary key="AAAjKWAGZAACVsiAA/", tablet=null, server=5a392d04a3924313b38b311cf514afab, status=Not found: key not found (error 0). Cause: Couldnot persist row : (string servid="AAAjKWAGZAACVsiAA/") : Row error for primary key="AAAjKWAGZAACVsiAA/", tablet=null, server=5a392d04a3924313b38b311cf514afab, status=Not found: key not found (error 0)



    Kudu entry for table (cb_subs_pos_services) is shown


    servid

    AAAjKWAGZAACVsiAA/

    account_link_code_n

    24431620

    acc_serv_flag_v

    S

    action_parm_strg_v

    NULL

    application_name_v

    NULL

    authorised_by_n

    NULL

    authorised_date_dt

    NULL

    cancelled_by_n

    NULL

    cancelled_date_dt

    NULL

    cancel_comments_v

    NULL

    cancel_reason_code_v

    NULL

    charges_present_flg_v

    NULL

    chrono_num_n

    NULL

    collected_upfront_v

    N

    comments_v

    NULL

    created_by_n

    2072

    critical_service_flg_v

    N

    execute_immediate_flg_v

    NULL

    from_self_care_flg_v

    N

    last_modified_date_d

    2021-07-24 15:27:05.382000000

    location_code_v

    DD_CV

    login_name_v

    NULL

    margin_amount_n

    0

    move_to_schdl_flg_v

    NULL

    order_amount_n

    239435

    order_ref_no

    26974758

    order_type_v

    O

    print_work_order_indicators_v

    0:0:0

    priority_flg_n

    0

    processed_date_dt

    2021-07-20 12:28:09.702000000

    reason_code_v

    NULL

    receipt_ref_num_v

    NULL

    refunded_date_dt

    NULL

    rejected_reason_v

    NULL

    requested_date_dt

    2021-07-20 12:27:47.428000000

    schdl_link_code_n

    10749976

    scheme_ref_code_n

    NULL

    serial_num_n

    1

    service_code_v

    FXL

    service_key_code_v

    INST

    serv_acc_link_code_n

    24431621

    status_optn_v

    PS

    trans_date_dt

    2021-07-20 12:27:47.346000000

    trans_num_v

    NULL

    op_insert_date

    NULL

    op_update_date

    2021-07-24 15:27:04.784000000

    Oracle entry:


    Table DDL on Kudu:

    CREATE EXTERNAL TABLE cbs.cb_subs_pos_services (  servid STRING NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  account_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  acc_serv_flag_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  action_parm_strg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  application_name_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  authorised_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  authorised_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  cancelled_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  cancelled_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  cancel_comments_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  cancel_reason_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  charges_present_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  chrono_num_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  collected_upfront_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  comments_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  created_by_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  critical_service_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  execute_immediate_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  from_self_care_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  last_modified_date_d TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  location_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  login_name_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  margin_amount_n INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  move_to_schdl_flg_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  order_amount_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  order_ref_no STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  order_type_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  print_work_order_indicators_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  priority_flg_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  processed_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  reason_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  receipt_ref_num_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  refunded_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  rejected_reason_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  requested_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  schdl_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  scheme_ref_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  serial_num_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  service_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  service_key_code_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  serv_acc_link_code_n BIGINT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  status_optn_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  trans_date_dt TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  trans_num_v STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  op_insert_date TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  op_update_date TIMESTAMP NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,  PRIMARY KEY (servid) ) PARTITION BY HASH (servid) PARTITIONS 16 STORED AS KUDU TBLPROPERTIES ('external.table.purge'='TRUE', 'kudu.master_addresses'='rb-hadoop-03.mtg.local,rb-hadoop-04.mtg.local,rb-hadoop-05.mtg.local')


    Table DDL on Oracle:


     CREATE TABLE "CBSOGG"."CB_SUBS_POS_SERVICES"

       (          "ORDER_REF_NO" VARCHAR2(20 BYTE),

                  "SERIAL_NUM_N" NUMBER(3,0),

                  "ORDER_TYPE_V" VARCHAR2(1 BYTE),

                  "TRANS_NUM_V" VARCHAR2(20 BYTE),

                  "TRANS_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "SERVICE_CODE_V" VARCHAR2(3 BYTE),

                  "ACCOUNT_LINK_CODE_N" NUMBER(10,0),

                  "SERV_ACC_LINK_CODE_N" NUMBER(10,0),

                  "SERVICE_KEY_CODE_V" VARCHAR2(4 BYTE),

                  "SCHEME_REF_CODE_N" NUMBER(6,0),

                  "ACTION_PARM_STRG_V" VARCHAR2(200 BYTE),

                  "PROFIT_CENTRE_CODE" VARCHAR2(6 BYTE),

                  "PRIORITY_FLG_N" NUMBER(2,0) DEFAULT 0,

                  "OFFER_GROUP_CODE_V" VARCHAR2(2 BYTE),

                  "CRITICAL_SERVICE_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N',

                  "CHARGES_PRESENT_FLG_V" VARCHAR2(1 BYTE),

                  "ORDER_AMOUNT_N" NUMBER(14,0),

                  "COLLECTED_UPFRONT_V" VARCHAR2(1 BYTE) DEFAULT 'N',

                  "RECEIPT_REF_NUM_V" VARCHAR2(20 BYTE),

                  "STATUS_OPTN_V" VARCHAR2(2 BYTE),

                  "EXECUTE_IMMEDIATE_FLG_V" VARCHAR2(1 BYTE),

                  "MOVE_TO_SCHDL_FLG_V" VARCHAR2(1 BYTE),

                  "REQUESTED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "PROCESSED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "CREATED_BY_N" NUMBER(10,0),

                  "AUTHORISED_BY_N" NUMBER(10,0),

                  "AUTHORISED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "CANCELLED_BY_N" NUMBER(10,0),

                  "CANCELLED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "REFUNDED_DATE_DT" TIMESTAMP (6) WITH TIME ZONE,

                  "REFUND_TRANS_NO_V" VARCHAR2(20 BYTE),

                  "FROM_SELF_CARE_FLG_V" VARCHAR2(1 BYTE) DEFAULT 'N',

                  "SCHDL_LINK_CODE_N" NUMBER(10,0),

                  "REASON_CODE_V" VARCHAR2(10 BYTE),

                  "COMMENTS_V" VARCHAR2(300 BYTE),

                  "CANCEL_REASON_CODE_V" VARCHAR2(10 BYTE),

                  "CANCEL_COMMENTS_V" VARCHAR2(200 BYTE),

                  "LOCATION_CODE_V" VARCHAR2(10 BYTE),

                  "REJECTED_REASON_V" VARCHAR2(300 BYTE),

                  "LOGIN_NAME_V" VARCHAR2(30 BYTE),

                  "BANK_DETAILS_V" VARCHAR2(1000 BYTE),

                  "APPLICATION_NAME_V" VARCHAR2(20 BYTE),

                  "MARGIN_AMOUNT_N" NUMBER(14,0) DEFAULT 0,

                  "CHRONO_NUM_N" NUMBER(10,0),

                  "PRINT_WORK_ORDER_INDICATORS_V" VARCHAR2(5 BYTE) DEFAULT '0:0:0',

                  "ERROR_DESC_V" VARCHAR2(200 BYTE),

                  "SALE_TRANSACTION_NUMBER_V" VARCHAR2(50 BYTE),

                  "LAST_MODIFIED_DATE_D" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

                  "ACC_SERV_FLAG_V" VARCHAR2(1 BYTE) DEFAULT 'S',

                  "RESUBMIT_COUNT_N" NUMBER(4,0),

                  "WAIVE_OFF_APPROVAL_V" VARCHAR2(2 BYTE),

                   SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS,

                   SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,

                   SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,

                   SUPPLEMENTAL LOG DATA (ALL) 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 "CBS2" ;


     CREATE INDEX "CBSOGG"."CB_SUBSPOS_#MAIN_ACC_LINK$NU" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("ACCOUNT_LINK_CODE_N")

      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 "CBS2" ;


     CREATE INDEX "CBSOGG"."CB_SUBSPOS_#USER_CODE$NU" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("CREATED_BY_N")

      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 "CBS2" ;


     CREATE INDEX "CBSOGG"."CB_SUBSPOS_INDX#ACC_LINK$NU" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("SERV_ACC_LINK_CODE_N", "SERVICE_KEY_CODE_V")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;


     CREATE INDEX "CBSOGG"."CB_SUBSPOS_INDX$5" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("ORDER_REF_NO")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;


     CREATE INDEX "CBSOGG"."SUBS_POS_SERVICES#SCHDL_LINK$3" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("SCHDL_LINK_CODE_N")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;


     CREATE INDEX "CBSOGG"."SUBS_POS_SERVICES#SCHDL_LINK$4" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("ORDER_REF_NO", "SERIAL_NUM_N")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;


     CREATE INDEX "CBSOGG"."SUBS_POS_SERV_IND1" ON "CBSOGG"."CB_SUBS_POS_SERVICES" ("TRANS_NUM_V")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;


     CREATE INDEX "CBSOGG"."TRANS_DATE$1" ON "CBSOGG"."CB_SUBS_POS_SERVICES" (SYS_EXTRACT_UTC("TRANS_DATE_DT"))

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      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_TAB" ;

  • Paulzip
    Paulzip Member Posts: 8,422 Blue Diamond

    Why will the application stop running? Please explain why you think that's the case, because the example I gave is completely transparent and I can't see why any application would stop running because of it.

    Robeen
  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    Thanks for the update.

    I have tested the scripts you provided.. but golden gate cannot handle the invisible column.

    Please see error below

    GGSCI (dware1 as [email protected]) 8> add trandata cbs_core.test


    2021-07-25 08:25:58 WARNING OGG-01896 Table CBS_CORE.TEST has an identity column which is not supported. This table will be ignored by Extract.


    2021-07-25 08:26:06 ERROR  OGG-15129 Could not find definition of primary key for CBS_CORE.TEST. Error: Oracle.

    I have performed the steps below to add the primary key to a table and autofill.

    on source:

    alter table TEST add constraint PK_T_TABLE_TEST primary key (UNIQUE_ID)

    /


    select * from TEST;

    alter table TEST add UNIQUE_ID integer

     generated by default on null as identity not null

    /


    Output:

    I will need to find a way how to map invisible columns on target for golden gate.

    Thanks,


    Roshan

  • Paulzip
    Paulzip Member Posts: 8,422 Blue Diamond

    OGG-01896 Oracle Identity Column is used by table.

    Reason: Oracle Identity Column is used by table. Integrated capture is not possible on prior to 18c databases.

    Solution: Use either one of the below

    1. Remove Oracle Identity Column from tables and use sequence instead.

    2. Upgrade the database to 18c


    GoldenGate 12.2 supports INVISIBLE columns

    Oracle Goldengate 12.2 now provides support for replication of tables with INVISIBLE columns which was not possible in earlier releases.

    Which seems to suggest you are on an old version of Oracle. Yet another reason to keep your DBs up to date!

    Robeen
  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    Thanks for the update.

    I think I will keep the DB version on 12.1.0.2 because it will take time to upgrade. OGG version is on 18.1.0.0.1

    Kindly advise how I modify the identity to sequence?
    create table test(
    "A" NUMBER(2),
    "B" NUMBER(2),
    "NAME" VARCHAR2(10 BYTE),
    "ADDRESS" VARCHAR2(20 BYTE));
    
    insert into test values(10,10,'new','new');
    alter system switch logfile;
    select * from test;
    alter table TEST add UNIQUE_ID integer
    generated by default on null as identity not null
    /
    alter table TEST add constraint PK_T_TABLE_TEST primary key (UNIQUE_ID)
    /
    alter table TEST modify UNIQUE_ID invisible
    


    Thanks,


    Roshan

  • Robeen
    Robeen Member Posts: 2,066 Silver Badge

    Hi,

    I created the sequence.. but now I am getting a different error

    GGSCI (dware1 as ext[email protected]) 39> add trandata cbs_core.test,cols(user_id) nokey
    
    2021-07-25 15:35:28 ERROR  OGG-15129 Could not find definition of primary key for CBS_CORE.TEST. Error: Oracle.
    
    GGSCI (dware1 as [email protected]) 40> info all
    


    Kindly advise.

    Thanks