Script:To find the swapping/Paging

#! /bin/bash

#

# Page on Page on swap.sh: Shows the swap usage of each process

# Author: 

swap_total=0

for i in /proc/[0-9]*; do

pid=$(echo $i | sed -e 's/\/proc\///g')

swap_pid=$(cat /proc/$pid/smaps |

awk 'BEGIN{total=0}/^Swap:/{total+=$2}END{print total}')

if [ "$swap_pid" -gt 0 ]; then

name=$(cat /proc/$pid/status | grep ^Name: |

awk '{print $2}')

echo "${name} (${pid}) ${swap_pid} kB"

let swap_total+=$swap_pid

fi

done

echo

echo "Total: ${swap_total} kB"

From <https://www.quora.com/How-can-I-determine-which-process-is-contributing-to-paging-on-Linux> 

Difference between IOPS and Throughput

This is the analogy I came up with when talking about Throughput and IOPS.

Think of it as :

You have 4 buckets (Disk blocks) of the same size that you want to fill or empty water.
You'll be using a jug to transfer the water into the buckets. Now your question will be :

At a given time (per second), how many jugs of water can you pour(write) or withdraw (read)? This is IOPS

At a given time (per second) what's the amount(bit, kb, mb, ect) of water the jug can transfer into/out of the bucket continuously? This is throughput.

Additionally, there is a delay in the process of you pouring and or withdrawing the water. This is Latency.

There's 3 things to consider when talking about IOPS and Throughput.
Size (file size/ block size)

Patterns (Random/Sequential)

Mix (Read/Write) percentage

Let's consider as the sample the 3000 IOPS and SQL database engine, the block size in terms of db engine is called the page size and for SQL Server it's equal to 8 KB. If you wish to calculate the actual throughput, if the IOPS defined, you will end up with the formula below:

throughput = [IOPS] * [block size] = 3000 * 8 = 24 000 KB/s = 24 MB/s


Source : https://stackoverflow.com/questions/15759571/iops-versus-throughput#:~:text=4%20Answers&text=IOPS%20measures%20the%20number%20of,have%20about%20the%20same%20size.

SAP application server to database server latency Checks

SAP application server to database server latency can be tested with 

ABAP report /SSA/CAT -> ABAPMeter

Downtime Optimization – Get insights using the new TDO app

https://blogs.sap.com/2020/09/16/downtime-optimization-get-insights-using-the-new-tdo-app/

How to get this app?

There are two ways entering the app:
Using the SAP ONE Support Launchpad
Using the direct link: https://launchpad.support.sap.com/#/downtimeoptimization

Upgrade phase "EU_CLONE_ALLSIZES" aborted with severe errors

Problem: Upgrade phase "EU_CLONE_ALLSIZES" aborted with severe errors

Error Details: 

ETQ399 Read 161305 entries for table 'EXTABLES'. 

4 ETQ291 Database operation "TABLE-EXISTANCE-TEST" completed successfully 

3EETQ399 Table 'XXXXX' does not exist, cloning will fail! 

3EETQ399 Table 'XXXXX' does not exist, cloning will fail! 

3 Found 7 non-existing tables where cloning will fail 1EETQ204 Upgrade phase "EU_CLONE_ALLSIZES" aborted with severe errors ("202011XXXXX")

Solution :

1> List the tables that you can exclude from cloning ( Tables that can be excluded and has no impact )

2> Create a file with the table list

eg: File name : EUCLONEDEFS_ADD.LST

      Path : /usr/sap/S12_upg/SUM/abap/var

      Format : <Tablename> noclone

Helpful Points:

•  /usr/sap/S12_upg/SUM/abap/control has the EUCLONEDEFS_ADD.LST which has the supporting information and the log files from the /usr/sap/S12_upg/SUM/abap/log directory.
• Place the file in /bin or /control if the above option did not work

NIECONN_REFUSED issues during sapcontrol execution and sapcontrol debug option

This is a website that helps how to resolve issues with sapcontrol

https://blogs.sap.com/2015/09/07/how-to-solve-nieconnrefused-issues-during-sapcontrol-execution/


Also some additional points:

Check the directory /sapmnt/<SID>/global/sapcontrol for multiple log files having some numbers_portno._<Instance no._hostname

Those need to be removed for a particular instance which is not getting started or identified.

And run the sapstartsrv command for particular instance.


You can also find the exact root cause of the problem with below debug option:

/usr/sap/<SID>/<instance>/exe/sapcontrol -prot NI_HTTP -nr <inst number> -function GetInstanceProperties -debug

Snote: 2192740 - sapcontrol : -debug option does not show debug information

SSL handshake with .....:443 failed: SSSLERR_PEER_CERT_UNTRUSTED

Problem: SSL handshake errors are listed in dev_icm file despite the correct ciphersuite settings

Solution: Always refer to 510007 - Additional considerations for setting up SSL on Application Server ABAP for the correct settings.

• Check what protocols are supported by the client . This can be checked from the Chrome browser (Cntrl+Shift+I --> in the security overview )
• Check if the ciphersuites on SAP supports the client settings. This can be checked from sapgenpse command line ( sapgenpse tlsinfo <ciphersuite combination> 
• Upload the client's SSL certificates in STRUST client standard
• Check if your client is requesting SNI

Ensure the following parameters are set in accordance

Recommended Configuration of Available TLS Protocol Versions (required for enabling TLSv1.2)

Over the course of year 2016, a growing number of TLS servers were reconfigured to abort/reject TLSv1.0 handshakes, or they are requring forward secrecy (PFS) cipher suites for access. The currently recommended settings for TLSv1.2 interoperability are (requiring at least CommonCryptoLib 8.4.38, recommending at least 8.5.4):

        ssl/ciphersuites           =  <your settings>

 

        ssl/client_ciphersuites  =  <your settings>

 

        icm/HTTPS/client_sni_enabled = TRUE --> This should be the problem solver

 

        ssl/client_sni_enabled = TRUE

Some Servers (including Servers hosted by Content Distribution Systems such as cloudfront) are being co-hosted with lots of other servers on a single IPv4 address, and are accessible only when Clients include TLS extension server_name_indication (SNI) from rfc6066 in their ClientHello handshake messages.  Sending of TLS extensions is unfortunately not backwards compatible with a small, but non-marginal set of old Servers, so TLS extensions are not sent by default.  For SAP Netweaver 741+ Kernels, sending of TLS extension SNI can be enabled through profile parameter icm/HTTPS/client_sni_enabled starting with Kernel Patch 2124480.  Sending of TLS extension SNI as client can alternatively be enabled in 722 Kernel patchno 223 and higher and 721 Kernel patchno 921 and higher through profile parameter ssl/client_sni_enabled, see SAP Note 2384290.

How to disable Hana system replication

1. Stop the secondary system:

   sapcontrol -nr <instance_number> -function StopSystem HDB
   
   

2. On secondary system unregister the secondary system:

   hdbnsutil -sr_unregister
   
   

3. If you want to use this secondary as a normal SAP HANA installation from now on, you have to start it to complete the unregistration. On the secondary execute:

   sapcontrol -nr <instance_number> -function StartSystem HDB
   
   

4. On primary system disable system replication:

   hdbnsutil -sr_disable

recoverSys failed: no valid master host found

Solution:

1> Check if the sapstartsrv is running.If not, start it and initiate the recovery again

Command: sapcontrol -nr <instno> -function StartService <HanaSID>

Securing HANA 2.0 Cockpit via SSL / HTTPS

Problem:

Generating SSL certificates for hana does not update the Cockpit/XS SSL Certificates

Solution:

IF the normal HANA Certificates and PSE files are already existing you can just continue with the commands below.
Please keep in mind that the XSA will be automatically restarted at the end and that the HANA Cockpit will therefore be temporarily unavailable.

Procedure is described in the note 2631903 - HANA Basic How-To Series - Securing HANA 2.0 Cockpit via SSL / HTTPS (EXAMPLE: Microsoft CA edition)
  

Useful commands

cd $SECUDIR

cp -p blueprint_sapsrv.pse cockpithttps.pse

sapgenpse export_p12 -p cockpithttps.pse -C 0 cockpithttps.12

mv cockpithttps.12.p12 cockpithttps.p12

openssl pkcs12 -in cockpithttps.p12 -nocerts -nodes | sed -ne '/-BEGIN PRIVATE KEY-/,/-END PRIVATE KEY-/p' > privatekey.key

openssl pkcs12 -in cockpithttps.p12 -clcerts -nokeys | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > certificate.pem

openssl pkcs12 -in cockpithttps.p12 -cacerts -nokeys | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > cachain.pem

cat certificate.pem cachain.pem > combinedcerts.pem


TAKE CARE - XSA WILL BE RESTARTED NOW!!!

XSA set-certificate --cert combinedcerts.pem --key privatekey.key

HDB daemon does not start after a unclean stop/shutdown of services

Problem: HDB daemon does not start after a unclean stop/shutdown of services and the daemon status file still has the old value

Solution: 

Command:

HDB stop ( give a try )

if not

HDB kill-9 ( not recommended for production systems )

sapcontrol -nr <> -function StopService

sapcontrol -nr <> -function StartService <SID>

HDB start

This should give a  clean restart of the services.

HDB Daemon does not start after Hana host rename failed

Problem: 

Hana host rename is attempted and failed.

Rename fails with hdbdaemon not starting.

Solution:

Hana rename generally touches the following files during rename.Give a check if it really has changed the old hostname in all the locations.

Configuring user home '/hana/shared/SID/home'

   ---------------------------------------------------------

 INFO:     /hana/shared/SID/home/.cshrc already exists -> overwriting

 INFO:     Copy /hana/shared/SID/global/hdb/install/support/CSHRC to /hana/shared/SID/home/.cshrc

 INFO:     /hana/shared/SID/home/.sapsrc.sh already exists -> overwriting

 INFO:     Copy /hana/shared/SID/global/hdb/install/support/SAPSRC.SH to /hana/shared/SID/home/.sapsrc.sh

 INFO:     /hana/shared/SID/home/.sapsrc.csh already exists -> overwriting

 INFO:     Copy /hana/shared/SID/global/hdb/install/support/SAPSRC.CSH to /hana/shared/SID/home/.sapsrc.csh

 INFO:     /hana/shared/SID/home/.bashrc already exists -> overwriting

 INFO:     Copy /hana/shared/SID/global/hdb/install/support/BASHRC to /hana/shared/SID/home/.bashrc

INFO:     /hana/shared/SID/home/.profile already exists -> overwriting

 INFO:     Copy /hana/shared/SID/global/hdb/install/support/PROFILE to /hana/shared/SID/home/.profile

 INFO:     /hana/shared/SID/home/.sapenv.csh already exists -> enhancing

 INFO:       File /hana/shared/SID/home/.sapenv.csh is up to date

 INFO:     /hana/shared/SID/home/.sapenv.sh already exists -> enhancing

 INFO:       File /hana/shared/SID/home/.sapenv.sh is up to date

INFO:   ---------------------------------------------------------

 INFO:   Updating system configuration files...

 INFO:     Updating '/hana/shared/SID/global/hdb/install/config/sapprofile.ini'

 INFO:       Value 'SAPLOCALHOST' in section '' is up to date. ('SIDhdb00')

 INFO:       Value 'DIR_PROFILE' in section '' is up to date. ('/usr/sap/SID/SYS/profile')

   File '/hana/shared/SID/global/hdb/install/config/sapprofile.ini' is up to date

 Updating '/hana/shared/SID/profile/DEFAULT.PFL'

 INFO:       File '/hana/shared/SID/profile/DEFAULT.PFL' is up to date

 INFO:   Adjusting instance...

 INFO:     Updating instance configuration files for host (SIDhdb00)...

 INFO:       Updating '/hana/shared/SID/profile/SID_HDB00_SIDhdb00'

 INFO:         File '/hana/shared/SID/profile/SID_HDB00_SIDhdb00' is up to date

 INFO:       Updating '/hana/shared/SID/HDB00/SIDhdb00/sapprofile.ini'

INFO:         File '/hana/shared/SID/HDB00/SIDhdb00/sapprofile.ini' is up to date

check the entries in /usr/sap/sapservices file. It should contain the updated entries

Option 1: When the database is online or nameserver service is active, check system view M_TOPOLOGY_TREE for topology information. Run on SYSTEMDB for MDC.
SELECT * FROM M_TOPOLOGY_TREE

Option 2: When database is down, use hdbnsutil to collect topology information.

hdbnsutil -exportTopology <filename>

Option 3: From fullsysteminfodump (KBA 1732157 for FSID collection) you can read topology from topology.txt. In system replication environment topology.txt of secondary site is empty, because nameserver is not really active.

Option 4: Starting with SAP HANA 2.0 SPS 03 the topology is periodically dumped to the SAP HANA trace file directory using JSON format and the following file name notation: nameserver_topology_<host>.json

Export the topology using the command

Export:

hdbnsutil -exportTopology myExport.txt

Import:

hdbnsutil -importTopology myExport.txt

Follow snote 2606272 - HANA: How to read topology

XS/Cockpit URLs SSL certificates are not updated after HANA DB SSL Certificate renewal

Problem: XS URLs  SSL certificates are not updated after HANA DB SSL Certificate renewal

Cause: 

AP HANA 2.0 Cockpit is based on SAP HANA XS Advanced (XSA) which handles certificates a bit different than the former XS classic implementation.

The XSA SSL certification is sometimes overwritten with a new installation/update.

Solution:

You need to bundle the certificate chain and import it to the XSA cert path.

Export certificates for XSA

sapgenpse export_p12 -p cockpithttps.pse -C 0 cockpithttps.p12

Export private key, certificate and CA chain & combine certificate and CA chain into 1 file.

1. openssl pkcs12 -in cockpithttps.p12 -nocerts -nodes | sed -ne '/-BEGIN PRIVATE KEY-/,/-END PRIVATE KEY-/p' > privatekey.key
2. openssl pkcs12 -in cockpithttps.p12 -clcerts -nokeys | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > certificate.pem
3. openssl pkcs12 -in cockpithttps.p12 -cacerts -nokeys | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > cachain.pem
4. cat certificate.pem cachain.pem > combinedcerts.pem
5. openssl pkcs8 -topk8 -in privatekey.key -out private_pkcs8.key -nocrypt

Implement private key and combined certificate file to XSA

1. xs set-certificate <DOMAIN> -c combinedcerts.pem -k private_pkcs8.key
2. XSA restart

(( Get the domain from commands : xs domains ))

Opening the Cockpit URL should now change to new SSL Certs

FAIL: NIECONN_REFUSED (Connection refused), NiRawConnect failed in plugin_fopen()

Problem:

HDB start command will issue the error

"FAIL: NIECONN_REFUSED (Connection refused), NiRawConnect failed in plugin_fopen()"

Scenario:

• Command 'HDB start' failed with below error:FAIL: NIECONN_REFUSED (Connection refused), NiRawConnect failed in plugin_fopen()
• sapcontrol commands also failed with error 'FAIL: NIECONN_REFUSED (Connection refused)'
• service sapstartsrv was not running, checked by command 'ps -ef | grep sapstartsrv'
• From OS log file /var/log/messages, find error occurred during HANA startup

Cause:

Permission error

Troubleshooting:

From the /var/log/messages, the following error was found

"Unable to change to Directory /usr/sap/SID/HDBXX/hostname/trace

Resolution:

The instance profile may be damaged by the unexpected or mistaken shutdown.

The SAPLOCALHOST parameter was pointing to wrong hostname due to which the sapstartsrv agent was not able to find/write the log files under the respective hana trace directory. After the parameter was corrected, the agent was able to start and HANA resumed its services.

Supporting Snote:

2591190 - HANA failed to start with error: FAIL: NIECONN_REFUSED (Connection refused)

Useful Commands:

/usr/sap/SID/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr <inst num> -function Start

/usr/sap/SID/HDBXX/exe > strace -afH -o /tmp/strace.txt sapcontrol -prot NI_HTTP -nr <inst num> -function Start

Could not Create Full TEXT INDEX- SUM ERROR- MAIN_POSTPROC/JOB_RSFIXUPG

Solution: 

From SE14 ,

Check the indexes that exists for that table.

Usually SAP SUM will try to create a new FULL TEXT INDEX with a different name as a standard and as there are already indexes available, it fails to create the new INDEX with the error "SAP DBTech JDBC: [289]: cannot use duplicate index name:"

From SE11, 

Drop the existing FULL TEXT INDEXES and re-run the SUM STEP

Commands that are helpful : 

DROP FULLTEXT INDEX "<table>~<index>" ON "<Schemaname>"."<table>" 

CREATE FULLTEXT INDEX "<table>~<index>" ON "<Schemaname>"."<table>" ("TXT20") ASYNC LANGUAGE DETECTION ('EN', 'DE') PHRASE INDEX RATIO 0.200000 FUZZY SEARCH INDEX ON SEARCH ONLY ON FAST PREPROCESS OFF TEXT MINING OFF TEXT ANALYSIS OFF TOKEN SEPARATORS '\;,.:-_()[]<>!?*@+{}="&#$/' COMPRESSION LEVEL 0

Log Segments With 'RetainedFree' Status

Problem: There are many log segments on the Primary site with the status "RetainedFree".

Solution:

1. Disable the replication on the Primary Site

hdbnsutil -sr_disable --force

2. Reclaim the logsegments

Alter system SAVEPOINT;

Alter system RECLAIM LOG;

3. Enable the replication on the Primary Site.

hdbnsutil -sr_enable --name=<PrimarySiteName>

Notes: 

For Reference, there are couple of Snotes that are helpful.

2416014 - Log Segments With 'RetainedFree' in the Former Secondary and Third Systems

2821539 - /HANA/LOG full with log segments in Retainedfree state

SAP HANA TenantDB is locked with license key error System locked; license is invalid or expired but SYSTEMDB has a valid license

Issue :

SYSTEMDB has a valid license and is not locked

Tenant is locked with the error "System locked; license is invalid or expired"

This error will occure in

1. HANA DATABASE 1.0
2. HANA DATABASE 2.0
3. Multi-Tenant Database Container

Scenario :

License keys that are applied directly to the TENANT database will override any licenses on the SYSTEMDB.

Solution:Remove the license key from the TENANT database. This allows the TENANT to inherit the SYSTEMDB license key.

To do this, run the following SQL command from the TENANT (not the SYSTEMDB)

UNSET SYSTEM LICENSE ALL

This does not require a restart of the SYSTEMDB or TENANT. However, a new connection will need to be established.

R3trans Error: Rejected as server is temporarily Overloaded

Problem: When checked "R3trans -d" results in error "Rejected as server is temporarily overloaded"

Envronment : SAP ABAP stack and HANA 2.0 SP03

Reason: This problem generally appears when underlying HANA database rejects any connections from the client . HANA would have reached the maximum limit on Global allocation memory. You can check the status from HANA Studio or any monitoring feature.

Use the following SQL statement to diagnose whats occupying the memory

SELECT top 5 Schema_name,

       table_name,

       memory_size_in_main,

       memory_size_in_delta,

       raw_record_count_in_delta,

       last_merge_time

FROM SYS.M_CS_TABLES

where record_count > 0

order by raw_record_count_in_delta desc;

Mini checks can also be used to identify the overview of largest tables.

Solution: 

As a temporary workaround, the "admission_control" parameter that is enabled by default in HANA 2.0 SP03 has to be disabled so that HANA accepts any requests from clients.

or

Unload "tablename" - from DBACOCKPIT / HANA STUDIO can be used to unload any large table to free up  the memory to perform any further actions.

Once the access is gained onto HANA , identify the growth of the respective table/ perform housekeeping on the DB