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!
Thanks for blog post.
There is already detailed post on HANA cleaner
https://blogs.sap.com/2018/10/29/hana-housekeeping-using-hanacleaner/
Not sure of the value add here.
Regards,
Hi Arun,There is no hard and fast rule that similar content / steps should not be posted and hence there is no need for judgement for value addition or not . So it does not matter. It is just what it is from my point of view in my own words. In that case no one should post anything as the steps are already specified in the HANA cleaner dump itself
Well said.
Thanks for the information!
Does it work for cleanup of catalog backups if backups are to backint ?
Hi Kalyana, Yes.. Because we are only cleaning up the entries in hana db catalog , it will work. Even in our environment we have backint only and it works well
Hi Rajarajeswari,
I have a question, when I create a key in hdbuserstore, do I have to create two different keys one for Systemdb and one for TenantDB or only one key for tenant and systemdb is possible ?
For example
SystemDB (first command)
hdbuserstore set CLNR hostname: 30013 user password
TenantDB (second command)
hdbuserstore set CLNR hostname: 30015 user password
Would that be right?
thank you
Best regards
Yes...Because in SystemDB nameserver plays the role of indexserver and tenant db already has its own index server
Hi,
Thanks for the post.
MY question is, How can I give multiple filesystems in HANA CLEANER.
What I observerd is after upgrade HANA from 1.0 to latest 2.0 the catalog backups are not getting cleared from the filesystems this might be due to SYSTEMDB and Tenant DB path change.
So I have to give the exact path for SYSTEMDB & Tenant DB.
Could you please help me with the format to mention multiple filesystems in HANACLEANER ?
Regards,
Amar S Ramraje
Hi,
Thanks for sharing, very useful.
Just a quick question. Do any these jobs have potential to cause locks? And as per your experience are there any performance hits?
Thank you.
Hi,
I'm using the HANACleaner on serveral HDB 1.0 SPS12 and HDB 2.0 SPS05 environments and I'm wondering, why I cannot delete the backup files for the SYSTEMDB on my HDB 2.0 SPS05 instances.
I'm using the switches
-bd 7
-bb true
-br true
in terms of backup handling.
The cleanup of the backup catalog and backup files on the filesystem for the tenant DB works just fine.
Any ideas?
Regards
Thomas
http://people.sap.com/rajarajeswari_kaliyaperumal Thanks for the post. We have HANAcleaner on all our environment. However, what we realize is that if HANACLEANER1 user has a issue with password or account locked, the script fails to run nor it sends you a email when the cleaner is not running. It only sends email on successful run of the script. This is a big problem, because you don't know if the script is successfully running on all the environments. Does this make sense ?
Hello Vijay,
if this behaviour is still the case with the latest version you can open an issue at GitHub - chriselswede/hanacleaner: A house keeping script for SAP HANA
This looks like an improvement to general behaviour of HANAcleaner.
http://people.sap.com/rajarajeswari_kaliyaperumal
In your documentation you plan auditcleaner.sh and tracecleaner.sh with crontab.
00 02 * * SAT /usr/sap/<SID>/HDB<nn>/HANACleaner/auditcleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_audit.log 2>&1
00 00 * * SUN /usr/sap/<SID>/HDB<nn>/HANACleaner/tracecleaner.sh > /usr/sap/<SID>/HDB<nn>/HANACleaner/log/hanacleanerlog_trace.log 2>&1
But unfortunately you dont say how to create these files in Step 5.
Hello http://people.sap.com/rajarajeswari_kaliyaperumal
Thanks for sharing valuable contents. if possible please share how to configure hanasitter.
Regards,