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 adm or, in case you use a CRON job, with the same environment as the 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 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 :3nn15,:3nn15,:3nn15,……………………,:3nn15 HANACLEANER1
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//HDB/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//HDB/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@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/rtedumpcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/rtedumpcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/alertscleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/alertscleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/logsegmentcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/logsegmentcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/objectlockcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/objectlockcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/objectlockcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/objectlockcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/ObjectHistoryTableCleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/ObjectHistoryTableCleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/diskfragmentation.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/diskfragmentation.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/eventscleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/eventscleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/Generalfilcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/Generalfilcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/backupcleaner.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/backupcleaner.cfg -op /usr/sap//HDB/HANACleaner/log
=========================
sidadm@:/usr/sap//HDB> more /usr/sap//HDB/HANACleaner/Generalfilcleaner1.sh
source $HOME/.bashrc
python /usr/sap//HDB/HANACleaner/hanacleaner.py -ff /usr/sap//HDB/HANACleaner/Generalfilcleaner1.cfg -op /usr/sap//HDB/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@:/usr/sap//HDB/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@:/usr/sap//HDB/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@:/usr/sap//HDB/HANACleaner> more diskfragmentation.cfg
#Disk fragmantation -fl fragmentation limit%,-fo output fragmentation
-fl 20
-fo true
=========================
sidadm@:/usr/sap//HDB/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@:/usr/sap//HDB/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//HDB/HANACleaner/log
-gw hanacleanerlog
=========================
sidadm@:/usr/sap//HDB/HANACleaner> more inihistorycleaner.cfg
#ini file cleaner -ir inifile content history retention in days
-ir 366
=========================
sidadm@:/usr/sap//HDB/HANACleaner> more logsegmentcleaner.cfg
#Log Segment Cleaner -lr-maximum number of free log segments per #service
-lr 1
=========================
sidadm@:/usr/sap//HDB/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@:/usr/sap//HDB/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@:/usr/sap//HDB/HANACleaner> more objectlockcleaner.cfg
#object lock cleaner for UNNNOWN -kr-min retained unknown object lock days
-kr 1
=========================
sidadm@:/usr/sap//HDB/HANACleaner> more rtedumpcleaner.cfg
#dump cleaner with 15 days of retention period
-dr 15
=========================
sidadm@:/usr/sap//HDB/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@:/usr/sap//HDB/HANACleaner> more auditcleaner.cfg
#audit cleaner -ur retention time [days] of the audit log table
-ur 200
=========================
sidadm@:/usr/sap//HDB/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//HDB
#-zl true
=========================
sidadm@:/usr/sap//HDB/HANACleaner> more Generalfilcleaner1.cfg
#General file cleaner eg:hanacleaner logs -gr-etention days for #any general file,-gw-filename parts
-gr 8
