×

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 : hdbsql

- Using hdbsql - hdbsql parameters - Executing SQL - Hiding header welcome message - Hiding column header - Update column delimiter - Formating output result - hdbsql and shell script

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
<
sap hana alerts 65 log backup id .. advertisement

How to troubleshoot sap hana alert 65 log backup with id ... has been running for longer than ...?

This alert 65 is showing that the latest log backup is not completing in the given time. On the basis of sap hana default or predefined parameter settings, it is taking too long. The current sap hana alert 65 could be linked to various situations. Huge backup catalog size could produce such a result. Also, poor disk i/o could also increase the overall backup time and trigger the alert 65. A slow network communication between remote backup software (Backup type: backint) and the sap hana system could also produce the same effect. Finally, the alert 65 could be triggered when a sap hana service crashes. Corrupted log backups or log backup in truncated status could be indirectly linked to the alert messages.

How to solve hana alert 65: a log backup with id xxx has been running for longer than xxx ?

The message "a log backup with ID < id > has been running for longer than < seconds > seconds" can be triggered for various reasons. Sap advices to check and follow a number of investigation steps.

Note : "alert 65" is also described as "Runtime of the log backups currently running"

Alert 65 example from hana studio :

Investigating sap hana alert 65 following the sap note 2081845

A slow sap hana log backup writing process could be due to many different reasons, going from log backup file size to a slow network. The alert 65 may be occurring now and then, but whether it is happening once or many times in a space of few days or few weeks, it is a good idea to investigate and get some explanations. It may be one off event but the reason behind may be fatal next time.

Here is a list of reasons :

Sap hana configuration recommandations regarding alert 65

SAP is advising to have certain parameters configuration set up as default.

global.ini -> [persistence] -> log_backup_timeout_s = 900 global.ini -> [persistence] -> log_segment_size_MB = 1024 If the XS engine is used within you sap hana system: xsengine.ini -> [persistence] -> log_segment_size_mb = 64

Sap hana recommanded configuration default values via studio


Select the Configuration tab from the main administration console and search for the following parameter :
(log_backup_timeout_s) and (log_segment_size_mb).

parameter : (global.ini) --- log_backup_timeout_s:

recommanded log backup time out
parameter : (global.ini) and (xsengine.ini)--- log_segment_size_mb:

recommanded log backup size

Updating recommanded configuration via SQL


if you need to, here are SQL syntaxes to use in order to modify any of the recommended parameters

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence', 'log_backup_timeout_s') = '900' WITH RECONFIGURE; ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence', 'log_segment_size_mb') = '1024' WITH RECONFIGURE; ALTER SYSTEM ALTER CONFIGURATION ('xsengine.ini', 'SYSTEM') SET ('persistence', 'log_segment_size_mb') = '64' WITH RECONFIGURE;

Backup catalog recommandations linked to alert 65

The backup catalog holds historical backup references; but remember, It grows in size as time goes by with more and more database backup details. Backup catalog is backed up automatically and any single database backup will take longer to occur because of a huge backup catalog being part of the backup itself. Overall backup time frame could be affected by the backup catalog if it is not regularly purged.

Here are things to do :

1/ Housekeeping should be performed regularly in order to avoid huge backup catalog size
Backup catalog housekeeping shell script

2/ Check for any failure from your backup catalog cleaning procedure.

3/ Check the backup catalog size :
Get sap hana backup catalog details

In order to purge your backup catalog, run either an existing purging backup catalog procedure or use the following sql syntax :

Manual SQL command :
BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID [WITH FILE | WITH BACKINT | COMPLETE]

Purging backup catalog ex : =========================== $> hdbsql -i 07 -U USERBACKUPK -o /tmp/backupid_$$.txt -a "SELECT top 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP,-7 and ENTRY_TYPE_NAME='complete data backup' and STATE_NAME='successful'" $> BACKUPID=`cat /tmp/backupid_$$.txt` $> hdbsql -i 07 -U USERBACKUPK -a "backup catalog delete all before backup_id ${BACKUPID} complete"

Check I/O issues which could trigger sap hana alert 65

Checking up sap hana tenant I/O performance can be done via hana studio. Select the System Information tab from the main administration console. If you have not installed the sap hana minicheck, please go to the sap site to download the sap hana sql minicheck.


See sap note : 1969700 - SQL statement collection for SAP HANA - (sap id required)

There are 4 sections to look at :

SQL: "HANA_IO_KeyFigures_Details"
SQL: "HANA_IO_KeyFigures_Total"
SQL: "HANA_IO_KeyFigures_Details_History"
SQL: "HANA_IO_KeyFigures_Total_History"

Doubleclick the item you want to obtain details from :

hana studio minicheck i/o


Result example for I/O, KeyFigures, Total, history

hana studio minicheck i/o

Check time delay between remote backup procedure and the sap hana system

Investigate log backup execution time to see whether there are any wait events between the remote backup tool server and current SAP HANA server.

https://support.sap.com/en/my-support/knowledge-base.html

cdtrace cd DB_LTS tail -1000 backup.log | egrep 'state|progress' 2017-12-04T18:18:14+01:00 P053125 1602288e6ab INFO BACKUP state of service: dpserver, linux07:30270, volume: 3, BackupExecuteDataBackupInProgress 2017-12-04T18:18:14+01:00 P053125 1602288e6ab INFO BACKUP state of service: indexserver, linux07:30252, volume: 2, BackupExecuteDataBackupInProgress 2017-12-04T18:18:14+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 0% 0/27548192768 2017-12-04T18:18:14+01:00 P053125 1602288e6ab INFO BACKUP progress of service: dpserver, linux07:30270, volume: 3, 0% 0/83890176 2017-12-04T18:18:18+01:00 P053125 1602288e6ab INFO BACKUP progress of service: dpserver, linux07:30270, volume: 3, 99% 83886080/83890176 2017-12-04T18:18:18+01:00 P053125 1602288e6ab INFO BACKUP progress of service: dpserver, linux07:30270, volume: 3, 100% 83890176/83890176 2017-12-04T18:18:19+01:00 P053125 1602288e6ab INFO BACKUP state of service: dpserver, linux07:30270, volume: 3, BackupExecuteDataBackupFinished 2017-12-04T18:18:43+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 6% 1610612736/27548192768 2017-12-04T18:19:03+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 12% 3221225472/27548192768 2017-12-04T18:19:16+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 16% 4294967296/27548192768 2017-12-04T18:19:33+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 21% 5905580032/27548192768 2017-12-04T18:19:45+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 25% 6979321856/27548192768

Check whether all sap hana services are up and running


A very easy way to check for sap hana services and eventually restart any one of them is to use hana cockpit.

1) Click the SAP HANA System Administration
2) Click the Database Name
3) Check whether all services are running successfully ( Green )

hana cockpit 1 and sap hana database services
hana cockpit 2 and sap hana database services

Check log backup status are in free and in writing state.

In a normal situation, log backup status will be found as "Free" and "Writing". It means the current log backup file content is writen rapidly to the disk with no particular delay (status writing), log backup file with free status is then used for the next log backup writing.

sap hana log backup normal status

In an unusual situations, status could be set to TRUNCATED. In that case, new log backup are generated instead of re-using existing one with Free status. A number of log backup in a 'TRUNCATED' status could lead to space starvation. Truncated log backup are not reused and therefore new log backup are created until no space is left.

sap hana log backup truncated status

How to free disk space and remove sap hana log backup in truncate state?

Here is a way to free (to removed) all the truncated log backup and release space used.

1) Connect to the tenant database and run the command



SQL> ALTER SYSTEM RECLAIM LOG.

This command should release all the space occupied by log backup files in truncated state.

faxadm@linu07:/usr/sap/FAX/HDB02> hdbsql Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d LTS -u system -p KeepSecret01 Connected to fax@linu7.best.sap.hana.train:30047 hdbsql TLS=> ALTER SYSTEM RECLAIM LOG;

2) Check all truncated log backup are being released.

faxadm@linu07:/usr/sap/FAX/HDB02> hdbsql Welcome to the SAP HANA Database interactive terminal. Type: \h for help with commands \q to quit hdbsql=> \c -i 00 -d LTS -u system -p KeepSecret01 Connected to fax@linu7.best.sap.hana.train:30047 hdbsql TLS=> select b.host, b.service_name, a.state, count(*) from "PUBLIC"."M_LOG_SEGMENTS" a join "PUBLIC"."M_SERVICES" b on (a.host = b.host AND a.port = b.port) group by b.host, b.service_name, a.state | HOST | SERVICE_NAM | STATE | COUNT(*) | | ------ | ----------- | ------- | -------------------- | | linu07 | indexserver | Writing | 1 | | linu07 | indexserver | Free | 6 | | linu07 | dpserver | Writing | 1 | | linu07 | dpserver | Free | 1 | hdbsql TLS=> select file_name, state, in_backup from M_LOG_SEGMENTS | FILE_NAME | STATE | IN_BA | | ----------------------------------------------------------------- | ------- | ----- | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000000.dat | Writing | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000001.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000002.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000003.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000004.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000005.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000006.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00003.00007/logsegment_000_00000000.dat | Writing | FALSE | | /hana/log/FAX/mnt00001/hdb00003.00007/logsegment_000_00000001.dat | Free | FALSE |

3) If Log backup state are still in truncated state. (Continue with step 6 - Objectif :Restarting Tenant database)

Note : If log backup files in free state appear, do not jump too quickly to step 6, give time to sap hana to removed all truncated files first before deciding whether or not to restart the sap hana tenant database.

4) Proceeding to a complete BACKUP (FILE backup type ) for the tenant database.

$> hdbsql -U USERBACKUPK "backup data FOR TLS using FILE ('COMPLETE_DATA_BACKUP-20171203','TLS')" # Follow backup progression looking at date time and % # ========================================================= $> cdtrace $> cd TLS $> tail -f backup.log 2017-12-04T18:18:43+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 6% 1610612736/27548192768 2017-12-04T18:19:03+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 12% 3221225472/27548192768 2017-12-04T18:19:16+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 16% 4294967296/27548192768 2017-12-04T18:19:33+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 21% 5905580032/27548192768 2017-12-04T18:19:45+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 25% 6979321856/27548192768 2017-12-04T18:22:30+01:00 P053125 1602288e6ab INFO BACKUP progress of service: indexserver, linux07:30252, volume: 2, 31% 8589934592/27548192768

5) Stop the application server.

A) connect to the server application server
B) stopsap

6) Stop the tenant database ( via hana cockpit )

sap hana starting tenant database

7) Start the tenant database ( via hana cockpit )

sap hana starting tenant database

8) Check log backup status

select file_name, state, in_backup from M_LOG_SEGMENTS | FILE_NAME | STATE | IN_BA | | ----------------------------------------------------------------- | ------- | ----- | | /hana/log/FAX/mnt00001/hdb00003.00007/logsegment_000_00000000.dat | Writing | FALSE | | /hana/log/FAX/mnt00001/hdb00003.00007/logsegment_000_00000001.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000000.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00002.00007/logsegment_000_00000001.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00005.00007/logsegment_000_00000002.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00005.00007/logsegment_000_00000003.dat | Writing | FALSE | | /hana/log/FAX/mnt00001/hdb00005.00007/logsegment_000_00000004.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00004.00007/logsegment_000_00000005.dat | Free | FALSE | | /hana/log/FAX/mnt00001/hdb00004.00007/logsegment_000_00000006.dat | Free | FALSE |

9) Start application server

A) connect to the server application server
B) startsap

Sap hana SQL

Sap hana tools

- - -