Get Data Moving 1
Results 1 to 8 of 8

Thread: HZ_LOCATIONS disappears from CDC Designer in minutes

  1. #1
    A8User is offline Junior Member
    Join Date
    Dec 2012
    Posts
    17
    Rep Power
    0

    HZ_LOCATIONS disappears from CDC Designer in minutes

    Hi,

    I am currently using Attunity CDC for Oracle for about 50 tables. For 49 of these tables, I have no problems. However, with one table, AR.HZ_LOCATIONS, it won't stay in the CDC Designer instance.

    I go about adding this table in the same way as every other one- open the designer, add table, run script, and go. The related table is then added to my SQL Server Instance, and I'm good to go.

    However, usually within 30 minutes, this table is deleted, and completely removed from my CDC instance. I can search it, and it just doesn't show as being added. I can add it again, and the same thing occurs.

    Is there somewhere I can look as to why this table is being deleted from my CDC instance? Obviously, I'm frustrated as to what could be causing this only for 1 / 50 tables.

    Thank you,

    Mark

  2. #2
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Mark,

    The reason the table is dropped from the target can be related to "breaking" schema changes that are applied to the source table.

    The Oracle CDC Instance monitors for DDL events on the source captured tables and, writes relevant events to the cdc.ddl_history table. The CDC Instance will adapt to source table schema changes to the extent possible but, when breaking schema changes occur on the source table, such as drop columns in the middle or incompatible data type changes, the mirror table is dropped along with its capture instances.

    This behaviour can be controlled with the "cdc_stop_on_breaking_schema_changes" advanced option that, when set to True, indicates to stop when breaking schema change is detected, instead of drop the mirror table and its capture instances.


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    A8User is offline Junior Member
    Join Date
    Dec 2012
    Posts
    17
    Rep Power
    0
    Hi Gadi,

    Thanks for the response.

    What kind of schema changes could be occurring so rapidly with this table? This table hasn't been changed in Source, and it definitely isn't being changed every 30 minutes.

    Also, for the option you mentioned to set CDC to stop instead of kick out changed tables, I have two questions.

    1. Where is this change applied? I assume I enter that string in the Advanced tab in CDC Designer, and set the value, but I just wanted to be sure.

    2. If this change is made, does it stop the entire CDC instance, or just the change tracking for that table? Both of these could be an issue, so in preparing the solution I'd like to be sure I completely understand it.

    Thank you,

    Mark
    Last edited by A8User; 01-14-2013 at 01:11 PM.

  4. #4
    A8User is offline Junior Member
    Join Date
    Dec 2012
    Posts
    17
    Rep Power
    0
    Okay. I changed the value of that advanced option to true, and the CDC instance stopped. Collecting Diagnostics, I got the following:

    timestamp,type,node,status,sub_status,status_messa ge,source, text_data,binary_data
    "1/14/2013 7:59:44 PM","INFO","DOV-DC4-V017","RUNNING","IDLE","ORACDC303I:The Oracle CDC instance for service CDC DEV and database CDC_NPS received a reconfigure signal.","service","",""
    "1/14/2013 7:59:44 PM","INFO","DOV-DC4-V017","RUNNING","IDLE","ORACDC305I:The value of the option 'cdc_stop_on_breaking_schema_changes' was changed to 1.","infrastructure","",""
    "1/14/2013 8:18:09 PM","INFO","DOV-DC4-V017","RUNNING","INIT","ORACDC323I:The metadata validation for Oracle table cdc.AR_HZ_LOCATIONS_CT failed. Column OBJECT_VERSION_NUMBER index was changed.","source","",""
    "1/14/2013 8:18:09 PM","ERROR","DOV-DC4-V017","ERROR","MISCONFIGURED","ORACDC419E:The mirror table AR.HZ_LOCATIONS does not match the corresponding Oracle table.","engine","",""
    "1/14/2013 8:18:09 PM","ERROR","DOV-DC4-V017","ERROR","MISCONFIGURED","ORACDC414E:The Source component failed with return code 12.","engine","",""
    "1/14/2013 8:18:15 PM","INFO","DOV-DC4-V017","ERROR","STOP","ORACDC312I:The Oracle CDC instance for service CDC DEV and database CDC_NPS finished abnormally.","service","",""

    The line highlighted in Red seems to be the issue. Does that mean anything to you? IE, what could cause that to happen? I am also sending this to my DBA to take a look at. In the mean time, I am removing that field from the CDC instance to see if that work around will take care of it.

    (The cdc.ddl_history table is empty).

    Thanks,

    Mark

  5. #5
    A8User is offline Junior Member
    Join Date
    Dec 2012
    Posts
    17
    Rep Power
    0
    Update:

    Removing the field specified in the diagnostics from the CDC instance does not help. it still gives the same error message (with another field) and removes the table from CDC.

    Thanks,

    Mark
    Last edited by A8User; 01-14-2013 at 06:38 PM.

  6. #6
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Mark,

    Can you attach the table metadata or the create table statement? Also, let me know if the table has unused columns, you can check that using:

    SELECT * FROM sys.dba_unused_col_tabs;


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    DimitriF is offline Junior Member
    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    I am now facing the same problem as the original poster (incidentally, we are sourcing from the same kind of Oracle system). In my case, the CDC instance is dropped even though the CDC_stop_on_breaking_schema_changes is set to True. This is a problem because this would likely go unnoticed and cause data integrity problems in downstream systems.

    In the trace log, I see this:

    ORACDC323I:The metadata validation for Oracle table cdc.AP_AP_CREDIT_CARD_TRXNS_ALL_CT failed. Column CARD_ID index was changed.
    ORACDC310I:CDC was disabled for the mirror table AP.AP_CREDIT_CARD_TRXNS_ALL because of metadata changes.
    ORACDC311I:The mirror table AP.AP_CREDIT_CARD_TRXNS_ALL was dropped because of metadata changes.

    The cdc.ddl_history table is empty.

    Table DDL script is attached.
    AP_CREDIT_CARD_TRXNS_ALL.zip

    I don't have sufficient permissions on the source system to select from sys.dba_unused_col_tabs.

  8. #8
    DimitriF is offline Junior Member
    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    An update on this. The reason for the CDC_stop_on_breaking_schema_changes option not working was that it has to be spelled in all lower case, i.e. cdc_stop_on_breaking_schema_changes. This might be related to the fact that our SQL Server instance is using a case sensitive collation.

    As to the actual problem of CDC being disabled for one table: The CDC deployment script was originally created from one Oracle database, but when we deployed it, we had to point to another Oracle database which was expected to have an identical schema. In reality, the column order for some of the tables, including the affected table, was different between the two Oracle databases. When the first change occurred for the affected table, the product detected that a column name does not match its IDs, which is effectively a metadata change, and stopped capture for this table.

    To troubleshoot this kind of problem, it is helpful to compare the column_id from cdc.captured_columns against the COLUMN_ID from the ALL_TAB_COLUMNS Oracle view, for each captured table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •