Performing Handling System Offline Situations

In today's world, where almost every device is connected to the network, it's extremely important that the network is up and running correctly. In an SAP HANA database system, the network is important as well. End users connect to the database to execute all kind of queries. This can be done directly using SQL or via a middleware application. The SAP HANA database itself can be set up as a multi-host scale-out system that distributes the data over several servers. Without a network, external end-user connections and internal server-to-server connections would fail.

Because external and internal network connections are important for a SAP HANA system, you should test both by pinging SAP HANA and non-SAP HANA hosts in your network. If all the hosts can be reached, then the network is available and can be excluded.

123
ping <SAP HANA host>
ping <internal host>
ping <external host>

Using a ping, you can test that the remote hosts are reachable, but maybe the network packages are taking the long way home due to a routing problem in the network. You can check the network path to the remote host using the following command:

12
traceroute <SAP HANA host>
traceroute <internal host>

As soon as you know that the network is up and running, you can start testing if the SAP HANA hosts are functioning within normal parameters. Connect to the SAP HANA host(s) using your preferred method (SSH, XRDP, VNC, and so on) and check the Linux system logs.

As the SAP HANA hosts are normally up and running 24/7, check whether there were unplanned and unexplained restarts. You can check this with the following command:

1
last | grep boot

Looking at the Linux system log files to analyze the system is one of the most important tasks when troubleshooting a system. Since the move from syslog to systemd, kernel messages and messages of system services are handled by systemd.

Systemd was introduced in SLES 12 and RHEL 7 and replaces the traditional init scripts. Systemd also introduced its own logging system called journal.

Systemd manages the journal as a system service under the name systemd-journald.service and it is switched on by default. In a systemd-enabled Linux system, the systemd-journald service collects all messages from the kernel, boot process, syslog, user processes, standard input, and system service errors in a centralized location.

You can check the last 50 boot error messages in the journal with the following command:

12345
journalctl -n 50 -p err -b

-n = number of messages to display
-p = message priority
-b = display boot messages

journalctl -n 50 -p err -k

-k = display kernel messages

Storage problems can result in severe database problems, database standstill or, even worse, data loss.

Avoiding storage problems is part of every layer in the Linux software and hardware stack. Modern hard drives are capable of detecting and correcting minor errors in block reads. SAN and NAS have built-in error correction and redundancy to handle power and hardware failure. Modern Linux file systems are all journal-based and can correct errors created due to power failures. Last but not least databases also support many different techniques to survive power failures and incorrect service shutdown situation.

If the SAP HANA database system 'stopped' due to power, hardware, or software failures, you should check if all file systems are available again after the server has restarted. Depending on the storage system used you can investigate the storage problem more deeply.

Checking if the database is up and running sounds like a good plan, but with all the services running it might still be the case that the end user or middleware application cannot connect. Such a situation can happen when, for example, the SAP HANA system is up and running, but the network doesn't allow SQL connections due to a firewall having been reconfigured.

To check if all the SAP HANA services and hosts are available on the Linux host, you can execute the following commands:

As <sid>adm user:

12
sapcontrol -nr <instance number> -function GetProcessList
sapcontrol -nr <instance number> -function  GetSystemInstanceList

You also need to check whether or not the system can be reached over the SQL interface. When you are already connected to the SAP HANA host via the SSH session, check the SQL interface with the following command:

As <sid>adm user:

1
hdbsql -n localhost -i <instance number> -d <Tenant name> -u <your database user>

Enter your password when requested. You are now in the HDBSQL terminal. From the HDBSQL terminal you can get SAP HANA connection information by executing the command:

1
\s 

Checking the SQL connection only from the local host isn't sufficient as it could be the case that SAP HANA SQL is blocked on the network. To make sure that this isn't the case, you should perform a HDBSQL connection test also via the network from the end-user LAN and the application server network.

From the SAP S/4HANA ABAP application server, as <sid>adm user:

1
hdbsql -n <SAP HANA host> -i <instance number> -d <Tenant name> -u <your database user>

Enter your password when requested. You are now in the HDBSQL terminal. From the HDBSQL terminal you can get SAP HANA connection information by executing the command:

1
\s 

The SAP HANA cockpit 2.0 provides a single point of access to a range of tools for administration and detailed monitoring of SAP HANA databases. It also integrates development capabilities required by administrators through the SAP HANA Database Explorer.

The SAP HANA cockpit 2.0 is a web-based HTML5 user interface that you access through a browser. It runs on SAP HANA extended application services, advanced model (XS advanced). The cockpit handles single-container and multi-container systems as of SAP HANA 1.0 SPS 12. You can use the cockpit to monitor and manage multiple SAP HANA database systems and tenants.

The cockpit can be installed separately on dedicated hardware, shared hardware or in an existing SAP HANA database tenant. This provides greater flexibility, because it allows you to manage more than one SAP HANA system in a single administration environment.

SAP HANA cockpit provides centralized system and database administration features, such as database monitoring, user management, and data backup. Administrators can use the cockpit to start and stop services, to monitor the system, to configure system settings, and to manage users and authorizations. The cockpit provides applications that allow you to manage SAP HANA options and capabilities (for example, SAP HANA dynamic tiering). These applications are only available if the options or capabilities are installed.

To start the cockpit, open the following URL in our training landscape: https://wdflbmt7288.wdf.sap.corp:51026. After logging on, you are presented with an overview of the databases assigned to your user account.

In the overview, you can select the Database Directory tile or a dedicated group tile, like the Group01 in this screenshot, to quickly see the status of the SAP HANA systems.

From the Database Directory tile, you can navigate to your SAP HANA system overview page, where a detailed status of the selected SAP HANA system is displayed.

The Database Directory gives an aggregated overview of each database for which you are responsible. In the Database Directory, you can see that a system or tenant is in trouble when the status Stopped, Running with issues, or Unknown is displayed. To investigate these problems in more detail, you start the cockpit System Overview page for this system or tenant by selecting the corresponding line.

When the Database Directory shows that the whole SAP HANA database system is having problems, it is important to quickly investigate the root cause of the problem so that you can get the system up and running again.

Even when the SAP HANA database system is down, the cockpit can be used to investigate the root cause of the problem. This system-down analysis is done via the SAP start service connection.

When a important system is down, you want to start it as soon as possible. This is a logical course of action, but it could make the root cause analysis more difficult, because during a restart, important low-level log or trace files can be overwritten. So a best practice is that before starting the system, you save all the important log and trace files for later investigation.

To support this, SAP HANA provides a full system information dump. This information dump lets you control which logs to save, so you can use these saved logs to troubleshoot the issue after you have restarted the SAP HANA database.

In the Database Directory, you can also specify the database user credentials required to drill down to an individual database, which is necessary unless single sign-on is in effect for that database.

The Database Directory shows a high-level status overview of all the databases belonging to groups to which you have been granted access. For each database, you can drill down for more information.

When you open the cockpit's Database Overview page for a system that shows the status Stopped, No SQL access, or Unknown, it is very likely that you cannot connect to the SAP HANA database using a SQL connection. The cockpit starts, but cannot retrieve the monitoring data using SQL. This results in almost all cards showing the text Cannot load data.

It is best that you start the Database Overview page of SYSTEMDB instead, because from this Database Overview page you can retrieve some information via the SAP host agent infrastructure. Via the SYSTEMDB, you can get information on the status of the SAP HANA services.

This means that you cannot use the default monitoring cards to further investigate the problem. Depending on the error situation, the SAP HANA Cockpit will present you the relevant cards that can be useful during the investigation. In a system down you will find the Manage full system information dumps application to be available, but the Troubleshoot unresponsive systems isn't because the SAP HANA Cockpit can't connect to the SAP HANA index service.

To collect the diagnosis information, perform the following steps:

1. Search for the Alerts and Diagnostics card.
2. Choose the Manage full system information dumps link.
3. Choose Collect Diagnostics, and in the dropdown list choose Collect from Existing Files or Create from Runtime Environment.
4. In the pop-up window choose the information items you want to collect. In the bottom-right corner, choose Start Collecting.
5. When all the data is collected, the fullsysteminfodump_<SID>_<DBNAME>_<HOST>_<timestamp>.zip file is displayed in the collections table.

Once the collected diagnosis information ZIP files are available, you can download them by choosing Download. The files are saved locally in your browser’s download directory.

The diagnosis information is collected by the Python support script fullSystemInfoDump.py, which collects a range of information from your system for diagnosis purposes. It can be triggered from SAP HANA cockpit, or directly from the command line.

The fullSystemInfoDump.py script allows you to collect information from your system, even when it is not accessible using SQL. You can then add this information to a support message, or use this information to investigate the root cause on your personal computer. The script is part of the SAP HANA server installation and can be executed directly from the command line.

If you are logged on as the operating system user, <sid>adm, the fullSystemInfoDump.py script is part of the server installation and can be run from the command line. It is located in the directory $DIR_INSTANCE/exe/python_support.

Start the script from its location with the command:

1
python fullSystemInfoDump.py

You can modify the command with several command line options. To see the available options, specify the option --help. All options related to getting a system dump are fully described in SAP Note 1732157.

If the system can be reached by SQL (and you have not specified the option --nosql), the script starts collecting diagnosis information. If the system cannot be reached by SQL, the script starts collecting support information, but does not export data from system views.

The script creates a ZIP file containing the collected information and saves it to the directory $DIR_GLOBAL/sapcontrol/snapshots. $DIR_GLOBAL typically points to /usr/sap/<SID>/SYS/global.

The name of the ZIP file is structured as follows: fullsysteminfodump_<SID>_<DBNAME>_<HOST>_<timestamp>.zip.

The time-stamp in the file name is Coordinated Universal Time (UTC). The HOST and SID are taken from the sapprofile.ini file.

The output directory for the ZIP file is shown as console output when the script is running, but you can look it up with the command hdbsrvutil -z | grep DIR_GLOBAL= .

The Python support script fullSystemInfoDump.py collects the following information from your system for diagnosis purposes.

In the SAP HANA cockpit - Home screen, select the Database Directory or your personal group tile. In the Database Directory screen, choose the Manage Databases link. To start the tenant database, in the Manage databases screen, choose the Start button. This will perform a normal tenant database start.

In the SAP HANA cockpit - Home screen, choose the Database Directory tile and choose the SYSTEMDB that is stopped. In the Database Overview screen, search for the Services card. To start the database system, in the Services card choose the Start Database button. This will perform a normal database start of the SAP HANA database.

The SAP HANA system restart sequence quickly restores the system to a fully operational state.

When you restart an SAP HANA system, the following activities are executed by the restart agent of the persistence layer:

1. The data volume of each service is accessed to read and load the restart record.
2. The list of open transactions is read into memory.
3. Row tables are loaded into memory.
4. Open transactions are processed using the redo log, as follows:

   • Write transactions that were open when the database was stopped are rolled back.

   • Changes to committed transactions that were not written to the data area are rolled forward.

     The first column tables start being reloaded into memory as they are accessed for roll forward.

   Note

   Because a regular or "soft" shutdown writes a savepoint, there are no replay log entries to be processed in this case.
5. Aborted transactions are determined and rolled back.
6. A savepoint is performed with the restored consistent state of the database.
7. Column tables that are marked for preload, and their attributes, are asynchronously loaded in the background (if they have not already been loaded as part of log replay).

   The preload parameter is configured in the meta-data of the table. This feature is useful, for example, to make certain tables and columns that are used by important business processes available more quickly.

8. Column tables and their attributes that were loaded before restart, start reloading asynchronously in the background (if they have not already been loaded as part of log replay or because they are marked for preload).

   During normal operation, the system tracks the tables that are currently in use. This list is used as a basis for reloading tables after a restart.

Reloading column tables, as described in steps 7 and 8, restores the database to a fully operational state more quickly. However, it does create performance overhead and may not be necessary in non-production systems. You can deactivate the reload feature in the indexserver.ini file by setting the reload_tables parameter in the sql section to false. In addition, you can configure the number of tables whose attributes are loaded in parallel using the tables_preloaded_in_parallel parameter in the parallel section of the indexserver.ini file. This parameter also determines the number of tables that are preloaded in parallel.

Now that the SAP HANA database system is up and running, you can continue to investigate the failure. If you cannot find the root cause of the failure, open an SAP support message and attach the diagnosis information collected in the SAP HANA cockpit - Full system information dump application.