Backup Catalog Housekeeping within SAP HANA

Environment

SAP HANA, platform edition

Cause

1. The location of the data and log backups have been left at the default location of /usr/sap/<SID>/<HOST>/backup/data and /usr/sap/<SID>/<HOST>/backup/log'.
2. Backup logs are now filling up the filesystem;

Resolution

It is possible to remove old backups from the HANA database using the following procedure:

1. Issue the SQL command 'ALTER SYSTEM RECLAIM LOG' to free up log segments;
2. Next, in SAP HANA Studio, go to the Backup and Recovery menu and choose the Backup Catalog tab;
3. Locate any old backups that are no longer needed and right click the backup;
4. Click 'Delete Data Backups' and choose the 'Catalog and Backup Location' radio button:
   　　
5. Click 'Finish' to execute;

Remarks:

• It is recommended to store data backups and log backups away from the default location of '/usr/sap/<SID>/<HOST>/backup/data' and '/usr/sap/<SID>/<HOST>/backup/log' to a location with ample space away from the HANA appliance software. Changing data backup and log backup settings can be done by selecting the 'Configuration' tab within the Backup and Recovery menu and altering the destination of the log files.
• This can be done online and the SAP HANA will start using this new location once finished writing to the previous log backup.
• No database restart is required.
• Do NOT delete log files at OS level as this can render the HANA database inconsistent and unusable and will result in the need for a database recovery;
• Alternatively to Deletes backups from the backup catalog using BACKUP CATALOG DELETE Statement;

See Also

• SAP Note 1642148 - FAQ: SAP HANA Database Backup & Recovery
• Housekeeping: Deleting and Archiving Backups
• Monitoring Disk Space

2921915 - Housekeeping with BACKUP CATALOG DELETE Statement

Symptom

• You tried to perform backup catalog housekeeping task in Hana Studio's backup console by following the steps given in KBA 2096851 and encountered issues, such as a frozen page and/or memory errors.

Environment

•  SAP HANA Database

Reproducing the Issue

• Perform backup catalog bousekeeping task in Hana Studio's Backup Console

Cause

• Catalog too big to be handled by Backup Console

Resolution

You can use the BACKUP CATALOG DELETE Statement to perform the housekeeping task in SQL console by following these steps below:

• 1. Find out the number of entries in backup catalog and size of latest catalog backup file (in bytes). Also check the date of the oldest entries to see when was the last housekeeping job.

SELECT COUNT(*) FROM M_BACKUP_CATALOG
SELECT TOP 1 BACKUP_SIZE FROM M_BACKUP_CATALOG_FILES WHERE SOURCE_TYPE_NAME='catalog' ORDER BY BACKUP_ID DESC
SELECT TOP 10 * FROM M_BACKUP_CATALOG

• 2. Get a list of all successful data backups. Identify the oldest one you want to keep and record its backup id and timestamp.

SELECT * FROM M_BACKUP_CATALOG WHERE ENTRY_TYPE_NAME='complete data backup' AND STATE_NAME='successful' ORDER BY BACKUP_ID DESC

This procedure will  delete all log and data backup entries older than the data backup you choose. You will not be able to perform point-in-time recovery beyond its timestamp, but data backups created earlier can still be used for recovery. If you want to keep physical copies of older backups, see Step #4.

•  3. With the timestamp you got from Step #2, run statements like

select count(*) from M_BACKUP_CATALOG where SYS_START_TIME<'<timestamp>'

to see how many entries were older than the data backup you chose, thus you can estimate the shrink in catalog file size after the deletion.

•  4. Run BACKUP CATALOG DELETE Statement. Most common usage:

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID xxxx COMPLETE

Here xxxx is the backup ID you got from Step #2. This will delete catalog entries for all log and data backups dated before xxxx and all physical backup files in the file system and third-party backup tool.

Instead of COMPLETE you can use the following options to controls physical file deletion. Keep in mind that the catalog entries stored in database will always be deleted regardless of the option you choose.

* WITH FILE : Also delete physical backups in the file system.
* WITH BACKINT : Also delete physical backups in the third-party backup tool.
* <do not specify an option>: No physical backup deletion. If you choose this option, you will not be able to use this tool to delete the corresponding physical files in the future. You will have to manually remove them from the file system.

Hana will use file name and location stored in the catalog when the backup was created. So if you rename or move an old data backup it will be considered as deleted, and you can still use it for recovery.

You may still experience hanging/memory errors with this statement if there are too many entries to be deleted at once. In this case you can go back to Step #2 and choose an older backup to reduce database load.

After a successful execution, if you want to see a list of all files deleted, check backup.log for entries like the following:

2019-11-11T06:40:01-07:00 P109085 0 INFO LCM backup file /usr/sap/.../log_backup_1_0_xxxxxxx deleted

See Also

  BACKUP CATALOG DELETE from SAP HANA SQL and System Views Reference

Keywords

log backup, backup catalog,