Troubleshooting Sap Hana performance, alert and errors requires a methodological approach. Different areas have been identified within the hana system environment where issues have been recorded. Solutions for sap hana problems have been found from various sites addresses.
Looking for a solution to a problem on Sap Hana can be time consuming. Sometime there is no choice than to call the Sap Hana Support. However the world community is also a source of treasure where Sap hana solutions can be found.
Troubleshooting in sap hana is not only related to error and performance but also to abnormal situation. It does happen that parameter settings do not take action as expected. As such it is very important to take a step forward and put things right as soon as possible in order to avoid any problem in the future.
When tracing errors and poor performance reasons within your Sap Hana system, it is important to look at the primary cause of it. There are several sectors to consider within the Hana troubleshooting analyses process.
Common Sap Hana Problems and issues
Common and regular sap hana issues can be categorized in 4 types. Other problems exist but they happen one in a blue moon or they are more serious situations and they are not part of the regular issues. Database does not start, performance problem ,. . . should not be regular problem otherwise there is something very wrong with your system.
An issue does not specially mean there is a problem but an action is required in order to avoid one.
Here are the 4 commons situations:
1/ Connections problems from regular or technical users.
ex: locked user, expired password
2/ High Memory Consumption.
ex: An application batch or a data import is running,
3/ Backup failure.
ex: Could not run because of locked user,
4/ Disk almost full.
ex: too many log and trace files
What to do to unlocked a Sap Hana user?
First of all, find out how serious is the situation. A technical user would be a serious case. Batch or backup procedures will fail. That would mean an impossible data update or an impossible database recovery. When it comes to a locked personal user, you have to check the reason and unlock it if necessary.
For what reasons a sap hana user would be disabled?
There are different reasons why a hana user can be locked. It is therefore important to investigate in the right direction in order to respond adequately and unlock the user. In most cases, a user will be locked because of too many login attempts with the wrong password. However, a Hana user can also be locked because of security policy towards temporary users accessing the company premises. An account will be automatically locked after a specific date.
How to keep technical user up and running in sap hana?
All users have the same password policy. By default it has to be updated every 182 days (maximum_password_lifetime). If not the hana user account will be locked. That situation is, however not practical for every type of user. A technical user is a user type such as Backup user or Operational User. It is not attached to a person. If a technical user is disabled, backup or batch processes will stop working. Therefore it is recommended to disable the maximum_password_lifetime policy for technical users. Connection security will be maintained for each hana technical user using a secured Key. see also : how to secure a sap hana connexion ?
One way to unlock a user account in sap hana?
Someone calling the database administration because his or her account is not accessible is very common in a big company organization. It is to expect in the following circumstances: 1/ At the end of summer holidays when everyone come back. Either someone would have forgotten his password or his password may be expired, 2/ When a general account is shared and someone new tried too many times to connect. The account is locked due to too many failed connections.
Unlock user after too many login attempts
Script Unlock User
1. Unlock user : ALTER USER < username > DROP CONNECT ATTEMPTS; 2. reset login pocily : ALTER USER < username > RESET CONNECT ATTEMPTS;
Disable password life time constraint for Technical Users
Script disable password 182 days lifetime limit
ALTER USER < username > DISABLE PASSWORD LIFETIME;
The Situation : Connexion is done via a secured key but the user linked to the key has been dropped or de-activated .
Quick Solution : 1. - Re-create or Re-activate User 2. - Reinitiate the secured key
Investigation and solutions : -1. Show secured key definition : hasadm@linux06:/usr/sap/HAS/HDB03> hdbuserstore list -2. Check User name and status : SELECT * FROM "SYS"."USERS" WHERE USER_NAME = 'AUSERNAME'; --> Look at : VALID_UNTIL, PASSWORD_CHANGE_TIME, USER_DEACTIVATED, IS_PASSWORD_ENABLED -3. - If NO USER : Recreate lost username via SQL script. ex : CREATE USER < USERNAME > PASSWORD APwd01 NO FORCE_FIRST_PASSWORD_CHANGE; ---- If the user is DE-ACTIVATED : unlock / re-activate the user ---- If it is a TECHNICAL USER : disable the maximum_password_lifetime policy -4. Re-initialize secured key : $> hdbuserstore SET USERSECUREKEY linux06.ab.ddl-angl.dom:30141 AUSERNAME USERpwd01 5. Test connexion via userstoredkey : $> hdbsql hdbsql=> \c -i 01 -U USERSECUREKEY Connected to HAP@linux06.ab.ddl-angl.dom:30141
Connexion error - case 2
10: authentication failed SQLSTATE: 28000
The Situation : Connexion is done via a secured key but the user password linked to the key has been updated.
Quick Solution : 1. - Reinitiate the secured key with the new password
Investigation and solutions : 1. Show secured key definition : hasadm@linux06:/usr/sap/HAS/HDB03> hdbuserstore list 2. Check User name and status : SELECT * FROM "SYS"."USERS" WHERE USER_NAME = 'AUSERNAME'; --> Look at : VALID_UNTIL, PASSWORD_CHANGE_TIME 3. Re-initialize securec key : $> hdbuserstore SET USERSECUREKEY linux06.ab.ddl-angl.dom:30141 AUSERNAME NEWUSERpwd01 4. Test connexion via userstoredkey : $> hdbsql hdbsql=> \c -i 01 -U USERSECUREKEY Connected to HAP@linux06.ab.ddl-angl.dom:30141
Predefined Memory threshold alert will be triggered and will appear in sap hana studio and sap hana cockpit. Memory is a very important part of sap hana and any alert messages should be look at seriously.
NB : You have to be registered with SAP SUPPORT in order to access and read Sap note solution description.
Sap Note Alert 54 is linked to a sap hana savepoint taking too long to finish.
Poor save point response time in Sap Hana can be due to different reasons :
- Long SQL Queries, - Running jobs, - Backup on the go, - Too many blocked transactions.
> 1/ Search for alert file regarding CPU and IO :
The Alert file Name would be : - "Host CPU Usage", - "Sync/async read ratio", - "Sync/async write ratio"
Using hana studio : --> SAP HANA Administration Console, Then --> "Alert"tab and "Diagnosis Files" tab
On the server :
Search file names in log an trace directories
For SYSTEMDB : /usr/sap/< SID >/HDB< instance nb >/< hostname >/trace for SYSTEMDB
For Tenant database : /usr/sap/< SID >/HDB< instance nb >/< hostname >/trace/DB_< tenant name >
> 2/ Get Savepoint duration details using :
> 3/ Investigate IO Issues. --> Use the Minicheck I/O reports in Hana Studio
> 4/ Analyze All CPU issues. 3 possible reasons :
A/ The Alert threshold setting is incorrect in Sap Hana B/ High CPU is real. Here are some investigation areas :
- SQL Queries, - Hanging Queries and Jobs - Data loading - Too long transactions.
C/ In a case of a Sap Hana bug. The Sap support will tell you.
Sap Note Alert 65 : is linked to backup log processes taking too long to complete.
Slow Sap Hana log backup can be due to different reasons :
The HANA backup catalog is backed up as part as each backup. The catalog continuously grows in size with times. Therefore backup time will increase in relation to the overall backup size.
Check whether backup catalog housekeeping is done regularly.
3. There are I/O Issues
Slow I/O will result in poor backup performance.
Check Hana I/O performance
4. Delays in communication between BACKINT and SAP HANA.
Check log backup (backup.log) for any delays between the BACKINT interface and SAP HANA.
5. A crash of a HANA Service can trigger the alert.
Check for any HANA crash dumps for the service that generated the alert.
How to troubleshoot sap hana using SQL queries ?
Sap Hana Problems can be identified rapidely using adequate SQL queries. The following free scripts are provided in order to find answers to specific Sap hana trouble situations. Also, Solution for specific errors can be find quickly using anlyses procedure steps.
1. Long write transactions will interfere with hana database performance considerably if it is not commited at regular interval.
1. Checking long uncommitted write transactions
SECONDS_BETWEEN(T.START_TIME, CURRENT_TIMESTAMP) TOTAL_TIME,
FROM M_TRANSACTIONS T, M_CONNECTIONS C
AND T.TRANSACTION_TYPE='USER TRANSACTION'
AND T.UPDATE_TRANSACTION_ID > 0
AND SECONDS_BETWEEN(T.START_TIME, CURRENT_TIMESTAMP) > 0;