Need help here.
We were ask to provide recommendation regarding the replication of our Production schema to Reporting Schema.
Here are the conditions:
1. Production schema and Reporting schema resides in one database server, only in different instance.
2. Tables to replicate is more or less 300 tables.
3. They only want to replicate changes that happen on the table on the said day.(insert,update)
4. Replication will only happen on the end of business hours(end of day).
What we have in mind is Materialized view.
What would be best to use, fast refresh or complete refresh?
What will be the effect on performance?Resources?
Or will there be any other method?
Appreciate your reply on this.
Using MV (Materialized views) is onle of the possible way how to do what you are asked for.
Fast refresh of MV is faster than complete refresh because it's based on "deltas" from last refresh. But it require MV log on table in primary DB. This MVLog consume same space in tablespace. And maintainig this MVlog during DML operation on table add some overhead to DML operation -> it's take a little bit longer time to complete.
How much space is consumend by MV log depends on many factors:
1/ number of DML changes on table
2/ number of consumers (there can be more than on MV based on same table using same MV log)
3/ period of MV refresh
Plus of using MV is that it is very simple and you don't need no special license to use them.
Other possible option is Golden gate (Streams previously)
Next option is procedural replication - you have to write your own replication mechanism.