Back it on up…..then recover it
My name is Man-Ted and I’m currently on the SAP HANA Product Support team located in Vancouver, BC. I’m writing this blog to give a look into backup and recovery.
Before getting into backing up the database lets first talk about what exactly are we backing up. Even though SAP HANA is an in-memory database there is still information written to disk as a fallback in case there is ever a failure, this is the persistence area. Inside the persistence area there is a log area and a data area.
The log area directory is defined in your global.ini under basepath_logvolumes, in this directory log segments are created when a transaction is committed. Log segments size can also be customized in the global.ini file under log_segement_size_mb.
The data area contains SQL data, undo log information, and modeling data. The data area is written to the persistence area at save point intervals, savepoint intervals are set in the global.ini under savepoint_interval_s (default value is 300) and the persistence area is basepath_datavolumes
Log backups, by default, are done automatically in the following scenarios:
- The log segment is full
- The log segment is closed after exceeding the configured time threshold
- The database is started
The log backup is a backup of the log area, once the log area segments are backed up the log area segments can be over written. In the global.ini you will want to make note of the following parameters:
· Basepath_logbackup – the location where the log backups will be stored, the default directory is $DIR_INSTANCE/backup/log
· Enable_auto_log_backup – enables automatic log backups
· Log_backup_timeout_s – by default the value is 900 seconds, when the timeout is reached a backup, if the value is 0 then it is disabled
· Log_mode – there are three log modes
o Legacy – Log segments are retained until a full data backup is performed. This mode not recommended
o Overwrite – Log segments are freed by savepoints and no log backups are performed. This made can be used for development environments, but never use in production.
o Normal – Log segments are automatically backed up. This is the default setting in SP3 and higher.
Estimate the Size of Your Backup
Prior to taking your database backup users should always ensure there is enough space on their drive, use the following queries to determine the estimated size of your backup.
This query will let you know the total size of the backup
● select sum(allocated_page_size) from M_CONVERTER_STATISTICS
This query will inform you what the size of each volume will take. The results of the query will be equivalent to the above query, but provide you with more granularity by grouping the services.
● select volume_id, sum(allocated_page_size) from M_CONVERTER_STATISTICS group by
In order to do a backup the following SYSTEM privileges
- CATALOG READ – Authorizes the user to have unfiltered read-only access to all system and monitoring views.
- BACKUP ADMIN or BACKUP OPERATOR – The difference between the two is that the BACKUP OPERATOR can only perform backups, while the BACKUP ADMIN can configure backups and delete backups.
Unlike the log backup, the data backup is not automatically backed by the system (the data backup can be scheduled by 3rd party backint tools, custom scripts, and DBA cockpit). During a backup the database is still operational. The data backup can be backed up by the following SQL
- BACKUP DATA ALL USING FILE (‘COMPLETE_DATA_BACKUP’)
Or can be done via the Backup Wizard, please following the screen caps
As you can see you can also cancel backups via the UI, but can also be canceled via the SQL editor with the following:
• BACKUP CANCEL <BACKUP_ID>
The following files are made after this backup has completed
<default backup directory>/<prefix_name>_0_1 (contains topology)
<default backup directory>/<prefix_name>_1_1 (contains name server persistency)
<default backup directory>/<prefix_name>_2_1 (normally contains index server persistency)
<default backup directory>/<prefix_name>_3_1 (normally contains statistic server persistency)
In the Backup Editor there are three tabs: Overview, Configuration, Backup Catalog
In this tab we can view the last success backup metadata and you can start and cancel a backup
Can configure backing setting here, change the data and log backup settings here as well. For the data backups you can change where the backup will go while for the log backup settings you can change the destination, interval, and if automatic backup is enabled.
Shows a history of backups and information about the backup when you highlight it
Also you can delete old backup via the Backup Catalog by right clicking on one of backups
The backup table location $(DIR_INSTANCE)/../SYS/global/hdb/metadata/BackupCatalog.xml
Clones an entire instance during operational state online (consistent state stored in the persistency of an instance). If there is an existing snapshot you will not be able to perform a backup, if you do try to create a backup while a snapshot exists you will see the following error
Could not execute ‘BACKUP DATA ALL USING FILE(‘COMPLETE_DATA_BACKUP’)’ in 6 ms 181 µs .
SAP DBTech JDBC: : general error: Backup error: The state ‘ManagerSnapshotExists’ of the BackupManager does not allow the requested operation
To create the database snapshot you would call
BACKUP DATA CREATE SNAPSHOT
To drop it up use
BACKUP DATA DROP SNAPSHOT
There is only one snapshot held at a time, for information on snapshots please refer to the SAP HANA Administration Guide.
Types of Recovery
There are 3 different types of recovery available to users
- Recover the database to its most recent state – uses the data and log backup and then will refer to the log persistence area and will replay the log
- Recover the database to the following point in time – uses the data and log backup area to bring the database back to a point in time, this could be needed if there is a logical error
- Recover the database to a specific data backup – uses on the data backup only, the log persistence area is cleared
Please note that for all of these types of recovery the database will need to be down.
How to Perform a Recovery
Note that unlike in a backup, users cannot cancel a recovery.
Select your recovery type
After this you will need to specify the data location
Lastly are the misc settings for the recovery
Once that is done a summary screen appears and you can then start by pressing “Finish”
Logs and Traces
If there are any issues with the backup and recovery please review the backup.log file. If more information is needed please review the name server trace