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>.
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.
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
- Logs and traces are under
/hana/shared/<SID>/<host>/trace. - Passwords, SIDs, hosts and paths in the examples are generic; replace them with your environment's values.