Propagation Error And Exception Queue Management

If you've tried to use the example from my previous blog post named "Transfer Files From One Server To Another With Oracle Streams", you may have faced some propagation issues. Step 7 of the post helps to quickly work around any issue by stopping and restarting the propagation. However, this is just a work around and in a real case scenario, you would want to manage the message in the exception queue.

This post goes deeper into the right way to fix any propagation issue; to begin, it simulates a propagation error, just in case you did not hit any by yourself ;-). The next section shows how you could fix the situation and manage the exception message by your own.

1. Simulate a Propagation Error

Like with DBMS_FILE_TRANSFER, external files transported with Oracle Streams must be multiple of 512 bytes in size. We'll use that limit to make a propagation process fail. To set up the example, follow "Step 1" to "Step 5" of the previous post "Transfer Files From One Server To Another With Oracle Streams". Once done, instead of executing "Step 6", execute the step below that will make the propagation fail.
On BLACK, create a file that is NOT a multiple of 512 bytes in size, instantiate a BFILE that points to it and enqueue the message with that points to the file in the Streams queue:

connect strmadmin/strmadmin

select * from GLOBAL_NAME;

GLOBAL_NAME
----------
BLACK

!dd if=/dev/zero of=/tmp/black/demo2.zero bs=511 count=1

declare
v_demotyp demotyp:=demotyp(2, bfilename('tmp_dir','demo2.zero'));
v_any     anydata;
begin
v_any:=anydata.convertobject(v_demotyp);
dbms_streams_messaging.enqueue(
  'strmadmin.streams_queue',
  v_any);
end;
/
commit;

This time the propagation fails with a message like the one below:

col error_message format a100
set lines 100
set long 10000
set longchunksize 10000

select error_message
from dba_propagation
where propagation_name='BFILE_PROPAGATION';

ERROR_MESSAGE
-------------------------------------------------------
ORA-19505: failed to identify file "/tmp/black/demo2.zero"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

You'll find the same error message in the queue propagation schedule:

select last_error_msg
from dba_queue_schedules
where schema='STRMADMIN'
 and qname='STREAMS_QUEUE'
 and message_delivery_mode='PERSISTENT';

LAST_ERROR_MSG
-------------------------------------------------------
ORA-19505: failed to identify file "/tmp/black/demo2.zero"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1   

2. Fix the Error

Fixing the error depends on what it is. Let's say in this case, you'll fix the error by padding the file with a "0x00" byte; You can do it with a dd and the seek parameter:

Important note:
Don't use such a command on a real file without evaluating first what the impact would be on the file itself!

!dd if=/dev/zero of=/tmp/black/demo2.zero count=1 bs=1 seek=511 

!ls -ltra /tmp/black/demo2.zero
-rw-r--r-- 1 oracle oinstall 512 2009-08-24 14:26 /tmp/black/demo2.zero

That done, dequeue the message from the exception queue and re-enqueue it in the Streams queue; in that case that's probably a good idea to have only one queue per queue table to know the original queue of the message in the exception queue. Here is how to do it; if you want more examples, refer to Metalink Note "233103.1 - Dequeuing Messages from an Exception Queue":

connect strmadmin/strmadmin

begin
dbms_aqadm.start_queue(
 queue_name => 'strmadmin.AQ$_STREAMS_QUEUE_TABLE_E',
 enqueue    => false,
 dequeue    => true);
end;
/

SET SERVEROUTPUT ON

DECLARE
 v_deq_options     DBMS_AQ.dequeue_options_t;
 v_msg_properties  DBMS_AQ.message_properties_t;
 o_msgid           RAW(16);
 v_any             anydata;
BEGIN
 v_deq_options.consumer_name:=null;
 v_deq_options.dequeue_mode := DBMS_AQ.remove;
 v_deq_options.navigation := DBMS_AQ.NEXT_TRANSACTION;
 DBMS_AQ.dequeue(
    queue_name          => 'strmadmin.AQ$_STREAMS_QUEUE_TABLE_E',
    dequeue_options     => v_deq_options,
    message_properties  => v_msg_properties,
    payload             => v_any,
    msgid               => o_msgid);
 dbms_streams_messaging.enqueue('strmadmin.streams_queue',v_any);
 COMMIT;
END;
/

begin
dbms_aqadm.stop_queue(
 queue_name => 'strmadmin.AQ$_STREAMS_QUEUE_TABLE_E' ,
 enqueue    => true,
 dequeue    => true);
end;
/

Once done, you'll see the error go away from the propagation and the schedule. The file, now of a correct size, will be transferred from BLACK to WHITE:

select error_message
 from dba_propagation
where propagation_name='BFILE_PROPAGATION';

ERROR_MESSAGE
-------------------------------------------------------

select last_error_msg
 from dba_queue_schedules
where schema='STRMADMIN'
  and qname='STREAMS_QUEUE'
  and message_delivery_mode='PERSISTENT';

LAST_ERROR_MSG
-------------------------------------------------------

Check the file has been received on the WHITE side:

!ls -ltra /tmp/white/demo2.zero
-rw-r----- 1 oracle oinstall 512 2009-08-24 14:33 /tmp/white/demo2.zero

This is it: a very simple way to fix propagation errors. Once again, clean up the environment before you leave it for your next demo; delete the demo2.zero file on both sides of your configuration and execute "Step 8 of the previous post" to delete queues, tablespaces, administrators and more...