Oracle Database Capture Methods
Question
How does HVR capture changes from Oracle databases?
Environment
- HVR 6
- Source: Oracle
Answer
HVR implemented its own log reader for Oracle. It provides additional options for capturing changes from the Oracle Database. The HVR downloads provide three options for log-based CDC from Oracle:
Direct
The direct method requires direct access to the transaction log files (redo and archived logs). HVR reads the files to parse relevant transaction data for replication. The direct method is applicable to many different deployment patterns, including:
- CDC from the primary database, clustered (using RAC) or not, with or without the use of pluggable databases
- Using Oracle's ASM (Automatic Storage Manager)
- Performing CDC on a physical standby database (using data guard) to offload the work to a standby database
- Running in archive log only mode, with CDC based solely on access to the archived logs to offload the work to another server without needing to duplicate the database
The direct method delivers the best possible performance for Oracle log-based CDC. Learn more on our Capture From Oracle Using Direct Redo Access page.
BFILE
BFILE allows for the use of the direct log method remotely (on a different server). Some use cases, for example using AWS RDS Oracle, don't allow direct access to the transaction logs. Using BFILE allows access to the logs through a database directory object. With CDC running remotely from the database, there is additional latency to retrieve log fragments, and therefore slower performance compared to using the direct method (depending on the setup). Find more information on setup and limitations on our Capture From Oracle Using BFile page.
SQL
HVR's SQL method leverages the Oracle Database's built-in LogMiner capability to perform log-based CDC, similar to Fivetran's method. You can use this method when CDC software cannot run on the database server and no other alternative options are available (for example, due to limited privileges or access to database). Relative to the direct and BFILE methods, the SQL method maxes out at the lowest volumes (single digit GBs of redo generation per hour). Learn more about the SQL method on our Capture From Oracle Using LogMiner page.
Context
Oracle is a very mature database technology. It continues to be used to host many organizations' most mission-critical databases. Because of its traditional market-leading position, the Oracle Database has also commonly been used storage for packaged applications' data.
To avoid impact on transactions, data replication technologies (including Fivetran) implement log-based Change Data Capture (CDC). Log-based CDC reads the changes from the transaction logs asynchronously based on the transaction making the change (and has no direct impact on the transactions). Learn more about CDC in the Fivetran blog's Change data capture: Definition, benefits, and how to use it article.
Oracle provides native capabilities for log-based CDC from the Oracle Database. However, to implement end-to-end replication in a heterogeneous setup, you must either build your own solution or license Oracle's GoldenGate technology.