ORA-26663

ORA-26663 - When Dropping a CDC Change Set (zt)

2010-05-18 18:28

ORA-26663 - When Dropping a CDC Change Set

http://www.ora00600.com/scripts/changedatacapture/drop_change_set.html

There are lots of times when you are new to a technology that you want to wipe the slate clean and start your 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.

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;

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:

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

Delete a specific error:

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

or 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;
/

Comments (0)