I hope that this is not a daft question -
We are working on a new data warehousing / reporting solution and basically we want to purge data on an Oracle 10g (10.4.0.2) environment which uses Streams to replicate certain tables to a data warehousing server which is also on an Oracle 10g (10.4.0.2)environment but we do not want the purge to be replicated onto the data warehouse system. (This will be purged seperately at a later date)
A suggestion was made that on the originating database (capture side), we could carry out the purge using a defined user, which then could be set up in Streams to ignore any LCR's which come from this user, thus not being played on the apply side (data warehouse). Apparently Streams uses the Oracle LogMiner to collect all records recorded in the redo logs and then extracts the LCR's which apply to the rules configured, this does not record which user has made a change to the data of the table(s) in question, thus resulting in all changes being replicated.
The other possibility is to modify the capture process to exclude deletes on all tables prior to the purge and then set them back once the purge has completed and the Streams is fully up to date. I would have thought that this method could cause problems if another process tries to delete something on a table which is supposed to be replicated in the data warehouse database whilst in the process of doing the purge, thus leaving inconsistencies between the two sides.
Has anybody come accross this type of problem before or can anybody offer any help?
Thanks
We are working on a new data warehousing / reporting solution and basically we want to purge data on an Oracle 10g (10.4.0.2) environment which uses Streams to replicate certain tables to a data warehousing server which is also on an Oracle 10g (10.4.0.2)environment but we do not want the purge to be replicated onto the data warehouse system. (This will be purged seperately at a later date)
A suggestion was made that on the originating database (capture side), we could carry out the purge using a defined user, which then could be set up in Streams to ignore any LCR's which come from this user, thus not being played on the apply side (data warehouse). Apparently Streams uses the Oracle LogMiner to collect all records recorded in the redo logs and then extracts the LCR's which apply to the rules configured, this does not record which user has made a change to the data of the table(s) in question, thus resulting in all changes being replicated.
The other possibility is to modify the capture process to exclude deletes on all tables prior to the purge and then set them back once the purge has completed and the Streams is fully up to date. I would have thought that this method could cause problems if another process tries to delete something on a table which is supposed to be replicated in the data warehouse database whilst in the process of doing the purge, thus leaving inconsistencies between the two sides.
Has anybody come accross this type of problem before or can anybody offer any help?
Thanks
