6 Replies Latest reply: Aug 24, 2011 2:31 PM by Ben Speckhard-Oracle RSS

    ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts

    882731
      We are getting unique constraint error in Production Database. We are able to merge most of the workspaces with no issues. One workspace is givign errors on merge.
      Here is the code

      begin
      dbms_wm.gotoworkspace('denialletterspoc_onmzpulp');
      dbms_wm.setdiffversions('denialletterspoc_onmzpulp','LIVE');
      dbms_wm.SetConflictWorkspace('denialletterspoc_onmzpulp');
      commit;
      DBMS_WM.BeginResolve('denialletterspoc_onmzpulp');
      DBMS_WM.ResolveConflicts(
      workspace => 'denialletterspoc_onmzpulp',
      table_name => 'MCM_MESSAGE',
      where_clause => ' id in (8403026,10134822,8259485,8259488,8426289,8426287,10186643,8426288) ',
      keep => 'CHILD'
      );
      exception when others then
      DBMS_WM.RollbackResolve('denialletterspoc_onmzpulp');
      raise;
      end;

      ORA-00001: unique constraint (T2.MCM_MESSAGE_PK) violated
      ORA-06512: at line 16

      I looked at the date in XXX_lt tables and it does not look suspecious. Please help us understand
      ORA-00001 and ORA-06512 error.
      Amit Gangwar
        • 1. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
          Ben Speckhard-Oracle
          Hi Amit,

          What version of Workspace Manager are you using?

          It's hard to say what could be causing the unique constraint violation without seeing the table data. I would recommend to run ResolveConflicts separately on each of the primary key values listed in the where_clause. The error should still occur for the one that is responsible. You could then query _LT based on that value which should help in determining the cause.

          Regards,
          Ben
          • 2. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
            883548
            Hi, Ben

            Our OWM_VERSION is 10.2.0.4.3.

            I tried disable and enable constraint MCM_MESSAGE_PK. But didn't fix our issue.

            Split where clause from " id in (...) " syntax to " id = ... " syntax will reduce performance heavily. And I did some test that " id in (...) " syntax works well in our dev env.

            I opened our trace file and get below plsql block. When I perform it get same error with our original codes. Please notice the part of "forall wm_indx in WM_rowids.first .. WM_rowids.last execute immediate 'begin insert into T2.MCM_MESSAGE_lt ... ...", error occurred here. But if I change the "forall" syntax to normal " for ... loop" syntax then no error here. Can you give me some advice or suggestion?

            DECLARE
            WM_rowids wmsys.lt_ctx_pkg.rowidTabType;
            WM_rowids_vt wmsys.lt_ctx_pkg.rowidTabType;
            WM_curver wmsys.lt_ctx_pkg.int_tab;
            WM_vtRow wmsys.lt_ctx_pkg.int_tab;
            WM_nextver wmsys.lt_ctx_pkg.nextver_tab;
            WM_found integer := 0;

            BEGIN
            dbms_wm.gotoworkspace('denialletterspoc_onmzpulp');
            dbms_wm.setdiffversions('denialletterspoc_onmzpulp','LIVE');
            dbms_wm.SetConflictWorkspace('denialletterspoc_onmzpulp');
            /* update the auxilliary table of snapshots */
            /* These are rows in the current version that have already been resolved once */
            update T2.MCM_MESSAGE_aux st
            set (snapShotChild, snapShotParent, versionParent) =
            (select p.WM_childds, p.WM_parentds, p.WM_parentver
            from T2.MCM_MESSAGE_pkc p
            where p.ID = st.ID),
            value = '0',
            wm_opcode = 'CC'
            where st.versionChild = 614
            and (ID) in (select ID
            from T2.MCM_MESSAGE_pkc p
            where (id in (8403026,
            10134822,
            8259485,
            8259488,
            8426289,
            8426287,
            10186643,
            8426288)));
            select distinct WM_ridchild bulk collect
            into WM_rowids
            from T2.MCM_MESSAGE_pkc
            where (id in (8403026,
            10134822,
            8259485,
            8259488,
            8426289,
            8426287,
            10186643,
            8426288))
            and WM_childver != 614;
            if (WM_rowids.first is not null) then
            WM_found := 1;
            forall wm_indx in WM_rowids.first .. WM_rowids.last
            update T2.MCM_MESSAGE_lt
            set retireTime = wmsys.owm_9ip_pkg.activeTimeForDML,
            nextver = wmsys.lt_ctx_pkg.getNextVer(nextver,
            614,
            version,
            'denialletterspoc_onmzpulp',
            'CRS_LEAF',
            22)
            where rowid = WM_rowids(wm_indx)
            returning wmsys.lt_ctx_pkg.getCurNextVer bulk collect into WM_nextver;
            /* These are the rows that have not yet been resolved after the latest BeginResolve */
            forall wm_indx in WM_rowids.first .. WM_rowids.last execute immediate
            'begin insert into T2.MCM_MESSAGE_lt(ID,DPS_APPLICATION_ID,TYPE_ID,NAME,PRIVILEGED,CAMPAIGN_ID,TEXT,FILE_NAME,GRAPHIC_WIDTH,GRAPHIC_HEIGHT,DESCRIPTION,ACTIVE,INCLUDE_IN_LIBRARY,MESSAGE_FILTER_ID,OP_USER_ID,OP_WORKSPACE,OP_TIME,version,createtime,retiretime,nextver,delstatus,ltlock)
            (select t1.ID,t1.DPS_APPLICATION_ID,t1.TYPE_ID,t1.NAME,t1.PRIVILEGED,t1.CAMPAIGN_ID,t1.TEXT,t1.FILE_NAME,t1.GRAPHIC_WIDTH,t1.GRAPHIC_HEIGHT,t1.DESCRIPTION,t1.ACTIVE,t1.INCLUDE_IN_LIBRARY,t1.MESSAGE_FILTER_ID,t1.OP_USER_ID,t1.OP_WORKSPACE,t1.OP_TIME,614, wmsys.owm_9ip_pkg.activeTimeForDML, null,:1, sign(delstatus)*(abs(delstatus)+1),
            wmsys.lt_ctx_pkg.checkngetlock(''C'', t1.ltlock, t1.nextver, ''denialletterspoc_onmzpulp'', 0,
            ''MODIFY'', t1.version, t1.delstatus, 0, 1)
            from T2.MCM_MESSAGE_lt t1
            where t1.rowid = :2); end;'
            using WM_nextver(wm_indx),
            WM_rowids(wm_indx)
            ;
            end if;


            if (WM_found = 1) then
            wmsys.lt_ctx_pkg.update_modified_tables('T2',
            'MCM_MESSAGE',
            614,
            'denialletterspoc_onmzpulp',
            22);
            end if;
            if (WM_found = 1) then
            forall wm_indx in WM_rowids.first .. WM_rowids.last
            delete from T2.MCM_MESSAGE_aux
            where (ID) in (select ID
            from T2.MCM_MESSAGE_lt
            where rowid = WM_rowids(wm_indx))
            and childState = 'denialletterspoc_onmzpulp';
            end if;
            /* now insert new syncing information about these rows */
            execute immediate 'insert into T2.MCM_MESSAGE_aux (
            select ID,
            p.WM_childstate, p.WM_parentstate,
            p.WM_childds, 614, p.WM_parentds, p.WM_parentver, ''0'',''CC''

            from T2.MCM_MESSAGE_pkc p
            where ( id in (8403026,10134822,8259485,8259488,8426289,8426287,10186643,8426288) ) )';
            END;

            Thanks

            Amos
            • 3. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
              883548
              Hi, Ben

              And I traced this error after add save exceptions into forall codes. I get below variable:

              WM_nextver(1):-1
              WM_rowids(1):AAAfk3AA3AAKc84AAV
              WM_nextver(2):-1
              WM_rowids(2):AAAfk3AA5AAKsdnAAP
              WM_nextver(3):-1
              WM_rowids(3):AAAfk3AA0AAMoq1AAP
              WM_nextver(4):-1
              WM_rowids(4):AAAfk3AA3AAKc84AAU
              WM_nextver(5):-1
              WM_rowids(5):AAAfk3AA0AAFXHwAAj
              WM_nextver(6):-1
              WM_rowids(6):AAAfk3AA5AAJtXlAAN
              WM_nextver(7):-1
              WM_rowids(7):AAAfk3AA5AAKsdnAAA
              WM_nextver(8):-1
              WM_rowids(8):AAAfk3AA2AAIWX1AAA

              Number of statements that failed: 1
              Error #1 occurred during iteration #2
              Error message is ORA-00001: unique constraint (T2.MCM_MESSAGE_PK) violated
              WM_nextver: -1
              WM_rowids: AAAfk3AA5AAKsdnAAP

              I queried result in performed session and found first row has been insert successed. But the second row failed. I insert second row manually successed, then I rollbacked it. It seem that error haven't relationship with data but loop method. Forall syntax have some problem here.

              Our DB version is 10.2.0.4.0.

              Can you give me some advice?

              Thanks

              Amos

              Thanks

              Amos

              Edited by: user6533462 on Aug 22, 2011 12:46 AM
              • 4. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
                883548
                Hi, Ben

                I googled error with forall and ORA-1. Our error similar with Bug 7000281 "Difference in FORALL statement behaviour in 11g This note gives a brief overview bug 7000281". But I can't confirm it. Can you give us some advice?

                Thanks

                Amos
                • 5. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
                  aschilling
                  hi Amit!

                  it sounds a bit as if you are running into bug #12730297 (see oracle support for details). Depending on what operations internally are triggered by OWM during conflict resolution I assume this can occur as well (in our case it only occurred for RefreshTable).
                  Some more details can also be found in this thread: False Unique Constraint Violation on RefreshTable
                  We also had the strange situation that the violation fired although the constraint was disabled. As you are running the exact same version as our production system it's possible that it is the same bug.
                  Important: our DBAs are still in contact with Oracle concerning this bug. The patch that was released due to our SR was not installable on 10.2.0.4.3, so we are still waiting for a new patch that works with our environment.
                  I'll drop some lines here in the forum as soon as we have the patch and our DBAs installed it successfully.

                  kind regards,

                  Andreas
                  • 6. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
                    Ben Speckhard-Oracle
                    Hi Amos,

                    This does look like it might be bug 7000281. It seems to also affect later versions of 10.2, so it might be worth filing an SR, to determine if in fact you are running into this bug. It certainly sounds like it, if converting the forall loop into a standard loop fixes the error.

                    Also, bug 12730297 is strictly a refresh bug. It should not be caused by ResolveConflict errors.

                    Regards,
                    Ben