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
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:
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
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.
Below is an easy to understand comparison between the two logging methods.
| Type | Online backups | Table space backups | Recover to any point in time | Automatic log file management | Performance | Maintenance |
| Circular Logging | No | No | No | Yes | High | Less compared to circular |
| Archive Logging | Yes | Yes | Yes | No | Reduced than circular | More compared to circular |
