During a savepoint, the database system writes the data that was changed since the last savepoint from the data cache (working memory) to the data area (permanent storage). The data written during a savepoint always represents a consistent state of the database. You can always restart the database, for example after a power outage, to a consistent state without importing log backups.
How Does the Database System Write the Data?
During a savepoint the database system does not overwrite the pages written to the data area at the previous savepoint, but instead writes the changed data to free positions. This is because the database system requires this information from the previous savepoint for a restart if there is a system failure during the new savepoint. Only after the database system has successfully completed the new savepoint does it release the pages of the old savepoint in the data area for overwriting.
When Does the Database System Carry out Savepoints?
In the ONLINE operational state and during restarts, the database system automatically carries out savepoints if the following conditions are met:
The database system has filled 2/3 of the log area with redo log entries since the last savepoint.
The database system has performed 5000 log I/O operations, and since the last savepoint at least the amount of time defined in the support database parameter MaxSavepointTimeInterval has elapsed.
Note that if you increase the value for MaxSavepointTimeInterval, you reduce the number of savepoints and thus the workload, but this also slows down the restart after a system outage (see Database Administration, Support Database Parameters).
Various database activities, such as starting a data backup, implicitly request a savepoint. All savepoints and the actions that were triggered by them are logged by the database system in the kernel log file.
Process Flow of a Savepoint
The savepoint is controlled by the database system using a server task ( Savepoint Coordinator). It runs as follows:
The system writes all modified permanent data pages from the data cache to the data area.
The system waits for all operations to end that need to synchronize with the savepoint, for example inserting a data record into a B* tree. The database system does not permit new operations of this type.
The system does not permit any transactions to be started or finished.
During a savepoint during normal database operation:
The system writes a redo log entry. This indicates from which point in time the system has to import the log entries, if a later restart has to be performed using the data from this savepoint.
During a savepoint during a restart:
The system determines the current position of the log reader and enters this in the restart information.
The system stores information about the transactions that are not yet complete in data pages, so that it is available for repeating or rolling back the transactions in the course of a restart.
The system imports the history information to the data cache.
The system marks all permanent data pages modified since step 1 in the data cache.
The system permits again all operations listed in steps 2 and 3.
Any changes carried out by the database users from this point in time no longer affect the data marked in step 7. The system can only write these changes to the data area once the current savepoint is completed.
The system writes the data pages marked in step 7 to the data area. In addition to the changes that were already completed with a COMMIT, this also includes the undo log files of transactions that are not complete, which are required by the system in the case of a restart.
The system writes all modified pages of the internal file directory to the data area.
The system writes all modified converter pages to the data area.
The system updates the restart information, which contains, among others, the savepoint version (converter version).
This completes the actual savepoint.
For more information, see Database Manager CLI, db_restartinfo.
Now the system releases for overwriting all the pages in the data area that are no longer needed.
The system wakes up those tasks that had requested or waited for a savepoint.
If the savepoint was triggered during the transfer to the OFFLINE operational state, the system now stops the database kernel.