User's Guide Part III. Using SQL Anywhere Chapter 25. Backup and Data RecoveryA transaction log mirror is an identical copy of the transaction log, maintained at the same time as the transaction log. Transaction log mirrors are used to allow complete recovery in the case of media failure on the log device.
Every time a database change is written to the transaction log, it is also written to the transaction log mirror file. By default, SQL Anywhere does not use a mirrored transaction log, but you can choose to use one when creating a database or you can make an existing database use a mirrored transaction log.
A mirrored transaction log provides extra protection of critical data. For example, at a consolidated database in a SQL Remote setup, replication relies on the transaction log, and if the transaction log is damaged or becomes corrupt, data replication can fail.
There is a performance penalty for using a mirrored log, as each database log write operation must be carried out twice. The performance penalty depends on the nature and volume of database traffic and on the physical configuration of the database and logs.
If SQL Anywhere is using a mirrored transaction log, and gets an error while trying to write to one of them (for example, if the disk is full), the database engine or server stops. The purpose of a transaction log mirror is to ensure complete recoverability in the case of media failure on either log device; this purpose would be lost if SQL Anywhere continued with a single log.
A transaction log mirror should be kept on a separate device from the transaction log, so that if either device fails, the other copy of the log keeps the data safe for recovery.
You can only use a mirrored transaction log on a database created with SQL Anywhere Release 5.0 or later.
Transaction log mirrors can be created at the following times:
You can choose to maintain a transaction log mirror when you create a database. This option is available either from Sybase Central or from the DBINIT command-line utility.
From Sybase Central, the transaction log mirror option is part of the Create Database utility.
For more information, see the Sybase Central online Help.
The following command line (which should be entered on one line) initializes a database named company.db, with a transaction log kept on a different device and a mirror on a different device still.
dbinit -t d:\log_dir\company.log -m e:\mirr_dir\company.mlg c:\db_dir\company.db
By default, a transaction log is used but no transaction log mirror is created. For a full description of DBINIT command-line options, see "Initialization utility options".
You can choose to maintain a transaction log mirror for an existing database any time the database is not running by using the transaction log utility. This option is available from either Sybase Central or the DBLOG command-line utility.
From Sybase Central, the transaction log mirror option is part of the Change Log File utility.
For more information, see the Sybase Central online Help.
The following command line starts a transaction log mirror for a database named company.db, which is already using one transaction log.
dblog -m e:\mirr_dir\company.mlg c:\db_dir\company.db
The following command line stops the company.db database from using a transaction log mirror, but continues maintaining a transaction log:
dblog -r c:\db_dir\company.db
The following command line stops the company.db database from using a transaction log mirror or a transaction log:
dblog -n c:\db_dir\company.db
With the transaction log utility you can also alter the name or directory of the transaction log and mirror. For a full description of DBLOG command-line options, see "Transaction log utility options".
You can choose to maintain a transaction log mirror for a write file when you create the write file using the write file utility, or at a later time using the transaction log utility.
The option to create a transaction log mirror when creating a write file is available from Sybase Central or from the DBWRITE command-line utility.
From Sybase Central, the transaction log mirror option is part of the Create Write File utility.
For more information, see the Sybase Central online Help.
The following command line (which should be entered on one line) creates a write file for a database named company.db, which is already using a transaction log. The write file has default extension .WRT, the write file transaction log has the default extension .WLG, and the write file transaction log mirror has the default extension .WML.
dbwrite -c -t d:\log_dir\company.wlg -m e:\mirr_dir\company.wml c:\db_dir\company.db c:\db_dir\company.wrt
For a full description of DBWRITE command-line options, see "Write file utility options".
You can change the transaction log and log mirror settings of a write file using the transaction log utility, in exactly the same way as described above for a standard database file.
You can erase transaction log mirrors using the Erase utility in Sybase Central or the DBERASE command-line utility.
The Erase utility is available in Sybase Central as the Erase Database utility or from the command line as the DBERASE utility.
To delete a mirror log file only:
dberase e:\mirr_dir\company.wml
To delete a transaction log file but not its mirror:
dberase e:\log_dir\company.log
When a database that is using a mirror starts up, the database engine or server carries out a series of checks and automatic recovery operations to confirm that the transaction log and its mirror are not corrupted, and to correct some problems if corruption is detected.
On startup, the database engine checks that the transaction log and its mirror are identical by carrying out a full comparison of the two files; if they are identical, the database starts as usual. The comparison of log and mirror adds to database startup time when you are maintaining a log mirror.
If the database stopped because of a system failure, it is possible that some operations were written into the transaction log but not into the mirror. If the database engine finds that the transaction log and the mirror are identical up to the end of the shorter of the two files, then the remainder of the longer file is copied over into the shorter file to produce identical log and mirror. After this automatic recovery step, the database engine starts as usual.
If the check finds that the log and the mirror are different in the body of the shorter of the two, one of the two files is corrupt. In this case, the database does not start, and an error message is generated saying that the transaction log or its mirror is invalid.
When a database engine or server detects a difference between the transaction log and its mirror in the body of the file, the engine does not start. You must take the following steps before starting the engine:
When a database engine detects a difference between the transaction log and its mirror, it has no means of knowing which is intact and which is corrupt.
To identify which file is corrupt using the database utilities:
dbtran sademo.log
The translation utility properly translates the intact file, and will report an error while translating the corrupt file.