1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Log mode CDC error: ORA-31514

Discussion in 'SQL PL/SQL' started by achouhan, May 15, 2014.

  1. achouhan

    achouhan Guest

    Hi All,

    I am trying to do Redo Log mode CDC in Oracle 11g Enterprise Edition on Windows Environment. I am using Talend ETL Tool for implementing CDC. All the tables and PL/SQL code are generated automatically by the Talend ETL tool for implementing CDC. All the create table commands and PL/SQL code are executed successfully in Oracle. But at the end when I try to capture the changes it throws the following error:
    java.sql.SQLException: ORA-31514: change set disabled due to capture error
    ORA-06512: at "SYS.DBMS_CDC_SUBSCRIBE"

    I am new to Oracle. I tried searching a lot to find resolution of this error but I am unable to resolve it.
    Can anyone help me identify the exact problem and how can I resolve this error?

    The commands for creating table and the PL/SQL code which is generated in Talend ETL Tool is pasted below:
    CREATE TABLE PUBLISHER.TSUBSCRIBERS
    (
    TALEND_CDC_TABLE_TO_WATCH VARCHAR2(35) NOT NULL,
    TALEND_CDC_TABLE_ID VARCHAR2(35) ,
    TALEND_CDC_SUBSCRIBER_NAME VARCHAR2(50),
    TALEND_CDC_CREATION_DATE DATE,
    CONSTRAINT PK_TSUBSCRIBERS PRIMARY KEY (TALEND_CDC_TABLE_TO_WATCH)
    );

    alter database force logging;

    alter database add supplemental log data

    alter user SYSTEM quota unlimited on users

    ALTER TABLE PUBLISHER.cdc_source
    ADD CONSTRAINT pk_cdc PRIMARY KEY (id);

    INSERT INTO PUBLISHER.TSUBSCRIBERS
    (
    TALEND_CDC_TABLE_TO_WATCH,
    TALEND_CDC_TABLE_ID,
    TALEND_CDC_SUBSCRIBER_NAME,
    TALEND_CDC_CREATION_DATE
    )
    values ('SOURCE.CDC_SOURCE','L00001',NULL,sysdate);

    alter table SOURCE.CDC_SOURCE
    add supplemental log data(all) columns;

    begin dbms_capture_adm.prepare_table_instantiation(table_name=>'SOURCE.CDC_SOURCE'); end;

    begin
    dbms_cdc_publish.create_change_set(
    change_set_name=>'L00001_cs_hotlog',
    description=>'hotlog change set for SOURCE.CDC_SOURCE',
    change_source_name=>'HOTLOG_SOURCE',
    stop_on_ddl=>'y'
    );
    end;

    begin
    dbms_cdc_publish.create_change_table(
    owner =>'SYSTEM',
    change_table_name=>'TCDC_L00001',
    change_set_name=>'L00001_cs_hotlog',
    source_schema=>'SOURCE',
    source_table=>'CDC_SOURCE',
    column_type_list=>'TALEND_CDC_STATE VARCHAR2(1),ID NUMBER(22,0),NAME VARCHAR2(3)',
    capture_values=>'new',
    rs_id=>'y',
    row_id=>'n',
    user_id=>'n',
    timestamp=>'n',
    object_id=>'n',
    source_colmap=>'n',
    target_colmap=>'y',
    options_string=>'tablespace users'
    );
    end;

    grant select on PUBLISHER.TCDC_L00001 to SYSTEM;


    begin
    dbms_cdc_subscribe.create_subscription(
    change_set_name=>'L00001_cs_hotlog',
    description=>'change data for SOURCE.CDC_SOURCE',
    subscription_name=>'sub_L00001'
    );
    end;

    begin
    dbms_cdc_subscribe.subscribe(
    subscription_name=>'sub_L00001',
    source_schema=>'SOURCE',
    source_table=>'CDC_SOURCE',
    column_list=>'ID,NAME',
    subscriber_view=>'TCDC_VIEW_L00001'
    );
    end;


    begin
    dbms_cdc_publish.alter_change_set(
    change_set_name=>'L00001_cs_hotlog',
    enable_capture=>'y'
    );
    end;

    begin
    dbms_cdc_subscribe.activate_subscription(
    subscription_name=>'sub_L00001'
    );
    end;