×

Sap Support

- Sap Support - Dump files for support

Log & Trace file

- Log and trace directories - Investigate backup log content
<

Tools : Hana Studio

- Installation - Using hana studio - Using Studio SQL Console

Tools : hdbrsutil

- hdbrsutil tips & tricks

Tools : Hana Cockpit

- Install hana cockpit 2 complete example - Un-Install hana cockpit 2
<

Disk

- Disk space used by log segments - Reclaim space from log segments - Retrieve table index space used on disk

Specific Operations

- Adding physical memory

Sap Hana User Privileges

- Role and privileges - Grant read access to a schema

Stored procedures

- Creating a procedure - Dropping a procedure - Compiling a procedure - Executing a procedure - Working with procedures - Using anonymous block

Sap Hana functions

- DateTime : ADD_YEARS - DateTime : EXTRACT

Parameters

- Configuring sap hana - Kill long queries automatically

SQL

- Sql guide line - How to use SQL -Sql references - Sql tips & tricks for tables

SQL Query

- SQL & Tables - SQL & syntaxes - How to practice SQL
<

Sap Hana Tables

- Managing tables - Various ways to create Tables - Sap Hana Tables - Sap Hana Temporary Tables

Table description & DDL

- Retrieve table description - Table description using table-columns-view - Table ddl with hana studio - Table ddl using 'get_object_definition' procedure - Table ddl using hdbsql

SELECT data

- Various ways to select data

INSERT data

- Various ways to insert data

Sap Hana Users

- Managing Users - Create user with hana studio - Create user with sql Script - Create multiple users in one go - Copy a user - Create User with SQL script - Create User via Hana Studio - Update User - Drop User

Create Standard User

- Create standard & personal User

Create Technical User

- Create a technical User - Create backup users - Create administrator users - Create Modeler User - Create Data provisioning User - Create Cockpit User - Create a user to unlock any users
<

How to restore a sap hana tenant database manually step by step?

The following page will show you manual analyse procedures and various manual commands to achieve a restoration from a complete backup.

Do remember, there are different possible methods to proceed to a restoration of a tenant database in sap hana. Sap hana studio is the easiest as it guides you into a number of choices. Checkup and validations are done automatically.

However, what we want to show you in here, is what is going on behind the scene of hana studio and what you need to know in order to proceed a restoration of a sap hana tenant database manually.


How to perform a sap hana tenant database restoration from a backint backup?

Different types of backup are possible in sap hana. Backint backup type is related to backup executed via an external backup tool. You can either backup a sap hana database to files format on disk or you can use a third-party backup tool to back up and recover sap hana databases. Performing daily backup on the local server is not the best strategy to follow as disk space is limited and local server is not the best place to manage backup files. Disk or volume space shortage may be source of trouble and could compromise the sap hana system environment.

An external backup tool is certainly more appropriate as backup management is externalized from the sap hana system. Scheduled backup, space monitoring and space requirement are all performed separately from the database system. Third party backup tool problems are unlikely to impact immediately the sap hana database especially when backup space shortage is concerned.

Performing "backint" backup is straight forward but "backint" configuration has been done first. In other words, the Third party tool agent has to be installed and configured in order to give external backup tool access to the sap hana database environment.

Performing sap hana tenant database restoration requires backup content validation process before the final recovery. Those steps are done automatically via hana cockpit and hana studio.

Tenant database Recovery first page menu on hana studio

How to check whether sap hana tenant database is in point-in-time recovery or not?

Point_in_time option will provide a recovery up to the time of the database crashes. Production database has be to in recovery mode at all time. However other environment such as development and qualification can be in overwrite mode. When sap hana database log mode in set to overwrite, backup can still be done but recovery will only be done at the date of the backup. There is no way to recover any changes made on the database after the backup.
See also : How to to check log mode setting?

select log_mode, case when log_mode = 'overwrite' then 'Not possible' else 'Possible' end as "point in time recovery" from ( SELECT DISTINCT log_mode FROM SYS.M_LOG_BUFFERS); | LOG_MODE | point in tim | | --------- | ------------ | | overwrite | Not possible |

Where are sap hana backint backup directories?

Third-party tools are performing "backint" backup type. Directory names are different than standard backup file directories. "Backing" backup directories for sap hana multitenant databases are as followed:

INSTANCE_NAME="HAZ" /usr/sap/${INSTANCE_NAME}/SYS/global/hdb/backint/SYSTEMDB /usr/sap/${INSTANCE_NAME}/SYS/global/hdb/backint/DB_< a tenant_database_name > For ex : ======== cd /usr/sap/HAZ/SYS/global/hdb/backint ls -lrt drwxr-x--x 2 hazadm sapsys 10 Jul 18 13:20 DB_THR drwxr-x--x 2 hazadm sapsys 62 Oct 24 23:15 DB_VHR drwxr-x--x 2 hazadm sapsys 62 Oct 24 23:15 DB_JHR drwxrwx--x 2 hazadm sapsys 10 Oct 25 15:34 SYSTEMDB

See also : How to find out backint log backup directories?

Where are sap hana data backup file directories?

Backup of type files are performed on the local file system server . It is done as physical files on the disk. It is performed via hana studio for instance. If you are looking for data backup files directories for sap hana multitenant databases, directories are as follows:

TENANTDB=DBV3 INSTANCE_NAME="HAZ" INSTANCE_NUMBER="07" cd /usr/sap/${INSTANCE_NAME}/HDB${INSTANCE_NUMBER}/backup/data/DB_${TENANTDB} For ex : ======== cd /usr/sap/HAZ/HDB07/backup/data/DB_TRS ls -lrt hazadm@linux3:/usr/sap/HAZ/HDB07/backup/data> ll total 28 drwxr-x--x 2 hazadm sapsys 102 Jun 20 16:01 DB_DBTEST drwxr-x--x 2 hazadm sapsys 111 Oct 31 15:12 DB_DBTEST2 drwxr-x--x 2 hazadm sapsys 6 Aug 21 23:16 DB_TRS drwxr-x--x 2 hazadm sapsys 4096 Aug 21 23:27 DB_FBW drwxr-x--x 2 hazadm sapsys 86 Aug 18 2016 DB_TBW -rw-r----- 1 hazadm sapsys 12288 Jun 7 2016 log_backup_0_0_0_0.1465310565686 -rw-r----- 1 hazadm sapsys 12288 Jun 7 2016 log_backup_0_0_0_0.1465310808409 drwxrwx--x 2 hazadm sapsys 78 Aug 21 23:27 SYSTEMDB

How to retrieve specific backup file names of a sap hana tenant database?

There are 2 possible sap hana backup types. All backup done on local disk are called "File" backup. Backup done on a remote location via a remote backup tool are called "Backint" backup. "File" backup name are retrievable either on the local server disk or running SQL queries from the database. As far as "backing" backup is concerned, there is no point to look for any file names while logged on the sap hana server. Although specific directories exist, "Backint" backup file names are NOT visible at the os level. In order to find out about "backint" backup file name you have to interrogate the sap hana database. The following 2 views holds all the details for any backup type.
M_BACKUP_CATALOG_FILES
M_BACKUP_CATALOG

M_BACKUP_CATALOG_FILES description: =================================== ENTRY_ID BACKUP_ID SOURCE_ID SOURCE_TYPE_NAME HOST SERVICE_TYPE_NAME REDO_LOG_POSITION FIRST_REDO_LOG_POSITION LAST_REDO_LOG_POSITION BACKUP_SIZE DESTINATION_PATH DESTINATION_TYPE_NAME EXTERNAL_BACKUP_ID LOG_SEGMENT_COUNT

SQL Query ======================== Important note: BACKUP_ID column refers to backup of type FILE. EXTERNAL_BACKUP_ID column refers to backup of type BACKINT DESTINATION_TYPE_NAME column tells whether a backup is of type "BACKINT" or "FILE" SELECT BACKUP_ID, EXTERNAL_BACKUP_ID, HOST, DESTINATION_PATH, BACKUP_SIZE, DESTINATION_TYPE_NAME FROM M_BACKUP_CATALOG_FILES WHERE BACKUP_ID >= (SELECT TOP 1 BACKUP_ID FROM M_BACKUP_CATALOG WHERE STATE_NAME = 'successful' AND ENTRY_TYPE_NAME = 'complete data backup' ORDER BY UTC_START_TIME desc); See also : M_BACKUP_CATALOG_FILES view details

How to get recent backup file names of a sap hana tenant database?

The easiest way to get any backup file names is to use sap hana studio. But other good alternatives exist. Running SQL queries is also a good method as many details about the backup itself can be obtained. SQL report has the advantage of being able to give a high level of data filtering. The simple filter is getting the most recent backup file names first.

hdbsql=> \c -i 02 -d DBC -u system -p ToRejoy01 Connected to tax@linux7.ssii.tf.world.eu:30266 hdbsql DBC=> \mu Multiline mode switched ON hdbsql DBC=> \al Aligned output mode switched ON SELECT BACKUP_ID, EXTERNAL_BACKUP_ID, HOST, DESTINATION_PATH, BACKUP_SIZE, DESTINATION_TYPE_NAME FROM M_BACKUP_CATALOG_FILES WHERE BACKUP_ID >= (SELECT TOP 1 BACKUP_ID FROM M_BACKUP_CATALOG WHERE STATE_NAME = 'successful' AND ENTRY_TYPE_NAME = 'complete data backup' ORDER BY UTC_START_TIME desc); | BACKUP_ID | EXTERNAL_B | HOST | DESTINATION_PATH | BACKUP_SIZE | DESTINA | | -------------------- | ---------- | ------ | ------------------------------------------------------------------------------------ | -------------------- | ------- | | 1508948290133 | 1508948338 | linux7 | /usr/sap/TAX/SYS/global/hdb/backint/DB_DBC/COMPLETE_DBC_2021022518h18_databackup_3_1 | 83886080 | backint | | 1508948290133 | 1508948337 | linux7 | /usr/sap/TAX/SYS/global/hdb/backint/DB_DBC/COMPLETE_DBC_2021022518h18_databackup_2_1 | 26289897472 | backint | | 1508948290133 | 1508948300 | linux7 | /usr/sap/TAX/SYS/global/hdb/backint/DB_DBC/COMPLETE_DBC_2021022518h18_databackup_0_1 | 1321 | backint | | 1508948538658 | 1508948542 | linux7 | /usr/sap/TAX/SYS/global/hdb/backint/DB_DBC/log_backup_0_0_0_0 | 12482 | backint | | 1508966105542 | 1508966135 | linux7 | /usr/sap/TAX/SYS/global/hdb/backint/DB_DBC/log_backup_0_0_0_0 | 11557 | backint | Notes : 1. Check the backup type ( DESTINATION_TYPE_NAME ), backint means backup has been done via a third-party tool. 2. Concerning Backint backup file, file names are not visible nor accessible from the os. Using 'ls' will show nothing. 3. When dealing with Backint backup, you have to refer to EXTERNAL_BACKUP_ID instead of BACKUP_ID

How to check which sap hana database backup files are required for a specific recovery?

You have got a list of backup file names and you want to know which one is available and ready to use for a recovery.
Note : This command works with "file" backup type.

hdbbackupdiag -h TENANTDB='BHT' hdbbackupdiag --check --dataDir /usr/sap/${SAPSYSTEMNAME}/HDB{$TINSTANCE}/backup/data/DB_${TENANTDB} hdbbackupdiag -i 1508948290133 hdbbackupdiag -f -B hdbbackupdiag -u "YYYY-MM-DD HH:MM:SS" hdbbackupdiag -u "2021-02-02 18:18"

How to check sap hana database backup consistency?

Keep in mind, you cannot rollback a recovery process. Corrupted backup will damage the hana database target. So, before starting a sap hana database recovery, check whether backup file names you need are available and usable.

Usage: hdbbackupcheck [options] Options: -h: display this usage information -v: display the headers of the backup -i : check whether the backup has the backup id specified -p : use this directory for the log files -e : external backup id for a backint call --backintParamFile : use parameter file specified for a backint call --dump: dump content of the backup (log and topology backup only) hazadm@linux7:cd /usr/sap/HAZ/HDB07/backup/data/DB_BHE> hazadm@linux7:/usr/sap/HAZ/HDB07/backup/data/DB_BHE> hdbbackupcheck -v COMPLETE_DATA_BACKUP_databackup_0_1 Check backup '/hana/shared/HAZ/HDB07/backup/data/DB_BHE/COMPLETE_DATA_BACKUP_databackup_0_1'. Destination of Type: file, Version: 6 Destination header information: DatabaseId: 57552971-9a52-2161-e100-00000ac8722a InternalStartTime: 1509458914765 / 2021-02-31T15:08:34+01:00 CurrDestInformation: [FILE][/usr/sap/HAZ/HDB07/backup/data/DB_BHE/COMPLETE_DATA_BACKUP_databackup_0_1] backupId: 1509458914722 ServiceName: indexserver NumberOfVolumes: 1 HostName: linux7 VolumeId: 2 DestId: 1 NumberOfDest: 1 SID: HAZ DatabaseName: BHE HanaVersion: 1.00.123.11.1499758750 HanaWeekstone: 0000.00.0 Architecture: little endian SrcPoolInformation[0]: [TOPOLOGY] SrcPoolInformation[1]: [DATABACKUP_INFO] SrcPoolInformation[2]: [SSFS] DstPoolInformation[0]: [FILE][/usr/sap/HAZ/HDB07/backup/data/DB_BHE/COMPLETE_DATA_BACKUP_databackup_0_1] Source header information: SrcType: 4 SourceInformation: [TOPOLOGY] srcVersion: 2 sourceSize: 491 Check backup content '[TOPOLOGY]'. Backup content '[TOPOLOGY]' successfully checked. Source header information: SrcType: 10 SourceInformation: [DATABACKUP_INFO] srcVersion: 1 sourceSize: 301 Check backup content '[DATABACKUP_INFO]'. Backup content '[DATABACKUP_INFO]' successfully checked. Source header information: SrcType: 7 SourceInformation: [SSFS] srcVersion: 1 sourceSize: 73 Check backup content '[SSFS]'. Backup content '[SSFS]' successfully checked. Backup '/hana/shared/HAZ/HDB07/backup/data/DB_BHE/COMPLETE_DATA_BACKUP_databackup_0_1' successfully checked.

How to retrieve the sap hana backup user store key details?

User store key has various purposes. It brings a type of security as password, port number and host name are not visible at the command line, but it also simplify the command line syntax itself. If you do not know the user store key used to run the backup, you can ask your sap hana administrator or the backup department team for any details. Otherwise you can get details about user store key and backup user using the following syntaxes :

You want to know all user key names : -------------------------------------- $> hdbuserstore list DATA FILE : /home/taxadm/.hdb/linux12/SSFS_HDB.DAT KEY FILE : /home/taxadm/.hdb/linux12/SSFS_HDB.KEY KEY BHT_BACKUP ENV : linux12.ssii.tf.world.eu:30241 USER: BACKUP_USER KEY BHQ_BACKUP ENV : linux12.ssii.tf.world.eu:30243 USER: BACKUP_USER KEY BHQ_ADMIN ENV : linux12.ssii.tf.world.eu:30266 USER: ADMIN_USER KEY BHZ_ADMIN ENV : linux12.ssii.tf.world.eu:30269 USER: ADMIN_USER You want to filter all user key names with BACKUP: -------------------------------------- $> hdbuserstore list | grep -i backup KEY BHT_BACKUP KEY BHQ_BACKUP You want details for a particular user key : -------------------------------------------- $> hdbuserstore list BHT_BACKUP KEY BHT_BACKUP ENV : linux12.ssii.tf.world.eu:30241 USER: BACKUP_USER

Which user to use for a sap hana tenant database restoration?

It is preferable to use a sap hana administrator user to perform any sap hana tenant database restoration. Although a BACKUP USER with BACKUP ADMIN can do the job, the administrator user will be able to perform also other actions such as stopping and starting the tenant database. Switching from one user to another to be able to carry on a sap hana database recovery would be a real pain especially in an emergency situation.

How to show and list all sap hana tenant databases status?

When it comes to recover a tenant database, it is important to know whether it is active or not. It does not matter, whether you want to go back in time with an old backup set or whether you have to recover and bring up to date your tenant database after a crash, your database will have to be shutdown before starting the recovery operation.

Do not forget, this operation is done at the SYSTEMDB level

Select * from M_DATABASES | DATABASE | DESCRIPTION | ACTIVE_S | ACTIVE_S | OS_USER | OS_GROUP | | -------- | --------------- | -------- | -------- | -------- | -------- | | SYSTEMDB | SystemDB-HAS-00 | YES | | | | | VBW | | YES | | | | | TBW | | YES | | | | | DBT01 | | STOPPING | | | | | TRS | | YES | | | |

How to stop the sap hana tenant database you want to restore?

In order to restore and recover a sap hana tenant database, it has to be stopped first. Whether you use hana studio, hana cockpit or hdbsql, the stop operation has to be done at at the SYSTEMDB level.

hdbsql $> \c -i 07 -d SYSTEMDB -u ADMIN_USER -p ToBeVerySecret04 hdbsql $> ALTER SYSTEM STOP DATABASE DBT01;

How to check specific sap hana tenant database status?

Remember the sap hana database that will need to be recovered and restored has to be shutdown before the recoving operation. There are many ways to check a sap hana database status. Hana cockpit and the SQL syntax will tell explicitly whether a database is stopped or not. The OS command line tool "sapcontrol" will NOT report on a shutdown database. If you don t see a sap hana tenant database name on the list, it means it is turn down. However, Make sure you are connected to the right server. The result will be the same: Your database name will NOT show up and you might think it is down.

SQL Syntax ================= Select * from M_DATABASES where database_name = 'DBT01'; | DATABA | DESCRIPT | ACTIVE_S | ACTIVE_S | OS_USER | OS_GROUP | | ------ | -------- | -------- | -------- | -------- | -------- | | DBT01 | | YES | | | | Os command line tool: sapcontrol ================================ bobadm@linux07:/usr/sap/BOB/SYS/profile> sapcontrol -nr 07 -function GetProcessList | \ awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }' 02.02.2021 09:31:15 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2021 03 13 14:07:46 2898:23:29 132500 hdbcompileserver HDB Compileserver GREEN Running 2021 03 13 14:08:46 2898:22:29 133329 hdbindexserver HDB Indexserver-VBW GREEN Running 2021 04 13 17:12:20 2152:18:55 96965 hdbindexserver HDB Indexserver-DBT01 GREEN Running 2021 05 23 10:51:11 1198:40:04 97453 hdbindexserver HDB Indexserver-TRS GREEN Running 2021 03 13 14:08:50 2898:22:25 133372 hdbindexserver HDB Indexserver-DBW GREEN Running 2021 03 13 15:00:47 2897:30:28 30612 hdbnameserver HDB Nameserver GREEN Running 2021 03 13 14:07:47 2898:23:28 132517 hdbpreprocessor HDB Preprocessor GREEN Running 2021 03 13 14:08:46 2898:22:29 133331 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2021 03 13 14:10:49 2898:20:26 137711 hdbindexserver HDB Indexserver-GRB GREEN Running 2021 06 22 10:39:45 478:51:30 129126

Where to find sap hana tenant databases backup log file directories?

Whether you are dealing with a backup or a restoration situation, you want to be able to follow what is really going on behind the scene in order to understand what is happening. To do so, it is possible to view the sap hana database event log file. ( backup.log ) There are different backup event log files for each individual tenant databases, Systemdb included.

The following directories are default directories for trace and log files on a sap hana multitenant database system.

Default directory : /usr/sap/< SID >/HDB< Instance nb >/< hostname >/trace/DB_< tenant database > Ex : --------- env | egrep -i 'instance|hostname|sapsystem' LOGDIR=/usr/sap/${SAPSYSTEMNAME}/HDB${$TINSTANCE}/${HOSTNAME}/trace/ LOGDIR=/usr/sap/BOB/HDB07/linux7/trace/ --------- alias ShowSytemdbBackupEventLogfileNameDirectoryAndFile='cd /usr/sap/${SAPSYSTEMNAME}/HDB${TINSTANCE}/${HOSTNAME}/trace/; pwd; ls -lrt back*.log' bobadm@linux7:/usr/sap/BOB/HDB07/linux7/trace> ShowSytemdbBackupEventLogfileNameDirectoryAndFile /usr/sap/BOB/HDB07/linux7/trace -rw-r----- 1 bobadm sapsys 1665 Oct 27 08:36 backupcheck.log -rw-r----- 1 bobadm sapsys 1108 Oct 27 08:36 backintcheck.log -rw-r----- 1 bobadm sapsys 84003465 Oct 30 10:19 backup.log -rw-r----- 1 bobadm sapsys 55572921 Oct 30 10:19 backint.log --------- TENANTDB='DBT01' alias ShowTenantbBackupEventLogfileName='ls -lrt /usr/sap/${SAPSYSTEMNAME}/HDB${TINSTANCE}/${HOSTNAME}/trace/DB_${TENANTDB}/backup.log' alias ShowAllTenantbBackupEventLogfileDirectories='cd /usr/sap/${SAPSYSTEMNAME}/HDB${TINSTANCE}/${HOSTNAME}/trace/; pwd; ls -lrt | grep DB_' --------- bobadm@linux7:/usr/sap/BOB/HDB07/linux7/trace> ShowAllTenantbBackupEventLogfileDirectories /usr/sap/BOB/HDB07/linux7/trace drwxr-x--x 2 bobadm sapsys 4096 Jul 16 23:35 DB_VBW drwxr-x--x 2 bobadm sapsys 4096 Sep 2 16:46 DB_TRS drwxr-x--x 2 bobadm sapsys 4096 Sep 21 13:43 DB_DBT01 drwxr-x--x 2 bobadm sapsys 4096 Oct 6 08:54 DB_DBW Note: There is a command line "cdtrace" available par default. type cdtrace at the prompt and it will bring you straight to the log and trace files directory bobadm@linux7$> cdtrace bobadm@linux7$> /usr/sap/BOB/HDB07/linux7/trace/ bobadm@linux7$> ls -lrt . . . -rw-r----- 1 bobadm sapsys 84003465 Oct 30 10:19 backup.log -rw-r----- 1 bobadm sapsys 55572921 Oct 30 10:19 backint.log drwxr-x--x 2 bobadm sapsys 4096 Jul 16 23:35 DB_VBW drwxr-x--x 2 bobadm sapsys 4096 Sep 2 16:46 DB_TRS drwxr-x--x 2 bobadm sapsys 4096 Sep 21 13:43 DB_DBT01 drwxr-x--x 2 bobadm sapsys 4096 Oct 6 08:54 DB_DBW

How to proceed to a sap hana tenant database restoration?

Note : It is advised to use Hana Studio to perform tenant database RECOVERY. However, if studio is not available it is also possible to use the steps described further down. You have, at this stage, gathered all details regarding backup files. You have done various checkup and you are now ready to proceed to a database recovery.

1/ Stop the tenant database. Remenber it has to be done at SYSTEMDB level. Connect to SYSTEMDB then stop the tenant database. · $> USRK=ADMINBACKUPK; · $> hdbsql -U ${USRK} "ALTER SYSTEM STOP DATABASE DBT3" 2/ Recover the tenant database from the chosen "Backint" or "File" backup. ( Using "backint" backup ) · $> hdbsql -U < user store_key > "RECOVER DATA FOR DBT3 USING BACKINT ('COMPLETE_DBT3_2021021018h18') CLEAR LOG" or · $> hdbsql -i 07 -d SYSTEMDB -u SYSTEM -p Only4usToKnow "RECOVER DATA FOR DBT3 USING BACKINT ('COMPLETE_DBT3_2021021018h18') CLEAR LOG" 3/ Check tenant database status at regular interval. · $> USRK=ADMINBACKUPK; · $> DBSTAT=/tmp/reportf.lst; hdbsql -A -U ${USRK} "Select * from M_DATABASES" > ${DBSTAT}; cat ${DBSTAT} or · $> hdbsql hdbsql>\c -i 07 -d SYSTEMDB -u SYSTEM -p Only4usToKnow ... hdbsql> Select * from M_DATABASES 4/ Check recovery event. Open a new session an run the following : · $> TENANTDB=DV3 · $> LOG=/usr/sap/${SAPSYSTEMNAME}/HDB${TINSTANCE}/${HOSTNAME}/trace/DB_${TENANTDB}/backup.log · $> tail -f $LOG 5/ Start the tenant database after the restoration has been completed. · $> USRK=ADMINBACKUPK; · $> hdbsql -U ${USRK} "ALTER SYSTEM START DATABASE DBT3" Points to remember : 1/ About the syntax "USING BACKINT('COMPLETE_DBT3_2021021018h18')" COMPLETE_DBT3_2021021018h18 refers to the backup files prefix. . This is the same for the syntax "USING FILE" See also : How to retrieve backup file names? 2/ $> HDBSettings.sh recoverSys.py It is to be used instead of hdbsql, when performing a complete sap hana systemdb recovery. See also : How to use HDBSettings.sh recoverSys.py?

Where to find sap hana backup and restoration syntaxes?

There are different ways to create a sap hana database backup. Backup types are not compatible between each other. In other word, you cannot recover from a "file" backup type using references of a "backint" backup type. You have to use corresponding syntaxes. ( Hence the use of Hana studio which is doing it all for you )

1/ Backup to file Syntax note : USING FILE ('This-Is-A-Prefix-Added-To-The-BackupFileName') A backup file name would be something like : /usr/sap/TAX/SYS/global/hdb/backint/DB_DBT3/This-Is-A-Prefix-Added-To-The-BackupFileName_databackup_3_1 /usr/sap/TAX/SYS/global/hdb/backint/DB_DBT3/This-Is-A-Prefix-Added-To-The-BackupFileName_databackup_2_1 /usr/sap/TAX/SYS/global/hdb/backint/DB_DBT3/This-Is-A-Prefix-Added-To-The-BackupFileName_databackup_0_1 · $> USRK=ADMINBACKUPK; · $> hdbsql -U ${USRK} "BACKUP DATA FOR DBT3 USING FILE ('FILE_COMPLETE_DBT3_20210210')" 2/ Backint Backup Syntax note : USING BACKINT ('This-Is-A-Prefix-Added-To-The-BackupFileName') · $> USRK=ADMINBACKUPK; · $> hdbsql -U ${USRK} "BACKUP DATA FOR DBT3 USING BACKINT ('BACKINT_COMPLETE_DBT3_20210210')" 3/ Restoration from file backup Recovery with hdbsql ( single tenant database ) · $> USRK=SYSTEMDBADMINBACKUPK
· $> hdbsql -U ${USRK} "RECOVER DATA FOR DBT3 USING FILE 'FILE_COMPLETE_DBT3_20210210' CLEAR LOG" 4/ Restoration from backint backup Recovery with hdbsql ( single tenant database ) · $> USRK=SYSTEMDBADMINBACKUPK · $> hdbsql -U ${USRK} "RECOVER DATA FOR DBT3 USING BACKINT 'BACKINT_COMPLETE_DBT3_20210210' CLEAR LOG" Recovery with HDBSettings.sh - DATA only ( Whole sap hana system ) · $>HDBSettings.sh recoverSys.py --command="RECOVER DATA USING BACKINT ('BACKINT_COMPLETE_SYSTEMDB_20210210') CLEAR LOG" Recovery with hdbsql - Point-in-Time recovery ( single tenant database ) · $> USRK=SYSTEMDBADMINBACKUPK · $> hdbsql -U ${USRK} "RECOVER DATABASE FOR DBT3 UNTIL TIMESTAMP '2021-02-09 11:45:02' USING DATA PATH ('/usr/sap/HAZ/SYS/global/hdb/backint/DB_DBT3') USING LOG PATH ('/usr/sap/HAZ/SYS/global/hdb/backint/DB_DBT3') CHECK ACCESS ALL USING BACKINT;" Recovery with hdbsql - Point-in-Time recovery ( Whole sap hana system ) · $> USRK=SYSTEMDBADMINBACKUPK · $> hdbsql -U ${USRK} "RECOVER DATABASE UNTIL TIMESTAMP '2021-02-08 15:35:27' USING DATA PATH ('/usr/sap/HAZ/SYS/global/hdb/backint/SYSTEMDB') USING LOG PATH (' /usr/sap/HAZ/SYS/global/hdb/backint/SYSTEMDB') USING BACKUP_ID 15373469 CHECK ACCESS USING BACKINT" Recovery with HDBSettings.sh - Point-in-Time recovery ( Whole sap hana system ) · $>HDBSettings.sh recoverSys.py --command="RECOVER DATABASE UNTIL TIMESTAMP '2021-05-10 10:17:35' CHECK ACCESS" -wait --timeout=600

How to follow a sap hana database backup or restoration events ?

All sap hana backup and restoration are recorded within the backup.log file. Third party tools backup events are also recorded within the backint.log file The backup.log events are easier to understand and provide more step by steps details.

The following linux script provides a way to follow tenant database events as it is happening.

TENANTDB="DV3" TINSTANCE=07 HOSTNAME="linux07" LOGDIR=/usr/sap/${SAPSYSTEMNAME}/HDB${TINSTANCE}/${HOSTNAME}/trace/DB_${TENANTDB} LOGFILE=backup.log LOG=${LOGDIR}/${LOGFILE} echo "log : " $LOG tail -300 $LOG | grep 'RECOVERY progress of service' echo "---------------------------------------" grep 'RECOVERY' $LOG echo "---------------------------------------" tail -f $LOG 2021-02-24T14:48:19+02:00 P020740 15f4e6c5425 INFO RECOVERY progress of service: indexserver, linux7:30252, volume: 2, 6% 1610612736/26474450944 2021-02-24T14:48:30+02:00 P020740 15f4e6c5425 INFO RECOVERY progress of service: indexserver, linux7:30252, volume: 2, 10% 2684354560/26474450944 2021-02-24T14:48:47+02:00 P020740 15f4e6c5425 INFO RECOVERY progress of service: indexserver, linux7:30252, volume: 2, 16% 4294967296/26474450944 2021-02-24T14:48:59+02:00 P020740 15f4e6c5425 INFO RECOVERY progress of service: indexserver, linux7:30252, volume: 2, 20% 5368709120/26474450944

How to start the sap hana tenant database which has been restored and recovered?

There is no particular or specific command to startup a sap hana tenant database after it has been recovered. There are many ways to start it up. The following syntax can be executed either in hana studio sql console or using hdbsql:

hdbsql $> \c -i 06 -d SYSTEMDB -u ADMIN_USER -p ToBeVerySecret04 hdbsql $> ALTER SYSTEM START DATABASE DBT01;

Sap hana SQL

Sap hana tools

- - -