Apply or Delete Apply errors and Restart Stream Apply process

Step 1: Check Apply status



select apply_name,status from dba_apply;


APPLY_NAME                     STATUS
------------------------------ --------
TARGET_APPLY                   ABORTED


If status is aborted then check dba_apply_error view


Step 2: Check Apply error

 select local_transaction_id,error_number,error_message from dba_apply_error;


LOCAL_TRANSACTION_ID  ERROR_NUMBER ERROR_MESSAGE
-------------------- ------------- ------------------------
9.59.1031 959    ORA-00959: tablespace 'CATTBS' does not exist


Step 3: Print Apply error statement and LCRs

SQL> select local_transaction_id from dba_apply_error;


If the local transaction identifier is 1.17.2485, then enter the following:


SQL> set serveroutput on size unlimited
SQL> spool error.log
SQL> EXEC print_transaction('1.17.2485');
SQL> spool error.log



Step 4: Re-execute apply error

-- To Execute specific transaction

exec DBMS_APPLY_ADM.EXECUTE_ERROR ('');

-- To Execute all errors:

exec dbms_apply_Adm.execute_all_errors;


Step 5: Delete the apply errors

-- Check the errors in the error queue:

select apply_name, LOCAL_TRANSACTION_ID, ERROR_MESSAGE from dba_apply_error;

-- To delete specific transaction:

exec DBMS_APPLY_ADM.DELETE_ERROR('');

-- To delete all errors:

exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS('');


Step 6: Check Apply status


select apply_name,status from dba_apply;


APPLY_NAME                      STATUS
------------------------------ --------
TARGET_APPLY                    ABORTED