ASA9 database has row at an invalid offset

[Former Member]

The problem

We have an old ASA9 database that has been corrupted, but from which we need to salvage as much as possible. Following the manual at Rebuilding a version 9 or earlier database with the Unload utility (dbunload), we get:

---

SQL Anywhere Unload Utility Version 17.0.4.2053 Connecting and initializing ***** SQL error: Internal database error *** ERROR *** Assertion failed: 201418 (9.0.2.3961) Row (0x38c:0x7) has an invalid offset -- transaction rolled back

---

Unfortunately, we have only this .db file (there do not appear to be any backups or transaction log files).

My questions

1. Are there any tools that can fix such database corruption?
2. If not, is the internal storage format documented anywhere such that we might be able to identify the problem and fix manually?
3. Failing both of those, what other solutions might exist? Do SAP or third party solution providers offer a(n affordable) data recovery service?

[Breck_Carter]

Check out Stellar Phoenix Repair for SQL Anywhere.

Also check out this "first glance" blog post ( thanks for remembering, Vlad! 🙂

[Vlad]

Always welcome. Thank you for your blog posts.

[Former Member]

Thank you for this suggestion, which initially seemed to be extremely promising. Sadly, after scanning the database it reports that there are 0 tables/views/indexes/triggers within it (which is evidently nonsense as I can connect to SQL Anywhere and query dozens of tables without problem); I've called Stellar for support, but they say there's nothing more they can do.

[Breck_Carter]

If you can start the database with dbsrv9.exe, and connect to it with dbisql, you may be able to execute individual UNLOAD TABLE statements to extract the data from all but the one failing table.

"%ASANY9%\\win32\\dbspawn.exe"^
  -f^
  "%ASANY9%\\win32\\dbsrv9.exe"^
  -c 100M^
  -o dbeng9_log_ddd9.txt^
  -os 10M^
  -x none^
  -zl^
  -zp^
  -zt^
  ddd9.db

"%ASANY9%\\win32\\dbisql.exe"^
  -c "ENG=ddd9;DBN=ddd9;UID=dba;PWD=sql;CON=ddd9-1"

UNLOAD TABLE t TO 'C:\\\\TEMP\\\\t.dta';

If you care about the failing table ( maybe you don't :), you may be able unload some of it using trial and error.

UNLOAD SELECT * FROM t WHERE pkey < 10000 TO 'C:\\\\TEMP\\\\t.dta';

[Vlad]

Breck, I forgot the software that you tested. Do you remember there was an application that you ran against your Foxhound database and it was able to read your tables? What was the name, I am too lazy to google today 🙂

p.s. my browser has corrected your name 😞

[Former Member]

Aye, I had feared this might be the only option (and indeed it looks like it might). Unfortunately it's a large database with quite a lot of tables... I'll give it a go and see what I can get. Thanks!

[MarkCulp]

Without a backup or transaction log it will be difficult to recover all of your data.

Answers to your specific questions:

1. SAP does not provide any tools that will 'fix' a corrupted database. There are tools that can be used to attempt to extract the data from the corrupted database. The primary tool is to use dbunload to unload the table data. In the case of a corrupted database you may need to do one or more 'unordered' (dbunload -u) unloads to get as much out of the table as possible.
2. The database file format is not publicly documented. IIRC the license agreement that you sign when you download and install the software explicitly states that you are not allowed to reverse engineer the software (i.e. standard IP protection rules).
3. If you have support contract then Tech Support may be able to help you recover your data.

HTH

[Former Member]

Alas, the problem as described in the question arose when using dbunload (and indeed with the -u flag). I wonder how the Stellar product suggested by Breck was built without an understanding of the underlying format (perhaps they got access to such information through a separate licence agreement). And unfortunately, we do not have a support contract...

[MarkCulp]

Yes, this is expected when you have corruption in a table. The intent is that you would attempt to unload the table that has corruption (using dbunload or the UNLOAD SQL statement) and that would give you at least some of the table contents. There is no easy way of recovering the row(s) on the page that has the corruption.

If the index is not corrupted, you can also try to craft a SQL statement that uses the UNLOAD statement ordered by the primary key (or some other index) in ASC and then DESC order. This will give you all - or most - of the rows from the table before and after the corruption (assuming there is only one page in the database that is corrupted). If there are more than one page corrupted then restricting the index range may allow you to extract more rows fro the table.

HTH