DBMS_APPLY_ADM – ORA-26663

There are lots of times when you are new to a technology that you want to wipe the slate clean and start a process again because you are not sure where it went wrong. I attempted this after receiving several errors within Change Data Capture, and the capture and/or apply processes were receiving the ORA-26663 error message.

So, I thought it would make sense to drop the change set and start again. It was then that I received these errors:

ORA-26663: error queue for apply process CDC$A_TEST_CHGSET must be empty
ORA-06512: at  "SYS.DBMS_APPLY_ADM_INTERNAL", line 283
ORA-06512: at  "SYS.DBMS_APPLY_ADM_INTERNAL", line 270
ORA-06512: at "SYS.DBMS_APPLY_ADM", line  691
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line  602
ORA-06512: at line 2

Now I can’t even drop the change set to start again! Before you think about rebuilding the schema or database, check out how to work around this problem:

First, check what errors you have:

SELECT  *
FROM  DBA_APPLY_ERROR;

The DBA_APPLY_ERROR view is great for finding out what issues there are, along with the v$streams_capture view which also has some useful information.

Next, decide whether you want to delete or re-execute the  errors. If the error was caused by DDL being issued against one of the source  tables and you had stop_on_ddl set to true within the change set, then you will  probably want to delete the error once you have checked that the DLL is not  harmful. If you have corrected the cause of the error, you will probably want  to re-execute the error so that it passes through.

You can delete all errors using the DBMS_APPLY_ADMN Oracle supplied package, like so:

begin
DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name=>'CDC$A_TEST_CHGSET');
end;
/

Or you can delete a specific error using the same DBMS_APPLY_ADM package but a different procedure in it:

begin
DBMS_APPLY_ADM.DELETE_ERROR(local_transaction_id=>'8.18.2491');
end;
/

or, if you wnat to, re-execute all errors:

BEGIN
DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(apply_name=> 'CDC$A_TEST_CHGSET',
execute_as_user  => false);
END;
/

Here is an example of how you would remove the DDL error, recover from the error and restart the change set:

begin
dbms_cdc_publish.alter_change_set(change_set_name => 'test_chgset',
recover_after_error=> 'Y',
remove_ddl => 'Y',
enable_capture => 'Y');
end;
/

Like it, share it...

Category: Change Data Capture