Heard you’re fighting for Supplemental Logs again?

‍

Once upon a time, on-premise databases were king! Back then you could do anything you wanted, provided you had the green light from the mighty keepers of the Oracle: the DBAs. If you have recently tried to extend a gentle hand to your on-premise database to invite its valuable content into the modern world, it is likely that your gracious palm was promptly slapped away and dismissed. You are not alone.

‍

A bit of context

Databases have logs and logs are good. Logs allow you to instantly communicate what’s happening on your database, restore from a point in time, replicate and many more things.

In the Oracle world, the basic logs from the Redo Logs are very partial and usually only contain a fraction of the information needed to exploit a record. They generally only contain the actual change between the previous state of a record and the new one. Assuming a table with multiple columns, and our user 1 just got a birthday, a basic log could look like this:

This is sufficient for a database to reconstruct a state from an entire log history but definitely not for any other system to actually use - since they would need to hold a copy of the whole log history to actually get the latest missing information on user 1. Fortunately there is a feature for that: Supplemental Logs. Supplemental Logs, adds the missing context to logs so they can be exploited statelessly, that is without the need to keep the whole history before. Basically, a supplemented log would look something like this:

But why?

Now the whole context is here, and you can go wish Alice at 101 a happy birthday without the headache of having to figure out whose birthday it is. There are a great many use-cases for having Supplemental Logs and here are just a few:

1. Data Replication: In environments where data must be replicated to another system, supplemental logging ensures that enough information is available in the redo logs to accurately replicate the changes. This can be particularly important when using tools like Oracle GoldenGate or other replicators for data replication.

2. Reduce Stress on System Resources: by enabling users to work with logs you greatly reduce the stress on system resources since large bulk transfers can now be broken down in tiny increments and no longer require large database scans.

3. Integration with First-party Services: In order to integrate a monolithic component into a microservice universe, it needs to emit events that make sense to any consumer service. Supplemental Logs enable that.

4. Integration with Third-party Tools: many third-party tools for tasks like data integration, replication, or analysis require supplemental logging to function. In fact a large chunk of the modern data stack is inaccessible as long as Supplemental Logs are not enabled.

5. Cost Savings: Supplemental Logs are mandatory for stateless data replication which tends to bring orders of magnitude in savings versus stateful alternatives (for instance Extract-based pipelines like ETL or ELT).

6. Recovery: While Oracle's default Redo Logs provides enough information to recover the database to a consistent state, supplemental logging provides more detailed information about changes. This can be valuable when fine-grained recovery or analysis of changes is required.

7. Support for Logical Standby Databases: In a Data Guard environment, a logical standby database keeps the data in sync with the primary database by applying SQL statements. Supplemental logging ensures that the necessary information is available to construct these SQL statements.

8. Auditing and Compliance: Some regulatory requirements might necessitate tracking changes to specific data within the database. Supplemental Logs can be vital for both internal auditing and for demonstrating compliance with external regulatory requirements. Here are a few examples:‍

- GDPR & CCPA: In the context of GDPR, tracking changes to personal data can be important for compliance with various provisions, such as the right to be forgotten or data portability. Supplemental logging can help in tracking these changes accurately.

- HIPAA: In healthcare environments, HIPAA requires stringent control and auditing of access to personal health information. Supplemental logging can provide additional detail about changes to this sensitive data.

- IFRS, SOX, Basel III, All of these impose strict auditing and reporting requirements of which detailed tacking of changes to financial data is a critical component. Supplemental Logging can help meet ensure that every change is properly captured.

- PCI DSS: For organizations that handle credit card data, PCI DSS imposes requirements for tracking and auditing access to this data. Supplemental logging could be used to capture detailed information about changes to credit card or transaction data.

On paper, it’s a done deal. But…

‍

So where's the issue?

If you have tried getting Supplemental Logs enabled on an existing Oracle Database, you have likely faced a very dreadful series of pushbacks from the dreaded DBAs.

‍

Supplemental logging in Oracle can be a double-edged sword. While it offers many advantages, particularly in the context of cost-savvy integration, compliance, operations, and detailed change tracking, it can also introduce certain challenges and risks. Here's an overview of common push-backs from DBAs, along with a little perspective to those arguments:

‍

Performance Impact

Supplemental logging can put additional load on the system by writing more information to the redo logs. This can slow down transactions and impact overall database performance. Performance impact is hard to accurately estimate without actual testing, so the best way to get an order of magnitude is to test. Everything else is speculation paralysis. Here’s a good way to benchmark impact:

1. Select a table that has a fairly predictable level of usage at specific times

2. Observe CPU usage, Average Transaction Response Time, and I/O Throughput over the period. You can add any other metric you could want to assess for performance.

3. Enable supplemental logging on the chosen table

4. Observe the selected metrics over the same time period, the usage pattern shouldn’t differ too much between both observation periods or it will introduce bias.

5. Compare and decide, do you feel comfortable with the numbers you observed?

💡 Perspective: The performance impact is typically minimal and can be managed with proper sizing, tuning, and configuration. It can also be greatly mitigated by enabling Supplemental Logs only on specific tables within the target database. That, btw, is a good way to start: one table at a time while monitoring for traces of performance impact.

‍

Increased Storage

Supplemental logging can linearly increase the size of the Redo Log files, leading to higher storage requirements. This can result in increased costs. The actual storage impact can be estimated using the formula below. Once again it is an estimate but carefully selected input should give you a good idea of the extra storage size.

Storage Impact (GB) = Number of Transactions per Second * Size of Supplemental Log per Transaction (KB) * Retention Time (Seconds) / 1024  / 1024

💡 Perspective: While this is a valid concern, storage doesn’t impact overall performance and is a very predictable component that can be monitored at a granular level to ensure it doesn’t get uncomfortable. The cost argument of increased storage can be met with the compute savings from processing incomplete logs and subsequent ETL costs - as well as opportunity cost from blocked projects.

‍

Complexity (Configuration, Management…)

Setting up and managing supplemental logging can be complex, requiring careful configuration and ongoing monitoring to ensure that it meets the specific needs without overloading the system.

💡 Perspective: Performance monitoring should already be in place but dedicated monitoring on log size isn’t always. The feared complexity itself can be managed by reading the documentation, proper testing, and adherence to best practices from the Oracle doc. DBAs are already experts on the topic.

‍

4. Potential Security Risks

If not properly secured, supplemental logging might expose sensitive data, as it could include details that are typically not logged. This could result in a security risk if the logs were to be accessed by unauthorized users.

💡 Perspective: Ensuring that only authorized personnel have access to the logs and that they are stored securely can address this concern. If anything, this is a compliance argument, access policies and security measures should already be in place for compliance reasons. If that’s not the case, it may be worth an audit. It’s also worth reviewing what data that wasn’t logged before will now be logged as there may be an existing recovery risk on those datapoints.

‍

In the end the decision to enable supplemental logging can only be achieved with the collaboration between DBAs. The support from other stakeholders, particularly top-down buy in can help ensure that the right decision is made.

‍

Real-time and Cost Efficient Oracle Replication with Popsink

Looking for real-time and cost efficient replication from Oracle sources? Head out to this page for more on easily setting up a live sync from Oracle with Popsink.