转:Salvaging Data When There are Corrupt Pages

Salvaging Data When There are Corrupt Pages in the Database This document provides steps you can use to attempt to salvage data if your database has corrupt pages. This document applies to SQL Anywhere Studio versions 5.5.03 through 9.0.2.

When using Adaptive Server Anywhere a correctable type of database corruption reported to Technical Support is:

• version 5.5.0x: Assertion Error 50213 or 50260 "Page number on page does not match page requested."
• versions 6, 7, and 8: Assertion Error 200601 "Page for requested record not a table page or record not present on page"
• version 9: Assertion Error 201501 "Page for requested record not a table page or record not present on page."

This type of database corruption is the result of a problem encountered when the database server attempts to search the linked list of the database for the specific table or record and the table or record is not present on the page found in the linked list. This can also indicate a problem with the database linked list, or pages missing from the database linked list.

The ideal situation for recovery from any type of database corruption is to use a tested backup and recovery strategy. A tested backup and recovery strategy ensures that there is no loss of data from database corruption.

For more information on backup and recovery, see - Chapter 12.

This type of database corruption can be infrequent as it only causes a database assertion if the pages in the linked list are accessed during runtime or when the database is being validated. For this reason, this type of database corruption can go undetected for a long period of time. Depending on the age of the database and the version used to create the database, the corruption may also exist in the backup copies of the database. This is why it is important to have a tested backup and recovery strategy that includes a proper validation of the backup. A proper validation of the backup must be done with the validation running on the database in read-only mode, or preferably against a copy of the backup. The actual backup of the database cannot be altered because then it is no longer a proper backup.

If there is no tested backup and recovery strategy, or if it is discovered that the backups are corrupt, there is a method that may allow you to salvage data from the corrupt pages. Depending on the number of pages corrupted, this type of corruption can be isolated to a single table, or spread over spanning multiple tables. With data salvage, there is never a guarantee as to the amount of data that can be salvaged or the integrity of the data that is salvaged.

The procedure to salvage data from a corrupt database uses the Unload utility (dbunload). For more information about the Unload utility, see - Page 533.

Data salvage working around the corrupt tables

The Unload utility provides the ability to automatically rebuild a database using either the -ar, -an, or -ac options. Automatically rebuilding the database, although helpful in most situations, is not appropriate for data salvage. The following procedure below outlines steps to manually rebuild the database. There may be data loss, and potentially, data integrity problems during the manual rebuild. Any data integrity problems need to be resolved before this process can be completed.

To start, create a \working directory on the computer that will be used to store the database file and transaction log. Move a copy of the database file and transaction log into the \working directory. Next create an \unload directory on the computer that will be used to store the .dat files create by the Unload utility. This demonstration assumes the \working and \unload directories are created on the root of the computer, although this configuration is not required. This example uses a database named asa.db.

To unload the database

1. Execute the following command from the \working directory:

   dbunload -c "UID=DBA;PWD=SQL;DBF=asa.db" c:\unload

   You will see output similar to the following:

   > Adaptive Server Anywhere Unload Utility Version 9.0.2.3137
   Unloading "DBA"."sales_order" into c:\unload\410.dat (relative to server)
   Unloading "DBA"."sales_order_items" into c:\unload\411.dat (relative to server)
   ***** SQL error: Internal database error *** ERROR *** Assertion failed: 201501
   (9.0.2.3137)
   Page for requested record not a table page or record not present on page

   This message indicates that there is corruption in the sales_order_items table.

2. Execute the following command to unload the data, avoiding the corrupt table

   dbunload -c "uid=DBA;pwd=SQL;dbf=asa.db" -u c:\unload -e "DBA"."sales_order_items"

   Output similar to the following appears:

   Adaptive Server Anywhere Unload Utility Version 9.0.2.3137
   Unloading "DBA"."sales_order" into c:\unload\410.dat (relative to server)
   Unloading "DBA"."contact" into c:\unload\412.dat (relative to server)
   Unloading "DBA"."customer" into c:\unload\413.dat (relative to server)
   Unloading "DBA"."fin_code" into c:\unload\414.dat (relative to server)
   Unloading "DBA"."fin_data" into c:\unload\415.dat (relative to server)
   Unloading "DBA"."product" into c:\unload\416.dat (relative to server)
   Unloading "DBA"."department" into c:\unload\417.dat (relative to server)
   Unloading "DBA"."employee" into c:\unload\418.dat (relative to server)
   

   Note: If another assertion failure occurs, then there are multiple tables that have corruption in the database. Modify the -e option on the dbunload command l ine to include all tables that have corruption. For example, if both the sales_order_items and contact tables have corruption, then the command line will look as follows:

   dbunload -c "UID=DBA;PWD=SQL;DBF=asa.db" -u c:\unload -e "DBA"."sales_order_items", "DBA"."contact"

   The \unload directory now contains a series of .dat files that contain all the records stored in the tables that were unloaded by the Unload utility. There should be a .dat file for every table, except the corrupt tables, that existed in the database. There is also a reload.sql file in the \working directory that allows the tables to be loaded into a new database.

3. To get a complete definition of the database schema, execute the following command:

   dbunload -c "UID=DBA;PWD=SQL;DBF=asa.db" -n -r reload_database.sql

4. Using a text editor, open reload.sql and reload_database.sql and search both files for RELOAD DATA. Copy the entire RELOAD DATA section from reload.sql into the same section in reload_database.sql. For example:

   -------------------------------------------------
   -- Reload data
   -------------------------------------------------

   LOAD TABLE "DBA"."sales_order" ("id", "cust_id", "order_date", "fin_code_id", "region", "sales_rep" )
   

   FROM 'c:\\unload\\410.dat'
   FORMAT 'ASCII' QUOTES ON
   ORDER ON ESCAPES ON
   CHECK CONSTRAINTS OFF COMPUTES OFF
   STRIP OFF DELIMITED BY ','
   
   go
   commit work
   
   go

   Depending on the version of SQL Anywhere Studio, the syntax for the LOAD TABLE command may vary. There should be a LOAD TABLE statement for each table in the database, except the tables excluded because of corruption. Add a LOAD TABLE statement for each table that was excluded because corruption. In the example above the sales_order_items table had corruption.

5. Add the LOAD TABLE statement for the sales_order_items table using the following syntax. Ensure that the LOAD TABLE statement includes the correct syntax for all the columns of the table definition.

   LOAD TABLE "DBA"."sales_order_items" ("id", "line_id", "prod_id", "quantity", "ship_date")

   •  
     STRIP OFF DELIMITED BY ','
     FROM 'c:\\unload\\sales_order_items.dat'
     FORMAT 'ASCII' QUOTES ON
     ORDER ON ESCAPES ON
     CHECK CONSTRAINTS OFF COMPUTES OFF
     

6. Save the reload_database.sql file.

Data salvage from the corrupt tables

To salvage data from the corrupt table or tables, you must retrieve as many rows as possible around the corruption. To select around the corruption, use the primary key to order the data. If no primary key exists on the table, then use an index. If a primary key and index (or multiple indexes) exist on the table, use both as they each have some uniqueness that may result in one having a smaller amount of data loss.

To salvage data from the corrupt tables

1. Start the database using the personal database server and connect to the database using Interactive SQL by executing the following commands: dbeng9 asa.db

   dbisql -c "UID=DBA;PWD=SQL;ENG=ASA"

2. Execute the following statement from Interactive SQL:

   SELECT * FROM "DBA"."sales_order_items"
   ORDER BY id ASC
   > > # c:\unload\sales_order_items.dat
   

   This outputs data from the sales_order_items table starting at the first primary key entry until the database asserts.

3. Restart the database, and then connect to Interactive SQL by executing the following commands: dbeng9 asa.db

   dbisql -c "UID=DBA;PWD=SQL;ENG=ASA"

4. Execute the following statement from Interactive SQL:

   SELECT * FROM "DBA"."sales_order_items"
   ORDER BY id DESC
   > ># c:\unload\sales_order_items.dat
   

   This outputs as much data as possible from the sales_order_items table, starting at the last primary key entry until the database asserts. The > ># syntax appends any new rows generated by the SELECT statement to the sales_order_items.dat file. To verify how much data is missing from the sales_order_items table, open the sales_order_items.dat file in a text editor. There may be some data loss when attempting to salvage data from the corrupt table. If there is significant data loss, then try and determine a range of the primary keys where excessive data loss exists. When a range has been determined attempt to salvage more data from the table based on the range of primary keys in the SELECT statement.

5. Restart the database and connect to Interactive SQL using the following commands: dbeng9 asa.db

   dbisql -c "UID=DBA;PWD=sql;ENG=ASA"

6. Execute the following command in Interactive SQL:

   SELECT * FROM "DBA"."sales_order_items"
   WHERE id BETWEEN 2500 AND 3000
   ORDER BY id ASC
   > ># c:\unload\sales_order_items.dat
   

   To salvage the greatest amount of data from the corrupt table, change the range of the primary keys in the BETWEEN clause until the database asserts. This process can be long and tedious if the corruption is widely spread throughout the table. Commonly, corruption in a table is grouped in ranges based on the primary key, but this is not always the case. If there are multiple indexes on the table, use the different indexes for the WHERE and ORDER BY searching conditions. Dropping the indexes and primary key on the table may also produce more data using a SELECT statement. The more data that can be salvaged from the corrupt table, the better the integrity of the data that will exist when the new database is created.

   Note: The optimizer will still make the decision as to the best plan to attempt to execute the given SELECT statement. A side effect of the optimization plan is that the query can fail at the same given point. If this is the case, then you can attempt to supply the optimizer with index hints for the specified index, but there is no guarantee the optimizer will use the index.

   For information about index hints, see - Page 445.

Creating a New Database from the Salvaged Data

After salvaging as much data from the database as possible, create a new database with the same initialization parameters as the existing database. To create the new database, use the Initialization utility. Most initialization parameters can be determined by using The Information utility (dbinfo). Note that in some cases you may not be able to run dbinfo on a corrupt database.

Note: The Information utility only returns information about Java in the database for Adaptive Server Anywhere 8.0.0 or higher. If you suspect you are using Java in the database with Adaptive Server Anywhere 7 or earlier, use the process outlined in .

For more information about the Initialization utility, see - Page 485

For more information about the Information utility, see - Page 483

To create a new database

1. At a command prompt, execute the following command:

   dbinit asa.db

2. Start the new database by executing the following command:

   dbeng9 -c 50p asa.db

3. Connect to the database from Interactive SQL:

   dbisql -c "UID=DBA;PWD=sql;ENG=ASA"

4. Execute the following statement from Interactive SQL:

   READ C:\working\reload_database.sql

   This reads the database schema and table contents into the newly-created database from the reload_database.sql file. There could be data integrity problems when loading the table contents into the new database. The most likely data integrity problem that will be encountered is the result of primary/foreign key relationships. If foreign keys exist with no primary keys, then the foreign key relationship cannot be established. You must massage the data to either reform these relationships using dummy rows to replace the primary/foreign keys or delete the foreign key row from the data set. These types of data integrity problems must be resolved to fully rebuild the database with the salvaged data.

What to do if you cannot resolve the database assertion

If the above procedure does not work to resolve the database assertion, then it is recommended that you open a Technical Support case with iAnywhere Solutions. Customers with a support plan can open a case using Case Express accessed online at .

North American customers that do not want to use Case Express or do not have a support contract with iAnywhere Solutions can contact Technical Support by calling 1-800-8Sybase (1-800-879-2273). Customers that do not have a support plan will incur a fee for opening a case with Technical Support.

For customers outside of North America, information regarding how to contact Technical Support can be found at .

iAnywhere Solutions has a data salvage service for the salvage of data from a corrupt database. This is a paid service, accessed by opening a Technical Support case. The fee for the data salvage is in addition to any Technical Support contract or case fees. The data salvage service requires a signed agreement between the company opening the Technical Support case and iAnywhere Solutions. The contract specifies that iAnywhere Solutions makes no guarantee as to the amount of data salvaged, if any, its integrity, or the time required to salvage any data during the service. This service should be used as a last resort if all other options have been exhausted. The best way to protect the data in a database is through a tested backup and recovery strategy. The data salvage service for the salvage of data from a corrupt database is not intended to be a substitute for a tested backup and recovery strategy.