streams apply troubleshoot

Apply
#Sharing the apply
#For every apply process the apply order will be mantained,
#you'll likely use a single apply process for a schema

--here we create the apply process on destination
--ADD the capture rules using the DBMS_STREAMS.ADD_TABLE_RULES procedure, see above

begin
--make your reflections on this value, see DBA_APPLY_PARAMETERS for existing values
DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_TEST',parameter => 'disable_on_error', value => 'y');
end;

BEGIN
DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_test');
END;



Apply troubleshooting

Apply Parameters
select * from DBA_APPLY_PARAMETERS

Apply rules
select DBA_RULES.* from dba_apply, DBA_RULE_SET_RULES, DBA_RULES
where DBA_RULE_SET_RULES.rule_set_name (+)= dba_apply.rule_set_name
and DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name

Apply status
SELECT dba_apply.*, sysdate FROM DBA_apply where apply_name = 'APPLY_TEST'
select * from dba_rules where upper(rule_condition) like upper('%TESTONA%')

How to delete it?
execute DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'apply_test');
execute DBMS_APPLY_ADM.DROP_APPLY(apply_name => 'apply_test');

BEGIN
DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_test');
END;

Errors in apply? Is the apply process running? Remember to re-execute pending errors before restarting the apply
--First check sequence
select * from dba_apply
select * from dba_apply_error
execute DBMS_APPLY_ADM.EXECUTE_All_ERRORS;
select * from dba_apply_error
execute DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_MTS');
select * from DBA_APPLY_PROGRESS

#Check the handler exists
SELECT * FROM DBA_APPLY_DML_HANDLERS WHERE APPLY_DATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME;

#What is on alert log if the apply stop due to an exception?
Wed Aug 27 01:01:45 2008
Streams Apply Reader AS05 for APPLY_TEST2 with pid=38 OS id=12691 stopped

#You also find a trace in bdump in the form al12_as02_10503.trc _as##_####.trc
#You may resubmit the failed apply using:
begin
DBMS_APPLY_ADM.EXECUTE_ERROR(local_transaction_id => '24.21.1447');
end;
#or
execute DBMS_APPLY_ADM.EXECUTE_All_ERRORS;

#The apply process stopped for a full tbs, now the tbs is ok how many messages are now to be dequeued?
SELECT s.SUBSCRIBER_NAME, q.QUEUE_SCHEMA, q.QUEUE_NAME, s.LAST_DEQUEUED_SEQ,
s.NUM_MSGS, s.TOTAL_SPILLED_MSG
FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a
WHERE q.QUEUE_ID = s.QUEUE_ID AND s.SUBSCRIBER_ADDRESS IS NULL AND s.SUBSCRIBER_NAME = a.APPLY_NAME;
subscriber_namequeue_schemaqueue_namelast_dequeued_seqnum_msgstotal_spilled_msgAPPLY_TEST2STRMADMINSTREAMS_QUEUE1834350APPLY_TESTSTRMADMINSTREAMS_QUEUE18900
#Tere are 43 messages to dequeue

#If the apply process as stopped due to an exception
#YOU MUST EXECUTE ERRORS BEFORE STARTING IT AGAIN
#the error queue must be be empty oterwhise new changes are applied and lead to incinsistency

#Data on destination has been manually repaired, how to clear to error queue?
BEGIN
--CAUTION!!! Any pending LCR will be deleted
delete from SYS.apply$_error;
commit;
execute immediate('alter system flush shared_pool');
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_TEST');
END;

#Is there an apply history(10g)?
select * from DBA_HIST_STREAMS_APPLY_SUM