SAP SQL Anywhere
Page tree
Skip to end of metadata
Go to start of metadata

Purpose

When using a SQL Anywhere database, unexpected operating conditions, software issues or hardware issues can have the potential to cause data corruption inside the database. This document provides an overview of how SQL Anywhere handles these potential data corruption conditions and the various options for recovery once a data corruption has been detected.

Overview

Assertions are checked conditions in the SQL Anywhere server that prevent data corruptions during operation. In SQL Anywhere 12 and lower, upon detecting an assertion the database server will immediately stop processing client requests, return with a SQL error to all clients, and will present an error message with a six-digit number and a brief description of the assertion on the database console.

In SQL Anywhere 16, the two database server switches dbsrv16 -uf and dbsrv16 -ufd control the database server behavior when an assertion occurs on an individual database.

Assertion Behavior

  • During start-up or during operation of the SAP SQL Anywhere database, there is an erroneous operating condition encountered and an error message is printed to the database console log

    *** ERROR *** Assertion failed: ###### (xx.x.x.xxxx)
    Error message follows with information about the cause of the assertion.

    The assertion number is a six-digit number, and the numbers following the assertion number in brackets will indicate the version and build of the SQL Anywhere database server that asserted. Details about the assertion are provided in a text line following the error.
     
  • A client connection that is already connected to the database or attempts to connect to the database may also see the error:

    Internal database error "Assertion failed: ###### (xx.x.x.xxxx)" -- transaction rolled back
    SQLCODE=-301, ODBC 3 State="40W01"

    No other work may be performed on the database server and all further SQL commands will result in the reported client error.
    At the time of the assertion message, the SQL Anywhere database server will also produce a diagnostic .DMP file and .CRASH_LOG file in the SADIAGDIR environment variable's file path with the file name prefix
    SA1X_YYYYMMDD_HHmmss_pid

    Where 'SA1X' is the major version of SQL Anywhere, 'YYYYMMDD' is the date on which the assertion occurred, the 'HHmmss' is the time of day the assertion occurred and 'pid' is the Process ID of the crashing process.

    Running the Support Utility (dbsupport) with 'dbsupport -lc' may list this same entry as an outstanding crash entry to be submitted to SAP Support.

Assertion Behaviour for specific databases in SQL Anywhere 16

SQL Anywhere 16 now allows database administrators finer control of the database server behaviour when an assertion occurs. Starting in version 16, not all assertions are treated as server-level fatal error causing the entire server to shutdown. A new database server option "-ufd { abort | restart | escalate }" controls now how the database server reacts when it encounters one of these 'database-level' assertions on a specific database:

    • 102801 - "Unable to find table definition when creating/altering a column"
    • 102802 - "Unable to undo index changes resulting from a failed column alteration"
    • 102805 - "Unable to find table definition for a table to be dropped"
    • 102806 - "Unable to delete row from SYSTABLE"
    • 102809 - "Unable to add to SYSIXCOL when creating an index"
    • 102811 - "Unable to add to SYSIDXCOL when creating a foreign key"
    • 102813 - "Unable to find primary table definition when dropping a foreign key"
    • 200130 - "Invalid page found in index"
    • 200100 - "Unable to open dbspace when allocating index page"
    • 200106 - "attempting to add row twice"
    • 200112 - "Failed to undo index delete"
    • 200115 - "Expecting page read to be an index page (table %d, page 0x%x, file %d)"
    • 201500 - "Page pointer for requested record id is NULL"
    • 201501 - "Page 0x%x:0x%x for requested record not a table page"
    • 201503 - "Record 0x%x not present on page 0x%x:0x%x"
    • 201120 - "Attempt to write an invalid definition page"
    • 201129 - "File is shorter than expected"
    • 201130 - "File is shorter than expected"
    • 201133 - "Attempt to free page %x in file %d beyond end of dbspace %x"
    • 201135 - "page freed twice"
    • 201140 - "page freed out of order"
    • 201138 - "Attempt to allocate page %x in file %d beyond end of dbspace %x"
    • 201139 - "page not free"

The -ufd switch has the following options available:

    • '-ufd abort' (the default) server will report an assertion in the console log, stop the faulting database but remain running
    • '-ufd escalate', will cause server to escalate assertion and cause the whole server to shut down  (similar to SQL Anywhere 12 and earlier behaviour)
    • '-ufd restart' will try to make an attempt to restart the database which reported the original assertion 

Note that this behaviour was added to give administrators a chance to fix/restore a database without an impact to other running databases. When recovering an asserting database, the database will  have to undergo the automatic recovery process in order to become operational again.

What to do when you receive an assertion failed message:

  1. Record the assertion number and message that appear in the database server messages window or log file. The assertion number and message are important in attempting to determine the cause of the assertion.
  2. If a pre-SQL Anywhere 16 database server has asserted, shut down the database server, if it is still running. This is very important if multiple databases are running on a single database server. This action protects the other databases running on the database server from potential corruption.
  3. Make a backup copy of the database file and transaction log. You should also make a backup copy of any old transaction logs that have yet to be deleted. The backup copies of these files should be completed with a straight file-to-file copy (do not use dbbackup or another backup procedure). Old backup copies of the database file and transaction logs should not be overwritten. It is important to maintain the database file and transaction log in the state immediately after the assertion failure for both recovery and root cause analysis purposes.
  4. Attempt to restart the database server with the database file, as usual.
  5. If the database starts successfully, validate the database using the Validation utility (dbvalid) or the sa_validate system procedure or the VALIDATE DATABASE statement.
    1. If the validation reports errors or another assertion failure occurs, then it is possible that the database file is corrupt. In this scenario, the best course of action is to resort to use your database recovery process of your tested backup and recovery strategy (see below). Retain copies of the corrupted database files if you wish to submit the files to SAP Support for a later root-cause analysis of possible sources for the assertion.
    2. If the validation completes successfully and reports no errors, then the database can be put back into production. If subsequent assertion failures occur, revert to your tested backup and recovery strategy. If you are still experiencing problems after reverting to your tested backup and recovery strategy, contact SAP Support.
  6. Even if the validation process does not return errors after validating a database, once an assertion condition has been hit, there is still potential for corruption of the database file during runtime. The only way to guarantee removal of all pre-existing corruptions in the database file is to rebuild the database using the unload/reload process. The ability to restart the database file on a database server alone does not guarantee that the database file is free from corruption. See: The rebuild process for SQL Anywhere 10 and later databases

If the database does not start correctly in step 5, use your database recovery strategy to recover from your most recent and viable database backup.

If there is no viable database backup available, then forcing the database to start without the transaction log is an option only if the database is not involved in replication or synchronization (e.g. is being used with MobiLink, SQL Remote, or Replication Server). To have the database start with a new transaction log, shut down the already running database, rename the old transaction log, and then restart the database using the command:

  dbengX -f database.db

where 'X' is 50, 6, 7, 8, 9, 10, 11, 12, or 16, depending on the major version of SQL Anywhere being used.

Normally, when the database server goes through automatic recovery it rolls the database file back to the previous checkpoint and then applies all transactions in the transaction log after that checkpoint. Using dbengX -f allows recovery to the last checkpoint without applying the transactions in the transaction log. This is only true if the transaction log is not present in the location where the database file expects the transaction log to be located. If the transaction log is present, then the database server attempts to apply the transactions in the log since the last checkpoint, regardless of whether -f was used on the database server command line. If you suspect that the transaction log is corrupt, then the transaction log must be renamed before using dbengX -f to ensure that the original transaction log does not get used.

Can databases be protected against assertion failures?

The best database protection against an assertion failure is a tested backup and recovery strategy that accounts for operating system crashes, disk failure, file corruption, and total machine failure. A tested backup and recovery strategy allows for minimum downtime in the event of an assertion failure.

If you are using SQL Anywhere 16, you can configure the server behavior upon receiving an assertion via the -uf and -ufd database server options. 

Recovering from a valid backup of the database and transaction logs

Recovering the database from a valid backup of the database file and transaction log is the best method for recovering from a database assertion or corruption. If there are no transaction logs missing in the sequence from the last valid backup to the time of the assertion failure, then there will be no data loss as a result of the assertion. The following steps should be taken as part of your backup recovery strategy.

Validate the database backup

The database backup should be validated prior to the database failure, in order to verify the integrity of the backup.

Note: Always use a second copy of the backup or run the database server in read-only mode to test the validity of the database. Backups of the database and transaction log must not be changed in any way to be restored sucessfully. If there were no transactions in progress during the backup, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG RECOVER (dbbackup -k recover) or WITH CHECKPOINT LOG NO COPY (dbbackup -k nocopy) options for the image backup, you can check the validity of the backup database by either directly using read-only mode or by validating a copy of the backup database.

However, if transactions were in progress, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG COPY (dbbackup -k copy), the database server must perform recovery on the database when you start it. Recovery modifies the backup copy, which is not desirable. In these cases, you must validate a writable second copy of the backup copy and subsequently delete the second copy when it is finished validating.

You can ensure that no transactions are in progress when the backup is being made with the BACKUP DATABASE WAIT BEFORE START clause (dbbackup -wb) so that the database server does not need to perform recovery steps during validation. In this case, you can also perform a validity check on the backup directly using the read-only database option.

In addition to directly validating the backup copy of the database, to guarantee that the backup database is not corrupt, you can run the Unload utility (dbunload) against the backup database copy in order to ensure the backup copy data unloads successfully to create a new database from the backup copy (known as a database rebuild). If the database backup copy rebuilds without any errors on the reload, then you know the backup copy is valid.

	dbunload -c "UID=username;PWD=password;DBF=backup-database.db;ENG=rebuild_engine;DBS=-r" -an validate-backup-unload.db

For more information about rebuilding a database, see the SQL Anywhere documentation:

Restore the database backup

 Now that the backup of the database has been validated, restore a copy of the backup. You apply all outstanding transaction logs to this restored copy of the database. Applying the transaction logs executes all transactions run against the database up to the time of the assertion failure. If the transaction log has not been truncated since the last backup, you only need to apply the single transaction log from the online database that has asserted. This can be done as follows: 

	dbengX restored-backup-database.db -a asserted-database.log

where X is 50, 6, 7, 8, 9, 10, 11, 12, or 16, depending upon the major version of SQL Anywhere used.

If the transaction logs have been renamed and truncated, then use this same command to apply each of the transaction log pieces, in chronological sequence. The first transaction log to be applied is the transaction log that was backed up with the database file. To avoid any naming conflicts, all the transaction logs to be applied should be stored in a separate directory from the database file. When all the transaction logs have been applied, restart the database. A new transaction log is created for the database, which becomes the current transaction log.


	dbengX c:\restore\restored-backup-database.db -a c:\backup\full\backup-database.log
	dbengX c:\restore\restored-backup-database.db -a c:\backup\incremental\140101AA.log
	dbengX c:\restore\restored-backup-database.db -a c:\backup\incremental\140101AB.log
	dbengX c:\restore\restored-backup-database.db -a c:\backup\incremental\140101AC.log
	dbengX c:\restore\restored-backup-database.db -a ...
	dbengX c:\restore\restored-backup-database.db -a c:\database\asserted-database.log

For SQL Anywhere version 10 and later, you can use the -ad database option instead of the -a database option. This option specifies a directory containing all of the transaction log files to be applied to the database. It also automatically determines the order of the transaction logs to be applied.

dbengX c:\restore\restored-backup-database.db -ad c:\restore\logs\

This process will not break MobiLink synchronization or SQL Remote replication, but all of the applied transaction logs must be kept in an offline transaction logs folder in case there is an offset in one of these transaction logs that is required for synchronization or replication. If this is not a synchronization or replication environment present, the applied transaction logs do not need to be retained.

Warning: When importing data to the database using the LOAD TABLE SQL statement, by default when data is loaded from an external data file (i.e. LOAD TABLE table-name FROM filename;), only the  LOAD TABLE statement is recorded in the transaction log, not the actual rows of data that are being loaded. This presents a problem when trying to recover the database using the transaction log if the original data file has been changed, moved, or deleted. It also means that databases involved in synchronization or replication do not get the new data.

To address the recovery and synchronization considerations, two logging options are available for the LOAD TABLE statement: WITH ROW LOGGING, which creates INSERT statements in the transaction log for every row that is loaded, and WITH CONTENT LOGGING, which groups the loaded rows into chunks and records the chunks in the transaction log. These options allow a load operation to be repeated, even when the source of the loaded data is no longer available.

 

Recovering a database if there are no valid backups, but a full valid transaction log exists that has never been truncated since the database was created

 SQL Anywhere database architecture uses a transaction log and database file. The transaction log records all statements executed against the database. As a result, a single, all-inclusive transaction log can be used to recreate an entire database. This process will break synchronization and replication.

To recover the database with this process:

  1. Use the Database Initialization Utility (dbinit) to create a new database with the same initialization parameters as the existing database. You can obtain most of the initialization parameters by using the Information utility (dbinfo). Note that in some cases you may not be able to run the Information Utility on a corrupt database if it will not start on a database server without asserting.
    c:\data>dbinfo -c "uid=dba;pwd=sql;dbf=database.db"
    SQL Anywhere Information Utility Version 16.0.0.1324
    Database  : c:\data\database.db
    Log file  : c:\data\database.log
    Log mirror: none
    Page size : 8192
    Encrypted : No
    Strings padded with blanks for comparisons: Yes
    CHAR collation sequence: ISO_1(CaseSensitivity=Respect)
    CHAR character set encoding: ISO_8859-1:1987
    NCHAR collation sequence: UCA(CaseSensitivity=UpperFirst;AccentSensitivity=Respect;PunctuationSensitivity=Primary)
    NCHAR character set encoding: UTF-8
    Database checksums enabled: Yes
    Encrypted tables supported: No

    c:\data>dbinit -dba dba,sql -z ISO_1 -zn UCA -a -b -c -p 8192 c:\restore\database.db
    SQL Anywhere Initialization Utility Version 16.0.0.1324
    CHAR collation sequence:  ISO_1(CaseSensitivity=Respect)
    CHAR character set encoding:  ISO_8859-1:1987
    NCHAR collation sequence:  UCA(CaseSensitivity=UpperFirst;AccentSensitivity=Respect;PunctuationSensitivity=Primary)
    NCHAR character set encoding:  UTF-8
    Database is not encrypted
    Creating system tables
    Creating system views
    Setting option values
    Database "database.db" created successfully
  2. Make a backup copy of the current transaction log in case any problems occur while attempting to translate the transaction log.
  3. Translate the transaction log using the Log Translation Utility (dbtran) to a .SQL file
    c:\data>dbtran database.log c:\restore\database.sql
  4. Start the database server with the newly created blank database. You may want to start the database server with a larger initial cache size (-c database server option) to improve performance:
    c:\data>cd c:\restore
    c:\restore>dbspawn dbsrv16 -c 75P -n reload_engine database.db
  5. Using Interactive SQL, apply the SQL script file created by the Log Translation Utility:
    dbisql -c "uid=dba;pwd=sql;eng=reload_engine;dbn=database;" database.sql

What are the considerations for replicating or synchronizing databases?

In a replication or synchronization environment, care must be taken with the transaction log and the transaction log offsets. When a database is rebuilt, the transaction log offsets are not the same as the original database, and this will affect these types of environments. For this reason, the database should not be forcefully started without the transaction log. If the database needs to be rebuilt, it should be done as follows:

dbunload -ar path-for-old-dbfiles -c "UID=DBA;PWD=sql;ENG=dbserver"

Alternatively, you can rebuild the database manually using the steps in the documentation. 

Note: This restriction does not apply to a SQL Anywhere MobiLink consolidated database because there is no dependency on the transaction log offsets for the MobiLink synchronization server. However, care should still be taken with the consolidated transaction log for recovery purposes. This restriction does apply to MobiLink remote databases. The ideal method for recovering from an assertion failure in an environment involved in replication or synchronization is using a tested backup and recovery strategy.

For more information on creating a backup and recovery strategy, see http://dcx.sybase.com/index.html#1200en/dbadmin_en12/understand-developing-backups.html.

A checklist for collecting diagnostics for a "root cause" analysis of a database assertion:

When opening a support incident for SAP Support assistance to help diagnose a possible "root cause" for an assertion, please include the following information with your incident submission:

  1. The exact assertion error message number and description (e.g. "Assertion 201501 (xx.x.x.xxxx) - Page for requested record not a table page or record not present on page"), including the
    database server version and build number.
  2. The fulll command-line (database server options) used to start the database server
  3. A copy of the database console log output (-o) file:

    dbsrv16 -o dbsrv16.txt ...
  4. A copy of the corrupted database file and transaction log that received the assertion failure message.
  5. A copy of any backups of the database previous to the assertion occurring (if available).
  6. Details about the machine (CPU, memory, disk), drivers and operating system that the database server is on
    • For Microsoft Windows, export the system information from the "System Information" tool (msinfo32) tool and attach the .NFO file to the support incident.
    • For Linux, the output from the commands:

      uname -a > uname.txt
      lscpu > lscpu.txt
      free > free.txt
      mount > mount.txt
      df -h > df.txt

  7. The details of the crash .dmp / .crash_log from the first time of when the assertion occurred. You can directly browse to these files via the SADIAGDIR directory and attach them to this message, or submit them directly to SAP automatically via the Support Utility (dbsupport):

    dbsupport -sa

    If you are automatically submitting the crash dump to SAP, be sure to record your submission number and attach the submission number to the SAP Support incident you are also opening. You still need to create a separate support incident with SAP to have the dump file analyzed and have SAP provide a response regarding the root cause
  8. All operating system event logs from the time around when the assertion occurred
    • For Microsoft Windows, go to Control Panel > Administrative Tools > Event Viewer > Windows Logs > [Every log Available] > Save All Events As... > *.evtx
    • For Linux, the syslogd output log. (i.e. /usr/var/syslog).
  9. Question: Is this database server running on a virtual machine, and did the virtual machine undergo a memory snapshot at the time of the assertion?
  10. Question: Is the database file local or remote to the server machine? Has the database server ever been used to access the database over a Windows/SMB/CIFS file share on a different computer?
  11. Question: Is the operating system configured to go to 'Hibernate' or 'Sleep', under any circumstances? Did the user forcibly put the computer in 'Sleep' or 'Hibernate' mode prior to the corruption occurring?
  12. Question: Was there an unexpected power loss to the server machine at the time of the assertion? Is there a UPS/battery backup available to the server machine?

Related Content

Related Documents

Related Product Documentation

Related SAP Notes/KBAs

  • SAP KBA 1958942 - Error: "Assertion failed: ######" / SQLCODE -301 "Internal Database Error" - Handling an Assertion Failure
  • SAP KBA 1959030 - How To Salvage Data When There are Corrupt Pages in the Database

 

 

  • No labels