Free Sap Hana troubleshooting Guideline How to find solutions for hana errors?
Sap Hana Troubleshooting Step by Step
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 addresse. Links are provided when needed.
Troubleshooting sap hana requires analyses and investigations. Sap hana is a new database product and solutions for bugs and errors are not always available up front on the net. It does not make it easy but the sap support is always available when no solution can be found.
Free Sap Hana troubleshooting procedures
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 futur.
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.
Free Sap hana solutions.
There are solutions available in the world community. Sap hana is a young product getting maturity. Solutions are getting shared on the web more and more. Going through the slow process of Sap support to resolve common issues is less and less needed.
Common Sap Hana Problems and issues
After a new sap hana system has been installed, errors and recurrent blocking situations would be happening occasionaly until the administrator user decides which step forward to use in order to avoid those problems. Most of the time, it could be sorted out via parameter settings.
What to do in case of locked user in Sap Hana?
First of all, find out how serious is the situation. A technical user would be a serious case as no batch nor backup procedure can be performed. 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 pratcial 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 recommanded to disable the maximum_password_lifetime policy for technical users. Connexion security will be maintained for each hana technical user using a secured Key. see also : how to secure a sap hana connexion ?
How to unlock a user in sap hana ?
Here are syntaxes exemples :
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;
Sap hana is a solftware and like any software there are bugs that you might be going to through at one time or another
What to do when things do not work as expected in Sap Hana?
With time non expected sap hana behavious can be discovered. The hana environment production may not be immediately affected but an action is required to avoid a problem in the futur.
In most cases it is related to sap hana bugs. You are strongly advised to contact Sap support in order to obtain a solid solution.
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.
How to deal with sap hana alert messages?
NB : You have to be registered with SAP SUPPORT in order to access and read Sap note solution description.
Sap Note Alert 54 is in relation 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 in relation 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.
Free Sap Hana troubleshooting with SQL scripts
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.
How to troubleshoot sap hana using SQL queries ?
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
SELECT T.HOST, T.PORT, C.LOGICAL_CONNECTION_ID, C.CLIENT_HOST, C.CLIENT_PID, T.TRANSACTION_ID, T.UPDATE_TRANSACTION_ID, SECONDS_BETWEEN(T.START_TIME, CURRENT_TIMESTAMP) TOTAL_TIME, T.MIN_MVCC_SNAPSHOT_TIMESTAMP
FROM M_TRANSACTIONS T, M_CONNECTIONS C
WHERE T.CONNECTION_ID=C.CONNECTION_ID AND T.TRANSACTION_STATUS=’ACTIVE’ AND T.TRANSACTION_TYPE=’USER TRANSACTION’ AND T.UPDATE_TRANSACTION_ID > 0
AND SECONDS_BETWEEN(T.START_TIME, CURRENT_TIMESTAMP) > 0;