Database logging mechanisms

All databases have logs associated with them which keep a record of changes to the database. Lets consider IBM DB2 database for understanding the database logging techniques.

 A database that uses archival logging can be backed up online. To reach a specified point in time, you can perform a rollforward recovery. A database that uses archival logging is therefore also called recoverable.There is another type of logging , Circular logging which keeps all restart data in a ring of log files. It starts logging in first file in the ring, then moves on to the next, and so on, until all the files are full. Circular logging overwrites and reuses the first log file after the data it contains has been written to the database. This continues as long as the product is in use, and has the advantage that you never run out of log files.

In circular logging only full backups of the database are allowed while the database is offline. The database must be offline (inaccessible to users) when a full backup is taken. So consider the case of a real time database which needs to be backed up and uses circular logging. For example a telecommunication company will want to log all details of a user's call details.A call detail record contains details of a telecommunication transaction, such as:

1. Phone number of the calling party
2. Phone number of the called party
3. Call start time and date
4. Call duration
5. Identification of the telephone exchange or equipment writing the record
6. A unique sequence number identifying the record
7. Additional digits on the called number used to route or charge the call
8. Call type (voice, SMS, etc.)
9. Any fault condition encountered
Now suppose a database backup is to be done for which it needs to stopped or taken to an offline mode. However users will continue to make or receive calls. We as mobile users are not aware when our service provider will take a database backup. Hence if circular logging is used , backups lose any incoming data while the backup operation is in progress.

Archive logging is exactly opposite to circular logging. Online, Incremental and Delta backups are supported only if the database is configured for archive logging. All activities against the database are logged during an online backup. After an online backup is complete, the database manager forces the currently active log to be closed, and as a result, it will be archived. As a result of this, online backup has a complete set of archived logs available for recovery. In simple terms:

Archive Logging = Serial Logging ( no overwrite of log files as in circular logging )

When an online backup image is restored, the logs must be rolled forward at least to the point in time at which the backup operation completed. Circular logging can recover data only to a specific point in time at which full backup was taken, it is also known as version recovery. Archive logging can recover data to any point in time hence also known as full recovery or rollforward recovery

The advantage of choosing archive logging is that recovery tools can use both archived logs and active logs to restore a database either to the end of the logs, or to a specific point in time. The advantage of using circular logging is that you never run out of log files or storage space issues.
Below is an easy to understand comparison between the two logging methods.

TypeOnline backupsTable space backupsRecover to any point in timeAutomatic log file managementPerformanceMaintenance
Circular LoggingNoNoNoYesHighLess compared to circular
Archive LoggingYesYesYesNoReduced than circularMore compared to circular