I need to have separate database for reporting reasons (staging area) which should be a copy of primary database.
I’m taking into consideration Asynchronous Change Data Capture Oracle feature. Database size will be around several TBs. I am afraid about performance issues during moving data between databases.
I will appreciate if anyone can help me understand pros and cons for using asynchronous CDC or maybe I should think about something else (Data Guard?).
Thank you in advance,
Considering the data size, and more over if transaction rate is also high, I would highly recommend Data Guard. Replication method will give you operational overhead to maintain it. If you use Oracle 11g, there is Active Standby option (separate license). This works if your staging environment has the same OS platform and version as your production.
Additionally, if your production instance can afford a few minute downtime, I highly recommend this method as my top recommendation: use disk array mirroring, alter database begin backup and suspend, break the mirror or split volume group, mount volume group to staging server, recreate controlfile if necessary and startup your staging/reporting instance.
I am using 10gR2 and not able to accept any downtime. Could you please elaborate more why I should use DataGuard instead of CDC (asynchronous mode)?
According to my understanding both method (CDC async and DG) use redo logs movement…
The point is that CDC can be used under a standard license when DG needs a separate one.
CDC will replicate data/change in a more high-level, "logical" way than physical DB. Based on my experience with logical data replication, it is more vulnerable to system interruption and require relatively higher operational overhead (human interviention). That is why with considering your estimated high transaction rate, I recommend physical data guard. And even I recommend a lower-level replication at storage-array level if your system can afford minimal downtime (which is not in this case).