SQL error: Internal database error *** ERROR *** Assertion failed: 201501 (10.0.1.3415)

[Former Member]

A SQLA10 production database has been crashing at a customer site.

Here's the error which displayed when I tried to unload the database...

Unloading "DBA"."schedule_profile" into C:\resq\resqprod2\db\unload\719.dat (relative to server)

Unloading "DBA"."schedules" into C:\resq\resqprod2\db\unload\720.dat (relative to server)

***** SQL error: Internal database error *** ERROR *** Assertion failed: 201501 (10.0.1.3415)

Page for requested record not a table page or record not present on page -- transaction rolled back

The database 'C:\resq\resqprod2\db\resqprod2.db' could not be unloaded.

The database 'C:\resq\resqprod2\db\resqprod2.db' could not be unloaded.

I think that backups may also have this error.

Can the database be salvaged?

Thank you,

Doug

[Former Member]

Does anyone have a link or two which demonstrates how to setup a system where the customer's admin will be emailed if dbvalid detects a problem with the database backup file? 

Thank you to all on this list.

Doug

[jeff_albion]

Hi Doug,

See "Maintenance Plans" in Sybase Central - these can be set up to do backup and validation, and then e-mail the results to a DBA.

Underneath the covers, this really executes a CREATE EVENT statement and creates an event which you can then go back and customize even further, if you wish.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Hello Doug,

You can use -e table1, table2,... to avoid the corrupt table(s) rolling back the process and continue salvaging.

Regards,

Jinwoo Park

SAP Active Global Support

[Former Member]

Hi Jinwoo,

If I eliminate the schedules table, most of the data in the customer's app would be eliminated.

The error seems to be saying that a pointer is pointing to an invalid page.

Does a utility exist which would eliminate all pointers to invalid pages?

Or a switch would would allow Unload to continue past the errors?

Does sap/sybase offer a recovery service?

Thank you,

Doug

[jeff_albion]

Hi Doug,

The error seems to be saying that a pointer is pointing to an invalid page.

Actually, the assertion message is suggesting that we were scanning for a row (record) on a particular page where we expected to find it, and didn't - the table page is likely corrupt.

Does a utility exist which would eliminate all pointers to invalid pages?

No. If the database is corrupted, it is best to move to your database recovery procedure from a valid backup. See KBA 1959391 - How can a SQL Anywhere (stand alone) database be restored from a full or incremental backup?

If you do not have a valid database backup but can still start and connect to the corrupted database (and it seems that you can), you can attempt to salvage the data out of the corrupted database tables manually to extract it to a new database (as Jinwoo suggested). See KBA 1959030 - How To Salvage Data When There are Corrupt Pages in the Database. Using -e to skip tables is appropriate and then trying to select the data that is not contained on invalid pages via the KBA instructions is your best option in this case.

Does sap/sybase offer a recovery service?

No, there is no such "recovery service" underneath SAP and any previous mentions of salvages were one-off contracts underneath Sybase Professional Services, and not a technical support service. These contracts are not available at SAP.

This topic was discussed thoroughly on the SQL Anywhere forum ( http://sqlanywhere-forum.sap.com/ ) previously here and here.

---

The prevention for this situation is to have properly validated backups, preferably in multiple backup generations to provide many opportunities for recovery. See: http://wiki.scn.sap.com/wiki/x/3QNcFg

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Hi Jeff,

The links supplied in your reply are not accessible to me.

I'm asked for a user name and password.

But the username and password which allows access to this forum, does not work.

Back to my issue...  Say there is no valid backup and that the assertion error was reported on the schedules table--which contains most of the application's data.

Would it be possible to recover the database and most of the data in the schedules table?

Thank you,

Doug

[jeff_albion]

Hi Doug,

To access KBAs, you need to use an SAP S-User, which is registered with a valid and active SAP support account (the same account you would use to open a technical support incident with us at http://support.sap.com/ ). This is not the same login you would use for the SCN.

For more information, see: User Data Administration - Help | SAP Support Portal

Back to my issue...  Say there is no valid backup and that the assertion error was reported on the schedules table--which contains most of the application's data.
Would it be possible to recover the database and most of the data in the schedules table?

Possibly. You need to use "dbunload -e schedules" and then see if you can select any remaining data using available keys/indexes on that table. e.g. for "SalesOrderItems" in the demo database:

   UNLOAD
     SELECT * FROM "GROUPO"."SalesOrderItems"
     ORDER BY "ID" ASC
   TO 'c:\\unload\\SalesOrderItems.dat';

   UNLOAD
     SELECT * FROM "GROUPO"."SalesOrderItems"
     ORDER BY "ID" DESC
   TO 'c:\\unload\\SalesOrderItems.dat'
    APPEND ON;

If "SalesOrderItems" was corrupted, both of these commands will create the assertion error again and you will need to restart the database each time.

The KBA linked goes into the details about the entire process to use and how to try and restore the database from this data.

If 'schedules' is the main table of the database though, I imagine you will have lots of foreign key relationships that are now broken and you will have to resolve these manually...

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Thanks Jeff.  I decided to start again from an uncorrupted month-old backup.  I then used archived HR and Time&Attendance interface files to "catch up" the database.  Just curious:  Would a utility that allowed db unload to continue thru errors, and then reported all referential integrity issues--allowing those records to be removed--would that be a useful utility, in database corruption issues?

[jeff_albion]

Hi Doug,

Would a utility that allowed db unload to continue thru errors, and then reported all referential integrity issues--allowing those records to be removed--would that be a useful utility, in database corruption issues?

From my experience, usually not - trying to identify which pieces of information should be deleted (i.e. should it be all child rows - even if other rows from other tables are also referring upon them?) is always a manual process and usually quite business-process specific. For instance, you know that "schedules" is a very important table to the application and related rows may or may not be useful, but a "database utility" just blindly dropping records in other tables related to missing "schedules" rows would not know anything about these relations. Can some of this data be saved...?

As a general practice, we always assume the customer knows more about the business purpose of the database than any of our utilities would, and treat all data (even in a corrupted state) as valuable and let the customer then figure out what is really required, or what can be lost.

--

Of course, an even better solution to salvaging anything would be to have a validated backup and restore strategy...

Cheers,

Jeff Albion

SAP Active Global Support