HANA log_mode for point in time restore

[Former Member]

We had done a few tests in the area of Backup and Restore of HANA System using both the Local and Backint based backups

One of our tests failed for a point in time restore of the HANA system that should have used both Data and the log backups to restore based on the time we chose

Here is the error we found

Further checks revealed the Data restore was successful and the recovery failed during the log segment restore

At this time the log_mode was set to NORMAL on the destination HANA System

We suspected the issue could be either

1. Corrupted log

2. Or the log_mode setting

Due to lack of time we tried changing the Point of restore to a different time than before and also used log_mode= OVERWRITE

This time the recovery completed and we were left with an open question if log_mode could have been responsible or a corrupted log

No further tests could be carried on due to time constraints of the project so this open question could not be validated

To validate this point I did another point in time recovery test for a different HANA system using backint tool leaving the log_mode in NORMAL state and the recovery worked

We have asked the Backint service provider if there are any specific settings for the log_mode for doing a point in time recovery and waiting for a feedback

I am posting this to share the results of my tests and seeking feedback if there is any documentation from SAP that confirms the log_mode setting while doing a point in time recovery

[HayBouten · Accepted solution]

Hi Sunil,

The log_mode=normal means that the log segment must be backed up before it can be re-used. With this log backup you also create the chain of log backups that you need to perform a restore and recovery. This is also true for a Point In Time (PIT) recovery.

The log_mode=overwrite means that the log segment is freed by a save point. This log mode is only useful in scenarios where you don't need to restore and recover the database. This might be in a sandbox or test system.

So when you tried the to do the PID recovery that was only possible because the log_mode=normal was set. The reason why it failed will be difficult to figure out with the limited info that you provided, but from what you told us here the PIT with log_mode=normal wasn't the cause of the error.

-- Hay

[Former Member · Accepted solution]

Thank you Hay

Yes I totally understand the usage of log_mode and we have been using log_mode=NORMAL for all our critical HANA Systems

Based on my tests I have got some mixed results so I would like to confirm if there is any SAP note to confirm that we do not have to change the log_mode for a point in time restore

I do not think we have seen any such note so it is implied that this setting is not supposed to be changed at any time for point in time restore Right?

[HayBouten · Accepted solution]

Log_mode=normal is de default mode and is recommended to provide support for Point In Time recovery. So yes, you should use log_mode = normal for a PIT.

With log_mode=overwrite it is not possible to do a PIT.

Maybe SAP Note 2091951 - Best Practice SAP HANA Backup & Restore is interesting? Or SAP Note 1642148 - FAQ SAP Backup & Restore.

[Former Member · Accepted solution]

As discussed the documentation from SAP does not confirm if you need to set a specific log_mode for Point in time recovery

I have tested the point in time recovery using both log_modes and it works

So I do not agree with your comment that PIT is not possible with log_mode=overwrite

In would search further to see if there is any document on specific setting for log_mode for recovery

[HayBouten · Accepted solution]

Sorry Sunil,

But you are wrong. The text on the PIT recommendation is coming from the official SAP HANA Administation Guide document. (Page 423).

Log mode NORMAL is recommended to provide support for point-in-time recovery.

and a bit lower

With log mode OVERWRITE, a point-in-time recovery is not possible ... 

So with log mode overwrite you can only recover to a specific data backup.

[Former Member · Accepted solution]

I see the update in the HANA Administration guide

Thank you for pointing this out

In my case the results are contradictory, we have been able to do a PIT using log_mode=OVERWRITE and it works fine

[lbreddemann · Accepted solution]

Sunil Tallapaneni wrote:

In my case the results are contradictory, we have been able to do a PIT using log_mode=OVERWRITE and it works fine

Here's where you miss an important point.

Log mode OVERWRITE does not imply that it will be not possible to perform a PIT in any case.

Why would we create a feature specification like that?

What it doesn't do is guaranteeing you that you will be able to perform a PIT.

It's absolutely possible and even likely (if you change enough of your data) that a PIT is not possible anymore.

That's the whole point.

You may loose data and the option to recover it.

In other words: with your tests you just got lucky.

[Former Member · Accepted solution]

As mentioned before I totally understand the setting log_mode=OVERWRITE and NORMAL

I shared my experience of being able to complete a PIT recovery of the Database with log_mode=OVERWRITE

You seem to be very confident that it should not work and I was lucky in my test

Lars:

Is there a feature specification that confirms what is the error we may receive if a PIT is done with log_mode=OVERWRITE?

If not, is there an option for you to test a recovery and share with us the error you may get when a PIT recovery is made with log_mode=OVERWRITE?

That would confirm I was lucky with my test as I do not think we would want to be guessing which option to use at the time when a Database needs to be recovered from its backups

[lbreddemann · Accepted solution]

Look, it's fairly simple:

As long as all required data is still in the log area, the PIT recovery will work (obviously).

That was the case for your examples.

It's not so much about preventing that a PIT recovery in log mode overwrite works.

It's about guaranteeing that a recovery will work if you use log mode normal.

That's what we (SAP) do: we have this overwrite mode feature and tell you that we don't guarantee a PIT recovery. We don't say that it wouldn't ever work. We don't prevent it to work.

We just make no promises.

You're basically asking that I create a case where performing a PIT recovery with log mode overwrite doesn't work. What would that prove? Neither would it prove you or me wrong or right.

When it comes to recovery it's all about what the system can guarantee you and log mode overwrite will eventually overwrite data that would be required for the recovery. That's why it cannot guarantee you that the PIT recovery will work.

[Former Member · Accepted solution]

I agree with your comment "It's all about guaranteeing that a recovery will work if you use log_mode NORMAL"

Infact in my case the PIT recovery had failed with log_mode=NORMAL and worked with log_mode=OVERWRITE, this made me share my experience and understand from others on their experience

I would try and do some more tests if we are able to in future and share my feedback if I find any results otherwise

Thank you for your comments on this