2 Replies Latest reply on Mar 19, 2010 5:34 AM by Asheesh Ambardar

    Oracle CDC/ Streams question - ORA-31514

    760774
      Hi

      We are using CDC for the past year and it has been running pretty smoothly. Currently while activating a new subscription or extending existing subscription windows, we are getting the error ORA-31514: change set disabled due to capture error. However when I look at the change tables, the data looks up to date. The all_apply and all_apply_error views are empty. we have not found any obvious errors on the streams/ CDC. Can anyone help on how I can investigate further to resolve this issue ? looks like everything is fine till teh point of getting into change tables but somehow unable to support subscription.

      Thanks
        • 1. Re: Oracle CDC/ Streams question - ORA-31514
          Herald ten Dam
          Hi,

          in Chapter about CDC in the Oracle manuls, the error is described and some fixes are mentioned see http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/cdc.htm#i1027192 and scroll a little down.

          Normally I try the next code (is also mentioned in above manual):
          BEGIN
          DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
          change_set_name     => 'YOUR_CHANGE_SET_NAME',
            recover_after_error => 'y',
            remove_ddl          => 'y');
          END;
          /
          I hope it starts after the statement. If not have a look at your alert.log, here you can find also errors about CDC.

          Herald ten Dam
          Superconsult.nl
          • 2. Re: Oracle CDC/ Streams question - ORA-31514
            Asheesh Ambardar
            Welcome to the forum !!

            What CDC configuration is in use?
            At high level, here are the steps that you need to follow:
            *1.* Check the DBA_APPLY_ERROR view for any exceptions. Main reasons for exception are:
            <ul>
            <li> System issues e.g. Disk out of space, tablespace issues
            <li> Schema changes, e.g. column datatype got changed because of a DDL
            </ul>
            *2.* As mentioned by Herald, execute the ALTER_CHANGE_SET API with recover_after_error => 'Y' and remove_ddl => 'Y' option
            *3.* After recovering the change set, enable it using the following API call:
            BEGIN
            DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
            change_set_name => 'YOUR_CHANGE_SET_NAME',
            enable_capture  => 'Y');
            END;
            /
            Cheers,
            AA