Making backups is easy, and most administrators do this. But only a successful restore from a backup can confirm that the backup is really good. In this article, we will perform a database restore using only the files from the backup.
There are two possible recovery scenarios. In the first, HANA becomes bad due to human errors, but the server itself is OK. The database can be restored using the HANA Studio's graphical interface (right-click, restore, etc.). Usually, there are no problems with this kind recovery.
In the second case, the reason for the recovery is the server's hardware problems. Of course, I have a backup of whole the server too, but suppose that we install the server from scratch and then install the HANA software. Now we need to restore the database, having only a backup copy. It is this scenario that we will consider.
NOTE:This case is about file storing backup, no BACKINT here.
The backup is performed by the BACKUP DATA command using hdbsql CLI. The backup can be obviously FULL, DIFFERENTIAL (only data changed since the full backup) and INCREMENTAL (data changed since any previous backup). In addition, HANA creates its own backup of current logs, similar to Oracle archive logs, every 15 minutes (by default) to the $DIR_INSTANCE/backup/log directory (by default). They are important for recovery to a certain point in time. Every time HANA performs a backup, it creates a copy of the Backup Catalog in the same ($DIR_INSTANCE/backup/log) location. This file is called like log_backup_0_0_0_0.1520355726710, where a large number is the epoch time for this copy of the catalog. The Catalog contains information about all previously made backups, including the previously mentioned archive logs
This place is easily becomes full if not cleaned by the administrator. I have a script that does compress not recent files and deletes too outdated files. However, the metadata about all of these backups remains in the Backup Catalog, which continues to grow. You can clear it with the hdbsql CLI command:
hdbsql=> BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID XXXX ;
The latest version of script do this automatically for you, leaving only history for last month in catalog.
For other purposes it is recommended to use other users, than SYSTEM. We will create a user named BACKUP in accordance with the recommendations:
hdbsql=> CREATE ROLE BACKUP_ROLE; hdbsql=> GRANT BACKUP ADMIN,CATALOG READ,MONITORING to BACKUP_ROLE; hdbsql=> CREATE USER BACKUP PASSWORD "ComplexPassword" NO FORCE_FIRST_PASSWORD_CHANGE; hdbsql=> ALTER USER BACKUP DISABLE PASSWORD LIFETIME; hdbsql=> GRANT BACKUP_ROLE to BACKUP; hdbsql=> SELECT * FROM "SYS"."USERS"; hdbsql=> SELECT * FROM "PUBLIC"."EFFECTIVE_PRIVILEGES" WHERE USER_NAME = 'BACKUP';
You need to configure a connection without a password to the database, otherwise you will have to specify the password in your script, which is worse.
sidadm> hdbuserstore DELETE BACKUP sidadm> hdbuserstore -i SET BACKUP localhost:30015 BACKUP sidadm> hdbuserstore LIST sidadm> hdbsql -U BACKUP "select * from dummy"
And, finally, a backup script itself. Schedule it to run by user <sid>adm as shown in example in scripts header. A destination directory DDIR has to exist and be writable by <sid>adm. Fix location to your need.
#!/bin/bash
# By Oleg Volkov (voleg)
# Copy it to sidadm/bin and run it as sidadm cronjob:
# 2 */6 * * * [ -x $HOME/bin/hana_backup_local.sh ] && $HOME/bin/hana_backup_local.sh 2>&1 | logger -t hana-backup
# Reload environment (important for cron jobs)
source $HOME/.bashrc
# This SQL return previous good full backup id from catalog:
SQL="SELECT top 1 min(to_bigint(BACKUP_ID))
FROM SYS.M_BACKUP_CATALOG
where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP,-28)
and ENTRY_TYPE_NAME='complete data backup'
and STATE_NAME='successful' ;"
HDBSQL="$DIR_EXECUTABLE/hdbsql -U BACKUP"
DDIR="/hana/backup/raw" ; [ -d $DDIR ] || mkdir -v -m 751 $DDIR || exit 1
STAMP="$(date +%Y%m%d%H)"
# Cleanup old files (to free space before):
find $DDIR -type f -mtime +13 exec rm -fv "{}" \;
TYPE=""
# Do FULL or DIFF on 6 morning:
if [ $(date "+%H") = "06" ] ; then
# Make FULL on Sun (0th weekday)
if [ $(date "+%w") -ne 0 ] ; then
TYPE="DIFFERENTIAL"
fi
else
TYPE="INCREMENTAL"
fi
echo " ** Doing ${TYPE:-FULL} backup"
$HDBSQL "BACKUP DATA $TYPE USING FILE ('$DDIR/$STAMP');" || { echo "Backup failed, script aborted" ; exit 1 ; }
if [ -z $TYPE ] ; then
# Clean backup catalog from outdated backups:
IDFILE=$(mktemp)
$HDBSQL -o $IDFILE -a $SQL
BACKUPID=$(cat $IDFILE)
rm -f $IDFILE
if [ -z $BACKUPID -o 'x'$BACKUPID = 'x?' ] ; then
echo " ** No backup to purge from catalog"
else
echo " ** BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID"
$HDBSQL "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID"
fi
fi
echo " ** Saving backup catalog"
BCATALOG=$(ls -1tr $DIR_INSTANCE/backup/log/log_backup_0_0_0_0*|tail -1)
cp -av $BCATALOG ${DDIR}/
Schedule it run hourly or less frequent. It is pre-configured to run a FULL backup in Sun at 06:xx, DIFF at 06:xx on rest days and INC in any other cases.
It remains only to take care of the backup to the tape directory /hana/backup/raw.
As we said, we have a fresh installed HANA server ready to restore the database on it.
Restore /hana/backup/raw directory with latest files.
Locate the most recent FULL backup:
/hana/backup/raw # ls -1tr | egrep -v "log_backup|differential|incremental" 2018030119_databackup_0_1 2018030119_databackup_4_1 2018030119_databackup_2_1 2018030119_databackup_1_1 2018030119_databackup_3_1
As you can see, the prefix for the full backup in our case is 2018030119. Indeed, as you remember from the script, the backup was done USING FILE ('$DDIR/$STAMP'). Almost the same syntax will be used at the time of recovery. Stop the database first:
sidadm> HDB stop
Then, create a command file for restore using detected prefix:
sidadm> echo "RECOVER DATA USING FILE ('/hana/backup/raw/2018030119') CLEAR LOG" > $DIR_INSTANCE/work/recoverInstance.sql
Now clear HANA's log and trace files so that it's easier to read them, then run the instance:
sidadm> cdtrace && rm * sidadm> HDB start
If you received the final OK, the restore was completed, otherwise check the file named like nameserver_HOSTNAME.30001.003.trc.
You can get the following error:
[5294]{-1}[-1/-1] 2018-03-04 21:34:55.913776 e Backup BackupMgr_Manager.cpp(06575) :
RECOVER DATA finished with error:
[448] recovery could not be completed, [110092] Recovery failed in nameserver startup:
check services against topology failed:
mismatch:
1 scriptservers configured in source topology, 0 in destination system
In this case, configure scriptserver start file, like:
sidadm> echo -e "[scriptserver]\ninstances = 1" > /usr/sap/$SAPSYSTEMNAME/SYS/global/hdb/custom/config/daemon.inithen repeat all the steps: stop database, create recover command file, clean trace files and start the instance.
First we need to restore the FULL backup. Other types of backup will attempt to roll forward volumes that still not exist in the initial database. If you have not yet restored FULL, return to the section above and do that.
Restoring DIFF and INC backups requires Backup Catalog avaliable. Restore CATALOG (file named like log_backup_0_0_0_0.xxx) and put it next to backup data. The catalog timestamp should be equal or higher than backup file timestamp. Translate backup file timestamp to human form like:
/hana/backup/raw> ls -ltra
total 20729192
..
-rw-r----- 1 ndbadm sapsys 3556777984 May 7 06:02 2019050706_databackup_differential_1557025321571_1557198125950_2_1
-rw-r----- 1 ndbadm sapsys 1757184 May 7 06:02 log_backup_0_0_0_0.1557198143352
drwxr-x--x 2 ndbadm sapsys 4096 May 7 11:24 .
/hana/backup/raw> date -d @$(echo 1557198125950 |awk '{printf"%d\n",$1/1000}') "+%Y-%m-%d %H:%M:%S"
2019-05-07 06:02:05
The last date will be UNTIL TIMESTAMP. Stop database:
sidadm> HDB stop
Then, create a command file for restore using detected UNTIL TIMESTAMP :
sidadm> echo "RECOVER DATABASE UNTIL TIMESTAMP '2019-05-07 06:02:05' CLEAR LOG USING DATA PATH ('/hana/backup/raw') USING LOG PATH ('/hana/backup/raw') " > $DIR_INSTANCE/work/recoverInstance.sql
Now clear HANA's log and trace files so that it's easier to read them, then run the instance:
sidadm> cdtrace && rm * sidadm> HDB start
The CLEAR LOG option is tricky. Follow to HANA errors in trace file. Sometime it wants this option, sometimes not.