Hana – Stopped statisticsserver service – Backup wont run

Hana – Stopped statisticsserver service – Backup wont run

When running HANA on AWS,   you should always stop the hana service manually before stopping / restarting the AWS instance,   if not,  the statistics server appears to have a problem.

This does not appear to be EXPECTED behavior,  just something that we have figured how to work around.

If you happen to forget this step,  or the server is restarted without an administrator remembering this step,  then you will need to manually restarted

Additionally,  if for some reason your server runs out of memory or disk space,  HANA can continue running however the statisticsserver process dies and can not be restarted through the studio.

And if the statistics server is not running,  you will not be able to run the BACKUP services within HANA,

So to restart HANA manually,  ssh to your linux client and run this from root.

# su -l hdbadm
# ./HDB stop
# ./HDB start

Refresh from HANA studio and you should  see the statisticsserver process running

– Right Click -> Administration -> Landscape -> Services tab

 

For Reference the error that led me to this issue was one that I received when attempting to run a backup

 Backup error: Backup is not possible because the service 'statisticsserver' at 'vhcalhdbdb:30005' responsible for the volume '2' does not run

HANA Database Backup using Cron

HANA Database Backup using Cron

To create backups using HANA we

  1. create a user
  2. create a user key
  3. create a script
  4. schedule the script to run via cron job

1) Create a HANA user to use specifically for Cron Job – Daily HANA Database Backup

Open an SQL Window and run the following to create a user,  give them access to backup,  and then remove their password so they can not connect via login.

create user backup_operator password Xxxxxxxx1;
grant backup operator to backup_operator;
alter user backup_operator disable password lifetime;

2)  Create a HANA user key to be used for automated connection to the HANA database by the Backup User

Login to the server console using putty.
Under the hdbclient/ directory use the hdbuserstore command to create a key, -i makes it so you will then have to type in a password key from the command above

#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbuserstore -i SET cronbackupkey localhost:30015 backup_operator

Next,  list all keys so that we know where the keys are that we will use to run the cron job automatically.

#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbuserstore list
DATA FILE : /home/backupuser/.hdb/sid-hdb/SSFS_HDB.DAT

KEY CRONBACKUPKEY
 ENV : localhost:30015
 USER: backup_operator

Run a quick check of the new key by running a test command, this is where the password you entered above will be used

#/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U cronbackupkey "select now() from dummy;"|more
CURRENT_TIMESTAMP
"2014-12-26 21:46:24.799000000"
1 row selected (overall time 600 usec; server time 98 usec)

If you run into a situation where the password is wrong, you may end up with a message usage as this:

* 416: user is locked; try again later: lock time is 1440 minutes; user is locked until 2014-12-27 21:35:34.3170000 (given in UTC) [1440,2014-12-27 21:35:34.3170000] SQLSTATE: HY000

If that happens, fix the password by running the hdbuserstore -i DELETE cronbackupkey and hdbuserstore -i SET command above with the correct password than run the following commands to allow the user access again.

alter user backup_operator RESET CONNECT ATTEMPTS;

Using these method the automated method finally came together. Keep in mind the the password for connecting to the database is stored in the key, so if you update the password in the database for the user, you will need to also update the password stored in the key.

 

3) Create a bash script to backup the HANA database to a time and date file

Create a bash script file you can run from a cron job,  that does the work of creating a backup file.  I create a wrapper script instead or running  a command from the cron job,  so I can decide in the script whether I would like to receive an email with the output of the command.

#touch /customcommands/hanabackup
#chown hdbadm.sapsys /customcommands/hanabackup
#chmod 754 /customcommands/hanabackup
#vi /customcommands/hanabackup
tmpfile=/tmp/`date +s`
textsearch="successful"
sqlcommand="BACKUP DATA USING FILE ('$(date +F_%k%M)')"
/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U CRONBACKUPKEY $sqlcommand>$tmpfile
#look up the backup that just completed"
/vol/vol_HDB/sysfiles/hana/shared/HDB/hdbclient/hdbsql -U CRONBACKUPKEY "SELECT top 1 *
 FROM M_BACKUP_CATALOG
 WHERE ENTRY_TYPE_NAME = 'complete data backup'
 ORDER BY UTC_START_TIME desc">>$tmpfile
success=`grep $textsearch $tmpfile`
if [ "$success" == "" ]; then
 echo "HANA BACKUP FAILED: `date`"
 echo "SQL COMMAND: $sqlcommand"
 echo "TEXT NOT FOUND:$textsearch"
 echo
 echo "File Out Put Below"
 echo ---------------------------------
 echo
 cat $tmpfile
 exit 10
fi
exit 0

This script will output a message ONLY if the HANA backup fails,  if it is successful,  it just quietly completes

 

4) Finally setup a cron job which runs the HANA database backup once a day

create a crontab entry to run one time per day at 1:01 am

#crontab -e
MAILTO=myemail@myemail.com
1 1 * * * /customcommands/hanabackup

In order to test that this works, you can force a test failure by setting a data_backup_buffer that is to high for your system see a recent post where we corrected this issue.
You can set a much higher limit than you system allows, and you can confirm that you have the CRON process send you an email each time that the process fails.

HANA error [447] backup could not be completed, [1000002] Allocation failed – Solution

HANA error [447] backup could not be completed, [1000002] Allocation failed  – Solution

When attempting to backup a HANA database, I received an error which stopped the backup from progressing

[447] backup could not be completed, [1000002] Allocation failed ; $size$=536870912; $name$=ChannelUtils::copy; $type$=pool; $inuse_count$=1; $allocated_size$=536870912

When I looked at which files were created as part of the backup I found that the entire backup did not fail,  actually it was only the ‘statisticsserver’ file that would have failed.

While the exact reasons for failure are still a mystery,  I found several posts online encouraging me to reduce the size of the data_backup_buffer_size.
Since I had already had several successful backups using the default information,  I was skeptical.   Turns out it did work,  by reducing the size of the buffer to only 256MB instead of 512MB,  the backup started to work again

 

But the thing that had changed in my HANA installation was the license,  so what was it about the reduction that made it work,  I have 33GB of space I can use in the system.

So,  while I have been able to resolve the issue and I can now create backups,   I do still have an open question to HANA to find out what it is about the data_backup_buffer_size that prevented me from using the default 512  to run the system after it has worked previously?

  • Do I now have full memory?
  • Am I only licensed to use a certain amount of memory and I can’t exceed it?
  • Do I have another setting in one of the other .ini files which limits how the data can be used?

 

Any comments on how i can control how this setting is used is appreciated!

 

Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS

Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS

I need to setup a PHP Website to connect to a HANA.  The site is already installed with PHP and MySQL. Time permitting,  I will also document the process of converting the existing MySQL Database to HANA. The environment:

  • Hana OS: SUSE Linux Enterprise Server 11 SP3 x86_64 (64-bit)
  • Web Server: Ubuntu 14.04 LTS
  • PHP Version:5.5.9-1ubuntu4.5

First install the php odbc package

  1. sudo apt-get install php5-odbc

PHP ODBC Information

#php -i|grep odbc
/etc/php5/cli/conf.d/20-odbc.ini,
/etc/php5/cli/conf.d/20-pdo_odbc.ini,
odbc
ODBC_LIBS => -lodbc
odbc.allow_persistent => On => On
odbc.check_persistent => On => On
odbc.default_cursortype => Static cursor => Static cursor
odbc.default_db => no value => no value
odbc.default_pw => no value => no value
odbc.default_user => no value => no value
odbc.defaultbinmode => return as is => return as is
odbc.defaultlrl => return up to 4096 bytes => return up to 4096 bytes
odbc.max_links => Unlimited => Unlimited
odbc.max_persistent => Unlimited => Unlimited
PDO drivers => mysql, odbc

Install HANA Database Client

Before starting,   Since the Web server is on Amazon,  I just took a quick snapshot so I could rollback to a ‘Pre HANA Client State’ on the server if I needed to.

  1. Download the HANA using this link  SAP Hana Client  download the client,  you must be a member.  Sign up as a public user with all of your information,  you will be required to give them some information about your profession industry and company.    There are several screen and it takes quite a few before you get to the download.  BTW:If this link is broken,  please email me and let me know. -[ if this link does not work,  try it here }
  2. Extract the file into a temporary location and cd into the working directory
    1. cd /tmp
    2. tar -xvzf /home/michael/sap_hana_client.tgz
    3. cd sap_hana_linux64_client_rev80/
  3. Nexe ensure that the scripts that need to execute have execute privileges
    1. chmod +x hdbinst
    2. chmod +x hdbsetup
    3. chmod +x hdbuninst
    4. chmod +x instruntime/sdbrun
  4. Finally,  run the installation program
    1. sudo ./hdbinst -a client
    2. press <enter> to accept the default installation path: /usr/sap/hdbclient
    3. You will see  informaiotn like the following then you can take a look at the log file to see any other details of the installation.
      Checking installation...
      Preparing package 'Python Runtime'...
      Preparing package 'Product Manifest'...
      Preparing package 'SQLDBC'...
      Preparing package 'REPOTOOLS'...
      Preparing package 'Python DB API'...
      Preparing package 'ODBC'...
      Preparing package 'JDBC'...
      Preparing package 'HALM Client'...
      Preparing package 'Client Installer'...
      Installing SAP HANA Database Client to /usr/sap/hdbclient...
      Installing package 'Python Runtime'...
      Installing package 'Product Manifest'...
      Installing package 'SQLDBC'...
      Installing package 'REPOTOOLS'...
      Installing package 'Python DB API'...
      Installing package 'ODBC'...
      Installing package 'JDBC'...
      Installing package 'HALM Client'...
      Installing package 'Client Installer'...
      Installation done
      Log file written to '/var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log'
    4. more /var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log

I saw some reports online that you would have to isntall ‘libaio-dev’,  I did not have to,  but in case you do,  here is the command

  1.  sudo apt-get install libaio-dev

Now you can connect to the hana client

  1. /usr/sap/hdbclient/hdbsql

Ignoring unknown extended header keyword

This happened to me,  this is most likely because the tgz file was created on BSD.  to address this install bsd tar and replace the ‘tar’ command above with the bsdtar command below.

  1. apt-get install bsdtar
  2. bsdtar -xvzf /home/michael/sap_hana_client.tgz

Testing that the client can connect to a HANA server

To confirm that the client works, run the client and connect to the HANA server

  1. # /usr/sap/hdbclient/hdbsql
    Welcome to the SAP HANA Database interactive terminal
    Type: h for help with commands
          q to quit
  2. hdbsql=> c -n xxx.xxx.xxx.xxxx:port -u System -p mypass

I had to type a port,  most likly because the server was not installed on a default port.

Creating an ODBC Connection to HANA Server

First Install the unixodbc packages

  1. sudo apt-get install unixodbc unixodbc-dev odbcinst
  2. ls  /etc/odbc*
    /etc/odbc.ini /etc/odbcinst.ini

Open the /etc/odbc.ini file and type the following

[hanadb]
Driver = /usr/sap/hdbclient/libodbcHDB.so
ServerNode =xxx.xxx.xxx.xxx:30015

Note that the blue matches the location you installed to,  you must make sure these match or you will receive a cryptic [ISQL]ERROR: Could not SQLConnect message.

Connect using your username and password

  1. isql hanadb username passwor
    +---------------------------------------+
    | Connected! |
    | sql-statement |
    | help [tablename] |
    | quit |
    +---------------------------------------+

 

Implementing the HANA  ODBC in PHP

As long as you have been able to connect via the ODBC methods above,  PHP should be a breeze.

Just place the following in your code.

  1. $link=odbc_connect(“hanadb”,”myusername”,”mypassword”, SQL_CUR_USE_ODBC);
  2. $result = odbc_exec($link,”select * from sys.m_tables “);
  3. while($arr=odbc_fetch_array($result)) print_r($arr);

 

A couple of gotchas:

  • Hana does not have the concept of ‘databases’,  they use schemas so the user you connect with must be setup to use the Schema you want by default.
    • Or you can prefix every table with your Schema name
    • Or you can set the default  context to the one you want before each connection
    • I accomplished this by adding an extra line under the odbc_connect();   odbc_exec($dblink, “set schema MYSSCHEMA”);

That’s it!   More posts to come on HANA.

 

 

 

 

Thanks to this post for help http://scn.sap.com/community/developer-center/hana/blog/2012/09/14/install-hana-client-on-ubuntu

Call Now Button(208) 344-1115

SIGN UP TO
GET OUR 
FREE
 APP BLUEPRINT

Join our email list

and get your free whitepaper