SAP HANA log backup

[Former Member]

We are looking for a workable way in which we can regularly backup our HANA logs and a process that automatically deletes the old logs to reclaim the space. We have not found an automated process for doing log backup and thus, was wondering whether anyone here might be able to offer some light in how we get this going.

The Backup and Recovery document describes enabling/disabling log, log path, but it does not say how to automate log backup as part of scheduled process. We are using HANA Studio version 1.0.25 with DMIS 700/SP5, and would be grateful if any could suggest an actual process.

[Former Member]

Hi All,

I am using the backup script to backup the hana database but I don't get how the clean up of the log backups is supposed to work. In the crontab I added three entries:

15 21 * * 0 /usr/sap/tools/bin/backup.sh -q -p --retention=4

15 22 * * 0 /usr/sap/tools/bin/backup.sh -ll -cl -od --output-file=delete-outdated-logs.txt

15 23 * * 0 /usr/sap/tools/bin/moveLogBackup.sh

this way I keep 4 backups and then delete the oldest one, that part is working. Also the delete-outdated-logs.txt is created when an old backup is removed and files are deleted (according to the log files).

However, when I look in the /usr/sap/<SID>/HDB00/backup/log directory and do an 'ls' command there then I still see a lot of files older then the oldest backup (some are really small). Shouldn't this script simply remove all files from this directory older then the oldest backup available? Now the disk still runs full after a while.

By the way, I am running the crontab entries under <SID>adm

Kind Regards,

Nico van der Linden

[lbreddemann]

Hello Soyful,

SAP provides a script via SAP note

1651055 Scheduling SAP HANA Database Backups in Linux

that can be scheduled and configured to create regular backups.

On top of that, the script can be used to find log backups that are not required any more and a way to delete those log backups.

Best regards,

Lars

[Former Member]

Hi Lars

Is there any plan in the immediate future to make this log backup function available through back-end SAP system, rather then a script? Something like DB13C?

Cheers

Soyful

[lbreddemann]

Hello Soyful,

just to clarify: the actual log backups are created by HANA automatically anyhow.

The script is just there to schedule data backups and to do the math and find out which log backups are actually still required.

What you still have to do is to move away the log and data backups to a safe location.

For this task, there is no SAP tool available.

External backup tool certification is currently ongoing and will be available with SPS5 afaik.

regards,

Lars

[former_member186487]

Hi,

The script and the document attached to it are just an excellent document to read with.

But running a script a very challenging. It cannot be run as it is so I tried to enter three information like HDB instance number, SID, server. When I run it then it just displays some jargon about system and then hdbsql prompt.

I do not know what to do from here.

Can anybody shed some light as what part of the script should be changed to suit the individual need? I could only get three parameters to be entered.

Your help will be greatly appreciated.

Best Regards,

SS

[lbreddemann]

Please check the PDF file attached to the note. It explains in depth what the script does and how it could and should be used.

- Lars

[former_member186487]

Thank you for the reply.

I did read the guide as I said. It explained very well.

I exactly copied the command as it is and did not run.

I checked the script and found that some user parameters should be defined because the error was looking for exact directories paths.

I did and ran it again but this time it does not erred out but opens up an hdbsql prompt after displaying few lines about the same values like instance number etc.

What should I do it from here?

I tried multiple options but same thing.

Please advise.

Regards

[lbreddemann]

Well, it usually helps us (to help you), if you show us exactly what you did and exactly what you got for an error message.

- Lars

[former_member186487]

Hi,

Here is the output of the command  -

host12345:~> ./backup.sh -ld -cl

Username  : ""
DB name   : ""
Instance  : "00"
server    : ""
xuserkey  : "-I"
nullvalue : "?"
fieldsep  : "NULL"
cmdsep    : ";"
CMDTrace  : "FALSE"
autocommit: "TRUE"
noHeader  : "TRUE"
useDBS    : "TRUE"
batch     : "FALSE"
rollbOnErr: "FALSE"
Command   : "NULL"
hdbsql=>

I do not know what to do from this point.

Please help me.

Regards,

SS

[lbreddemann]

Hi Surendra,

looks like you didn't provide the logon data correctly.

Did you setup the securestore credentials?

Try

     hdbuserstore list

to get a list of all keys that are defined for your current os user.

The next thing is that you need to edit the script, so that it uses this secure logon key.

To do that, you've to set two parameters in the script:

[...]

270 USE_HDBUSERSTORE=TRUE

271 # Key for hdbuserstore. Only needed if you intend to use

272 # hdbuserstore to authenticate against SAP HANA Database in

273 # hdbsql.

274 USERSTORE_KEY=BACKOP

275 ######## Information regarding the user store #################

276 # The hdbuserstore executable is part of the hdbclient package.

277 # To use the hdbuserstore, install the linux x86 64 bit

278 # hdbclient package on the SAP HANA Database server. This will

279 # install into /usr/sap/hdbclient

280 # There you will find the hdbuserstore executable. You can

281 # create entries in hdbuserstore by calling:

282 # /usr/sap/hdbclient/hdbuserstore SET <key_name> <server>:<port> <DB_user_name> <DB_user_password>

283 # The <port> is the SQL port of the index server, i.e. 3<instance>15

284 # For a HANA database user to execute a database backup, it is

285 # sufficient if the user has the system privileges

286 # "BACKUP ADMIN" and "CATALOG READ".

[...]

Obviously, I set my userstore key to BACKOP and I got the respective entry in my secure user store:

hanadm@vml3012:/usr/sap/HAN/HDB00/backup> hdbuserstore list

DATA FILE       : /home/hanadm/.hdb/vml3012/SSFS_HDB.DAT

KEY BACKOP

  ENV : vml3012:30015

  USER: backop

I've no clue how you managed to get the error message you posted, but I can tell that it's the output of hdbsql with the -t option set (print debug information).

- Lars

[former_member186487]

Thank you again Mr Lars for reply.

I tried that and was able to get away with the error I reported earlier to you.

One thing I want to point it the script is in .sh and not in .sql.

I am running as shown below and getting a sql error -

host12345:/usr/sap/SID/hdbclient> ./backup.sh -ld

* 257: sql syntax error: incorrect syntax near ".": line 1 col 21 (at pos 21) SQLSTATE: HY000

* 257: sql syntax error: incorrect syntax near ".": line 1 col 13 (at pos 13) SQLSTATE: HY000

Can yo uplease advice how I can get away with this error. I tried to read the script and could not get any idea.

Your help will be greatey appreciated.

Best regards,

SS

[lbreddemann]

The script is a bash-shell-script, of course it's not SQL then...

However, the error message you get is from one of the SQL commands the scripts creates and feeds into hdbsql.

It's very likely, that some of the configuration steps for the script still are not done correctly.

Unfortunately, unless I see how you set the different settings in the script, there's no way I can take a guess what's going wrong on your side.

- Lars

[lbreddemann]

Surendra

Although you sent me the file via email without my consent (usually I just put those emails into the SPAM folder) I had a look into it.

The STATS_SCHEMA parameter value was left out in your script, but WRITE_STATS_TO_TABLE was still set to TRUE:

#######################################################

######### statistics into database tables #############

# switch: write to DB tables? yes/no

WRITE_STATS_TO_TABLE=TRUE

# target schema to write to:

STATS_SCHEMA=

# table to write statistics to:

STATS_TABLE=Z_SCRIPT_BACKUP_STATISTICS

# table to write backup log info to:

LOG_TABLE=Z_SCRIPT_BACKUP_RUNS

# key in HDB User Store for writing statistics entries

USERSTORE_KEY_STATS=${USERSTORE_KEY}

Please fill in the schema name where you want to have your backup statistics written to and the SQL error should be history.

And of course this parameter is listed in the documentation as on of the parameters that MUST be set...

- Lars

[former_member186487]

It works.

I just have to understand the output as it is little different than what I get using SQL in Studio.

Thank you.

SS

[former_member228196]

Hi Lars, could you upload your PDF again ?(can't see it now)

[lbreddemann]

The PDF is attached to the SAP note. So please check it there.

- Lars

[Former Member]

Does this work with USE_HDBUSERSTORE=FALSE ?

I kept getting *-10709: Connect failed (no reachable host left)

I have created a connection details which is being fed to ${BACKUP_SQL}

I tried ./hdbsql -n hostname -i instance -u username -p password, connection was successful.

Thank you

Welly

[lbreddemann]

Please don't revive years old discussions

Open a new discussion instead.

Lars (moderator)