Statement Memory Limit in SAP HANA

The parameter is called "Statement_memory_limit". This parameter will help ensure a single user’s statement does not cause “Out of Memory” scenarios on the HANA appliance. A system wide “Statement_memory_limit” was introduced with HANA SP8

When reaching the statement memory limit, a dump file is created with "compositelimit_oom" in the name. The statement is aborted, but otherwise the system is not affected. The unit of measure is GB.

Default : 0 (no limit)

Set system level Statement memory limit

a. Login into HANA Studio and navigate to the “Configuration” tab.
b. Open "Global.ini–>memorymanager"
c. Set "statement_memory_limit" = desired limit in MB’s


1> This limit will be system specific depending on the Global Allocation Limit, if the system limit has never been set and the goal is to only enable user specific limits set the limit to a very high percentage of the Global allocation limit. For example if global allocation limit is set to 234496 MB, set the Statement_memory_limit to 200000 MB. The system limit will never be met and basically ignored.



2> The system limit HAS to be set to enable USER limits.
3> Note setting the "Statement_memory_limit_threshold" instead or in addition to the "statement_memory_limit" will DISABLE the USER limit. This has not been determined if a bug with Rev 91 or SAP design.

Enable user level Statement memory limit :

1. Login into HANA studio.
2. Navigate to Security–>Users and select the user the limit is required for.
3. Double click the user and go to the "User Parameter" tab.
4. Click the "+" button to add a new parameter.
5. Select the "Statement Memory Limit" parameter.
6. Enter a the desired CAP in GB and save user. Below example is set to 5 MB.

Allocation limit in SAP HANA

Collectively, all processes cannot consume more memory than the global allocation limit. The allocation limit limits the amount of memory that can be used by individual processes. The value is the maximum allocation limit in MB.

Parameter Name - allocation_limit
Default Value - A missing entry or a value of 0 results in the system using the default settings. The default allocation limit is calculated in the same way as the default global allocation limit.

About Parameter -
The allocation limit limits the amount of memory that can be used by individual processes. The value is the maximum allocation limit in MB.

-- set the global_allocation_limit=2048

hdbsql=> select round(allocation_limit/1024/1024,2) allocation_limit from m_host_resource_utilization;
ALLOCATION_LIMIT
-----------------------------
13605.15


-- set the global_allocation_limit=20480

> select round(allocation_limit/1024/1024,2) allocation_limit from m_host_resource_utilization;
ALLOCATION_LIMIT
-----------------------------
20480

SAP HANA Parameters

I am happy to say that I have put to gather some excellent SAP HANA Parameter which will help you to get more detail about SAP HANA parameters. Please check below for HANA Parameters.

FAQ :  What is the purpose of SAP HANA parameters?

SAP HANA parameters having configuration options and that can be used to influence the behaviour of SAP HANA in various ways. there are reasons for setting parameters are:

- Individual security settings
- Workaround for reported bugs
- Influencing CPU and memory resource consumption at host level
- Definition of file and directory locations in HANA

1. SAP HANA - Parameters Related to Memory Consumption

SAP HANA Parameter -  global_allocation_limit
SAP HANA Parameter -  allocation_limit
SAP HANA Parameter -  statement_memory_limit
SAP HANA Parameter -  statement_memory_limit_threshold

2. SAP HANA - Parameters Related to Unloads and loads

SAP HANA Parameter -  unload_lower_bound
SAP HANA Parameter -  unused_retention_period
SAP HANA Parameter -  unused_retention_period_check_interval
SAP HANA Parameter -  tablepreload_write_interval

3. SAP HANA - Parameters Related to Reload and Preloads

SAP HANA Parameter -  reload_tables
SAP HANA Parameter -  tables_preloaded_in_parallel
SAP HANA Parameter -  preload_column_tables

4. SAP HANA - Parameters Related to Shared Memory

SAP HANA Parameter -  keep_shared_memory_over_restart
SAP HANA Parameter -  reuse_shared_memory_for_restart
SAP HANA Parameter -   keep_shared_memory_over_restart_timeout
SAP HANA Parameter -   reuse_shared_memory_for_restart_timeout

Global Allocation Limit in SAP Hana

The global allocation limit limits the amount of memory that can be used by the system as a whole. The value is the maximum allocation limit in MB. Note:In a system that supports multitenant database containers, the global allocation limit configured at the system layer of the global.ini file is always effective regardless of any value configured at the database layer.

Parameter Name - global_allocation_limit
Default Value - A missing entry or a value of 0 results in the system using the default settings. The global allocation limit is calculated by default as follows: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB. Or, in the case of small physical memory, physical memory minus 1 GB.

About Parameter - The global_allocation_limit parameter is used to limit the amount of memory that can be used by the database. The value is the maximum allocation limit in MB. A missing entry or a value of 0 results in the system using the default settings. The global allocation limit is calculated by default as follows: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB. Or, in the case of small physical memory, physical memory minus 1 GB.

Change the Global Memory Allocation Limit -

The SAP HANA database preallocates a pool of memory from the operating system over time, up to a predefined global allocation limit. You can change the default global allocation limit in the global.ini configuration file.

Prerequisites - You have the system privilege INIFILE ADMIN.

Procedure :

1> In the Administration editor, choose the Configuration tab.
2> The configuration files that contain the configuration information for the system are displayed.
3> Expand the global.ini configuration file and then the memorymanager section.
4> In the context menu for the global_allocation_limit parameter, choose Change.
5> The Change Configuration Value dialog box appears.
6> Enter a value for the entire system and/or individual hosts.
7> If you enter only a value for the system, it is used for all hosts. For example, if you have 5 hosts and you set the limit to 5 GB, the database can use up to 5 GB on each host (25 GB in total). If you enter a value for a specific host, then for that host, the specific value is used and the system value is only used for all other hosts. This is relevant only for multiple-host (distributed) systems.

how to check log backup in sap hana

Step 1:    Login to Database host as sidadm
     
Step 2:   Check if BKPMON key is working fine

hdbsql -jU bkpmon
Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands
       \q to quit

hdbsql=>
hdbsql SID=>

    •If not check user privileges and grant missing role

Step 3:    Check what is log mode:

  SELECT DISTINCT log_mode FROM SYS.M_LOG_BUFFER

Step 4:    Check if log backups are running fine:

 select * from "SYS"."M_BACKUP_CATALOG" order by SYS_START_TIME desc

Checking Bkpmon User Status: 

Run the below command from OS :

> hdbuserstore list

DATA FILE       : xyz/usr/sap/SID/home/.hdb/vsa01/XSFS_HDB.DAT
KEY FILE        : xyz/usr/sap/SID/home/.hdb/vsa01/XSFS_HDB.KEY

KEY BKPKEY
  ENV : vadbabc:3001355
  USER: bkpkey
KEY WW
  ENV : vadbabc:3001355
  USER: system

How to Reset System User Password in SAP Hana

If the SYSTEM user’s password is lost, you can reset it as the operating system administrator by starting the index server in emergency mode.

Step 1. Login to SAP HANA Server

Log on to the server on which the of the master index server is running

Step 2.Switch to SAP HANA Admin user

Now we need to switch to SAP HANA admin user using following command
su – sidadm

Step 3.Stop the Database

Shut down the instance by executing the following command:/usr/sap//HDB/exe/sapcontrol -nr -function StopSystem HDB

Step 4.Start the Nameserver in Background

Now start the name server in background by executing the following commands:

○xyz/usr/sid/HDB/exe/hdbnameserver &

Step 5.Start the  Compile Server in Background

start the compile server by executing the following
○xyz/usr/sid/HDB/exe/hdbcompileserver &

Step 6.Start the Indexserver with Special Flag resetUserSystemNote

Now start the index server by executing this command

xyz/usr/sid/HDB/hdbenv.sh
○xyz/usr/sid/HDB/exe/hdbindexserver -resetUserSystemNote

Note:In a scale-out system, you only need to execute the commands on the master index server.

The following prompt appears: resetting of user SYSTEM – new password

Step 7.Enter a new password for the SYSTEM user.

You must enter a password that complies with the password policy configured for the system.
The password for the SYSTEM user is reset and the index server stops.

Step 8.Kill Compileserver and Nameserver

In the terminals in which they are running, run the following command

HDBinfo

HDB info displays the list of hdb process that are running.

Now you kill the corresponding Process ID of compile server and name server by below command

Kill -9

Step 9.Start the Database

Then start the instance by executing the following command:xyz/usr/sid//HDB/exe/sapcontrol -nr -function StartSystem

If you previously deactivated the SYSTEM user, it is now also reactivated. This means you will need to deactivate it again.

How to deactivate user in SAP Hana

Deactivate the SYSTEM User:

As the most powerful database user, SYSTEM is not intended for use in production systems. Use it to create lesser privileged users for particular purposes and then deactivate it.

SYSTEM is the database superuser. It has irrevocable system privileges, such as the ability to create other database users, access system tables,etc. In addition, to ensure that the administration tool SAP HANA cockpit can be used immediately after database creation, SYSTEM is automatically granted several roles the first time the cockpit is opened with this user.Note SYSTEM does not automatically have access to objects created in the SAP HANA repository.

In a system with multitenant database containers, the SYSTEM user of the system database has additional privileges for managing tenant databases, for example, creating and dropping databases, changing configuration (*.ini) files of databases, and performing database-specific data backups.

Prerequisites:

You have the system privilege USER ADMIN.

The administrator can deactivate a user account with the following SQL command or SQL console of the SAP HANA studio:

ALTER USER DEACTIVATE USER NOW
Example:

ALTER USER SYSTEM DEACTIVATE USER NOW

After the user account is deactivated, the user cannot log on to the SAP HANA database until

the administrator resets the users password.


Note: You can still use the SYSTEM user as an emergency user even if it has been deactivated. Any user with the system privilege USER ADMIN can reactivate SYSTEM with the statement

 ALTER USER SYSTEM ACTIVATE USER NOW.

---This will list down the deactivated/locked users.

select * from "sys"."users" where user_deactivated='true';
---or---
select * from "public"."users" where user_deactivated='true';

select name, user_deactivated, invalid_connect_attempts from "sys"."p_principals_" where name  ='username'