Recover deleted rows

[Thomas_cim]

Hi there,

short question, short answear (I hope).

We've got a table in which many rows have been deleted with 'delete from'.
The table hasn't been modified since then, so I assume the deleted rows haven't been really deleted, and are physically there although they can't be selected anymore because the DMBS marked them as deleted.
(The table is BTree I think)

Any ways to get them back ?
-> No, no update, no unload, we're loving the risk :-)

Thomas

[fba]

Are the database and the table journaled and do you still have the journal files?
If that is the case, then you could use the auditdb command to get the deleted records.

[Thomas_cim]

Nice hint, but we're akting there without safety net and double bottom - so, no journal files.

The only solution that I can imagine is something like 'change some bits in the table file, and so the dbms thinks this rows haven't been deleted' ...

But I think it this possible, it's maybe not worth the work...

[rhann]

Any ways to get them back ?

I assume the DELETE has been committed and I believe you've mentioned in the past that this database is not journalled, so sadly those rows are gone for good.

There is virtually no cost to journaling. Your customer really has no good reason not to do it.

[Thomas_cim]

@rhann:
In this case the failure happend 6 days ago, and the customer even has no backups (ckpdb or unload).
And believe me, I've got no idea why they don't have backups...

I've told my colleague that I see no chance of recovering this rows, but he keeps on saying 'Ive read that a delete in a Btree doesn't really delete the rows, so they must be there !', and so I asked here for the secret Voodoo to bring them back :-)

[Thomas_cim]

I've just checked it, the table had about 4 million rows before the delete, and now it has about 20 thousand rows.
BUT, the Table-Datafile is still 46 GB in Size. So, yep, it seems that these rows are still there.

[fba]

If the data would still be there, you would have to change the fmap pages of the table.

[fba]

Of course, you won't probably be able to recover everything as pages have probably been overwritten since the delete. You'd also have to work around non-data pages. All this is not possible. Maybe Ingres support has tools for this, so if you have a support contract I would suggest to contact them.
I agree with Roy that your customer should consider to enable journaling. Of course they will need some -depending on the number of changes between checkpoints- disk space for this.
If the data is important for the customer then at least they should have a backup and restore strategy/policy.

[rhann]

So, yep, it seems that these rows are still there.

The space is still allocated from the file system but the space will be a mix of freed pages containing deleted rows, freed pages that once were leaf pages for the deleted rows, possibly some freed index pages, plus some pages that have never been used. What is more, the freed data pages will (in general) contain a mix of rows that were deleted recently and rows that were deleted months ago, with no way to know which is which.

Even if the customer is reckless enough to be willing to hack some pages to get stuff back they'd have no idea what they were gettting back.

You clearly understand this customer is borderline crazy so all I can offer is my sympathy and a suggestion that you should be very firm with them for their own good.

Undeleting these rows is complete non-starter.

[kschendel]

When you say "the table hasn't been modified since", do you mean modify as in the modify statement, or are you saying that no changes of any sort have been made? Getting the deleted rows back isn't happening, I'm afraid, but if the table was updated (inserted into) then new rows have likely overwritten at least some of the old ones.

If the table hasn't been updated in any way since the delete, then in theory the data rows are still there, although for the 2K page type I have some recollection that the line table entry (or the start of the row, I forget which) might sometimes get partially overwritten with a delete flag. In practice, though, the rows are gone; there's no internal utility of any sort (that I know of) that revives rows. So you would be talking about a new custom utility that would have to be written by a developer familiar with the details of DMF, and which may or may not have any chance of working, and which would (assuming it's possible) undelete everything, desired or not.

If it's worth enough $$$$ to the customer then you can beg Tech Support to be allowed to throw money at it, but I rather think it's hopeless.