I have inherited an AQ system and had to do a database restore of DB1 (reseed) from a backup. I have substututed real names of queues and links etc with generic names for security’s sake in the decsription. There is now a 21M row backlog in the the DB1_queue in database DB1. Queue to queue propagation is set up to another queue_DB3 in another database DB3.
There is also another queue_DB2 in a different database DB2 that is working fine and propagating to the DB3_queue. The DB2 database was also restored for a reseed from a backup.

DB1 Encountered snapshot too old error.
Docs say : “ORA-1555

You might get this error when using the NEXT_MESSAGE navigation option for dequeue. NEXT_MESSAGE uses the snapshot created during the first dequeue call. After that, undo information may not be retained.

The workaround is to use the FIRST_MESSAGE option to dequeue the message. This reexecutes the cursor and gets a new snapshot. FIRST_MESSAGE does not perform as well as NEXT_MESSAGE, so Oracle recommends that you dequeue messages in batches: FIRST_MESSAGE for one, NEXT_MESSAGE for the next 1000 messages, then FIRST_MESSAGE again, and so on.
“

I increased the UNDO size and started the dequeue again using sqldeveloper option : Start Dequeue and it has been running for 18 hours and may well fail again, so if it does I want to dequeue in smaller sets of data as per the workaround above.

I can’t work out how to do this and unfortunately I am new to queues and I can’t afford to lose this data either so I am hesitant to start dabbling when I am not confident in what I am trying.

The propagation code I inherited and re-used to start propagation is of the form :
dbms_aqadm.schedule_propagation

(queue_name => ‘DB1_queue’

,destination_queue => ‘DB3_schema.DB3_queue’

,destination => ‘connect_to_DB3.world’

,start_time => sysdate –SYSDATE indicate immediate

,duration => null –propagation until stopped

,latency => 0); –Indicates gap before propagating

So the way it should work normally is data comes into the DB1_queue and gets auto dequeued and sent onto the DB3_queue. It wasn’t doing this automatically either hence why I selected the start dequeue option as above. It seems to be set up the same as the DB2_queue which is working fine.

I am not sure if this is enough information ? Any help would be much appreciated.

Thx

as above. Typin minimum 20 characters.