HANA Housekeeping using HANACleaner

Purpose

The blog provide guidelines on the necessary steps that needs to be followed to automate housekeeping task using SAP HANACleaner script.

Background

It’s really a good practice to set up housekeeping task from the early stage of your project as there are some task which you know in advance whereas some housekeeping task comes after your production system go-live like Table Growth.

This blog demonstrate the steps involved to perform housekeeping for cleanup of backup catalog entries. The idea behind performing this activity is that, with time entries in backup catalog table grows which result in higher data footprint at persistent level results in more memory utilization. So, this is one of many tables on which we can perform housekeeping task and keep it under threshold.

Overview

This section will provide an overview on what is HANA Catalog and where it used and how to check its size etc. and all the relevant information with respect to backup.

1. Backup Files – Delete according to backup retention times

2. Backup Catalog – Delete old entries according to backup retention times

3. Trace files backup.log and backint.log – Delete old entries according to backup retention times

For database operation, backup catalog is loaded into main memory so the size of backup catalog matters.

In persistent storage, your actual data/log, parameter and trace files are stored, and the backup of data and log volume are placed in data and log backup. The backup catalog for file-based backups is written as a separate backup to the location where the log backups are stored.

NOTE: Cleanup of backup catalog entries is also necessary if you have third party backup solution.

Here /backup is backup storage where all data and log backup files are places. The backup of “backup catalog” (log_backup_0_0_0_0.1539032862322) is placed in log backup directory along with log backup.

Manual Housekeeping

Backup files and catalog can manually be deleted using HANA Studio. The method to do so is as below –

In HANA Studio. Login to your SYSTEMDB and open “Backup”

Select the database for which you want to delete backup files and catalog. This option is only visible if you are in SYSTEMDB database. If you have logged in tenant database and navigated to backup folder, the option for database selection will not be available.

Right click on the data backup from which you want to delete older backups.

Here you can just delete the catalog or you can delete the catalog and backup files from the file system location.

It will provide you the list of backup files that will be deleted on choosing “Finish”. It will delete all complete, delta and log backups older than the backup you have selected.

IMPORTANT NOTE

In HANA, it is not possible to delete just the log or incremental backup from backup catalog. The reason is obvious as deletion of any random log and incremental backup will make data backup useless for point in time recovery.

Even if you try to delete an entry of log backup from backup catalog, it will result you with error

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <backup_id>

SAP DBTech JDBC: [452]: invalid backup id: Invalid backup id '<backup_id>'

Automatic Housekeeping – Using HANACleaner Script

Now to get rid of this manual task of deleting catalog, you can schedule a job to delete this catalog on regular basis. You have to perform one-time activity of scheduling a job for the same. The process of doing that is explained below.

Certain SAP HANA cleanup tasks like purging the backup catalog or deleting old trace files (SAP Note 2119087) need to be implemented individually. SAP HANACleaner is now available to perform this task automatically.

The SAP HANA cleaner is a house keeping script for SAP HANA. It can be used to clean the backup catalog, diagnostic files, alerts, to compress the backup logs, and much more. It should be executed by adm or, in case you use a CRON job, with the same environment as the adm. This script is expert tool designed by SAP support. You can use it, but SAP doesn’t take over any responsibility for problems originating from the use of this tool.

Always refer the latest version of SAP Note to get most updated HANACleaner script.

SAP HANACleaner can be used for the following cleanup tasks:

◈ Cleanup of backup catalog entries

◈ Cleanup of backups

◈ Cleanup of trace files

◈ Cleanup of backup.log and backint.log

◈ Cleanup of audit logs

◈ Cleanup of SAP HANA alerts

◈ Cleanup of free log segments

◈ Cleanup of internal events

◈ Cleanup of multiple row store containers

◈ Cleanup of data file fragmentation

◈ Cleanup of SAP HANACleaner logs

◈ Cleanup of arbitrary directories (e.g. SAP HANASitter logs)

◈ Creation of optimizer statistics for SDA tables

◈ Optimize compression of tables not compressed

◈ Optimize compression of tables with columns not compressed

◈ Optimize compression of tables with large UDIV overhead

◈ Cleanup of object lock history for ‘(unknown)’ objects

This document talks about cleanup of backup catalog entries. Similarly, cleanup for rest of the task can be schedule as per your business need.

Install Phython Script

You can install SAP HANACleaner in the following way:

Download the script mentioned in SAP Note 2399996 or you can use direct link -> https://github.com/chriselswede/hanacleaner.git

Copy it to a directory on your SAP HANA database server

Once it is copied, you can explore various options available to execute script. The following command provides you with an overview of the way how SAP HANACleaner works and the available configuration options:

sidadm> python hanacleaner.py --help

IMPORTANT NOTE

1. It is intended to be executed as <sid>adm on your HANA server (as environment variable for your python script is already set with <sid>adm on your HANA server).

2. It connects via host, port and DB user provided in hdbuserstore.

3. The DB user needs proper privileges.

Create Users

Create user in all HANA databases (System DB + Tenant DBs)

NOTE: Depending on what housekeeping tasks, the specific hanacleaner user will need specific sets of privileges. As I just want to delete backup catalog entries, I have used below set of roles.

Disable the expiry of user by executing SQL command –

ALTER USER HKPSCH DISABLE PASSWORD LIFETIME;

Create similar users in all Tenant Databases (I have 3 tenants, so have created users in all 3 tenants) and disable the expiry of users

Configure hdbuserstore

As mentioned earlier, python script connects via host, port and DB user provided in hdbuserstore

Configure “hdbuserstore” for all Databases

hdbuserstore set <KEY> <hostname>:<sql port> <username> <password>

---SYSTEMDB 

hdbuserstore set CLNRSCH <hostname>:30013 HKPSCH ***********

---Tenant 1

hdbuserstore set CLNRXXX <hostname>:30041 HKPXXX ***********

---Tenant 2

hdbuserstore set CLNRYYY <hostname>:30044 HKPYYY ***********

---Tenant 3

hdbuserstore set CLNRZZZ <hostname>:30047 HKPZZZ ***********

Here XXX, YYY, ZZZ are SID of tenant databases.

Once hdbuserstore is configured, you can cross check with below command

# hdbuserstore list

Execute Script

Before executing script, kindly perform checks to validate your backup catalog. Below example, I’m showing you everything on SystemDB but same needs to be followed for all tenant databases as well.

To check oldest backup entries in catalog and the size of catalog, execute below query in System DB and all tenant Database.

---Oldest Backup and Size of Backup Catalog

SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') EVALUATION_TIME,   

        LPAD(TO_DECIMAL(C.CATALOG_SIZE_MB, 10, 2), 15) CATALOG_SIZE_MB,   

        TO_CHAR(B.OLDEST_BACKUP_DATE, 'YYYY/MM/DD HH24:MI:SS') OLDEST_BACKUP_DATE,   

        LPAD(B.OLDEST_BACKUP_DAYS, 18) OLDEST_BACKUP_DAYS 

FROM ( SELECT TOP 1  BF.BACKUP_SIZE / 1024 / 1024 CATALOG_SIZE_MB   

        FROM  M_BACKUP_CATALOG B,

              M_BACKUP_CATALOG_FILES BF

        WHERE B.BACKUP_ID = BF.BACKUP_ID 

        AND BF.SOURCE_TYPE_NAME = 'catalog' AND B.STATE_NAME = 'successful'   

        ORDER BY B.SYS_START_TIME DESC ) C, 

      ( SELECT  MIN(SYS_START_TIME) OLDEST_BACKUP_DATE, 

                DAYS_BETWEEN(MIN(SYS_START_TIME), CURRENT_TIMESTAMP) OLDEST_BACKUP_DAYS   

        FROM  M_BACKUP_CATALOG 

      ) B

SELECT COUNT(*) FROM M_BACKUP_CATALOG;

Run HANA Mini Check from SAP Note 1969700.

Based on the age of HANA database, the size of backup catalog will increase. Above mini check shows you the size of backup catalog of SystemDB on which I have ran mini check. I have 3 tenant and the value of backup catalog on each comes are to be similar i.e 20 MB. So 80 MB has been utilized in total at persistent level. So housekeeping this table will help us to keep data footprint low which eventually utilize less memory.

Execute below command with <sid>adm

# python hanacleaner.py -bd 90 -br true -k CLNRSCH

Here it will delete entries in backup catalog older than 90 days. If you set -bb true, then along with the backup catalog entries corresponding backup files also gets deleted. But it is of no use, if you are using third party backup tool.

But if you use both -be and -bd, the most conservative, i.e. the flag that removes the least number entries, decides

For cleaning up the backup catalog (and possibly also backups) hanacleaner has the following input flags –

Now check the oldest backup days and size of catalog and you will see that entries are been deleted and catalog size has been reduced.

So for all tenant databases same python script needs to be executed along with its user key CLNR<SID>

# python hanacleaner.py -bd 90 -br true -k CLNRXXX

# python hanacleaner.py -bd 90 -br true -k CLNRYYY

# python hanacleaner.py -bd 90 -br true -k CLNRZZZ

Configure Cron Job

We will be using cron job to run this script weekly. Some customer don’t allow to use cron job, so schedule this script using third party tool.

Note: hanacleaner expects the environment of <sid>adm –> if we use CRON the same environment as <sid>adm has to be provided.

Check with shell <sid>adm uses

This shell script, hanacleaner.sh, provides the <sid>adm environment, with source $HOME/.bashrc and then executes the hanacleaner command:

Then a new crontab can be created, calling this shell script, e.g. every Saturday at 6 o’clock  in morning. You can use https://crontab.guru to get time for cron job

If you want to log the output to std_out set up the crontab like this:

NOTE: You can also insert email syntax in cron job, so on the completion the task it triggers an email along with output.