We need to configure the oracle adapter to listen to changes on a large number of tables in one instance and replicate the same into another instance .
Do we need to build notification for each table for each type of change(insert/delete/update) or could we set up operations at the schema level ?
Also, is there any statistics available , showing the impact of the enterprise adpater on the applications running on the database ?
An early reply would be appreciated
thanks
Ritesh
I would have thought replicating data to another instance would be better done with Oracle technology. I am no Oracle expert but I believe your source instance can produce “redo” logs which the destination instance can read. The two are then very nearly “in-sync” and in the event of disaster can be quickly sorted out.
Another option you may want to consider is using Java Stored Procedures. I have used these to publish webMethods events to an Enterprise Broker. You will need to consider performance and scalability of the solution.
Performance of the Oracle adapter in doing notification is affected by the polling interval, my guess is that you could easily severely affect the performance of your Oracle box.
Just some thoughts…
Regards
Geoff
If you want to use the automatic notification feature of the adapter, you will have to configure three notification operations for each monitored table - one for DELETE, one for INSERT, and one for UPDATE. I do not recommend going this route, because the adapter will not process notifications in historical order across multiple operations. I.e. if you do an INSERT followed by an UPDATE on the same row, you can receive the update notification before the insert notification.
The alternative is to use basic notification, with three handwritten triggers that all insert into a common buffer table. One additional column for the operation type should be included. That could be a CHAR(1) column, set to ‘D’, ‘I’, or ‘U’, for DELETE, INSERT, or UPDATE. On the receiving side, check this value first, and then branch to an appropriate operation.