Database: 8.1.7
OS: RedHat Linux 7.2

One of our clients needs a replication solution.

The current environment is large enough for a snapshot replication. There are
around 90 tables which will be replicated and these tables are in four
different schemas. Some of these tables are partitioned and have 1.5 to 2 GB of
data. No special datatypes like LOB, BLOB or even LONG are used in these. It is
a central database and data constantly arrives from around the country and
batch processing keeps on happening, with the highest number of inserts and
deletes occurring between 6 PM and 10 PM.

Earlier this client had tried replication themselves but faced certain errors.
Some details of their earlier try are below. There were two nodes one master
and one snapshot. Trusted Model was used in a way that only REPADMIN and
SNAPADMIN were created at master and snapshot site respectively. Every thing
was done manually and replication manager wasn’t used. There was only one
refresh group which had around 90 snapshots for 90 diff tables and the queries
used were simple select * statements for each table. The refresh interval was
set as 30 minutes. Snapshots were not updateable and “fast refresh” was used.

These are the problems which they faced. Initially replication slowed the
system greatly but that was expected as it is an overhead. Now however the
problem is that the refresh time is greater than the interval and as this is a
24-7 database with new updates continuously flowing in, and as the refresh time
is greater than the interval, when the next batch process arrives the links
break resulting in a crash of the replication mechanism. Usually
inserts/deletes would take more than 30 minutes and the next request comes in
the mean time which will break the database links.

Since I am gona try replication implementation once again, I want to ask what
choices do I have for replication. Should i go for procedural replication or
simple replication? Is there a certain way in which the snapshot queries should
be written, instead of simple select * statements. Kindly suggest!