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