Oracle databases form the back ends of business-critical applications such as ERPs, supply chain systems, CRMs, and more — applications where database processing performance is critical. Additionally, the data in these databases is critical for AI, analytics, and reporting. With Fivetran’s binary log reader for Oracle, you can enable your business to run all analytic and AI workloads while satisfying performance and security requirements.
From a performance perspective, the least intrusive way to perform change data capture (CDC) from a transaction processing database is to parse the transaction log contents. However, it is by far the most complex approach and difficult to implement with custom pipeline scripting. By contrast, Fivetran’s binary log reader for Oracle enables customers to automatically replicate changes from Oracle with both minimal overhead and no noticeable impact on database processing.
[CTA_MODULE]
Harnessing the power of database transaction logs
Most transactional databases use a transaction log to journal inserts, updates, and deletes. In case of any failure or crash, the transaction log is the foundation to recover the database to its most recently committed transaction and resume processing. Mature database technologies like Oracle can recover from virtually any crash thanks to the transaction log.
Log-based CDC uses the transaction log because transaction logs are guaranteed to contain the changes that have occurred on the database, allowing users to replicate these changes downstream.
Query tools vs. APIs vs. binary logs
Database technologies often offer APIs or features to leverage log-based CDC.
For example, Oracle’s LogMiner enables querying database changes. However, LogMiner does not support all database features and is quite a heavy-weight database operation, potentially impacting database processing and performance. Oracle also provides a high-volume alternative, xStream API, supporting more database features, but the use of xStream API requires an additional GoldenGate license and usage fees.
Given the downsides of either option, Fivetran decided to build our own binary log parser. We pull the transaction log and parse DML/DDL changes based on the database object metadata that the Fivetran user has access to read.
Fivetran acquired HVR in October 2021 in part to leverage HVR’s log parsing capability for database technologies, including Oracle. We applied the same general principles when designing a new Oracle connector while rewriting and optimizing code for even better throughput and performance.
Supplemental logging
Long a key feature of Oracle databases, supplemental logging is required by many log-based replication technologies, including Oracle’s GoldenGate and (historically) Fivetran.
What is supplemental logging?
By default, a database logs the bare minimum information to enable database recovery while maximizing database processing performance. For updates, this means only changed columns are logged, along with an internal reference to the row. Logical replication, especially the heterogeneous type (i.e., with a different technology on the target side), requires a reference to the row identifier, the primary key.
The concept of adding additional data to the transaction log is commonly called supplemental logging (e.g., by Oracle and IBM). Log-based database replication technologies require you to enable supplemental logging on the tables you replicate.
Are there downsides to supplemental logging?
Expert opinions vary on whether adding supplemental logging adds overhead, as the addition of supplemental logging will increase transaction log volume, and undo generation will be higher. However, database systems that are not IO-bound may not notice the difference.
Does supplemental logging have limitations?
The ability to add supplemental logging is an Oracle database feature. However, with the introduction of table and column names exceeding 30 characters in Oracle 12.2, supplemental logging on any such tables requires a GoldenGate license.
What is Fivetran’s binary log reader requirement?
Fivetran’s binary log reader for Oracle only requires minimal database-level supplemental logging. For Fivetran, this is sufficient to capture all database changes, including DML on tables with a name or column names exceeding 30 characters.
Note that we avoid any possible overhead of adding table-level supplemental logging.
Fivetran’s Oracle binary log reader solves a hard problem
Fivetran’s mission is to make access to data as simple and reliable as electricity. We take care of the complexities of data replication. You focus on leveraging consolidated data in the destination.
The Oracle binary log reader is a testament to our mission. It provides:
- High volume CDC
- No impact on source database processing
- Support for many database features - more as we continue to enhance the connector
If you run Oracle-based applications and need access to data in a data warehouse or data lake, try our binary log reader.
[CTA_MODULE]
