Similarities and Differences between Oracle and SQL Transaction Logs
In preparation for a presentation session about transaction log fundamentals at a past Silicon Valley SQL Saturday event in Mountain View, I looked into understanding more detail about the SQL Server transaction log.
To date, I have been more familiar with Oracle’s transaction log concepts and implementation. Between SQL Server and Oracle, there are some fundamental differences that I am discussing in this blog post.
Before diving into the differences, let’s talk about similarities.
- Of course, both in SQL Server and in Oracle the transaction log is used to maintain transactional consistency and to ensure the durability of the system. (I.e. if the database crashes then the transaction log is used to get the system back to the most recent committed state or to perform a point in time recovery.)
- Both in SQL Server and in Oracle the transaction log is sequentially written, and overwritten all the time.
- And both in SQL Server and Oracle the transaction log contains all information required to perform log-based change data capture to support transactional data replication.
But there are also a few differences in implementation. Some of the more striking ones:
1. Log groups
- SQL Server does not use the concept of a log group. There is a transaction log (typically one, although more can be configured) that consists of a single file (although multiple files can be configured. e.g. if a file on another volume must be added to accommodate growth).
- An Oracle Database, on the other hand, has at least two transaction log groups (redo log groups). Every group has at least one file in it. As the database processes transactions it will switch from one file to the next until all files are filled, and then (typically) go back to the first file and overwrite it.
2. Transaction log size
- A SQL Server transaction log does not have a static size. In SQL Server so-called “log truncation” is an important concept to allow the log to be overwritten. Log truncation happens automatically in the simple recovery model, and after a log backup in the full recovery model. If the log has not been truncated, but all space is used, then it will start growing. There are multiple reasons why that can happen (e.g. there are long-running transactions or replication has been set up and some data still has to be sent to the target.) Because a transaction log in SQL Server may grow, it can also be shrunk by the DBA.
- In Oracle, the transaction redo log files never grow automatically, and long-running transactions do not prevent transaction log files from being overwritten. Production Oracle Databases typically run in archive log mode, and a separate archiver process archives redo log files that have been filled up while the database is writing to another redo log file. To change the size of the redo logs on Oracle, the DBA has to add new groups and delete existing groups (after a (forced if needed) log switch). This process can be done with no downtime on the database.
- As a simple result of this difference, you are more likely to see a very long-running transaction in an Oracle Database than in a SQL Server database.
- SQL Server’s data replication solution, as well as some third-party data replication technologies, will take control over log truncation to ensure the SQL transaction log that is still required for replication is not removed. As a result, you will not lose any data (not even when running in a simple recovery mode assuming the system does not crash unrecoverably), but log truncation will be prevented if for whatever reason the data cannot be replicated. This can happen, for example, the target system crashed or if the network connection is unavailable. The unfortunate side effect will then be log file growth until it can no longer grow and the database stops processing transactions.
- On SQL Server by default HVR will take over the log truncation to ensure no data is lost. However there can only ever be one process controlling transaction log truncation per database, to coexist with other replication technologies or to set up multiple database capture operations out of a single SQL Server database, HVR can be configured to not manage the transaction log truncation. In that case, it will transparently switch between backed-up transaction logs and the current log as needed.
- In Oracle transaction redo log files can always be overwritten if they have been archived, which, on a well-configured system, happens long before the redo log file has to be overwritten. The replication technology would switch to the archived logs transparently if the data is no longer in the online redo logs. This is what HVR and other real-time data replication solutions – including Oracle’s own – do.