SAP HANA Backup & Recovery Strategy
During normal database operation, data is automatically saved from memory to disk at regular save points.
Additionally, all data changes are recorded in the redo log. The redo log is saved from memory to disk with each committed database transaction. If there is a power failure, the database can be restarted like any disk-based database, and it returns to its last consistent state by replaying
the redo log since the last save-point.
While save-points and log writing protect your data against power failures, save-points do not help if the storage itself is damaged. To protect against data loss due to disk failures, backups are required. Backups save the payload (the actual data) of the data area and log area to different locations. Backups are performed while the database is running.
The backup is taken using the built-in database backup feature of SAP HANA DB.
Backup is taken like below
Complete Data (Database Full Backup)
- Data in SAP HANA is constantly written into disk from memory at save-points. Data-backup can be performed with database being online
- SQL users require the system privilege BACKUP ADMIN or BACKUP OPERATOR to perform a data backup. If you execute data backups using SAP HANA studio, the privilege CATALOG READ is also needed. It is not recommended to use the SYSTEM user for normal database operation. Instead, we recommend that you create dedicated administration users with only the privileges necessary for backup and recovery.
- The recovery process can only be started by the operating system user <sid>adm.
- Data and log areas can only be backed up when the SAP HANA database is online. During data and log backup, the system is available as usual.
- All the configured services must be running such as indexserver, statisticsserver, etc.
- Backup can be executed using an arbitrary SQL interface like hdbsql. In addition SAP HANA database provides with the SAP HANA studio and the web-based SAP HANA cockpit two powerful UIs to fulfil that task. In addition DBA cockpit (ABAP stack only) can be used too.
- Backup and recovery always applies to the whole database. It is not possible to back up or recover individual database objects.
By default, data backups are written to the following destination: $DIR_INSTANCE/backup/data. You can specify a different destination when you perform the backup. Alternatively, you can change the default backup destination in the Backup section in SAP HANA studio.
To change the default destination for data backups:
1. In SAP HANA studio, go to Backup.
2. Go to the Configuration tab and choose Data Backup Settings.
3. Specify the new default destination in the Destination field.
Different data backups can be written to different destinations, but all the parts of one particular data backup are written to the same destination.
By default, log backups are written to the following destination: $DIR_INSTANCE/backup/log. You can change the backup destination in the Backup editor in SAP HANA studio.
To change the default destination for log backups:
1. In SAP HANA studio, go to Backup.
2. Go to the Configuration tab and choose Log Backup Settings.
3. Specify the new default destination in the Destination field.
In general changes to the data or log backup destinations take effect immediately.
Each backup name comprises the following elements: <path><prefix>
If no complete path is specified, the default backup destination is prepended to the backup name. For file-based backups, the backups are written to a destination in the file system. For backups made using third-party backup tools, a pipe is created in the file system. The pipe is always created in the directory /usr/sap/<SID>/SYS/global/hdb/backint. The backup tool decides where the backups are actually written.
You can use the prefix proposed by the system or you can specify a different prefix for the backup name. During backup, each service backs up its data to the specified backup destination.
Below is an example of a set of backup files from one data backup created with SAP HANA studio. The backups can have different names.
/backup/data/COMPLETE_DATA_BACKUP_databackup_0_1 (name server topology)
/backup/data/COMPLETE_DATA_BACKUP_databackup_1_1 (name server)
/backup/data/COMPLETE_DATA_BACKUP_databackup_2_1 (for example, index server)…
In the above example, the <path> is /backup/data/, the <prefix> is COMPLETE_DATA_BACKUP. databackup_0_1 is the suffix, which is automatically added by the system.
- To estimate the size of a data backup, table M_CONVERTER_STATISTICS. This system table contains the number of currently used blocks. To estimate the size of the next complete data backup, you can use either of the following SQL commands:
.01 select sum(allocated_page_size) from M_CONVERTER_STATISTICS
- The backup catalog provides information about the execution of backups, recoveries and their history. It enables the system to determine whether a recovery is possible and to choose which data and log backup to use to recover the database.
- The monitoring views M_BACKUP_CATALOG and M_BACKUP_CATALOG_FILES can be used to display information about the backup catalog.
- The system privilege CATALOG READ is needed for this.
- After the initial load has finished successfully, a data backup is strongly recommended.
- It is recommended to perform a backup (including configuration files) before the database software is upgraded to a new version.
- If the software upgrade fails, the database can then be recovered with no data loss.
- After adding or removing a host or a service, a data backup is recommended.
To ensure that you can recover your database to the most recent point in time without needing to replay a large number of log backups, you should perform data backups at regular intervals.
Archiving of log backups should be part of your overall backup strategy.
The configuration files are not backed up as part of the database backup.
Configuration files that contain customer-specific changes can be backed up manually in order to more easily identify and restore customer-specific changes in a recovery situation.
Configuration files that contain customer-specific changes are located by default in the following directories:
For global configuration settings:
For host-specific configuration settings:
Configuration files are only created in these directories if customer-specific changes are made to them after installation.
If no customer-specific changes have been made, these directories may be empty.
Automatic log backup can be enabled or disabled using parameter enable_auto_log_backup. By default, automatic log backup is enabled.
During normal system operation (log mode normal), it is recommended that automatic log backup be kept activated.
If automatic log backup is disabled, the log area grows until the file system is full. If the file system is full, the database will freeze until the situation is resolved.
After changing the log mode parameter, it is strongly recommended to create a full data backup of the database. If you use a HANA revision older than revision 90 you have to restart the database to activate the changes.
This parameter can have the following values:
log_mode = normal (default)
Log segments are automatically backed up if automatic log backup is enabled.
log_mode = normal is recommended to provide support for point-in-time recovery.
log_mode = overwrite
Log segments are freed by savepoints and no log backup is performed. This can be useful for test installations that do not need to be backed up or recovered.
log_mode = overwrite is not recommended for production systems. No point-in-time recovery is possible. For recovery, only data backups are used; the logs must be disregarded.
Log Backup Timeout
You can specify a timeout for log backups. The timeout determines the intervals at which the logs are backed up. If the log segments become full before the timeout, the logs will be backed up anyway. If the timeout exceeds but the log segment contains no commit entry, the log segment will not be backed up at this point in time. The log backup timeout is controlled by the parameter log_backup_timeout_s.
Specifying an appropriate time interval for log backups enables you to recover a SAP HANA database with minimum data loss. For example, if you need to recover the database in a situation where the log area is unusable, and only the data and log backups are available.
Recommended: Specify a time interval. For example 900s.
If you specify a timeout 0, the log backups are only made when a log segment is full and when services are restarted.
Note: The backup timeout only takes effect if automatic log backups are enabled.
Do not delete log segments on operating system level, as the log area will become unusable and the database may stop working immediately.
SAP HANA RECOVERY:
When either data or log area becomes unusable because of hardware failure or some other issues.For recovering SAP HANA database one has to recover using the last available data backup and by replaying the log backup
When some logical error occurs ,which requires the database to be recovered to point in time of failure,which requires the databack up to be recovered and log entries from the log ckup has to be replayed
Pre-prerequisites for recovery:
-> <sid>adm:user password is required to shutdown the target system where the backups have to be recovered upon
-> Similar configuration:between source and target system ,if backups are to be recovered on different target system
-> Atleast one databackup and logbackups taken before the system failure or the time to which the system needs to be recovered
Available Recovery types option with SAP HANA
Recovering the database to the most recent state:used for recovering the database to the time as close as possible to the current time . Databackup ,logbackup available since last databbackup and log area are required to perform the above type recovery
Recovering the database to the point in time :used for recovering the database to the specific point in time. . Databackup ,logbackup available since last databbackup and log area are required to perform the above type recovery
Recovering the database to specific database:Used for recovering the database to a specified data backup.specificdatabackup is required for the above type of recovery option.
Backup and recover using HANA studio
To Backup using SAP HANA Studio
From the context menu choose Backup option, choose the backup destination path and backup prefix then choose finish.
To recover using SAP HANA Studio
From the context menu choose Recovery option ,provide<SID>adm username and password and then choose the required recovery type.Then specify the location of the required backup files(databackup and logbackup).Install the license and finish the process.
Note 1:Constraints that applies with recovery of SAP HANA database are
Recovery of the backup from higher system release to lower system release is not possible.
Recovery can’t be interrupted or stopped in between ,once it is triggered
-> SAP HANA studio provides point-in-time recovery with full GUI support.
In a recovery situation, you can use SAP HANA studio to restore customer-specific changes (configuration settings) either before or at the end of the recovery. To recover your database from SAP HANA studio -> navigator view -> open context menu of the database -> choose “Recover…”. Confirm that the database can be shut down – recovery is only done when the database is offline. In the recovery wizard choose one of the recovery types:
Recover the database to its most recent state.
Recover the database to a certain point in time.
Recover the database to a specific data backup. (Just a specific data backup is used for recovery; Log entries are not replayed neither from the log backups nor from the log area.)
Caution: The SAP HANA database will be initialized with the specified data backup. This backup becomes the first backup of a new database life cycle. Older backups are not compatible with the log written after the recovery.
Recover the database to a certain log position. (Advanced option to be used in exceptional cases if a previous recovery failed.)
The SAP HANA database uses the backup catalog to identify the destination of the backups. You do not need to specify whether File or Backint was used for the backup.
- Online Revcovery is not possible; Database recovery is always a full recovery of the whole database and therefore always requires a downtime.
- SAP HANA studio shows the progress of the recovery and reports when it is finished.
- Also we can cancel a recovery while it is running. However, canceling a recovery makes the database inconsistent. To be able to work with the database, a further recovery would then be necessary
Backup or recovery problems, Below is the log/trace files location:
content of directory $SAP_RETRIEVAL_PATH/trace as minimum backup.log, backint.log (if you are using a third-party backup tool) and all *.trc files
list of the content (recursive) of your backup directories, typically $DIR_INSTANCE/backup
One thought on “SAP HANA Backup & Recovery Strategy”
Hello sap-admin Team,
I have a question.
I ran /sdf/hdb_sizing report. The report calculated 140 GB net size data.
For /hana/data I then calculated using formula the following size: 100GB (net size data) x 1,2 + 20 % = 144 GB
However, under /hana/data/HW/mnt0001 the file hdb0001.0002 is already 137 GB and the database size is 130 GB
Why was so little space calculated with the report.