Replay logfiles on corrupt database

[Former Member]

I've got a machine here that has not been maintained very well. Someone with little knowledge of MaxDB has only kept Windows backups and no database backups. Also, there has been no LOG backups, everytime the log was full, someone just added another one :). Now one of the DATAvolumes is corrupt (it shows 0 kb) and the database won't come up. Everyone here is convinced that it won't come up ever again. Also, on top of all, the Windows backups aren't available anymore either. People feel sorry for it and just move on

So now I've got some spare time and I want to take a closer look at it. I've got a corrupt database, and I've got a lot of LOG volumes (not backups). I'm not a MaxDB guru either, so I've got the following questions:

Isn't it true that if you do not backup the database or the logfiles at all, all the database transactions committed after the installation still reside in the logfile(s)?

With this in mind, couldn't it be possible to install the system just as it was right after the installation, and after that replay the existing logfiles? I've created a copy of the system where I can fool around. I've found lots of ways to recover a database with logbackups, but not with the logfiles itself.

Isn't there anything I can do at all with the existing logfiles, with all the precious data in it? It has been a portal and there are (or were ) a lot of documents in it.

Who can help me out?

[Former Member]

Problem is not solved, but it's proven it cannot be solved. You cannot build a new database and replay old logfiles.

[roland_mallmann]

Hi Martin,

can you tell us:

- which MaxDB version is being used?

- if the data volume that has a size of 0 KB is a volume that was added lately? You might be lucky in that case, as we then might have a possibility to remove that data volume from the configuration files and retry to start the DB. Before you try that, we'd really need more information regarding which exact data volume is affected and how many data volumes there are.

On a side note: you can't really solve a log full situation by adding a log volume, only by saving the log would you get out of it.

I do hope that the responsible person learned from this and won't do it again!

Regards,

Roland

[Former Member]

Thanks Roland,

The version of the original MaxDB is 7.6.00.35

I've upgraded it to 7.6.03.09 on my 'fool around' server.

No such luck of a later-on added datavolume, it is the second datavolume that is lost. Only logvolumes are added later on. There are 5 datavolumes of 15 GB and 4 logfiles of different sizes and different locations (yes, I know )

I know that a full logfile isn't solved by adding another one, maybe he has added one before it was full. I don't know. Fact is that the 4 logfiles are alltogether about 45 GB so he must have done something to figure it out.

The person in case has certainly learned his lesson, let me tell you that. And no, it isn't me :):)

Thanks again for your answer

[lbreddemann]

Hi Martin,

with the information in the log alone you cannot rebuild the now missing datavolume.

So - as usual - without a backup of the data area as a starting point, all log information are useless.

That's of course not specific to MaxDB, but applies to all DBMS supported by SAP.

Sorry, nothing to do about it.

regards,

Lars

[Former Member]

Hi Lars,

I started with nothing, so I can't lose anything.. I already feared this answer. I understand I can't rebuild information with nothing. Only thing is that the general idea was to build it again (by installing the system again) and replay all the later on added information (by replaying the existing logfiles). I still find it strange that in fact all the data is there ( in the logfiles), but I cannot get to it. Or I'm misinterpreting the whole logfile mechanism.

[roland_mallmann]

Hi Martin,

hm, can you try to:

a) bring the db into ADMIN mode

b) issue 'dbmcli -d <SID> -u <dbm-user>,<pw> db_restartinfo'

c) show us the result

Thanks,

Roland

[Former Member]

On the original server:

dbmcli on KP1>db_admin

OK

---

dbmcli on KP1>db_restartinfo

ERR

-24988,ERR_SQL: SQL error

-9209,System error: KB Log error

---

dbmcli on KP1>

On the second server:

dbmcli on KP1>db_restartinfo

OK

Used LOG Page 16333259

First LOG Page 2147483647

Restartable 1

Id Restart Record <FQDN>:KP1_20071118_181507

Id LOG Info

Consistent 1

Log Volume Enabled 1

Log Auto Overwrite 0

Master Servernode

Converter Version 7987

Oldest Compatible Version 7.6.03.04

Queue Count 1

Of course, KP1 is my sapsid

Oh, wait, the latter is not honest. I already tried to re-initialize the DB, so this is not accurate info. I'll post more info tomorrow.

Edited by: Martin Loohuizen on Nov 26, 2008 5:33 PM

[lbreddemann]

>Only thing is that the general idea was to build it again (by installing the system again) and replay all the later on added information (by replaying the existing logfiles).

>I still find it strange that in fact all the data is there ( in the logfiles), but I cannot get to it.

>Or I'm misinterpreting the whole logfile mechanism.

Well - half and half I'd say.

It's true, all changes to the database are logged to the log area.

BUT (yes, a big BUT here), the critical point here is the starting point.

To have all changes correctly applied, the database must be sure that the database is really in the exact state like it was, when the log information starts.

This synchronization is done via the restart_record and the log_info_page.

In theory, if we would be able to generate a completely identical data area as it was, back then, when the history of log information began, then we would be able to recover the data.

Just creating a new database won't do, since the db_create itself is not covered by redologs and ends up with a different data area each time (e.g. for each database a DB-Identifier with the timestamp of its creation is generated and saved).

So, technically the only way I can think of would be to create a empty data area and than hack into the restart_record to fake the MaxDB into a recovery.

If you are a MaxDB kernel developer you might be able to do that...

regards,

Lars

[Former Member]

Now that's basically the info I was looking for. I'll stop from here, I'm not a hardcore MaxDB developer :).

Lars, thanks for the info, you're a big contribution to this forum. Your name shows up many times in a very positive way.