Push replication rather than pull?

[dlcarden]

I have a snapshot that is to be placed outside our firewall, with the master site on our internal network. The way replication seems to work, the snapshot side make a request to the master site, initiating the refresh. My security nazis are freaking out about this and refuse to let that happen. Can replication be set up such that the master site initiates the communication?

 

[carp]

If you can use database links to get from the master to the slave (and this should be no problem), then you can set up triggers that will update the remote table(s) or schedule a job on your master database that refreshes the remote table.

 

[dlcarden]

Database links are no problem as they are required to set up the snapshot. But I don't know what kind of job to trigger from the master side. I can use the refresh package to do a complete refresh but this unacceptable - I can only do fast refreshes (complete refreshes truncate the table and load all the data - I can't have the down time). Are there other ways to force a fast refresh that can be initiated outside of the normal snapshot requesting it?

 

[carp]

I would think that you should be able to set up a trigger on the master table. Then any time there is an insert, update, or delete, the :new values could be used to perform an identical insert/update/delete on the remote table.<br>
You would have to be careful about handling rollbacks on the master table. <br>
<br>
One alternative would be to have the trigger save the changes to a local table along with a flag to indicate insert/update/delete. Then create a procedure to read the table and perform the appropriate actions on the remote table. A job could then be submitted to run the procedure at whatever frequency you want the refresh to happen.<br>
<br>

 

[BabuSathyanarayana]

Snapshots have been designed to work for a pull rather than a push. If you go away from using snapshots, you will have to build something equivalent to the Oracle supplied 'snapshot'package of your own that will push rather than push. Depending on the sophistication of the requirements you want this to be a SQL script file or a pl/sql package that is scheduled to run at regular intervals. Alternatively you can invoke the pushes via triggers on the source tables.