This discussion is archived
3 Replies Latest reply: Feb 21, 2011 12:55 PM by Ben Speckhard RSS

DBMS_WM.CommitBulkLoading command corrupting rows not in the bulk load

798066 Newbie
Currently Being Moderated
We have stumbled into some shocking behavior from the CommitBulkLoading command that we can consistently reproduce in multiple instances, running both 10g and 11g (our primary release is 11.1.0.7). We're curious to know if others have had similar experiences, or have figured out any bullet-proof workarounds.

Our goal is to incrementally bulk load data in two scenarios:

1. Legacy data that pre-dates our use of WM, and becomes incrementally available (converted) after our use of WM has begun. We are loading this data into ROOT_VERSION.
2. Incremental data that post-dates our use of WM and becomes available (converted) after our use of WM has begun. We are loading this data into LATEST.

Here's the simple test case which exhibits the same behavior as our not-so-simple real-world case. We start with:

1. An empty & unversioned table
2. EXEC DBMS_WM.ENABLEVERSIONING('TEST_TABLE','VIEW_WO_OVERWRITE');


Then, we tried to bulk load on a programmatic interval, for each scenario:

1. EXEC DBMS_WM.BEGINBULKLOADING(table_name=>'TEST_TABLE',workspace=>'LIVE', version=>[correct version], check_for_duplicates=>TRUE, ignore_last_error=>FALSE, single_transaction=>FALSE);

2. Insert any new rows/keys not yet in the versioned table, with correct version number and createtime/retiretime values (inserted row count can range from 0 to N).

3. EXEC DBMS_WM.COMMITBULKLOADING (table_name=>'TEST_TABLE', discards_table=>'WMD_TEST_TABLE', check_for_duplicates=>TRUE, enforceUCFlag=>FALSE, enforceRICFlag=>FALSE, ignore_last_error=>FALSE, single_transaction=>FALSE);


This procedure works fine when the inserted row count is >0. When the inserted row count is zero, however, the CommitBulkLoading procedure shockingly proceeds to overwrite the CREATETIME value on all pre-existing rows that have the same version number to SYSTIMESTAMP, without warning. This occurs in both scenarios. In the case of scenario one, using ROOT_VERSION, CommitBulkLoading also sets the DELSTATUS values on all ROOT_VERSION rows to NULL -- which completely invalidates the primary key and leaves the table in an invalid state. In both cases, all data associated with the given version is irreparably damaged. Furthermore, the primary mechanism to recover the table doesn't work: BeginBulkLoading and CommitBulkLoading both modify the table structure, which invalidates all flashback functionality (ORA-01466).

We can handle the programmatic case to avoid CommitBulkLoading when no new rows are inserted. However, we are afraid of the very real possibility that any inadvertant CommitBulkLoading command will completely destroy hundreds of thousands (or millions) of lines of data, and we will have to utilize RMAN recovery options, resulting in a major outage scenario. This has already happened in our development & testing instances.

Any and all insight is welcomed.
  • 1. Re: DBMS_WM.CommitBulkLoading command corrupting rows not in the bulk load
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    You are right. This is definitely a bug that has been around for a while. If you need a fix for your current version I would recommend filing a SR. I do not see any workarounds other than guaranteeing that you always bulk load at least one row, or in cases when you don't that RollbackBulkLoading is used.

    Also, I was not able to reproduce the case where the delstatus is being set to null. If you have an example, feel free to pass it along.

    Regards,
    Ben
  • 2. Re: DBMS_WM.CommitBulkLoading command corrupting rows not in the bulk load
    798066 Newbie
    Currently Being Moderated
    Thanks Ben,

    You are right, while we are encountering some situations where DELSTATUS gets set to null, this particular sequence of events doesn't do it. We haven't completely identified the context that sets the null values. However, this sequence of events does set all of the DELSTATUS values to 10 -- which still breaks the primary key, irreparably damages the data and invalidates the table. Here's our test script:

    ---------------------------------------------------------------------------------------------------------------

    CREATE TABLE TMP_BULK_CORRUPT_TEST
    (
    PRIMARY_ID NUMBER NOT NULL
    , DATA_CHAR VARCHAR2(20)
    , DATA_DATE DATE
    , DATA_NUM NUMBER
    , CONSTRAINT TMP_BULK_CORRUPT_TEST_PK PRIMARY KEY
    (
    PRIMARY_ID
    )
    ENABLE
    );

    -- Version the table
    EXEC DBMS_WM.ENABLEVERSIONING('TMP_BULK_CORRUPT_TEST','VIEW_WO_OVERWRITE');

    -- Execute normal bulk load #1, two primary IDs with multiple versions
    EXEC DBMS_WM.BEGINBULKLOADING(table_name=>'TMP_BULK_CORRUPT_TEST',workspace=>'LIVE',version=>DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'),check_for_duplicates=>TRUE,ignore_last_error=>FALSE,single_transaction=>FALSE);
    INSERT INTO TMP_BULK_CORRUPT_TEST_LT(PRIMARY_ID,DATA_CHAR,DATA_DATE,DATA_NUM,CREATETIME,RETIRETIME,VERSION) VALUES (1,'ID 1 Version 1', TO_DATE('01-JAN-1980','DD-MON-YYYY'), 1, TO_DATE('01-JAN-1980','DD-MON-YYYY'), TO_DATE('01-JAN-1985','DD-MON-YYYY'), DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'));
    INSERT INTO TMP_BULK_CORRUPT_TEST_LT(PRIMARY_ID,DATA_CHAR,DATA_DATE,DATA_NUM,CREATETIME,RETIRETIME,VERSION) VALUES (1,'ID 1 Version 2', TO_DATE('01-JAN-1985','DD-MON-YYYY'), 1, TO_DATE('01-JAN-1985','DD-MON-YYYY'), TO_DATE('01-JAN-1990','DD-MON-YYYY'), DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'));
    INSERT INTO TMP_BULK_CORRUPT_TEST_LT(PRIMARY_ID,DATA_CHAR,DATA_DATE,DATA_NUM,CREATETIME,RETIRETIME,VERSION) VALUES (1,'ID 1 Version 3', TO_DATE('01-JAN-1990','DD-MON-YYYY'), 1, TO_DATE('01-JAN-1990','DD-MON-YYYY'), NULL, DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'));
    INSERT INTO TMP_BULK_CORRUPT_TEST_LT(PRIMARY_ID,DATA_CHAR,DATA_DATE,DATA_NUM,CREATETIME,RETIRETIME,VERSION) VALUES (2,'ID 2 Version 1', TO_DATE('01-JAN-1983','DD-MON-YYYY'), 1, TO_DATE('01-JAN-1983','DD-MON-YYYY'), TO_DATE('01-JAN-1988','DD-MON-YYYY'), DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'));
    INSERT INTO TMP_BULK_CORRUPT_TEST_LT(PRIMARY_ID,DATA_CHAR,DATA_DATE,DATA_NUM,CREATETIME,RETIRETIME,VERSION) VALUES (2,'ID 2 Version 2', TO_DATE('01-JAN-1988','DD-MON-YYYY'), 1, TO_DATE('01-JAN-1988','DD-MON-YYYY'), NULL, DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'));
    SELECT COUNT(*) FROM TMP_BULK_CORRUPT_TEST_LT WHERE DELSTATUS IS NULL;
    EXEC DBMS_WM.COMMITBULKLOADING(table_name=>'TMP_BULK_CORRUPT_TEST',discards_table=>'WMD_BULK_CORRUPT_TEST',check_for_duplicates=>TRUE,enforceUCFlag=>FALSE,enforceRICFlag=>FALSE,ignore_last_error=>FALSE,single_transaction=>FALSE);

    -- Show rows from bulk load #1, data normal and as expected
    SELECT * FROM TMP_BULK_CORRUPT_TEST_LT ORDER BY primary_id, createtime;
    SELECT * FROM TMP_BULK_CORRUPT_TEST ORDER BY primary_id;

    -- Execute empty bulk load #2, which results in an ORA-02437 primary key violation due to corrupted DELSTATUS
    EXEC DBMS_WM.BEGINBULKLOADING(table_name=>'TMP_BULK_CORRUPT_TEST',workspace=>'LIVE',version=>DBMS_WM.GETBULKLOADVERSION('LIVE','ROOT_VERSION'),check_for_duplicates=>FALSE,ignore_last_error=>FALSE,single_transaction=>FALSE);
    EXEC DBMS_WM.COMMITBULKLOADING(table_name=>'TMP_BULK_CORRUPT_TEST',discards_table=>'WMD_BULK_CORRUPT_TEST',check_for_duplicates=>FALSE,enforceUCFlag=>FALSE,enforceRICFlag=>FALSE,ignore_last_error=>FALSE,single_transaction=>FALSE);

    -- Show rows from bulk load #2, all CREATETIME values reset to SYSTIMESTAMP, all DELSTATUS values reset to 10
    SELECT * FROM TMP_BULK_CORRUPT_TEST_LT ORDER BY primary_id, createtime;

    -- Commit again to get past error
    EXEC DBMS_WM.COMMITBULKLOADING('TMP_BULK_CORRUPT_TEST','WMD_BULK_CORRUPT_TEST',TRUE,FALSE,FALSE,TRUE,FALSE);

    -- Show rows from bulk load #2, data corrupted and irreparable
    SELECT * FROM TMP_BULK_CORRUPT_TEST_LT ORDER BY primary_id, createtime;
    SELECT * FROM TMP_BULK_CORRUPT_TEST ORDER BY primary_id;

    -- Unversion the table, first attempt fails
    EXEC DBMS_WM.DISABLEVERSIONING('TMP_BULK_CORRUPT_TEST');

    -- Unversion the table, ignoring error
    EXEC DBMS_WM.DISABLEVERSIONING('TMP_BULK_CORRUPT_TEST', IGNORE_LAST_ERROR=>TRUE);

    -- Drop the tables
    DROP TABLE TMP_BULK_CORRUPT_TEST;
    DROP TABLE WMD_BULK_CORRUPT_TEST;

    ---------------------------------------------------------------------------------------------------------------

    Given that there is no immediate fix (unless we get one through an SR), we have to establish and validate a pre-determined recovery path before we can take this to a production instance. Where can I look for information about recovering a versioned table from backup? We would likely use RMAN backups with archive logs to recover to the point immediately prior to calling DBMS_WM.BeginBulkLoading. Any special considerations to make sure that triggers and metadata on the recovered table aren't lost?
  • 3. Re: DBMS_WM.CommitBulkLoading command corrupting rows not in the bulk load
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    The setting the delstatus to 10 is effectively the same bug as setting the createtime to the systimestamp. So fixing the one bug will also fix the other.

    In regards to backup, triggers/views/etc can always be recreated for a versioned table. You just need to be sure that all of the schemas containing a versioned table and the WMSYS schema are rolled back as a single unit. You can use RMAN to do a full point in time recovery or do a full import/export. Just changing a single table isn't supported as this time.

    Regards,
    Ben

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points