Skip to Content

Hi Everybody!

 

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.

 

Persistence Area

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.

 

Log 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.

 

Data Area

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

/wp-content/uploads/2014/02/1_393228.png

Log backup

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

Legacy – Log segments are retained until a full data backup is performed. This mode not recommended

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.

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

volume_id

 

 

Authorizations

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.

 

 

Data backup

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

/wp-content/uploads/2014/02/2_393279.png

/wp-content/uploads/2014/02/3_393280.png/wp-content/uploads/2014/02/4_393284.png/wp-content/uploads/2014/02/5_393285.png

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)

 

 

Backup Editor

In the Backup Editor there are three tabs: Overview, Configuration, Backup Catalog

 

Overview

In this tab we can view the last success backup metadata and you can start and cancel a backup

/wp-content/uploads/2014/02/6_393296.png

/wp-content/uploads/2014/02/7_393297.pngPress this to start the backup wizard

 

/wp-content/uploads/2014/02/8_393298.pngPress this to view the backup.log file

 

/wp-content/uploads/2014/02/9_393299.pngThis button will stop the backup

 

Configuration

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.

/wp-content/uploads/2014/02/10_393300.png

Backup Catalog

Shows a history of backups and information about the backup when you highlight it

/wp-content/uploads/2014/02/11_393301.png

Also you can delete old backup via the Backup Catalog by right clicking on one of backups

/wp-content/uploads/2014/02/12_393302.png

The backup table location $(DIR_INSTANCE)/../SYS/global/hdb/metadata/BackupCatalog.xml

 

Configure table

/wp-content/uploads/2014/02/13_393303.png

Database snapshot

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: [2]: 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

  1. 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
  2. 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
  3. 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

/wp-content/uploads/2014/02/14_393304.png 

After selecting the recovery type you will select the log location/wp-content/uploads/2014/02/15_393306.png

   After this you will need to specify the data location

/wp-content/uploads/2014/02/16_393307.png

Lastly are the misc settings for the recovery

/wp-content/uploads/2014/02/17_393308.png

 

   Once that is done a summary screen appears and you can then start by pressing “Finish”

/wp-content/uploads/2014/02/18_393309.png

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

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Molly Yang

    Cool blog.  Very detailed. 🙂

    But one typo as below.

    How to Perform a Recovery

    Note that unlike in a backup, users cannot cancel a recovery.

    1)      Select your backup type?

    (0) 

Leave a Reply