MaxDB Recovery - Table Data Recovery Failed

[Former Member]

Dear All,

I'm trying to simulate the backup and recovery functionality of MaxDB by using Database Studio.

This is what I've done so far,

1) Create dummy table with data inside.

2) Performed a complete backup.

3) Performed log backup.

4) Removed all the data within the dummy table.

5) Performed a complete recovery of data and log backup.

However, the table is still empty after recovery.

Does this means that the recovery failed? Or the backup?

There is no error message or whatsoever during the backup and recovery.

I did some research and found some information about MaxDB recovery considerations, whereby it states that "recovering individual tables is not possible."

Is that what causes it?

Please advise further.

Many thanks in-advance!

[lbreddemann · Accepted solution]

> I did some research and found some information about MaxDB recovery considerations, whereby it states that "recovering individual tables is not possible."

>

> Is that what causes it?

Nope. Single table recovery is not supported by any relational database - at least not the way that you'd like it to.

The reason for that is not the technical implementation of data storage, but simple the way how relational databases have their data distributed over all the tables.

Due to normalization you usually have a huge number of dependencies between the rows of multiple tables.

Joined together these rows form your application information.

If you would restore just one table, the dependencies between this table and all the other tables would be broken.

The database management software cannot repair it so you would have to manually ensure that the restored table still fits to the rest of the database.

THAT is the reason why you always restore databases and not tables.

If you're thinking now: Wait, what about things like Oracles table flashback/flashback query then?

Well, these features do produce the mentioned inconsistencies as well, if applied to only one of the tables.

And they are actually no recovery tools.

Hope that clarifies your question.

Always try to get your database back to a consistent state!

regards,

Lars

[Former Member]

as mentioned by Lars above,

restore/recover after crash make no sense, just start DB

if you still want to do restore/recovery without loosing data, just run log backup

this way you will have ALL infromation from log area saved in log backups therefore you will

be able to resotre and recover from you backups

[Former Member]

Hi Ivan,

Thanks! I understood now.

Thanks alot for your time and patience!!

[Former Member]

Hello ,

try whole scenario again but do "recovery with initialization"

if log area still contains changes you did (like deleting entries from the table) and you do restore/recovery

theese changes are alo applied, thatis why data are missing

OR

do point in time recovery

Regards

Ivan

[Former Member]

Hi Ivan,

IT WORKS!!!

But to my understanding after some reading, "recovery with initialization" also means it deletes all data from the log volumes, which also means that I can't restore the database to the latest state but to the state of the complete backup.

The reason why I'm doing these testing is to find out how I can recover / restore my database to the state when it crashed.

Currently, my database crashes often and the recovery can only recover the data a day before the crash.

This means that I lost 1 day of data.

Any idea why this happens?

[Former Member]

Well,

when database crash, you can just start it , in most cases you will be able to bring db to the

same moment when it crashed , loosing only transactions which were running during crash or which were

not finished before last savepoint

-> you can also bring DB to admin mode after crash , then run log backup

so now you can do recovery with initialization to any point in time between your data backup and crash time

Ivan

Edited by: Ivan Levrinc on Jan 5, 2010 10:25 AM

[lbreddemann]

> But to my understanding after some reading, "recovery with initialization" also means it deletes all data from the log volumes, which also means that I can't restore the database to the latest state but to the state of the complete backup.

Sorry, but you're example is flawed.

In your example you willingly deleted data so this action is of course logged and will be redone at recovery time.

If you want to recover because the database crashed, than the crash is obviously not logged and a complete recovery will recover the database exactly up to the point of the crash.

> The reason why I'm doing these testing is to find out how I can recover / restore my database to the state when it crashed.

And why would a restore be necessary for that?

The automatic crash recovery at instance startup already takes care of the broken transactions.

No need to restore.

> Currently, my database crashes often and the recovery can only recover the data a day before the crash.

> This means that I lost 1 day of data.

> Any idea why this happens?

Could it be that you've disabled the logwriter?

In that case no automatic savepoints are written any longer.

regards,

Lars

[Former Member]

Hi Ivan,

Can you elaborate more on this part?

"-> you can also bring DB to admin mode after crash , then run log backup

so now you can do recovery with initialization to any point in time between your data backup and crash time"

Thanks in-advance!

[Former Member]

Hi Lars,

It's always GREAT to have you around... what you just explained makes PERFECT sense to me.

I will check on the logwriter function later.

[Former Member]

Hi Lars,

Can we actually disable the log writer as you mentioned?

From my understanding after reading several online articles, the log file will be created to records down all the actions or changes taken in the database, whether you like it or not, am I right?

I can only find the option to configure the "Automatic Log Backup".

[lbreddemann]

> Can we actually disable the log writer as you mentioned?

Yes, that's possible.

> From my understanding after reading several online articles, the log file will be created to records down all the actions or changes taken in the database, whether you like it or not, am I right?

That's correct. And it's good that it is like that.

This is one of the reasons why you would use a transactional RDBMS in the first place.

Otherwise you may as well stick to text files...

> I can only find the option to configure the "Automatic Log Backup".

Disabling the logwriter is a very special feature that should be used only when you know exactly what the effects are.

If you just don't want to prevent LOGFULL situations, either take log backups or switch to logmode OVERWRITE.

Usually your application performance won't suffer from the logwriter activity. If it does you've some bigger fish to fry!

Anyhow, since you will likely come across the commands for disabling the log writer, I better point you to my [blog|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/15412] [original link is broken] [original link is broken] [original link is broken]; in which I explain the side effects. So you at least can decide yourself whether you really need to switch off the logwriter.

If you do it - don't come crying "where's my data?" afterwards

regards,

Lars