SAP HANA DATABASE BACKUP AND RECOVERY
With the increase in volume of the data being stored these days ,it becomes important for all the databases to have a proper backup and recovery plans.
Backup and restore is the process of copying/storing data preemptively for the specific purpose of restoring, that same data in an event such as loss of data in case hardware failure or natural disasters or accidental deletion or corruption of data can be used for restoration.
SAP HANA BACKUP
Even though SAP HANA database is a IN-Memory database ,i.e data residing in memory ,it has got its own persistency .
Data is always saved from memory to disk at save points and data changes are written into redo log files.So that in case of failure they can be used for recovery.
Data in SAP HANA is constantly written into disk from memory at savepoints. Databackup can be performed with database being online
To perform a databackup in SAP HANA, below prerequisites are essential
- DATABASE USER: Backup in SAP HANA can only be performed with a database user, so it is necessary to create or use a existing user with the necessary privileges and authorizations
- BACKUP_ADMIN: This is the system privilege that the Database user used for performing the backup should have .
- CATALOG READ: System privilege that the Database user need to have for collecting the information required by the backup wizard
Data backup can be taken manually or can be scheduled
SAP HANA database backups up its services (nameserver,nameservertopology,indexserver,xsengine and statisticserver)
Which will be stored in a specific destination mentioned in configuration file global.ini with the parameters
basepath_databackup and basepath_logbackup
The parameters pointing as below to the default valuesbasepath_databackup=$(DIR_INSTANCE)/backup/data used for databackup,
basepath_logbackup==$(DIR_INSTANCE)/backup/log respectively for logbackup
Backup can be performed with the below sql query
BACKUP DATA USING FILE ('<path><prefix>')
The backup files generated by performing the databackup of SAP HANA database will be as below
Where COMPLETE_DATA_BACKUP is the prefix(default) ,which can be changed to the required prefix while performing a data backup.
Log backup in SAP HANA is backing up of the log segments and it depends on the log mode in which the SAP HANA is configured in global.ini file
By default automatic log backup will be enabled in the global.ini file(if in normal log mode) which can be changed .
Log backup will be performed independently of databackup at the interval specifiled in global.ini file with the parameter log_backup_timeout_s.
The log backup of the redo log segment is performed when any of the below scenarios occur
- When the data change occurs and log segment is full.
- The log segment is closed after exceeding the configured time threshold.
- The database is started.
Log Modes in SAP HANA
There are 3 types of log modes in SAP HANA which influence the log backup
• Normal: In this mode ,log backup happens where the log segment is nearing full which will prevent the log segment full situation.Log backup is automatic by default which can be changed by editing the global.ini parameter enable_auto_log_backup .For point in time recovery this mode is recommended.
• Overwrite:In this mode log segments are freed upon each savepoints and no log backup happens
So no point in time recovery can be done.
• Legacy:in this mode log segments are freed, once the full database backup is performed.Log backup is not performed.
Log backup generates a backup files of the log segments at the interval which is specified in the global.ini file parameter log_backup_timeout_s.if the log segment becomes full before the log backup timeout interval ,log backup will be performed .This log backup time interval can be set only if the database is running with automatic log backup enable
The path of the logbackup will be at the location $DIR_INSTANCE/backup/log
Configuration Files Backup
Backup of the SAP Hana database configuration file
SAP Hana database configuration files are not backed up automatically as a part of database backup .So all the configuration files like global.ini,indexserver.ini,nameserver.ini,sapprofile.ini,daemon.ini and other customer-specific configuration files needs to be backed up manually.
Backup related Views and tables
Useful Views and Tables for getting backup related information
M_CONVERTER_STATISTICS:useful for estimating the space required for the backups
M_BACKUP_CATALOG: provides information for a separate catalog entry that is identified by a backup ID. This information includes the type of backup and start and completion times of backup
M_BACKUP_CATALOG_FILES.: Provides information about the backups created, and the backup destinations that are used by data and log backups.
Canceling a running backup :
We can use the below sql to cancel a running backup ,for which the backup_id of the current running backup has to be fetch from the View M_BACKUP_CATALOG wherestate_nameattriburewill be “running” and the current timestamp
SQL: cancel <backup_id>.
2. SAP HA SAP HANA RECOVERY
Recovery becomes essential when either of the following occurs
- 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
Note 2:if the license expires on recovery ,new license has to be installed.