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.

SAP Note 2399996 – How-To: Configuring automatic SAP HANA Cleanup with SAP HANACleaner

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

DISCLAIMER:

ANY USAGE OF HANACLEANER ASSUMES THAT YOU HAVE UNDERSTOOD AND AGREED THAT:

1. HANACleaner is NOT SAP official software, so normal SAP support of HANACleaner cannot be assumed
2. HANACleaner is open source
3. HANACleaner is provided "as is"
4. HANACleaner is to be used on "your own risk"
5. HANACleaner is a one-man's hobby; developed, maintained and supported only during non-working hours
6. All HANACleaner documentations must be read and understood before any usage:
   - SAP Note 2399996
   - The .pdf file hanacleaner_intro.pdf
   - All output from executing python hanacleaner.py --help
7. HANACleaner can help you execute certain SAP HANA tasks automatically but is NOT an attempt to teach you SAP HANA
Therefore it is assumed that you understand all SQL statements that HANACleaner does to make changes in your system
To find out what crucial SQL statements HANACleaner will do without executing them, run with the additional flags
-es false -os true
To then learn what those statements do before you executing HANACleaner without "-es false", see SAP HANA Admin Guide or SAP HANA System Administration Workshops
8. HANACleaner is not providing any recommendations, all flags shown in the documentation (see point 6.) are only examples

For recommendations see SAP HANA Administration Workshops or other documentation, like e.g. SAP Note 2400024

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://ift.tt/2SuVSsu

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.

Regards,

Dennis

from SAP Blogs https://ift.tt/2Q5Zf7A



from WordPress https://ift.tt/2CSfjWq




Comments

Post a Comment

Popular posts from this blog

Add Custom Field in Output of Standard Report of ME2N

Image
Author: Vedarth Desai  A very common requirement from customer is to modify the outputs of standard reports. Here I have tried to explain how to modify the standard report  ME2N  output. Requirement Purchase Documents per Document Number: T-code:  ME2N  is a standard report that retrieves a purchase order details via a document number and other user-defined parameters. Minimum parameters in selection-screen we need to pass are valid  Scope of list  and Either of remaining fields. Scope of List defines the format in which report will get display. In general, Report output looks like below.   Here in this output we need to add one additional column which will display some information as per the requirement.  Here let’s add an extra field/column and display ‘Net Order Value Incl. Tax’.      There are mainly 2 steps to do the modification. Add desired field in Output structure. Write code to populate the value in th...
Read more

UK to Receive Egyptian LNG Cargo

British gas terminal Dragon LNG will receive a liquefied natural gas (LNG) shipment from Egypt at the end of October, LNG World News reported. The cargo is being shipped in the Shell-chartered Maran Gas Spetses which will arrive at Dragon LNG’s Milford Haven terminal on October 31. The gas was shipped from Egypt’s Idku liquefaction plant and was sailing in the Mediterranean Sea on October 24. Shell had increased the amount of natural gas exported from its Idku liquefaction plant to 250 million standard cubic feet per day (mmscf/d), up from 150 mmscf/d in fiscal year (FY) 2016/17, an industry source told Al Borsa in September. The Dutch oil giant’s ownership of the Idku plant has enabled it to export natural gas from its offshore Burullus and Rosetta fields. Shell aims to export a LNG argo every 15 days based on the facility’s current liquefaction capacity, the source added. from News – Egypt Oil & Gas https://ift.tt/2ORNMvH
Read more

Use Self-Signed SSL Client Certificate to connect SAP backend to SCPI with Custom Domain configured

Image
Purpose This document aims at showing how to connect your SAP backend (in this case SAP ECC 6.0) with your SCPI configured with a Custom Domain using the Client Certificate as authentication method. This is to avoid paying a CA to get your Client Certificate signed and to use  Self-Signed certificate created from STRUST instead. The main idea behind this demo is taken from Mandy Krimmel’s blog  How to Setup Secure HTTP Inbound Connection with Client Certificates .   Involved Systems SAP backend connected to SCPI via (synchronous) SOAP WS. Prerequisite SCPI Custom Domain correctly configured with a Server/Domain Certificate bound to the respective SSL Host. For more information, please refer to the following links: Custom domain, overview Using Custom Domain Sap neo-java-web-sdk installed on your local system, to download the tool click on this link . Basic concepts behind Custom Domain in SCPI.   Configure STRUST Create a new SSL Client PSE (aka SS...
Read more