LLI Wiki — My Digital Knowledge Base

SAP HANA: hdbsql, backup and backup catalog from the command line

hdbsql cheat sheet: connection, ports, status queries, SYSTEMDB and tenant backups, scripts and backup catalog cleanup.

Command-line cheat sheet for administering SAP HANA with hdbsql: connecting, status queries, backing up databases (SYSTEMDB and tenants), and browsing and cleaning the backup catalog.

Architecture

A HANA system (identified by its SID) contains a system database (SYSTEMDB) and one or more tenant databases (multitenant architecture). Each database is reached with -d <database_name>.

SAP HANA system: system database and tenant databases on a node

Connecting with hdbsql

hdbsql ships with the HANA client, for example /hana/shared/<SID>/hdbclient/scripts/hdbsql. To avoid putting the password in scripts, store a key in the secure store hdbuserstore:

hdbuserstore SET backup sqlserver01.example.com:30013 SYSTEM <PASSWORD>
hdbsql -U backup

Direct connection, by instance number and database:

hdbsql -i 00 -u SYSTEM -p '<PASSWORD>' -d SYSTEMDB
hdbsql -i 00 -u SYSTEM -p '<PASSWORD>' -d TENANT1

Useful options: -i instance number, -u user, -p password, -d database, -n host, -U hdbuserstore key, -x quiet mode.

In the interactive session, \s shows the connection status (host, SID, database, user, versions, encoding) and \q quits. For more readable output:

hdbsql TENANT1=> \mu
Multiline mode switched ON
hdbsql TENANT1=> \al
Aligned output mode switched ON

Ports

The SQL port depends on the instance number (xx):

Port Usage
3xx15 Single-database system (master index server); kept for the converted tenant when moving to multitenant
3xx13 SYSTEMDB SQL port
3xx40, 3xx41, 3xx42 1st tenant: TrexNet, SQL, HTTP
3xx43, 3xx44, 3xx45 2nd tenant: TrexNet, SQL, HTTP

With instance 00, SYSTEMDB therefore listens on port 30013.

Port coverage for connecting to tenant databases

Status and inventory queries

-- databases of the system (run on SYSTEMDB)
SELECT * FROM "SYS"."M_DATABASES";
-- users, schemas, tables
SELECT user_name FROM users;
SELECT * FROM schemas;
SELECT * FROM tables;
-- log segments per service and state
SELECT T2.HOST, T2.SERVICE_NAME, T1.STATE, COUNT(*)
FROM "M_LOG_SEGMENTS" T1 JOIN "M_SERVICES" T2 ON (T1.HOST = T2.HOST AND T1.PORT = T2.PORT)
GROUP BY T2.HOST, T2.SERVICE_NAME, T1.STATE;

At OS level: ps -ef | grep hdbindexserver checks that the process runs; after su - <sid>adm, HDB info lists the processes.

Start and stop

/usr/sap/<SID>/HDB<instance>/HDB start
/usr/sap/<SID>/HDB<instance>/HDB stop

From SYSTEMDB, a tenant is started with ALTER SYSTEM START DATABASE <tenant_name>;.

Backup

Full backup of SYSTEMDB to a directory (the last path element is used as the file name prefix):

BACKUP DATA USING FILE ('/hana/backup/full_test/SYSTEMDB/');
BACKUP DATA USING FILE ('COMPLETE') COMMENT 'data backup of 2018-10-16';
BACKUP DATA FOR FULL SYSTEM USING FILE ('/hana/backup/full_test/SYSTEMDB/') COMMENT 'Backup of SYSTEMDB';

A tenant backup is run the same way by connecting with -d <tenant>.

Daily script using hdbuserstore

#!/bin/sh
source $HOME/.bashrc
date="$(date +"%Y-%m-%d")"
time="$(date +"%Y-%m-%d %H:%M:%S")"
backup="backup data using file ('$date')"

echo "daily backup :" $time "Start" >> $DIR_INSTANCE/backup.log
$DIR_EXECUTABLE/hdbsql -U backup -x $backup
time="$(date +"%Y-%m-%d %H:%M:%S")"
echo "daily backup :" $time "End" >> $DIR_INSTANCE/backup.log

Script over several databases

#!/bin/bash
SERVER=localhost
INSTANCE=00
USER=SYSTEM
PASSWORD=<PASSWORD>

DATABASES='SYSTEMDB TENANT1 TENANT2'
for DB in $DATABASES; do
  BACKUP_FILE="/hana/backup/data/DB_$DB/"
  /hana/shared/<SID>/hdbclient/scripts/hdbsql -n $SERVER -i $INSTANCE -u $USER -p $PASSWORD -d $DB \
    "BACKUP DATA USING FILE ('$BACKUP_FILE', 'FULL') COMMENT 'Backup of $DB';"
done

The database list can also come from SELECT DATABASE_NAME FROM SYS.M_DATABASES; run on SYSTEMDB. Prefer hdbuserstore over a clear-text password in the script.

Backup catalog

SELECT * FROM M_BACKUP_CATALOG;

SELECT entry_type_name, backup_id, state_name,
       TO_VARCHAR(sys_start_time, 'YYYY/MM/DD') AS sys_start_time
FROM "SYS"."M_BACKUP_CATALOG";

To delete old entries by their identifier:

BACKUP CATALOG DELETE BACKUP_ID 1680859685888;

Export the catalog as JSON from the command line:

hdbsql -i 00 -u SYSTEM -p '<PASSWORD>' -d SYSTEMDB -A -xj 'select * from "SYS"."M_BACKUP_CATALOG";'

Notes

Mastodon LLIMastodon SocialFosstodon