Recovering from multiple transaction logs

SQL Anywhere allows transactions to span multiple transaction log files. If a database is backed up part way through a transaction, the transaction may span two transaction log files. When this occurs, the first part of the transaction is contained in the offline transaction log, while the second part of the transaction is contained in the online transaction log.

If you need to recover your database and you have multiple transaction logs, you must apply the transaction log files to the backup copy of your database in the correct order in case there are transactions that span multiple transaction logs. If the transaction logs are not applied in the correct order, then portions of transactions that span multiple transaction logs are rolled back.

You can use any of the following methods to apply transaction logs in the correct order:

  • Use the -a server option to apply each log individually to the backup copy of the database. You can use the Transaction Log utility (dblog) to determine the order in which transaction log files were generated. The utility generates and displays the earliest log offset in the transaction log, which can be an effective method for determining the order in which to apply multiple log files.

    See -a database option.

  • Use the -ad server option when starting the database to specify the location of the transaction log files. The database server determines the correct order for applying the transaction logs to the backup copy of the database based on the log offsets.

    See -ad database option.

  • Use the -ar server option when to have the database server apply log files associated with the database that are located in the same directory as the transaction log. The transaction log location is obtained from the database. The database server determines the correct order for applying the transaction logs to the backup copy of the database based on the log offsets.

    See -ar database option.

  • Use the Log Translation utility (dbtran) to translate one or more transaction logs into a .sql file that can be applied to the backup copy of the database. See Transaction Log utility (dblog).
Recovering from multiple transaction logs using the -ad server option

The -ad server option is used to recover a database by applying all the transaction logs from a specified directory to the backup copy of a database. When this option is specified, the database server applies the log and then shuts down the database.

To recover from multiple transaction logs using the -ad server option

  • Start the database server using -ad to apply the transaction logs to the backup copy of your database. See -ad database option.

Example

The following example applies the offline (backup) and current transaction logs to the backup copy of the sample database using the -ad database server option. The database server uses the log offsets in the transaction logs to determine the correct order in which to apply the log files.

  1. Copy the backup transaction log and current transaction log into a directory, for example, c:\backuplogs.
  2. Start the database server and apply the transaction logs to a backup copy of a database called backupdemo.db: 
    dbeng11 backupdemo.db -ad c:\backuplogs

    The database server applies the transaction logs to the backup copy of the database and then shuts down.

Recovering from multiple transaction logs using the -a server option

The -a server option is used recover a database by applying a single transaction log file to the backup copy of a database. When this option is specified, the database server applies the log and then shuts down—it will not continue to run. If you have multiple transaction logs, you must apply them one at a time in the correct order, from oldest to most recent.

To recover from multiple transaction logs using the -a server option

  1. Start the database server using -a to apply the backup transaction log to the offline (backup) copy of your database.

    See -a database option.

  2. Start the database server and apply the current transaction log to the backup copy of your database.

Example

The following example applies the offline (backup) and current transaction logs to the backup copy of the sample database using the -a database server option.

  1. Start the database server and apply a backup transaction log called backupdemo.log to the backup copy of a database called backupdemo.db: 
    dbeng11 backupdemo.db -a backupdemo.log

    The database server applies the backup transaction log to the backup copy of the database and then shuts down.

  2. Start the database server and apply the current transaction log called demo.log to the backup copy of the database: 
    dbeng11 backupdemo.db -a demo.log

    The database server applies the current transaction log to the backup copy of the database and then shuts down.

Recovering from multiple transaction logs using the dbtran utility

To maintain the integrity of your data when you use dbtran to translate multiple transaction logs, you must specify both the -m and -n options. The -m option instructs the Log Translation utility to generate a file (named by -n) containing all the transactions from the logs in the specified directory.

You need to use -m because if you translate each log individually using dbtran, any transactions that span transaction log files could be rolled back. This is because when dbtran translates a log, it adds a ROLLBACK statement to the end of the log to undo any uncommitted transactions. In cases where a transaction spans two logs, the COMMIT for the transaction occurs in the second log file. Operations at the end of the first log file would be rolled back by dbtran because the file does not contain a COMMIT for the transaction. Translating all the transaction log files in a directory using -m ensures that all your transactions are translated. See Transaction Log utility (dblog).

To recover from multiple transaction logs using the dbtran utility

  1. Run the Log Translation utility (dbtran) against the directory containing the transaction log files and output the resulting SQL statements into a .sql file.

  2. Start the backup copy of your database.

  3. Apply the .sql file generated by dbtran in step 1 to the backup copy of your database from Interactive SQL.

Example

The following example uses the dbtran utility to apply the backup and current transaction logs to the backup copy of the database.

  1. Run the Log Translation utility against the c:|backup directory and output the SQL statements into a file called recoverylog.sql: 
    dbtran -m "c:\backup" -n recoverylog.sql
  2. Start the backup copy of the database called backupdemo.db: 
    dbeng11 backupdemo.db
  3. Apply the recoverylog.sql file to the database from Interactive SQL: 
    dbisql "UID=DBA;PWD=sql;END=backupdemo" READ recoverylog.sql
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0