Skip to Content
Technical Articles

How to implement HANACleaner

HANACleaner -For automated cleanup of HANA DB:

 

If you are looking for some automated house cleaning tasks for Hana DB-HANACLEANER is the best tool to perform the same .

We have implemented the same in our system and it has helped us to prevent many issues

“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 <sid>adm or, in case you use a CRON job, with the same environment as the <sid>adm. See SAP Note [2399996](https://launchpad.support.sap.com/#/notes/=2399996) and SAP Note [2400024](https://launchpad.support.sap.com/#/notes/=2400024). For a list of all input flags execute with

`python hanacleaner.py –help` “

You can download HANA Cleaner and as of how to configure it using the below link.

https://github.com/chriselswede/hanacleaner

Though there is a proper document in the above location, I just wish to illustrate the steps and configuration I have set up in our landscape for HANA 1.0 environment. If you are looking for HANA 2.0, it will be almost same with minor changes.So please refer the document that comes with the installation in the above link in that case.

Step 1: User creation :

create user HANACLEANER1 password <password> NO FORCE_FIRST_PASSWORD_CHANGE;

ALTER USER HANACLEANER1 DISABLE PASSWORD LIFETIME;

Step 2: Adding the required authorization:

grant AUDIT ADMIN, AUDIT OPERATOR,BACKUP ADMIN,CATALOG READ,LOG ADMIN,MONITOR ADMIN,RESOURCE ADMIN,TRACE ADMIN to HANACLEANER1;

Object role: (Assign this in studio by right clicking the user created-HANACleaner1)

HOST_OBJECT_LOCK_STATISTICS_BASE           -select and delete

OBJECT_HISTORY – select and delete

STATISTICS_ALERTs_BASE  – select and delete

Step 3: Saving the password of HANACleaner1 in hana db user store in master node

hdbuserstore -i SET SYSTEMKEY <master_node>:3nn15,<worker node1>:3nn15,<worker node2>:3nn15,……………………,<Standby node>:3nn15 HANACLEANER1 <HANACleaner1 ‘s password>

Step 4: Create a folder and place the hanacleaner.py that we have downloaded from the github

From the github link copy the file hanacleaner.py and transfer it to the target server.

In the target server, create a folder /usr/sap/<SID>/HDB<nn>/HANACleaner/ and place this python script inside this folder.

Also create a empty folder inside this in the name log, which we will use it in last step to write the output = > /usr/sap/<SID>/HDB<nn>/HANACleaner/log

Step5: Create configuration files: *.sh and *.cfg like shown below

Here I have displayed the configuration files that I had created for our purpose. We can create similar configuration as required and adjust the configuration file value as per our requirement

Below are *.sh files which has reference to a .cfg file which will also be created by us

A.CREATING *.sh files

==========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/rtedumpcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/rtedumpcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/alertscleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/alertscleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/logsegmentcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/logsegmentcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/objectlockcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/objectlockcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/objectlockcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/objectlockcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/ObjectHistoryTableCleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/ObjectHistoryTableCleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/diskfragmentation.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/diskfragmentation.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/eventscleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/eventscleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/Generalfilcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/Generalfilcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/backupcleaner.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/backupcleaner.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>> more /usr/sap/<SID>/HDB<nn>/HANACleaner/Generalfilcleaner1.sh

source $HOME/.bashrc

python /usr/sap/<SID>/HDB<nn>/HANACleaner/hanacleaner.py -ff /usr/sap/<SID>/HDB<nn>/HANACleaner/Generalfilcleaner1.cfg -op /usr/sap/<SID>/HDB<nn>/HANACleaner/log

=================================================

B.Creating all the *.cfg files that we referred above

This is where we actually specify the arguments that we have decided and it can vary from landscape to landscape.

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more alertscleaner.cfg

#HANA Alert cleaner

#minimum number retained days of the alerts=ar,output alerts=ao,output deleted alerts=ad

-ar 15

-ao true

-ad true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more columncompression.cfg

#Table compression re-optimization of column store tables can be automated

#cc-Max allowed raw main records,-ce Max allowed estimated size,cr-Max allowed rows,-cs #(mb)-Max allowed size,-cd Min allowed distinct count,-cq-Max allowed UDIV quota,-cu Max #allowed UDIVs

-cc 20000000

-ce 1

-cr 20000000

-cs 1000

-cd 10

-cq 30

-cu 20000000

-cb 200000

-cp true

-cm true

-co true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more diskfragmentation.cfg

#Disk fragmantation -fl fragmentation limit%,-fo output fragmentation

-fl 20

-fo true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more eventscleaner.cfg

#Event Cleaner,-eh minimum retained days for handled events days,-eu-minimum retained #days for unhandled events days

-eh 5

-eu 30

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more Generalfilcleaner.cfg

#General file cleaner eg:hanacleaner logs;retention days beyond which files can be cleaned #5days,gd-directories,-gw-filename parts

-gr 5

-gd /usr/sap/<SID>/HDB<nn>/HANACleaner/log

-gw hanacleanerlog

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more inihistorycleaner.cfg

#ini file cleaner -ir inifile content history retention in days

-ir 366

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more logsegmentcleaner.cfg

#Log Segment Cleaner -lr-maximum number of free log segments per
#service

-lr 1

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more objectcleaner.cfg

#Unknown object lock entries cleanup -kr min retained unknown object lock days

-kr 1

 =========================

#object histor cleanup -om object history table max size in mb,-oo-output cleaned memory #from object table

-om 1

-oo true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more ObjectHistoryTableCleaner.cfg

#Object history cleanup -om object history table max size in mb,-oo-output cleaned memory #from object table

-om 50

-oo true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more objectlockcleaner.cfg

#object lock cleaner for UNNNOWN -kr-min retained unknown object lock days

-kr 1

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more rtedumpcleaner.cfg

#dump cleaner with 15 days of retention period

-dr 15

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more tracecleaner.cfg

#Trace files delete min retain for 30 days,output trace file, output the deleted trace file entry

-tc 30

-tf 30

-to true

-td true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more auditcleaner.cfg

#audit cleaner -ur retention time [days] of the audit log table

-ur 200

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more backupcleaner.cfg

#backup catalog cleanup older than 40 days and print the deleted entries

-be -1

-bd 40

-bb false

-bo false

-br false

#backuplog compress -zb backup logs compression size limit,-zp-zip path,-zl keep zip files

#-zb 1000

#-zp /usr/sap/<SID>/HDB<nn>

#-zl true

 =========================

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner> more Generalfilcleaner1.cfg

#General file cleaner eg:hanacleaner logs -gr-etention days for
#any general file,-gw-filename parts

-gr 8

-gd /usr/sap/<SID>/HDB<nn>/<server_name>/trace,/usr/sap/<SID>/HDB<nn>/servernode1/trace,/usr/sap/<SID>/HDB<nn>/servernode2/trace,/usr/sap/<SID>/HDB<nn>/node3/trace,/usr/sap/<SID>/HDB<nn>/node4/trace,/usr/sap/<SID>/HDB<nn>/node5/trace,/usr/sap/<SID>/HDB<nn>/node6/trace,/usr/sap/<SID>/HDB<nn>/node7/trace,/usr/sap/<SID>/HDB<nn>/node8/trace

-gw indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz,indexserver_alert*.gz

sidadm@<server_name>:/usr/sap/<SID>/HDB<nn>/HANACleaner>

 =========================

Step 6: Create crontab entries  (time table depends on your needs)

Ensure you have log folders created prior ->create a log folder insider the folder where HanaCleaner was places.

After that, schedule the jobs that has to be run on HANA DB via HANACleaner

00 00 * * SUN /usr/sap/<SID>/HDB<nn>/HANACleaner/tracecleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_trace.log 2>&1

0 22 1,15 * * /usr/sap/<SID>/HDB<nn>/HANACleaner/rtedumpcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_rte.log 2>&1

00 22 * * * /usr/sap/<SID>/HDB<nn>/HANACleaner/alertscleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_alert.log 2>&1

00 01,13 * * * /usr/sap/<SID>/HDB<nn>/HANACleaner/logsegmentcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_logsegment.log 2>&1

00 02 * * SAT /usr/sap/<SID>/HDB<nn>/HANACleaner/auditcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_audit.log 2>&1

30 22 * * * /usr/sap/<SID>/HDB<nn>/HANACleaner/objectlockcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_objectlock.log 2>&1

00 03 * * SUN /usr/sap/<SID>/HDB<nn>/HANACleaner/ObjectHistoryTableCleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_objecthistory.log 2>&1

00 02 * * SUN /usr/sap/<SID>/HDB<nn>/HANACleaner/diskfragmentation.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_datFragmentation.log 2>&1

15 22 * * * /usr/sap/<SID>/HDB<nn>/HANACleaner/eventscleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_eventCleaner.log 2>&1

00 00 1 * * /usr/sap/<SID>/HDB<nn>/HANACleaner/inihistorycleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_iniCleaner.log 2>&1

00 01 * * SAT /usr/sap/<SID>/HDB<nn>/HANACleaner/Generalfilcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_GeneralCleaner.log 2>&1

 

That is it . Our HANACleaner now does trace cleaning,RTE old dumps cleaning,alerts cleaning,log segment cleaning,audit logs clening,orphaned locks cleaning,disk de-fragmentation,events cleaning etc

Hope it was helpful!!
Click on like if you found this article useful and FOLLOW for more such articles.http://people.sap.com/rajarajeswari_kaliyaperumal

Please leave a comment or suggestion!

 

7 Comments
You must be Logged on to comment or reply to a post.