SAP Support

  • SAP support
  • Dump files for support

SQL

  • SQL guide line
  • SQL references
  • Using SQL
  • Using Tables
  • Using Procedure
  • Queries and Updates
  • Using variables
  • Anonymous blocks

SQL Hands on

  • Get sql scripting skills

Tables

  • Using tables efficiently?
  • Different ways to create a table
  • Row & Column table differences
  • Row & Column tables storage
  • Create Column Table syntax
  • Create table examples
  • Ways to insert data into tables
  • Ways to select data from tables

SAP Hana Jobs

  • Finding a job in sap hana

Sap Hana training courses

  • Online training courses

Tips & Tricks

  • About tables.
  • Unexpected table type

Administration tasks

  • Daily Tasks
  • Start/Stop database
  • Start/Stop Hana System
  • hdbrsutil process tips and tricks
  • Stop or cancel SQL queries
  • Refresh data from another tenant database

Administrator User

  • Overall user administration
  • Unlock SYSTEM User
  • Unlock User
  • Activate, Unlock a user
  • Reset System user lost password
  • Create administrator User
  • Create multiple users in one go
  • Copy a user
  • Check user active or not
  • Check user status
  • Check password expiration date
  • Reset User connection attempt
  • Update password expiration date
  • Update login until date
  • Change password on first login
  • Change password on next login
  • Show Password expiration date
  • Update Password expiration date
  • Connection & password security
  • Disable password validity period
  • New user password
  • Keep old password
  • Retrieve User details
  • Create any user type
  • Drop User
  • Update User
  • De-activate/Activate User
  • Troubleshoot User

Standard Users

  • Create User with SQL script
  • Create User via Hana Studio

Technical users

  • General Tips
  • Backup User
  • Modeler User
  • Data provisioning User
  • Cockpit User
  • A user to unlock users

Configuration

  • Configuration
  • Kill long queries automatically

Log & Trace Files

  • Log and trace directories
  • Investigate backup log content

About Memory

  • Memory analysis
  • Peak Used memory
  • Peak Memory between 2 dates
  • Physical memory
  • Memory Investigation Scenarios
  • Allocated memory
  • Used memory
  • Used memory - Tables
  • Used memory - System Tables
  • Used memory - Column tables
  • Used memory - Delta storage
  • Used memory - Row tables
  • Used memory - Services
  • Used memory SQL
  • Used memory - code & stack
  • Table storage comparison

Memory Operations

  • Adding physical memory

Disk

  • Reclaimable space
  • Reclaim space

Security

  • Security Guide Line
  • Before updating a password

Privileges

  • Roles and privileges
  • Grand read access on a schema
  • Privileges to unlock user
  • Grant privileges

Backup & Recovery

  • Overview
  • Configuration
  • Backup
  • Restore/Recover
  • Checking
  • Log backup directories
  • Get backup catalog size
  • Restore database manually
  • Analyse backup log file

Troubleshooting

  • Overview
  • Common issues
  • Bugs
  • Errors
  • Performance
  • Memory
  • Alerts
  • Scripts and Procedures
  • Volume Disk full
  • Long queries
  • Alert 10: passwd authentication
  • Alert 65 log backup running too long
  • Alert 413: cannot reuse password
  • Alert 414: password to be changed
  • Alert 431: password expiration
  • 503 Service not available
  • System User lost password
  • Backint backup failure
  • Hana System cannot start
  • Locked transactions

Analyses

  • Bugs
  • Errors
  • Performance
  • Memory
  • Alerts
  • Scripts and Procedures

Investigations

  • Get Sap hana version details?
  • Get to Log and Trace directories
  • Get log backup directories
  • Get Sap Hana default Port
  • Get Session Details
  • Get Open Session list
  • Get hana user status
  • Get dump files diagnosis

Hdbsql

  • Using hdbsql?
  • Executing SQL
  • Formating output

Hana Cockpit

  • Overview
  • General Configurations?
  • URL to access Tenant
  • URL to access SystemDB
  • Troubleshooting
  • Administration
  • Monitoring
  • Open & Access Cockpit

Hana Studio

  • Installation
  • Guideline
  • SQL Console

Hdbuserstore

  • Using hdbuserstore

SapControl

  • Using sapcontrol

Free Hands on

  • Free sap hana version

Hardwares

  • Hardware for sap hana
  • Possible Architectures

Smart Data Integration

  • Configure Smart Data Integration
  • Create Data Provisioning Project Users

FAQ

  • Frequently Asked Questions

upv6r1-201911

menu.
Best sap hana training
Digg Email Facebook Google Pinterest StumbleUpon Tumblr Twitter VK linkedin
sap hana user system reset lost password
advertisement


How to reset a corrupted SYSTEM User Password in sap hana?

There are several ways to change the system user password.
First of all whether it is at the SYSTEMDB level or at the tenant database level, the advisable procedure to follow is to use the standard SQL syntax: ALTER USER SYSTEM PASSWORD < new_password > to change the password. It can be done using hana studio SQL console or at the server level using hdbsql.
The second method is when you lost control, you cannot connect anymore or the system password is lost. Be Aware, this method is disruptive as the entire system has to be shutdown. It is not a complicated procedure but be careful and plan for service interruptions before going ahead. Take into account the time it takes to shutdown and restart the entire system, the time it takes for any application to become available to users. Production environment is obviously to be scheduled carefully but other environments such as development and validation will also affect projects, test and validation processes if it become unavailable.


Which procedure to follow to reset sap hana system user password

The procedure is simple but steps are different depending on whether you are dealing with the SYSTEMDB or with a single tenant database part of a multitenant database system. For the system database ( SYSTEMDB ) you just need to re-initiate a new password on the nameserver. For individual tenant database, resetting password will have to be done at the indexserver level. Keep in mind, dealing at the SYSTEMDB level required shutting down the whole system with all associated tenant databases. Dealing with a tenant database requires shutting down the database but does not affect other tenant databases nor the SYSTEMDB.


Steps to reset sap hana system password at the SYSTEMDB level

The following system password resetting method is for a system database of a multitenant database system. Note the procedure is the same for all hana versions 1 and 2. Keep in mind though, methods could change with time and with newer sap hana versions. It is advisable to check your hana system version in the first place and the sap documentation. The Sap Documentation below gives you access to all hana versions.

Preparing the operation.
- Contact all users who will be affected and agree about a date and time.
- Decide who will be dealing with the stop and start of the application server.
- Scheduled the operation.

0. ( Open an OS Session Appli Server )
Shutdown all related application servers.
1. ( Open an OS Session 1 )
: connect as (< sid >adm) to the server.
1.1 Check current services status

-- In this example all services are running $> sapcontrol -nr 07 -function GetProcessList 12.07.2017 09:31:15 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2017 03 13 14:07:46 2898:23:29 132500 hdbcompileserver HDB Compileserver GREEN Running 2017 03 13 14:08:46 2898:22:29 133329 hdbindexserver HDB Indexserver-BBT GREEN Running 2017 04 13 17:12:20 2152:18:55 96965 hdbindexserver HDB Indexserver-BHP GREEN Running 2017 05 23 10:51:11 1198:40:04 97453 hdbindexserver HDB Indexserver-LTB GREEN Running 2017 03 13 14:08:50 2898:22:25 133372 hdbindexserver HDB Indexserver-DBW GREEN Running 2017 03 13 15:00:47 2897:30:28 30612 hdbnameserver HDB Nameserver GREEN Running 2017 03 13 14:07:47 2898:23:28 132517 hdbpreprocessor HDB Preprocessor GREEN Running 2017 03 13 14:08:46 2898:22:29 133331 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2017 03 13 14:10:49 2898:20:26 137711 hdbindexserver HDB Indexserver-GRB GREEN Running 2017 06 22 10:39:45 478:51:30 129126

2. ( Open an OS Session 2 )
: Shut down the instance :
sapcontrol -nr -function StopSystem HDB
2.1 Check current services status
- (Nothing should be running)
$> sapcontrol -nr < instance > -function GetProcessList
3. In a new session ( OS Session 3 )
start the name server with the -resetUserSystem option :

/usr/sap/BAT/HDB07/hdbenv.sh
/usr/sap/BAT/HDB07/exe/hdbnameserver -resetUserSystem

4. Enter a new password for the SYSTEM user.
The following message will be showing up but dont be surprise others messages and details will keep on appearing as well:
"resetting of user SYSTEM - new password" : Wait until no message comes up anymore and enter the new password ( Copy / Paste password would be best method )

Important Notes : This procedure resets the password for SYSTEMDB and shutdown the nameserver.

5. ( OS Session 4 ) start the nameserver which has just been stopped :
$> sapcontrol -nr < instance > -function StartSystem HDB

Important Notes : Whether the SYSTEM user was deactivated volontary or not, it will be reactivated. It is your responsability to deactivate it again if it is part of your company policy.

6. Check system user connection with the new password.

Using hdbsql would be advisable in order to avoid misspelling with the password.
batadm@linux07$> hdbsql
hdbsql=> \c -i 07 -d SYSTEMDB -u system -p DoNotForgetThisTime01

7. ( Session Appli Server )Start all related application servers.

Sap Documentation


Steps to reset sap hana system password for a TENANT database

The following system password resetting method is for a tenant database of a multitenant database system sap hana version 2. Again cross check hana version and sap documention. The Sap document link below will give you access to any hana version on the reset password subject for a tenant database.

1. ( OS Session 1 )
Stop the tenant database using the SQL statement:
ALTER SYSTEM STOP DATABASE < database_name >
You can execute the command using hana studio or hdbsql.
ex : hdbsql

batadm@linux07> hdbsql => \c -i 07 -d SYSTEMDB -u USR_ADM -p ToKeepSecret01 Connected to BAT@localhost:30013 ALTER SYSTEM STOP DATABASE BBT; 0 rows affected (overall time 18.077877 sec; server time 18.074765 sec)

2. ( OS Session 2 )
Check the tenant database is down.
sapcontrol -nr < instance number > -function GetProcessList | grep -i < database name >

sapcontrol -nr 07 -function GetProcessList | grep -i BBT

2. ( OS Session 3 ) : connected as < sid >adm to the server.

2.1 Export the tenant database name to a OS variable then start the index server

export DBNAME=BBT /usr/sap/BAT/HDB07/hdbenv.sh /usr/sap/BAT/HDB07/exe/hdbindexserver -port < internal port > -resetUserSystem

2.2 Enter a new password when the message "resetting of user SYSTEM - new password" appears.

Important Notes : This procedure resets the password for tenant database and shutdown the related indexserver.

3. ( OS Session 1 )
Restart the tenant database with the SQL syntax:
ALTER SYSTEM START DATABASE < database_name >

Important Notes : Whether the SYSTEM user was deactivated volontary or not, it will be reactivated. It is your responsability to deactivate it again if it is part of your company policy.

6. ( OS Session 2 )
Check system user connection with the new password.

How to connect to a tenant database using hdbsql?
Sap Documentation

How long does it take to reset system user password in sap hana?

Without taking into account the system shutdown and startup, the procedure ( preparing and executing ) could take 15 to 30 minutes to reset a system user password. It is long time comparing to the simple SQL command which takes few seconds. The global time to get back to a usable database system depends on how long it takes to restart either the overall system or just the tenant database. Either way, a production database system downtime could be dangerously affected by this reset password operation.


How to prevent resetting sap hana system password at the OS level?

Like any other users the system user can be locked. The system user password can also be forgotten or lost. In order to face those 2 possibles situations and find an immediate non disruptive solution here is what can be done. Create a very specific user to unlock other user accounts or to change user password. The other possibility is to create a unlock and change user password stored procedure.


How to create a sap hana user to handle lost of password and locked user issues?

Putting in place a user administrator account has got many advantages. The task of dealing with everything regarding user accounts can be given to a specific team. New user, De-activated user, Change of password can be independantly dealt with. The system administrator can concentrate on more important issues such as performance, error and alert messages. In order the create a user manager account with the ability to unlock user and reset password, you have to assign the system privilege USER ADMIN to the account profile.

Resetting system password at Systemdb level example

Following "how to do" description is an important part with a real system passord reset example. This will help to understand what is required. The following sap hana system user reset password example has been created out of notes from a real situation.

A/. Get task details ready.

Server name : linux07
Instance number : 07
SID : BAT
Password ready: DoNotForgetThisTime01 ( cut and paste operation )
Different Contacts : Application users, Operation and monitoring, Project team leader, Operator

- Everyone concerned has been informed about the shutdown of the entire sap hana system.
- Application server stop and start tasks have been synchronized with the operator team.
- Who will stop and start the application server has been decided.
- 4 differents os sessions ready.

B/. Let s start the procedure.

O. ( Os session Appli ) . Stop application server. 1. ( OS Session 1 : connecter as batadm ) Check current services status: batadm@linux07:/usr/sap/BAT/HDB07> sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }' 21.09.2018 13:26:52 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2018 09 21 13:17:22 0:09:30 66124 hdbcompileserver HDB Compileserver GREEN Running 2018 09 21 13:17:32 0:09:20 67221 hdbindexserver HDB Indexserver-DBHR2 GREEN Running 2018 09 21 13:17:34 0:09:18 63324 hdbindexserver HDB Indexserver-DBBW2 GREEN Running 2018 09 21 13:17:34 0:09:18 67326 hdbindexserver HDB Indexserver-DBTBW GREEN Running 2018 09 21 13:17:34 0:09:18 67520 hdbnameserver HDB Nameserver GREEN Running 2018 09 21 13:17:23 0:09:29 66956 hdbpreprocessor HDB Preprocessor GREEN Running 2018 09 21 13:17:32 0:09:20 67703 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2018 09 21 13:20:39 0:06:13 73938 2. ( OS Session 2 : connecter as batadm ) : Shut down the instance : /usr/sap/BAT/HDB07/exe/sapcontrol -nr 07 -function StopSystem HDB 21.09.2018 11:43:50 StopSystem OK 3. ( OS Session 1 : connecter as batadm ) Check current services status - (All databases should be down) batadm@linux07:/usr/sap/BAT/HDB07> sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }' 21.09.2018 15:17:21 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GRAY Stopped 77506 3.1 ( OS Session 1 : connecter as batadm ) Check for any remaining processes. Nameseerver in particular. batadm@linux07:/usr/sap/BAT/HDB07> ps -ef | grep -i BAT batadm@linux07:/usr/sap/BAT/HDB07> ps -ef | grep -i hdbnameserver # # If you find a process, investigate before going any further # How to check for sap hana zombie process? # 4. ( OS Session 3 : connecter as batadm ) start the name server with the -resetUserSystem option : /usr/sap/BAT/HDB07/hdbenv.sh /usr/sap/BAT/HDB07/exe/hdbnameserver -resetUserSystem 4.1 Enter a NEW PASSWORD for the SYSTEM user. when the following message "resetting of user SYSTEM - new password" appears : wait until no other messages come up anymore and enter the new password ( Copy / Paste password ) 5. ( OS Session 4 : connecter as batadm ) start the instance : /usr/sap/BAT/HDB07/exe/sapcontrol -nr 07 -function StartSystem HDB 6. ( OS Session 1 : connecter as batadm ) Check current services status - - All databases should be up and running, - Rerun the following command wait until everything is GREEN batadm@linux07:/usr/sap/BAT/HDB07> sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }' 21.09.2018 13:26:52 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2018 09 21 13:17:22 0:09:30 66124 hdbcompileserver HDB Compileserver GREEN Running 2018 09 21 13:17:32 0:09:20 67221 hdbindexserver HDB Indexserver-DBHR2 GREEN Running 2018 09 21 13:17:34 0:09:18 63324 hdbindexserver HDB Indexserver-DBBW2 YELLOW Running 2018 09 21 13:17:34 0:09:18 67326 hdbindexserver HDB Indexserver-DBTBW YELLOW Running 2018 09 21 13:17:34 0:09:18 67520 hdbnameserver HDB Nameserver GREEN Running 2018 09 21 13:17:23 0:09:29 66956 hdbpreprocessor HDB Preprocessor GREEN Running 2018 09 21 13:17:32 0:09:20 67703 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2018 09 21 13:20:39 0:06:13 73938 batadm@linux07:/usr/sap/BAT/HDB07> sapcontrol -nr 07 -function GetProcessList | awk -F"," '{printf "%20s %20s %10s %10s %23s %15s %10s\n",$1, $2, $3, $4, $5, $6, $7 }' 21.09.2018 13:26:52 GetProcessList OK name description dispstatus textstatus starttime elapsedtime pid hdbdaemon HDB Daemon GREEN Running 2018 09 21 13:17:22 0:09:30 66124 hdbcompileserver HDB Compileserver GREEN Running 2018 09 21 13:17:32 0:09:20 67221 hdbindexserver HDB Indexserver-DBHR2 GREEN Running 2018 09 21 13:17:34 0:09:18 63324 hdbindexserver HDB Indexserver-DBBW2 GREEN Running 2018 09 21 13:17:34 0:09:18 67326 hdbindexserver HDB Indexserver-DBTBW GREEN Running 2018 09 21 13:17:34 0:09:18 67520 hdbnameserver HDB Nameserver GREEN Running 2018 09 21 13:17:23 0:09:29 66956 hdbpreprocessor HDB Preprocessor GREEN Running 2018 09 21 13:17:32 0:09:20 67703 hdbwebdispatcher HDB Web Dispatcher GREEN Running 2018 09 21 13:20:39 0:06:13 73938 7. Check connection with new password ( OS Session 1 : connecter as batadm) - hdbsql \c -i 07 -d SYSTEMDB -u system -p DoNotForgetThisTime01 Connected to localhost:30013 hdbsql SYSTEMDB=> 8. ( Os session Appli ) . Start application server

Adding more physical memory to a sap hana system
sap hana row tables used memory
 sap hana memory usage
create sap hana procedures
execute sap hana procedures
Using If condition in sap hana procedures
Using SQL While loop in a sap hana procedures
Using Sap hana variables in a sap hana procedures
sap hana sql for loop

Back to top

Copyright Ⓒ 2020 Best Sap Hana Training. All rights reserved
Recommended web hosting : www.siteground.com