- Task 1:Monitoring the Overview of system status and alerts
- · RSP also offers SAP HANA database backup features – see Task#2: Backing up the HANA system regularly.
- Task#2: Backing up the HANA system regularly.
- Task#3: Reclaim the log volume only in the exceptional case of a HANA database freeze.
- Task#4: Clean up the catalina.out and HANA trace file regularly.
This document aims to guide system administrators in the regular tasks of system administration for SAP Business One, analytics powered by SAP HANA (B1A), and SAP Business One, version of SAP HANA (B1H). As a system administrator for SAP HANA and B1A/B1H, you need to conduct system administration activities regularly to ensure the SAP HANA and B1A/B1H system are running well. These activities include monitoring the system status, backing up the system for data recovery, archiving or cleaning up the out-of-date backups and trace files to free disk space.
In addition to this guide, we highly recommend you refer to the SAP HANA Administration Guide for more detail about SAP HANA system administration. http://help.sap.com/hana_appliance
Task 1:Monitoring the Overview of system status and alerts
Option 1 (recommended): install and run the remote system platform (RSP) for SAP Business One
- Check the RSP System Status Report regularly.
The System Status Report provides important customer installation information allowing partners to mitigate any risks from potential issues. The System Status Report details include:
a) Disk space and back-up status checks
b) Detailed information on business setup and customization
c) Overview of hardware and software configuration
d) Traffic lights to visually indicate system readiness and areas that need attention
e) The reports highlight changes compared to previous reports and includes previous data
- Dedicated Heath Checks for SAP HANA database are available as of RSP 3.2. Results are consolidated in the System Status Report.
- Healing tasks for SAP HANA database are available as of RSP 3.2
· RSP also offers SAP HANA database backup features – see Task#2: Backing up the HANA system regularly.
Option 2: Use the SAP HANA Studio
The Administration and Modeler perspectives of the SAP HANA Studio provide a comprehensive system overview status of SAP HANA’s service running status, CPU, memory and disks etc., as well as provide alerts for system problems. Check that all the statuses are green. A green status indicates that the system has enough memory, CPU and Disk space.
1) Open the HANA Studio with the Administration perspective.
2) Double click on the System in the Navigator pane will cause the Administration view to open
3) Go to the Overview Tab
Check for system alerts. Handle them accordingly, especially any alerts with high priority.
Some explanations of sample alerts shown in the screenshot above:
1).”No data backup exists”: You should back up the system regularly to prevent data loss. Backups are discussed in Task 2 of this document.
2).”Your license will expire in 4 days”: Please request the permanent license key for HANA.
Check the detailed disk space used by each process in SAP HANA. To make sure there is enough disk space for HANA, you need to perform the operations below regularly. Please refer to tasks 3-5 in this document for more details.
-Archiving the log backup
-Reducing the log volume
-Cleaning up the trace files, etc.
There are three file types in the SAP HANA system:
HANA data snapshots are saved to the hard disk for persistence. Note: You must not delete these files manually from the file system, this would corrupt the HANA system.
HANA logs since the latest data save-point. These can be used to restore the data together with latest data snapshot into main memory in case of power downtime (planned or otherwise).
HANA trace files for system troubleshooting. See the diagnosis file and trace configuration tab for more details.
This provides the detailed run-time information.
Diagnosis (trace )Files:
Diagnosis files provide all the default HANA system trace files for diagnosis. The trace file of index server is most frequently used for system troubleshooting. To find the trace files of index server, follow the procedure below:
1) Choose Diagnosis Files tab
2) Enter the filter with “INDEX” for indexserver
3) Sort the Modified column in descending order.
4) Double-click the indexserver_<servername>.<port>.<file_sequence>
*These files are also available in the /usr/sap/NDB/HDB00/<hostname>/trace directory
Besides these default diagnosis files, custom traces can be configured using the Trace Configuration tab. For example, you can set up a custom trace that filters by database user, table/views/models, etc. Please refer to the following video for details:
HANA Academy – Using SQL Tracing in SAP HANA Studio
B1 Analytic Admin Console:
Since B1A 1.0 PL04, the B1A Admin Console provides the system status about CPU/Memory/disk.
HANA Overall Health Statistics:
Available on the Home page of the B1A Admin Console
Company level status:
Available on the Company page of the B1A Admin Console
Replication Status(B1A)/Data Staging(B1H):
You should check the replication status regularly for each company database in on the company Company page, ensuring to ensure that the replication status is started and displays a reasonable last run time.
In addition, the B1A Admin Console provides a data comparison between the MS SQL Server and the SAP HANA database.
Data Comparison Report: (only applicable to B1A)
Please ignore -1 difference for the table OCRY/OTER/OOND/OOIR; which is by design.
If the data is not being replicated properly or is not on time, check the HANA status.
Task#2: Backing up the HANA system regularly.
Visit http://scn.sap.com/community/business-one/blog/2013/07/24/how-do-i-use-the-cron-to-schedule-a-backup for the steps to schedule the backup using cron.
Like traditional DBMS, the SAP HANA system provides backup and recovery functionality for preventing data loss and for migrating data to another HANA server. To prevent data loss, backups should be performed regularly. Daily backup is recommended, however your schedule will depend on your backup policy. Additionally, to ensure enough disk space, old backups need to be archived to external storage or cleaned up.
System Level Backup: [Recommended]
The whole SAP HANA instance will be backed up, including schemas and models. It then can be recovered to another SAP HANA instance (on host with same name).
Option 1 (recommended): Schedule SAP HANA backups via RSP
This is the first option for SAP Business One partners and customers to automate the backup and house- keeping tasks. Since B1H 9.1 PL07, the following scheduling functions are available in RSP 3.1 PL07 with graphical user interface:
- SAP HANA instance backup
- Schema export: select a specific schema to be exported regular.
- House-keeping to delete older data backup and log backup. You can setup the retention size of data and log backup. RSP will help to delete the older backup in a safe way.
Please refer to note 2157386 – HANA backups scheduling in RSP
Option 2: Backup with HANA Studio
please refer to the following video about HANA system backup and recovery:
Option 3: Backup with HANA SQL Script
Syntax: BACKUP DATA USING FILE (‘<target_backup_file_name>’);
Refer to the following blog to use backup script in a secure way:
Schema Level Export/Import:
Export and import the data of a specific schema. Please follow the guide below:
Please be aware that potential data inconsistence could happen in schema export while data is entered to B1 with the other connection. Please check the following note for detail.
HANA SQL Script to backup with export command:
export <Schema>.”*” as binary into ‘<target_folder>’ with replace;
export SBODEMOUS.”*” as binary into ‘/hanamnt/backup/schema/SBODEMOUS’ with replace;
To recover the schema from the backup:
import <Schema>.”*” as binary from ‘<backup_folder>’ with replace;
import SBODEMOUS.”*” as binary from ‘/hanamnt/backup/schema/SBODEMOUS’ with replace;
To recover the schema with a new name from the backup:
Check the section below: How to import a schema with a different name?
By default, the database log will be backed up every 5 minutes by HANA system; the log backup files are used for system recovery together with data backup to a specific save-point. In order to have enough disk space for HANA, the out-of-date log backup files should be periodically archived with compression and moved to external storage; and even be deleted if they are too outmoded to be used for recovery.
The log backup path can be found or changed in the Configuration tab of the Administration perspective.
1) basepath_databackup: The default base path of the SAP HANA system backup for data has the following structure: </usr/sap/NDB/HDB00/backup/data>. The backup path can be specified during system backup.
2) basepath_logbackup:The default base path of the SAP HANA log backup, to be used to recover with HANA data backup to specific save point of HANA system has the following structure: </usr/sap/NDB/HDB00/backup/log>
3) log_backup_timeout_s: The log backup interval is 15 minutes by default.
If your persistence configuration looks like the screenshot below.
As ndbadm, on the Linux host, you can use hdbsrvutil -z |grep DIR_GLOBAL= |sed ‘s/\DIR_GLOBAL=/\ /g’ |head -1 to find the value of DIR_GLOBAL. With the value of DIR_GLOBAL you can determine where the data and log volumes reside.
How to archive a backup folder with compression?
1) To create an archive of a directory and its contents with compression, you would use the Linux command: tar –czvf name.tar.gz /path/to/directory
For example, archive the HANA log backup folder with the command:
tar –czvf log_backup_20130108.tar.gz /usr/sap/NDB/HDB00/backup/log
2) To create an archive of certain files, you would use the Linux command below:
tar –czvf name.tar.gz /path/to/file1 /path/to/file2 /path/to/file3
Please refer to the link below for more details:
How to move the folder?
Option 1: Using Linux command
mv /path/source /path/dest
For example: mv /usr/sap/NDB/HDB00/backup/log/log_backup_20130108.tar.gz /tmp/archived
Option 2: Using FTP.
You can download a FTP client such as FileZilla or WINSCP and use this tool to transfer the files from Linux HANA server to local file storage.
How to import a schema with a different name?
As of HANA revision 61 it is possible to export a schema and then import it with a different name. The syntax can be found below.
export “SBODEMOGB“.”*” as binary into ‘/home/SBODEMOGB‘ with replace threads 10;
import “SBODEMOGB “.”*” as binary from ‘/home/SBODEMOGB_901’ with replace threads 10 rename schema “SBODEMOGB” to “SBODEMOGB_NEW”;
Important note: Make sure the target schema name of B1 company as all upper case, do not use a mixed upper and lower case, which may result some issue in B1 operation after importing the company with a new name, like unable to create a Sales Order. Error msg: ADOC.
Task#3: Reclaim the log volume only in the exceptional case of a HANA database freeze.
Caution: Do not reclaim the log volume regularly, because this will affect HANA performance. This is an exceptional task.
When automatic backup is enabled (the default setting), it is only necessary in exceptional situations to use SQL command “ALTER SYSTEM RECLAIM LOG”. In normal log mode (default setting), a database freeze is probably the only situation in which it is necessary to reclaim the log in this way. As it can take some time to reformat the blocks in the log area, using this option would normally be counterproductive.
To check the log volume, go to Volumes in Administration, only if:
Case #1: HANA DB freeze, disk full event showed in alert.
Case #2: (to confirmed with HANA team and B1H dev team)
1) The total size of log volume is exceptionally large (e.g. 90% of total disk space),
2) And the Used/Total Size [%] is relevantly lower (e.g. <%50 on average).
Then it is necessary to run “ALTER SYSTEM RECLAIM LOG” within SQL Editor of HANA studio. As a result, HANA will shrink the log volume to free the unused log volume.
Task#4: Clean up the catalina.out and HANA trace file regularly.
Cleaning up the Tomcat log file in B1A or B1 analytics in B1H
1) Log on to the HANA server with PuTTY with the root user
2) Go to the Tomcat log folder of the B1A installation
3) Clear the content of catalina.out with the command: cat /dev/null > catarina.out
1) Connect to the HANA server with an FTP client
2) Delete the catalinda.out under <<B1A_Installation_Path/tomcat/log>> with FTP Client
Deleting HANA trace files:
1) Open the HANA Administration
2) Go to the Diagnosis Files table
3) Click “Delete Trace Files…”
or from the command line, as ndbadm, run rm -f /usr/sap/NDB/HDB00/<hostname>/trace/*
How to set up a background job to execute HANA SQL Script or bash script in Linux for system administration?
All the system statuses for CPU, Memory, disks, and related information are stored in the _SYS_STATISTICS Schema. You can build up your own HANA system management cockpit/dashboard/reporting with SAP HANA modeling based on these system tables. Then you can monitor the HANA system with MS Excel, dashboards, CR reports or custom developed applications.
You can even send the system status via email notification proactively with approach below:
Option 1: For B1H, you can use query manager, B1 alert, pervasive analysis and B1 mailer
1) Prepare a query accessing the HANA statistics schema to retrieve the HANA CPU/Memory/Disk etc.
2) Develop a dashboard for HANA system status with the query manager and pervasive analysis for HANA system status.
3) Schedule the query to run with a B1 alert in order to receive an email with the query result about the system status.
Option 2: Configure a Linux background job with the “crontab” command
1) The steps on how to set up a cron job in Linux are found here:
2) Here is the syntax for how to execute a HANA SQL statement with “hdbsql” in the crob job to setup mail notification about the job result.
hdbsql -i <InstanceNumber> -n <hana_server>:<port> -u <hana_db_user> -p <hana_db_password> -x -a -j ‘<hana_sql_statement>’
Please refer to the following video and blog for details.
HANA Academy – HDBSQL – Command Line Utility to Run Our First Query
How to backup HANA with script:
3) System administration with Linux commands
Linux provides powerful script for system administration, such as archiving files, move folders, delete folders, etc. It is possible to define your own system administration tasks with Linux commands in the cron job.
4) How to send email (optional, crob job already can send the job result with email.)
How to schedule a backup using cron?
You can find the steps to schedule a backup using the Linux cron tab in customer note 1651055 (https://service.sap.com/sap/support/notes/1651055)
You can watch https://sap.na.pgiconnect.com/p93414224/ if you would like a more detailed overview of how to configure backup. The recording also provides scripts that can be used to delete backup logs that are no longer required.
A more detailed explanation is available from http://scn.sap.com/community/business-one/blog/2013/07/24/how-do-i-use-the-cron-to-schedule-a-backup
Configuration best practices:
1. For B1A only, the log mode can be configured as “overwrite” for analytic.